Can I use one PARTITION definition for multiple window function calls?

Question

This is my query. I use the same window function sum() with the same partition definition for 14 result columns:

select id,weekly,
  sum(totalsteps) OVER (PARTITION BY id, weekly) as total_steps, 
  sum(totaldistance) OVER (PARTITION BY id, weekly) as total_distance,
  sum(veryactiveminutes) OVER (PARTITION BY id, weekly) as total_veryactive,  
  sum(fairlyactiveminutes) OVER (PARTITION BY id, weekly) as total_fairlyactive, 
  sum(lightlyactiveminutes) OVER (PARTITION BY id, weekly) as total_lightlyactive, 
  sum(totalsteps)  OVER (PARTITION BY id, weekly) as total_steps,
  sum(totaldistance) OVER (PARTITION BY id, weekly) as total_distance, 
  sum(veryactivedistance) OVER (PARTITION BY id, weekly) as total_veryactivedistance, 
  sum(moderatelyactivedistance) OVER (PARTITION BY id, weekly) as total_moderatelyactivedistance, 
  sum(lightactivedistance) OVER (PARTITION BY id, weekly) as total_lightactivedistance,  
  sum(sedentaryactivedistance) OVER (PARTITION BY id, weekly) as total_sedentaryactivedistance, 
  sum(calories) OVER (PARTITION BY id, weekly) as total_calories, 
  sum(totalminutesasleep) OVER (PARTITION BY id, weekly) as total_asleep, 
  sum(totaltimeinbed)  OVER (PARTITION BY id, weekly) as total_inbed
from (select *, date_trunc('week', activitydate) as weekly
  from activitysleep_merged 
  ) as weeklysum

Do I have to spell out OVER (PARTITION BY id, weekly) with each sum?
Is there better way to re-write my query?

Erwin Brandstetter · Answer 1 · 2022-02-17T01:25:50.270

You can use a WINDOW clause.

SELECT id, weekly
     , sum(totalsteps)    OVER w AS total_steps
     , sum(totaldistance) OVER w AS total_distance
     , ...
FROM  (SELECT *, date_trunc('week', activitydate) AS weekly FROM activitysleep_merged ) AS weeklysum
WINDOW w AS (PARTITION BY id, weekly);   -- !

You still have to repeat the OVER keyword, but can replace the actual definition of the partition with the identifier declared in the WINDOW clause.

This is a syntax shortcut, no effect on performance. Postgres will re-use the same partition in either case.

Rolling sum / count / average over date interval

Aggregation?

That said, your query suspiciously looks like you do not want window functions to begin with, but plain aggregation. While being at it, you don't really need that subquery either:

SELECT id, date_trunc('week', activitydate) AS weekly
     , sum(totalsteps)    AS total_steps
     , sum(totaldistance) AS total_distance
     , ...
FROM   activitysleep_merged
GROUP  BY 1, 2   -- !
ORDER  BY 1, 2   --  or BY 2, 1 ?

And you'll want the result ordered.

This produces a single (aggregated) row per (id, weekly) - as opposed to your original query, which returns one row per input row.

I threw in positional references as syntax shortcut, since this question was about short syntax. Related:

Select first row in each GROUP BY group?

Yes! I changed my query and it works! Thank you thank you! – Jade Young Feb 17 '22 at 01:29 — Jade Young, Feb 17 '22 at 01:29

D-Shih · Accepted Answer · 2022-02-17T00:58:01.203

2

You can try to use WINDOW Clause, The optional WINDOW clause has the general form

WINDOW window_name AS ( window_definition ) [, ...]

Then use OVER window_name for your aggregate function, it might be more elegant

 select id,weekly,
    sum(totalsteps) over w as  total_steps, 
    sum(totaldistance) over w as  total_distance,
    sum(veryactiveminutes) over w as  total_veryactive, 
    sum(fairlyactiveminutes) over w as  total_fairlyactive, 
    sum(lightlyactiveminutes) over w as  total_lightlyactive, 
    sum(totalsteps) over w as  total_steps,
    sum(totaldistance) over w as  total_distance,
    sum(veryactivedistance) over w as  total_veryactivedistance, 
    sum(moderatelyactivedistance) over w as  total_moderatelyactivedistance, 
    sum(lightactivedistance) over w as  total_lightactivedistance, 
    sum(sedentaryactivedistance) over w as  total_sedentaryactivedistance, 
    sum(calories) over w as  total_calories, 
    sum(totalminutesover w as leep) over w as  total_over w as leep, 
    sum(totaltimeinbed) over w as  total_inbed 
from (
    select *, date_trunc('week', activitydate) as weekly
    from activitysleep_merged 
) WINDOW w AS ( PARTITION BY id, weekly );

more detail we can refer WINDOW Clause

SQLfiddle

edited Feb 17 '22 at 00:58

answered Feb 17 '22 at 00:50

D-Shih

44,943
6
31
51

Hi I tried but it reminds me:'syntax error at or near "as" LINE 27: sum(totalsteps) w as total_steps, ' – Jade Young Feb 17 '22 at 00:57
I miss the `OVER` clause, I edited it – D-Shih Feb 17 '22 at 00:58
Thanks! I got it, but I got a lot of replicates for each id. How can I remove those replicates? Have Distinct() in front of each sum query? – Jade Young Feb 17 '22 at 01:10
I guess you might want to use `GROUP BY` with aggregate function instead window aggregate function, but there isn't any sample data I am not sure – D-Shih Feb 17 '22 at 01:15
Inspired by you, I use 'GROUP BY' function and it works. I also learn 'window' function. You're amazing! Thanks a lot! – Jade Young Feb 17 '22 at 01:28
Hi I have another question. I want to label each id by adding a column, for example, if total very_active minutes is greater than 60, then label the user as 'very active'. How can I achieve this with the query I wrote? Thanks! – Jade Young Feb 17 '22 at 02:10

Can I use one PARTITION definition for multiple window function calls?

2 Answers2

Aggregation?