Нарастающий итог 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

С 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. Вычисляй функцию последовательно для строк, начиная с первой. Для каждой очередной строки для вычисления значения бери строки от начала и до текущей.

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

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

Для второй:

31 = 1 + 30

Для третьей:

32 = 1 + 30 + 1

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

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