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

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?

CodePudding user response：

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?

CodePudding user response：

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