我可以为多个窗口函数调用使用一个分区定义吗？

Question

这是我的问题。对于14个结果列，我使用相同的窗口函数sum()，具有相同的分区定义：

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

我必须用每一笔钱拼出OVER (PARTITION BY id, weekly)吗？

有更好的方法重写我的查询吗？

Answer 1

您可以尝试使用WINDOW子句，可选窗口子句具有通用形式。

窗口window_name AS ( window_definition )，.

然后使用OVER window_name作为聚合函数，它可能会更优雅一些。

 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 );

更多细节，我们可以参考窗口条款

SQLfiddle

Answer 2

您可以使用窗口条款。

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);   -- !

您仍然必须重复OVER关键字，但是可以用在WINDOW子句中声明的标识符替换分区的实际定义。

这是一个语法快捷方式，不影响性能。Postgres将在任何一种情况下重复使用相同的分区。

相关信息：

• 滚动和/计数/平均超过日期间隔

聚合？

尽管如此，您的查询看起来可疑地像是做了而不是想要窗口函数，而是普通聚合。在这样做的同时，您也不需要这个子查询：

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 ?

你会想要得到结果的。

这将产生每个(id, weekly)中的单个(聚合)行，而不是原来的查询，后者每输入行返回一行。

我把位置引用作为语法快捷方式，因为这个问题是关于短语法的。相关信息：

• 在每一组中选择第一行？