Cumulative sum grouped by year, month and day in a JSON object

user_5808774 0
Viewed 298

Let's say I have a table orders with the following rows:

ID      Cost     Date (timestamp)
1       100      2020-06-30 21:18:53.328386+00
2       45       2020-06-30 11:18:53.328386+00
3       200      2020-05-29 21:32:56.620174+00
4       20       2020-06-28 21:32:56.620174+00

And I need a query that returns exactly this:

Month       Year       Costs
5           2020       {"1": 0, "2": 0, ..., "29": 200, "30": 200, "31": 200}
6           2020       {"1": 0, "2": 0, ..., "28": 20, "29": 20, "30": 165}

Please note that the column Costs has to be a json with the key being the day in the month and the value being the cumulative sum of all previous days in that month. I know this is probably not a task that postgres should be doing, but I'm just curious to see what is the solution to it (even if its not the most efficient in production environments)

count select date postgresql json
1 Answers

You can use two levels of aggregation and json_object_agg():

select date_month, date_year, json_object_agg(date_day, cnt) costs
from (
    select
        extract(month from date) date_month,
        extract(year from date) date_year,
        extract(day from date) date_day,
        sum(sum(cost)) over(
            partition by extract(month from date), extract(year from date)
            order by extract(day from date)
        ) cnt
    from mytable
    group by 1, 2, 3
) t
group by date_month, date_year
user_106767160
Related