Cumulative sum which is grouped by-CodePudding

I'm looking for a solution where I can use a cumulative sum in combination with a group by.

I have the following table:

office_id	time	revenue
1	2022-01-01 12:00:00	100
1	2022-01-02 13:10:00	50
1	2022-01-02 17:00:00	40

Using the following query, I can get the cumulative sum for each entry:

SELECT  office_id,
        date_trunc('day', time) as ts,
        sum(revenue) over (partition by office_id order by time) as cum_rev
FROM    business.revenue
ORDER BY office_id, ts;

Which gives me:

office_id	ts	cum_rev
1	"2022-01-01 00:00:00"	100
1	"2022-01-02 00:00:00"	150
1	"2022-01-02 00:00:00"	190

What I want to obtain is to group the data on the truncated time, ie:

office_id	ts	cum_rev
1	"2022-01-01 00:00:00"	100
1	"2022-01-02 00:00:00"	190

What changes to my query do I have to make to get this result? I have a feeling I should group by my ts field but it's not that easy unfortunately.

CodePudding user response：

(Assuming the office_id were supposed to be all 1s)

You need two levels of nesting, one to do the grouping sum and one the cumulation sum:

select office_id, ts, sum(sum) over (partition by office_id order by ts) from (
  SELECT  office_id,
    date_trunc('day', time) as ts,
    sum(revenue) FROM    revenue 
    GROUP BY 1, 2
  ) foo 
ORDER BY office_id, ts;

CodePudding user response：

select  office_id
       ,ts
       ,sum(rev) over(order by ts)       as cum_rev
from   (
        select   office_id   
                ,date_trunc('day', time) as ts
                ,sum(revenue)            as rev
        from     t
        group by office_id, date_trunc('day', time)
       ) t

office_id	ts	cum_rev
1	2022-01-01 00:00:00	100
1	2022-01-02 00:00:00	190

Fiddle

CodePudding user response：

I assumed 1, 2, 3 was supposed to be 1's

I just summed over the grain it sounds like you wanted, id and then just added a distinct to remove duplicate dates, but the aggregate will give you the total mount over id

SELECT DISTINCT office_id,
        date_trunc('day', time) as ts,
        sum(revenue) over (partition by office_id  order by date_trunc('day', time)) as cum_rev
FROM   business.revenue
ORDER BY ts;