Неуникальные значения в нарастающем итоге 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

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

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

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

(ORDER BY pi.purchase_id, pi.count)

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

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

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

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

40 = 37 + 1 + 1 + 1

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