Microsoft SQL Server
ЗАВЕРШЕНИЕ

параметры

замечания

Использование функций агрегирования с OVER

Используя таблицу Cars , мы вычислим общую, максимальную, минимальную и среднюю сумму денег, которую каждый из них потратил и много раз (COUNT), она привезла автомобиль для ремонта.

Id CustomerId MechanicId Модель Статус Общая стоимость

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

Остерегайтесь, что использование OVER таким образом не приведет к агрегации возвращаемых строк. Вышеприведенный запрос вернет следующее:

Дублированная строка (строки) может быть не такой полезной для целей отчетности.

Если вы хотите просто агрегировать данные, вам будет лучше использовать предложение GROUP BY вместе с соответствующими агрегатными функциями. Например:

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

Суммарная сумма

Используя таблицу продаж товаров, мы постараемся выяснить, как продажи наших товаров увеличиваются через даты. Для этого мы рассчитаем совокупную сумму общих продаж по порядку заказа на дату продажи.

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

Использование функций агрегирования для поиска последних записей

Используя библиотечную базу данных , мы пытаемся найти последнюю книгу, добавленную в базу данных для каждого автора. Для этого простого примера мы принимаем всегда увеличивающийся Id для каждой добавленной записи.

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

Вместо RANK могут быть использованы две другие функции. В предыдущем примере результат будет таким же, но они дают разные результаты, когда упорядочение дает несколько строк для каждого ранга.

• RANK() : дубликаты получают одинаковый ранг, следующий ранг учитывает количество дубликатов предыдущего ранга
• DENSE_RANK() : дубликаты получают одинаковый ранг, следующий ранг всегда один выше предыдущего
• ROW_NUMBER() : даст каждой строке уникальный «ранг», «ранжирование» дубликатов случайным образом

Например, если в таблице был не уникальный столбец CreationDate, и заказ был выполнен на основе этого, следующий запрос:

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

Может привести к:

Разделение данных на равнораздельные ковши с использованием NTILE

Предположим, что у вас есть экзамены для нескольких экзаменов, и вы хотите разделить их на квартили на экзамен.

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

ntile работает, когда вам действительно нужно определенное количество ведер, и каждый заполняется примерно до одного уровня. Обратите внимание, что было бы тривиально отделить эти оценки от процентилей, просто используя ntile(100) .