Нарастающий итог sql: Нарастающий итог в SQL / Хабр

Что такое накопительный(промежуточный) итог SQL и как его вычислить?

Время прочтения: 3 мин.

Расчет промежуточного итога SQL — это очень распространенный шаблон, часто используемый в анализе. В этой статье вы узнаете, что такое промежуточный итог и как написать SQL-запрос для его вычисления.

Что такое  промежуточный итог SQL?

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

Дата продажиКоличество проданных продуктовНакопительный итог
2021-04-101010
2021-04-111525
2021-04-12530

В первом столбце отображается дата. Во втором столбце показано количество проданных продуктов в этот день. В третьем столбце [Накопительный итог] суммируется общее количество проданных продуктов в этот день.

Например, в первый день (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-1010
2021-04-1115
2021-04-125

Данный запрос  выбирает дату продажи для всех пользователей. Нам также нужна сумма всех продуктов за каждый день, начиная с первого заданного дня (2021-04-10):

SELECT  [c_date] ,[kolvo_product],
         sum(kolvo_product) OVER (ORDER BY c_date)
		 AS total_product
  FROM [product_sales]

Результат:

Дата продажиКоличество проданных продуктовНакопительный итог
2021-04-101010
2021-04-111525
2021-04-12530

Чтобы вычислить промежуточный итог, мы используем 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.

Оконные функции SQL: скользящие агрегаты

Это пятая, заключительная статья из серии Оконные функции SQL. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

Разберемся на примерах. Здесь возьмем другую табличку — expenses. Она показывает доходы и расходы одного из сотрудников (пусть это будет Марина) за 9 месяцев 2020 года:

┌──────┬───────┬────────┬─────────┐
│ year │ month │ income │ expense │
├──────┼───────┼────────┼─────────┤
│ 2020 │ 1     │ 94     │ 82      │
│ 2020 │ 2     │ 94     │ 75      │
│ 2020 │ 3     │ 94     │ 104     │
│ 2020 │ 4     │ 100    │ 94      │
│ 2020 │ 5     │ 100    │ 99      │
│ 2020 │ 6     │ 100    │ 105     │
│ 2020 │ 7     │ 100    │ 95      │
│ 2020 │ 8     │ 100    │ 110     │
│ 2020 │ 9     │ 104    │ 104     │
└──────┴───────┴────────┴─────────┘
  • скользящее среднее,
  • фрейм,
  • нарастающий итог,
  • функции.

Все запросы можно повторять в песочнице.

Скользящее среднее по расходам

Судя по данным, доходы у Марины растут: 94К ₽ в январе → 104К ₽ в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца. Например:

  • скользящее среднее за февраль = (январь + февраль + март) / 3;
  • за март = (февраль + март + апрель) / 3;
  • за апрель = (март + апрель + май) / 3;
  • и так далее.

Рассчитаем скользящее среднее по всем месяцам:

Было

Стало

Столбец roll_avg показывает скользящее среднее по расходам за три месяца (текущий, предыдущий и следующий). Теперь хорошо видно, что расходы стабильно растут.

Как перейти от «было» к «стало»?

Отсортируем таблицу по месяцам:

select
  year, month, expense,
  null as roll_avg
from expenses
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │          │
│ 2020 │ 2     │ 75      │          │
│ 2020 │ 3     │ 104     │          │
│ 2020 │ 4     │ 94      │          │
│ 2020 │ 5     │ 99      │          │
│ 2020 │ 6     │ 105     │          │
│ 2020 │ 7     │ 95      │          │
│ 2020 │ 8     │ 110     │          │
│ 2020 │ 9     │ 104     │          │
└──────┴───────┴─────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать среднее по предыдущему, текущему и следующему значению из столбца expense:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее…

Одной гифкой:

Окно на каждом шаге сдвигается вниз, скользит — так и получается скользящее среднее. Чтобы описать на SQL, придется вспомнить концепцию фреймов, с которой мы познакомились в статье о смещении:

  1. Окно состоит из одной или нескольких секций (в нашем случае секция одна — все записи expenses).
  2. Внутри секции записи упорядочены по конкретным столбцам (order by year, month).
  3. У каждой записи свой фрейм.

Фрейм на каждом шаге охватывает три записи — текущую, предыдущую и следующую:

Вот как записать это на SQL:

window w as (
  order by year, month
  rows between 1 preceding and 1 following
)

С order by все понятно, а вторая строчка — это как раз определение фрейма: «выбрать строки от 1 предыдущей до 1 следующей». На следующем шаге разберемся с фреймами подробно, а пока закончим с нашим запросом.

Считаем среднее по расходам — это функция avg():

avg(expense) over w

Добавим округление и сведем все вместе:

select
  year, month, expense,
  round(avg(expense) over w) as roll_avg
from expenses
window w as (
  order by year, month
  rows between 1 preceding and 1 following
)
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │ 79.0     │
│ 2020 │ 2     │ 75      │ 87.0     │
│ 2020 │ 3     │ 104     │ 91.0     │
│ 2020 │ 4     │ 94      │ 99.0     │
│ 2020 │ 5     │ 99      │ 99.0     │
│ 2020 │ 6     │ 105     │ 100.0    │
│ 2020 │ 7     │ 95      │ 103.0    │
│ 2020 │ 8     │ 110     │ 103.0    │
│ 2020 │ 9     │ 104     │ 107.0    │
└──────┴───────┴─────────┴──────────┘

Скользящее среднее по расходам готово!

Фрейм

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «граница секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней — обрезается:

Вообще, у фреймов намного больше возможностей, но мы ограничимся этими. Подробности разберем на курсе.

Прибыль нарастающим итогом

Благодаря скользящему среднему, мы выяснили, что в expenses растут и доходы, и расходы. А как они соотносятся друг с другом? Хочется понять, находится ли человек «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.

Причем важно понимать не на конец года, а на каждый месяц. Потому что если по итогам года у Марины все ОК, а в июне ушла в минус — это потенциальная проблема (у компаний такую ситуацию называют «кассовым разрывом»).

Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):

  • кумулятивный доход за январь = январь;
  • за февраль = январь + февраль;
  • за март = январь + февраль + март;
  • за апрель = январь + февраль + март + апрель;
  • и так далее.

t_income показывает доходы нарастающим итогом, t_expense — расходы, а t_profit — прибыль.

t_profit = t_income - t_expense

Как рассчитать кумулятивные показатели?

Отсортируем таблицу по месяцам:

select
  year, month, income, expense,
  null as t_income,
  null as t_expense,
  null as t_profit
from expenses
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │          │           │          │
│ 2020 │ 2     │ 94     │ 75      │          │           │          │
│ 2020 │ 3     │ 94     │ 104     │          │           │          │
│ 2020 │ 4     │ 100    │ 94      │          │           │          │
│ 2020 │ 5     │ 100    │ 99      │          │           │          │
│ 2020 │ 6     │ 100    │ 105     │          │           │          │
│ 2020 │ 7     │ 100    │ 95      │          │           │          │
│ 2020 │ 8     │ 100    │ 110     │          │           │          │
│ 2020 │ 9     │ 104    │ 104     │          │           │          │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать суммарные показатели от начала таблицы до текущей строки:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее…

Одной гифкой:

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

window w as (
  order by year, month
  rows between unbounded preceding and current row
)

Считаем сумму по доходам и расходам — это функция sum():

sum(income) over w as t_income,
sum(expense) over w as t_expense,

Прибыль считаем как разницу между доходами и расходами:

(sum(income) over w) - (sum(expense) over w) as t_profit

Все вместе:

select
  year, month, income, expense,
  sum(income) over w as t_income,
  sum(expense) over w as t_expense,
  (sum(income) over w) - (sum(expense) over w) as t_profit
from expenses
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │ 94       │ 82        │ 12       │
│ 2020 │ 2     │ 94     │ 75      │ 188      │ 157       │ 31       │
│ 2020 │ 3     │ 94     │ 104     │ 282      │ 261       │ 21       │
│ 2020 │ 4     │ 100    │ 94      │ 382      │ 355       │ 27       │
│ 2020 │ 5     │ 100    │ 99      │ 482      │ 454       │ 28       │
│ 2020 │ 6     │ 100    │ 105     │ 582      │ 559       │ 23       │
│ 2020 │ 7     │ 100    │ 95      │ 682      │ 654       │ 28       │
│ 2020 │ 8     │ 100    │ 110     │ 782      │ 764       │ 18       │
│ 2020 │ 9     │ 104    │ 104     │ 886      │ 868       │ 18       │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

По t_profit видно, что дела у Марины идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.

Функции агрегации

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

  • min() и max()
  • count(), avg() и sum()

Разница только в наличии фрейма у скользящих агрегатов.

⌘ ⌘ ⌘

Мы рассмотрели четыре класса задач, которые решаются с помощью оконных функций в SQL:

  • Ранжирование (всевозможные рейтинги).
  • Сравнение со смещением (соседние элементы и границы).
  • Агрегация (количество, сумма и среднее).
  • Скользящие агрегаты (сумма и среднее в динамике).

Теперь попробуйте применить «окошки» на практике!

Чтобы узнать больше об оконных функциях или потренироваться — записывайтесь на курс

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

Вычисление текущей суммы SQL с предложениями OVER и PARTITION BY

Написано Беном Ричардсоном• 16 марта 2018 г. •
12:47•
Разработка базы данных, Заявления
• Один комментарий

HomeDatabase development, StatementsРасчет промежуточного итога с помощью предложений OVER и PARTITION BY в SQL Server

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

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

Давайте рассмотрим пример, чтобы было понятнее.

Как видите, третья строка столбца RunningAgeTotal содержит сумму всех значений в строках от 1 st до 3 rd столбца StudentAge, т. е. 14 + 12 + 13 = 39.

Точно так же значение строки 4   столбца RunningAgeTotal равно 49, что является суммой значений в строках с 1   по 4   строк столбца StudentAge.

В SQL Server предложение OVER можно использовать для расчета промежуточных сумм.

Давайте рассмотрим, как это использовать, на примере ниже.

Простой пример расчета промежуточного итога SQL

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

Сначала выполните следующий скрипт:

 СОЗДАТЬ БАЗУ ДАННЫХ School
ИДТИ
ЕГЭ школа
ИДТИ
СОЗДАТЬ СТОЛ Студенты
(
Id INT PRIMARY KEY IDENTITY,
Имя ученика VARCHAR (50),
StudentGender VARCHAR (50),
StudentAge INT
)
ИДТИ
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Салли», «Женщина», 14 лет)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Эдвард», «Мужской», 12 лет)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Джон», «Мужчина», 13 лет)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Лиана», «Женщина», 10 )
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Бен», «Мужчина», 11 лет)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Элис», «Женщина», 12)
ВСТАВЬТЕ В ЗНАЧЕНИЯ УЧАЩИХСЯ («Ник», «Мужчина», 9)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Джош», «Мужчина», 12 лет)
ВСТАВЬТЕ В ЦЕННОСТИ УЧАЩИХСЯ («Лиза», «Женщина», 10 )
INSERT INTO Student VALUES («Фитиль», «Мужской», 15 )

Этот сценарий создает таблицу «Студенты» в базе данных School. В таблице четыре столбца: Id, StudentName, StudentGender и Student. Оператор INSERT добавляет в базу данных 10 фиктивных записей.

Чтобы вычислить промежуточный итог sql, мы должны использовать предложение OVER и добавить столбец, для которого мы хотим вычислить промежуточный итог. Следующий сценарий вычисляет промежуточную сумму значений в столбце StudentAge и добавляет результат в столбец RunningAgeTotal.

 ЕГЭ Школа
ВЫБЕРИТЕ идентификатор, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY Id) AS RunningAgeTotal
FROM Student

В приведенном выше сценарии оператор SELECT извлекает столбцы StudentName, StudentGender и StudentAge вместе со столбцом промежуточного итога, т. е. RunningAgeTotal. Функция SUM Aggregate добавляет значения в столбец StudentAge, а предложение OVER определяет, что добавление должно выполняться в форме промежуточного итога, упорядоченного по столбцу Id. Вывод приведенного выше скрипта выглядит следующим образом:

Вычислить скользящее среднее значение SQL

Вы можете изменить сценарий в последнем разделе для расчета скользящего среднего возраста всех учащихся в таблице «Студенты». Для этого выполните следующий скрипт:

 ЕГЭ Школа
ВЫБЕРИТЕ идентификатор, StudentName, StudentGender, StudentAge,
СУММ (StudentAge) НА (ORDER BY Id) AS RunningAgeTotal,
AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage
FROM Student

Как видите, мы используем агрегатную функцию AVG для вычисления среднего возраста всех учащихся в столбце StudentAge. Вывод приведенного выше скрипта выглядит следующим образом:

Взгляните на третью строку столбца RunningAgeAverage. Он содержит среднее значение строк с 1 st по 3 rd в столбце StudentAge, т. е. (14 + 12 + 13)/3 = 13. промежуточный итог путем разделения данных по значениям в определенном столбце. Например, вы можете рассчитать промежуточную сумму sql возраста учащихся, разделенную по полу. Для этого вы должны использовать оператор PARTITION BY вместе с предложением OVER.

Взгляните на следующий пример:

 ЕГЭ Школа
ВЫБЕРИТЕ идентификатор, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (PARTITION BY StudentGender ORDER BY Id) AS RunningAgeTotal
FROM Student

Единственная разница между расчетом промежуточного итога для всех записей и расчетом промежуточного итога по полу заключается в использовании предложения PARTITION BY StudentGender в круглых скобках после предложения OVER. Приведенный выше сценарий вычисляет промежуточную сумму для значений в столбце StudentAge, разделенных значениями в столбце StudentGender. Вывод выглядит так.

Теперь взгляните на первые четыре значения в столбце RunningAgeTotal (выделены красным прямоугольником). Эти значения представляют собой промежуточную сумму студенток. Точно так же последние 6 строк (выделены зеленым прямоугольником) содержат промежуточную сумму возраста учащихся мужского пола в таблице «Студенты».

Проблемы с OVER, когда столбец имеет повторяющийся столбец

Проблема возникает, если столбец с повторяющимися значениями используется с предложением OVER для расчета промежуточного итога. Взгляните на колонку StudentAge. Элис, Эдвард и Джош имеют одинаковый возраст, то есть 12 лет. Точно так же Лиана и Лиза также имеют одинаковые значения в столбце StudentAge, то есть 10.

Если вы попытаетесь вычислить промежуточный итог, указав столбец StudentAge в круглых скобках после предложения OVER, вы увидите странные результаты. Запустим этот запрос:

 ЕГЭ Школа
ВЫБЕРИТЕ идентификатор, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY StudentAge) AS RunningAgeTotal
FROM Student

Вывод приведенного выше запроса выглядит следующим образом:

Во второй строке столбца RunningAgeTotal значение равно 29. Однако оно должно быть равно 19.потому что строки 1 st и 2 nd столбца StudentAge содержат 9 и 10 соответственно. В этом случае, поскольку обе строки 2 nd и 3 rd столбца StudentAge содержат повторяющееся значение, т. е. 10, значение для строки 2 nd столбца RunningAgeTotal вычисляется путем сложения 9, 10 и 10. Аналогичным образом для строки 3 rd столбца RunningAgeTotal используется значение из второй строки, равное 29.

Аналогично, если вы посмотрите на 5 -я -я строка столбца RunningAgeTotal, значение равно 76. На самом деле должно быть 40 + 12 = 52. Однако, поскольку 5--я -я, 6--я, -я и 7--я -я строки столбца StudentAge дублируются значений, например 12, промежуточный итог рассчитывается путем сложения 40 + 12 + 12 + 12 = 76. Этот промежуточный итог использовался для строк 6 th и 7 th столбца RunningAgeTotal, поскольку строки 6 th и 7 th столбца StudentAge содержат повторяющиеся значения в виде строки 5.

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

Читайте также:

Группировка данных с помощью функций OVER и PARTITION BY

Уроки использования OVER и PARTITION BY

Теги: sql, sql server, t-sql, запросы t-sql Последнее изменение: 13 октября 2022 г.

4 способа расчета нарастающей суммы с помощью SQL | by Ben Rogojan

Photo by Crissy Jarvis on Unsplash

Вычисление промежуточной суммы/скользящей суммы в SQL — полезный навык.

Часто может пригодиться для отчетности и даже при разработке приложений. Возможно, ваши пользователи могут захотеть увидеть промежуточную сумму набранных ими баллов или денег, которые они заработали. Как и большинство проблем в SQL, эту проблему можно решить несколькими способами.

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

Пролистайте вниз, если вы предпочитаете смотреть эти объяснения в виде видео.

Прежде чем прокручивать дальше, почему бы не подписаться на информационный бюллетень нашей команды, чтобы быть в курсе новостей науки о данных, инженерии данных и технологий! Узнайте больше здесь.

Использование аналитической функции — самый простой способ расчета промежуточной суммы. Аналитическая функция позволяет разделить данные по определенному полю. Например, в этом случае мы можем разбить скользящую сумму по driver_id и месяцу. Это даст нам промежуточную сумму по клиентам и месяцам. Таким образом, каждый месяц будет начинаться снова с 0,9.0003

См. приведенный ниже запрос, чтобы узнать, как использовать аналитическую функцию:

Это может быть полезно для сравнения того, сколько прибыли приносит водитель день за днем ​​в месяц. Возможно, вы сможете определить какую-то тенденцию и помочь вам заметить плохие драйверы или использовать ее для создания отчетов о производительности для драйверов.

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

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

Еще один способ решить проблему с промежуточным итогом — использовать самообъединение. Самосоединение означает соединение таблицы с самой собой. В отличие от обычного соединения, в котором для всех значений используется знак «=», мы будем объединять несколько значений. Сначала мы присоединим идентификатор водителя и месяц/год, как обычно, а затем присоединим дату поездки. Когда мы присоединяемся к дате поездки, мы будем использовать знак >=. Это объединит все даты из одной таблицы, которые больше, чем даты из другой таблицы.

Итак, если мы посмотрим на запрос ниже, мы увидим эту базовую структуру. Это немного сложнее, чем просто использование аналитической функции. Если вы смогли придумать это самостоятельно, отличная работа!

Вот как будут выглядеть данные в подзапросе. Если вы заметили, что поле t1.day имеет повторяющиеся значения для дат, где t1.day больше, чем t2.day. Делая это, вы можете агрегировать на t1.day и получить промежуточный итог.

Один из последних способов вычисления промежуточного итога — это использование подзапроса в операторе select. Это имеет некоторое сходство с самосоединением, за исключением того, что самосоединение происходит внутри оператора select. Обычно это не рекомендуется, потому что использование этого метода обычно заставляет запрос сканировать таблицу для каждой строки, которая существует в исходной таблице, снова и снова. Это, как правило, очень неэффективно, но мы считаем, что всегда полезно иметь общее представление обо всех возможных вариантах.

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

Это ограничило бы простоту динамичности. Например, что, если вы решите, что хотите видеть промежуточный итог каждые три месяца?

Есть идеи?

Есть несколько способов сделать это, включая некоторые из методов, описанных выше.