postgresql get weekly average of cases with daily data-CodePudding

I have a table called Table1. I am trying to get the weekly average, but I only have daily data. My table contains the following attributes: caseID, date, status and some other (irrelevant) attributes. With the following query, I made the following table which comes close to what I want:

However, I would like to add a average per week of the number of cases. I have look everywhere, but I am not sure how to include that. Has anybody any clues for how to add that.

Thanks.

CodePudding user response：

select 
  date, 
  countcase, 
  extract(week from date) as weeknbr, 
  avg(countcase) over (partition by extract(week from date)) as weeklyavg
from table1;

CodePudding user response：

To expand on @luuk's answer...

SELECT 
  date,
  COUNT(id) as countcase,
  EXTRACT(WEEK FROM date) AS weeknbr, 
  AVG(COUNT(id)) OVER (PARTITION BY EXTRACT(WEEK FROM date)) as weeklyavg
FROM table1
GROUP BY date, weeknbr
ORDER BY date, weeknbr

This is possible as the Aggregation / GROUP BY is applied before the window/analytic function.