Cumulative or Moving Aggregations

Private: Mastering SQL using Postgresql Writing Advanced SQL Queries Cumulative or Moving Aggregations

Let us understand how we can take care of cumulative or moving aggregations using Analytic Functions.

When it comes to Windowing or Analytic Functions we can also specify window spec using ROWS BETWEEN clause.
Even when we do not specify window spec, the default window spec is used. For most of the functions the default window spec is UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. You also have special clauses such as CURRENT ROW.
Here are some of the examples with respect to ROWS BETWEEN.
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ROWS BETWEEN 3 PRECEDING AND CURRENT ROW - moving aggregations using current record and previous 3 records.
- ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING - moving aggregations using current record and following 3 records.
- ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING - moving aggregations based up on 7 records (current record, 3 previous records and 3 following records)
We can leverage ROWS BETWEEN for cumulative aggregations or moving aggregations.
Here is an example of cumulative sum.

Warning

If you are using Jupyter based environment make sure to restart the kernel, as the session might have been already connected with retail database.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db

env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db

Note

Even though it is not mandatory to specify ORDER BY as per syntax for cumulative aggregations, it is a must to specify. If not, you will end up getting incorrect results.

%%sql

SELECT e.employee_id, e.department_id, e.salary,
    sum(e.salary) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_sal_expense
FROM employees e
ORDER BY e.department_id, e.salary DESC
LIMIT 10

10 rows affected.

employee_id	department_id	salary	sum_sal_expense
200	10	4400.00	4400.00
201	20	13000.00	19000.00
202	20	6000.00	6000.00
114	30	11000.00	24900.00
115	30	3100.00	13900.00
116	30	2900.00	10800.00
117	30	2800.00	7900.00
118	30	2600.00	5100.00
119	30	2500.00	2500.00
203	40	6500.00	6500.00

Warning

If you are using Jupyter based environment make sure to restart the kernel, as the session might have been already connected with hr database.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Note

Here is the example for cumulative sum for every month using daily_product_revenue in retail database.

%%sql

SELECT t.*,
    round(sum(t.revenue) OVER (
        PARTITION BY to_char(order_date, 'yyyy-MM')
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ), 2) AS cumulative_daily_revenue
FROM daily_revenue t
ORDER BY to_char(order_date, 'yyyy-MM'),
    order_date
LIMIT 10

10 rows affected.

order_date	revenue	cumulative_daily_revenue
2013-07-25 00:00:00	31547.23	31547.23
2013-07-26 00:00:00	54713.23	86260.46
2013-07-27 00:00:00	48411.48	134671.94
2013-07-28 00:00:00	35672.03	170343.97
2013-07-29 00:00:00	54579.70	224923.67
2013-07-30 00:00:00	49329.29	274252.96
2013-07-31 00:00:00	59212.49	333465.45
2013-08-01 00:00:00	49160.08	49160.08
2013-08-02 00:00:00	50688.58	99848.66
2013-08-03 00:00:00	43416.74	143265.40

Note

Here are examples for 3 day moving sum as well as average using daily_revenue in retail database.

%%sql

SELECT t.*,
    round(sum(t.revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_3day_revenue
FROM daily_revenue t
ORDER BY order_date
LIMIT 20

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
20 rows affected.

order_date	revenue	moving_3day_revenue
2013-07-25 00:00:00	31547.23	31547.23
2013-07-26 00:00:00	54713.23	86260.46
2013-07-27 00:00:00	48411.48	134671.94
2013-07-28 00:00:00	35672.03	138796.74
2013-07-29 00:00:00	54579.70	138663.21
2013-07-30 00:00:00	49329.29	139581.02
2013-07-31 00:00:00	59212.49	163121.48
2013-08-01 00:00:00	49160.08	157701.86
2013-08-02 00:00:00	50688.58	159061.15
2013-08-03 00:00:00	43416.74	143265.40
2013-08-04 00:00:00	35093.01	129198.33
2013-08-05 00:00:00	34025.27	112535.02
2013-08-06 00:00:00	57843.89	126962.17
2013-08-07 00:00:00	45525.59	137394.75
2013-08-08 00:00:00	33549.47	136918.95
2013-08-09 00:00:00	29225.16	108300.22
2013-08-10 00:00:00	46435.04	109209.67
2013-08-11 00:00:00	31155.50	106815.70
2013-08-12 00:00:00	59014.74	136605.28
2013-08-13 00:00:00	17956.88	108127.12

%%sql

SELECT t.*,
    round(sum(t.revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ), 2) AS moving_3day_revenue
FROM daily_revenue t
ORDER BY order_date
LIMIT 20

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
20 rows affected.

order_date	revenue	moving_3day_revenue
2013-07-25 00:00:00	31547.23	134671.94
2013-07-26 00:00:00	54713.23	170343.97
2013-07-27 00:00:00	48411.48	224923.67
2013-07-28 00:00:00	35672.03	242705.73
2013-07-29 00:00:00	54579.70	247204.99
2013-07-30 00:00:00	49329.29	247953.59
2013-07-31 00:00:00	59212.49	262970.14
2013-08-01 00:00:00	49160.08	251807.18
2013-08-02 00:00:00	50688.58	237570.90
2013-08-03 00:00:00	43416.74	212383.68
2013-08-04 00:00:00	35093.01	221067.49
2013-08-05 00:00:00	34025.27	215904.50
2013-08-06 00:00:00	57843.89	206037.23
2013-08-07 00:00:00	45525.59	200169.38
2013-08-08 00:00:00	33549.47	212579.15
2013-08-09 00:00:00	29225.16	185890.76
2013-08-10 00:00:00	46435.04	199379.91
2013-08-11 00:00:00	31155.50	183787.32
2013-08-12 00:00:00	59014.74	196605.61
2013-08-13 00:00:00	17956.88	199737.25

%%sql

SELECT t.*,
    round(avg(t.revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_3day_revenue
FROM daily_revenue t
ORDER BY order_date
LIMIT 20

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
20 rows affected.

order_date	revenue	moving_3day_revenue
2013-07-25 00:00:00	31547.23	31547.23
2013-07-26 00:00:00	54713.23	43130.23
2013-07-27 00:00:00	48411.48	44890.65
2013-07-28 00:00:00	35672.03	46265.58
2013-07-29 00:00:00	54579.70	46221.07
2013-07-30 00:00:00	49329.29	46527.01
2013-07-31 00:00:00	59212.49	54373.83
2013-08-01 00:00:00	49160.08	52567.29
2013-08-02 00:00:00	50688.58	53020.38
2013-08-03 00:00:00	43416.74	47755.13
2013-08-04 00:00:00	35093.01	43066.11
2013-08-05 00:00:00	34025.27	37511.67
2013-08-06 00:00:00	57843.89	42320.72
2013-08-07 00:00:00	45525.59	45798.25
2013-08-08 00:00:00	33549.47	45639.65
2013-08-09 00:00:00	29225.16	36100.07
2013-08-10 00:00:00	46435.04	36403.22
2013-08-11 00:00:00	31155.50	35605.23
2013-08-12 00:00:00	59014.74	45535.09
2013-08-13 00:00:00	17956.88	36042.37

Previous Topic

Back to Lesson

Next Topic