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

Неуникальные значения в нарастающем итоге SUM + ORDER BY

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

Разберем, что значит "не совсем так". Посмотри на запрос и его результат:

SELECT pi.purchase_id,
       pi.count,
       sum(pi.count) over (ORDER BY pi.purchase_id, pi.count) AS count_current,
       sum(pi.count) over () AS count_total
  FROM purchase_item pi
 ORDER BY pi.purchase_id,
          pi.count
#purchase_idcountcount_currentcount_total
111154
21303154
3213254
4313454
5313454
6423654
7513754
8614054
9614054
10614054
11734354
12814454
13914554
141014754
151014754
161055254
171115454
181115454

Особо интересуют строки

#purchase_idcountcount_currentcount_total
...............
3213254
4313454
5313454
6423654
...............

На 3-ей строке count_current был равен 32. На 4-й count равен 1, и казалось бы count_current должен быть равен 33 (32 + 1). Но он равен 34.

Причины становятся ясны, если взглянуть на ORDER BY в оконной функции и на 5-ю строку.

(ORDER BY pi.purchase_id, pi.count)
#purchase_idcountcount_currentcount_total
...............
321..
431..
531..
642..
...............

В 4-й и 5-й строке оба значения purchase_id и count совпадают. Поэтому с точки зрения сортировки они не различаются.

Агрегатные оконные функции для вычисления значения для очередной строки включают в набор все строки с совпадающими значениями из ORDER BY. Именно по этому в 4-й и 5-й строках count_current равен 34 (32 + 1 + 1).

Для вычисления значения count_current в 4-й и 5-й строке брались строки

#purchase_idcountcount_currentcount_total
111154
21303154
3213254
4313454
5313454

Для строк с 8 по 10 агрегатная функция так же принимает одинаковое значение:

#purchase_idcountcount_currentcount_total
...............
7513754
8614054
9614054
10614054
11734354
...............

40 = 37 + 1 + 1 + 1

P.S. В оконных функциях можно явно задавать размер окна (группу строк) для вычисления значения. Возможностей задания окна достаточно много и они заслуживают отдельной темы.

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