SQL Найти минимальное число для каждого значения в аналитической функции

Question

Я использую Microsoft SQL Server. Я хочу написать запрос только с аналитической функцией (например, не используя group by)

Я хочу написать запрос, который возвращает строки:

• MIN(Customer_number), количество (код), личное
• MIN(Customer_number), количество (код), бизнес

Я сделал две таблицы, например

в этом примере Customer_number = 1,2 должно быть только для строк следующим образом:

* 1,intermediate results counting,private
.
.
.
* 2, intermediate results counting, business
.
.
.

Я написал:

SELECT 
    MIN(subscribers.customer_number) OVER (PARTITION BY customers.customer_number, customer_type) AS cusNo,
    COUNT(subscribers.code) OVER (PARTITION BY customers.customer_number, customer_type) AS subscribes,
    customer_type
FROM
    customers 
JOIN
    subscribers ON subscribers.customer_number = customers.customer_number;

Я столько времени пытался понять, как вернуть правильный вывод, почему он не работает, и не смог его найти.

Если бы кто-то мог помочь мне с тем, как заказать его, и объяснить, что случилось, это было бы здорово.

Answer 1

Похоже, вам нужны только клиенты 1 и 2, что, как я понимаю, означает, что вы хотите, чтобы только частный клиент с наименьшим номером, а также бизнес-клиент с наименьшим номером.

Вы не хотите использовать группу по.

SELECT * FROM
  (
    SELECT
      --number each row, order by customer number, "group" by business type 
      ROW_NUMBER() OVER(PARTITION BY c.customer_type ORDER BY c.customer_number) rown, 
      --it isn't totally clear if you want the count of codes per customer or per type
      count(s.code) OVER (partition by c.customer_number) AS count_codes_by_cust,
      count(s.code) OVER (partition by c.customer_type) AS count_codes_by_type,
      customer_type 
    FROM
      customers c 
      INNER JOIN
      subscribers s
      ON s.customer_number = c.customer_number
  ) c
WHERE rown = 1 --cust #1 and cust#2 both have a rown of 1

Обратите внимание, что для ясности я оставил соединение подписчикам и подсчету кодов - критической концепцией, которой не хватало в вашей первоначальной попытке, было использование WHERE, чтобы ограничить вывод только двумя строками

Ваша первая попытка также может быть изменена для получения требования:

SELECT * FROM
(
 SELECT 
  min(c.customer_number) OVER (partition by c.customer_type) AS min_cust,
  c.customer_number,
  --it isn't totally clear if you want the count of codes per customer or per type
  count(s.code) OVER (Partition by c.customer_number) AS count_codes_by_cust,
  count(s.code) OVER (Partition by c.customer_type) AS count_codes_by_type,
  customer_type
 FROM 
  customers c 
  INNER JOIN
  subscribers s
  ON s.customer_number = c.customer_number
)d
WHERE
  min_cust = customer_number

Но недостаток этого подхода заключается в том, что он дает несколько строк, потому что объединение клиентов и подписчиков приводит к повторению номера клиента, и в результате получается более одной строки, где where min_cust = customer number верно: min(customer_number) over(...) выбрало "1" и "2" в качестве минимальных номеров клиентов, но благодаря присоединению к подписчикам 1 появляется 3 раза, а 2 - дважды

Подход row_number отличается в этом отношении = только одна строка на customer_type может иметь номер строки 1, поэтому вы получите только столько строк, сколько у вас есть различные типы клиентов. В более общем плане, если у вас есть таблица, в которой хранятся, например, несколько датированных версий документа, и вам нужна только последняя версия каждого документа, вы можете:

SELECT * FROM (
  SELECT 
    doc.*, 
    ROW_NUMBER() OVER(PARTITION BY filename ORDER BY filedate DESC) --latest is numbered 1
  FROM
    documents
)doc WHERE rown = 1

Чтобы увидеть действие более подробно, выделите и выполните только внутренние запросы, чтобы посмотреть необработанные данные, над которыми работает внешний запрос.