Кумулятивная сумма в Redshift

Очень часто для аналитических отчетов необходимо посчитать кумулятивную (накапливаемую) сумму элементов, например по мере увеличения времени (или другого критерия), таким образом можно просматривать общий вклад показателя в рамках критерия.

Redshift имеет прекрасный инструмент window functions, который позволяет гибко и без костылей вычислять различные параметры для аналитики.

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

Для начала найдем просто количество покупок в разрезе продуктов и дат:

SELECT purchase_date, COUNT(id_order_detail) AS day_purchases, product
FROM order_detail
WHERE product IN ('product_1', 'product_2')
GROUP BY purchase_date, product
ORDER BY product, purchase_date;
-------
purchase_date	product	day_purchases
2020-08-01	product_1          2
2020-08-02	product_1          1
2020-08-03	product_1          36
2020-08-04	product_1          1

2020-08-01	product_2          1
2020-08-02	product_2          1
2020-08-03	product_2          1
2020-08-04	product_2          1

SELECT purchase_date, COUNT(id_order_detail) AS day_purchases, product

FROM order_detail

WHERE product IN ('product_1', 'product_2')

GROUP BY purchase_date, product

ORDER BY product, purchase_date;

-------

purchase_date product day_purchases

2020-08-01 product_1 2

2020-08-02 product_1 1

2020-08-03 product_1 36

2020-08-04 product_1 1

2020-08-01 product_2 1

2020-08-02 product_2 1

2020-08-03 product_2 1

2020-08-04 product_2 1

Теперь запрос с кумулятивной суммой

SELECT purchase_date, product, day_purchases, SUM(day_purchases) OVER (
  PARTITION BY product ORDER BY product, purchase_date
  ROWS UNBOUNDED PRECEDING) AS cumulative_purchases
FROM (SELECT purchase_date, COUNT(id_order_detail) AS day_purchases, product
      FROM order_detail
      WHERE product IN ('product_1', 'product_2')
      GROUP BY purchase_date, product) AS purchases
ORDER BY product, purchase_date;
---------
purchase_date	product	day_purchases	cumulative_purchases
2020-08-01	product_1          2                    2
2020-08-02	product_1          1                    3
2020-08-03	product_1          36	       		39
2020-08-04	product_1          1                    40

2020-08-01	product_2          1                    1
2020-08-02	product_2          1                    2
2020-08-03	product_2          1                    3
2020-08-04	product_2          1                    4

SELECT purchase_date, product, day_purchases, SUM(day_purchases) OVER (

PARTITION BY product ORDER BY product, purchase_date

ROWS UNBOUNDED PRECEDING) AS cumulative_purchases

FROM (SELECT purchase_date, COUNT(id_order_detail) AS day_purchases, product

FROM order_detail

WHERE product IN ('product_1', 'product_2')

GROUP BY purchase_date, product) AS purchases

ORDER BY product, purchase_date;

---------

purchase_date product day_purchases cumulative_purchases

2020-08-01 product_1 2 2

2020-08-02 product_1 1 3

2020-08-03 product_1 36 39

2020-08-04 product_1 1 40

2020-08-01 product_2 1 1

2020-08-02 product_2 1 2

2020-08-03 product_2 1 3

2020-08-04 product_2 1 4

Кумулятивная сумма вычисляется здесь: SUM(day_purchases) OVER (
PARTITION BY product ORDER BY product, purchase_date
ROWS UNBOUNDED PRECEDING)

Стоит отметить что:
PARTITION BY – то по чему группируются строки
ORDER BY – сортировка строк в рамках группы
ROWS – детали ограничения набора строк

Так же стоит помнить, что оконные функции вычисляются одними из последних в запросе, а именно, перед последним ORDER BY (когда JOIN, WHERE, GROUP BY, HAVING уже отработали).

Как было сказано выше redshift предоставляет гибкие инструменты для аналитики, которыми легко и удобно пользоваться.

Odev

bug's life

Кумулятивная сумма в Redshift