Оконные функции

Нарастающий итог SUM + ORDER BY

Оконные функции начинают вести себя интересно, если внутрь OVER добавить ORDER BY.

Посмотри внимательно на столбцы count_current и count_total в результате запроса:

SELECT pi.purchase_id,
       pi.purchase_item_id,
       pi.count AS count_item,
       sum(pi.count) over (ORDER BY pi.purchase_id, pi.purchase_item_id) AS count_current,
       sum(pi.count) over () AS count_total
  FROM purchase_item pi
 ORDER BY pi.purchase_id,
          pi.purchase_item_id
#purchase_idpurchase_item_idcount_itemcount_currentcount_total
1111154
212303154
32313254
43413354
53513454
64623654
75713754
86813854
96913954
1061014054
1171134354
1281214454
1391314554
14101455054
15101515154
16101615254
17111715354
18111815454

С sum(pi.count) over () AS count_total все понятно. Так как в over ничего не указано, то sum вычисляется по всем строкам.

Но если внутри over написать ORDER BY, то функция будет вычисляться не по всем строкам, а от первой строки и до текущей, включительно (не совсем так конечно, но об этом в следующих заданиях).

Чтобы вычислить агрегатную функцию по окну с указанием ORDER BY внутри over, делай так:

  1. Разбивай все строки результата на группы в соответствии с PARTITION BY, указанным в over. В нашем случае PARTITION BY опущен, поэтому группа у нас одна - все строки.
  2. Сортируй строки в группах в порядке, указанном в ORDER BY внутри over.
  3. Вычисляй функцию последовательно для строк, начиная с первой. Для каждой очередной строки для вычисления значения бери строки от начала и до текущей.

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

Для первой строки берем одну единственную строку

#purchase_idpurchase_item_idcount_itemcount_currentcount_total
1111154

Для второй:

#purchase_idpurchase_item_idcount_itemcount_currentcount_total
1111154
212303154
31 = 1 + 30

Для третьей:

#purchase_idpurchase_item_idcount_itemcount_currentcount_total
1111154
212303154
32313254
32 = 1 + 30 + 1

И так далее...

С ORDER BY себя так ведут все агрегатные оконные функции, не только sum.

ПредыдущаяСледующая