Время прочтения: 3 мин.
Расчет промежуточного итога SQL — это очень распространенный шаблон, часто используемый в анализе. В этой статье вы узнаете, что такое промежуточный итог и как написать SQL-запрос для его вычисления.
Что такое промежуточный итог SQL?
В SQL промежуточный итог — это совокупная сумма предыдущих чисел в столбце. Посмотрите на пример ниже, в котором представлен список продаж продуктов в день:
Дата продажи | Количество проданных продуктов | Накопительный итог |
2021-04-10 | 10 | 10 |
2021-04-11 | 15 | 25 |
2021-04-12 | 5 | 30 |
В первом столбце отображается дата. Во втором столбце показано количество проданных продуктов в этот день. В третьем столбце [Накопительный итог] суммируется общее количество проданных продуктов в этот день.
Например, в первый день (2021-04-10) сотрудник продал 10 продуктов, и общее количество проданных продуктов составило 10. На следующий день (2021-04-11) сотрудник продал 15 продуктов; [Накопительный итог] равен 25 (10 + 15). На третий день (2021-04-12) сотрудник продал пять продуктов, и значение [Накопительный итог] равен 30. Другими словами, [Накопительный итог] — это текущее значение, которое меняется день ото дня. Это общее количество проданных продуктов за день.
Далее мы поговорим о SQL-запросе, который позволяет получить такой итог, и узнаем больше об оконных функциях.
Как вычислить совокупный итог в MSSQL
Если вы хотите вычислить промежуточный итог в MSSQL, вам необходимо знать оконные функции, предоставляемые вашей базой данных. Оконные функции работают с набором строк и возвращают агрегированное значение для каждой строки в наборе результатов.
Синтаксис оконной функции SQL, вычисляющей совокупный итог по строкам, следующий:
window_function ( column ) OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] ) |
Предложение OVER обязательно использовать в оконной функции, но аргументы в этом предложении необязательны.
Пример
В этом примере мы будем вычислять общую текущую сумму проданных продуктов каждый день.
Дата продажи | Количество проданных продуктов |
2021-04-10 | 10 |
2021-04-11 | 15 |
2021-04-12 | 5 |
Данный запрос выбирает дату продажи для всех пользователей. Нам также нужна сумма всех продуктов за каждый день, начиная с первого заданного дня (2021-04-10):
SELECT [c_date] ,[kolvo_product],
sum(kolvo_product) OVER (ORDER BY c_date)
AS total_product
FROM [product_sales]
Результат:
Дата продажи | Количество проданных продуктов | Накопительный итог |
2021-04-10 | 10 | 10 |
2021-04-11 | 15 | 25 |
2021-04-12 | 5 | 30 |
Чтобы вычислить промежуточный итог, мы используем SUM()агрегатную функцию и указываем столбец kolvo_product в качестве аргумента; мы хотим получить совокупную сумму проданных продуктов из этого столбца.
Следующим шагом будет использование предложения OVER. В нашем примере это условие имеет один аргумент: ORDER BY c_date. Строки результирующего набора сортируются в соответствии с этим столбцом ( c_date).
Для каждого значения в столбце c_date вычисляется общая сумма значений предыдущего столбца (т. е. сумма проданных продуктов до даты в текущей строке) и к ней добавляется текущее значение (т. е. продукты, проданные в день текущей строки). Общая сумма отображается в новом столбце, который мы назвали total_product.
На первом этапе (Дата продажи 2021-04-10) у нас 10 проданных продуктов. Сумма продуктов, проданных в этот день, та же — 10. На следующем шаге мы прибавляем к этой общей сумме (10) количество проданных продуктов на текущую дату (2021-04-11) — 15; это дает нам промежуточную сумму 25. В последней строке набора результатов (для последней даты продажи, 2021-04-12) промежуточная сумма равна 30.
Благодаря оконным функциям SQL легко найти кумулятивное общее количество проданных продуктов за заданный период времени. Например, в период с 10 апреля по 12 апреля 2021 года общее количество проданных продуктов равно 30.