Created
September 17, 2020 20:38
-
-
Save gregtap/2f6dac81c47f18964a56057c387cc851 to your computer and use it in GitHub Desktop.
SQL aggregated rolling sum
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH example AS ( | |
SELECT '2020-04-15' AS start_date, 6000.00 AS amount, "e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" AS fun_id | |
UNION ALL SELECT '2020-04-21', 7000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" | |
UNION ALL SELECT '2020-05-04', 13000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" | |
UNION ALL SELECT '2020-05-28', 4000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" | |
UNION ALL SELECT '2020-08-05', 30000.00 ,"e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7" | |
UNION ALL SELECT '2020-05-04', 1962.00 ,"89ba98b9-a4fa-41f3-9015-4e3cc29961ad" | |
UNION ALL SELECT '2020-07-16', 4038.00 ,"89ba98b9-a4fa-41f3-9015-4e3cc29961ad" | |
UNION ALL SELECT '2020-07-20', 10000.00 ,"ce257ea2-1a0c-42ff-9d1c-ea2073846628" | |
UNION ALL SELECT '2020-08-31', 25000.00 ,"9f38de3d-4cd7-4950-9d6d-5ddf68c67c20" | |
UNION ALL SELECT '2020-03-09', 45000.00 ,"8f4298ff-356c-4be9-8dd6-b12e1078f668" | |
UNION ALL SELECT '2020-09-07', 10000.00 ,"a505f602-eb2b-4d1e-90b4-39dfec282843" | |
UNION ALL SELECT '2020-09-07', 10000.00 ,"fa49d1c7-517a-4b7e-b0c8-1c616e858f9e" ) | |
SELECT fun_id, start_date, amount, SUM(amount) OVER (PARTITION BY fun_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_sum from example ORDER BY fun_id, start_date |
WITH example AS (
SELECT '2020-04-15'::date AS start_date, 6000.00 AS amount, 'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7' AS fun_id
UNION ALL SELECT '2020-04-21', 7000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
UNION ALL SELECT '2020-05-04', 13000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
UNION ALL SELECT '2020-05-28', 4000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
UNION ALL SELECT '2020-08-05', 30000.00 ,'e63e55f4-eadf-4c3d-8a99-0ec524b0dbb7'
UNION ALL SELECT '2020-05-04', 1962.00 ,'89ba98b9-a4fa-41f3-9015-4e3cc29961ad'
UNION ALL SELECT '2020-07-16', 4038.00 ,'89ba98b9-a4fa-41f3-9015-4e3cc29961ad'
UNION ALL SELECT '2020-07-20', 10000.00 ,'ce257ea2-1a0c-42ff-9d1c-ea2073846628'
UNION ALL SELECT '2020-08-31', 25000.00 ,'9f38de3d-4cd7-4950-9d6d-5ddf68c67c20'
UNION ALL SELECT '2020-03-09', 45000.00 ,'8f4298ff-356c-4be9-8dd6-b12e1078f668'
UNION ALL SELECT '2020-09-07', 10000.00 ,'a505f602-eb2b-4d1e-90b4-39dfec282843'
UNION ALL SELECT '2020-09-07', 10000.00 ,'fa49d1c7-517a-4b7e-b0c8-1c616e858f9e'
UNION ALL SELECT '2020-09-08', 42, 'fa49d1c7-517a-4b7e-b0c8-1c616e858f9e' )
SELECT fun_id, extract(week from start_date), array_agg(start_date), sum(amount) from example group by 1, 2 order by 1, 2;
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Table foo
d
<date>
a
<amount>
c
<client_id>
Retourne la liste, par semaine de la somme des amount avant la date d, pour tous les clients.
Given a start_date (Monday).
Client1 Week1 100
Client1 Week2 200
Client1 Week3 300
Client1 Week4 300
Client1 Week5 300
Client1 Week6 400
Client2 Week1 300