Question 114943 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – [Postgres][SQL]Window functions (count/average) where partitioning by weeks

Is there a way to run a window function where the partition is for a date column and I need the the values to be aggregated in a weekly interval.

For example in this dataset

Date Value
01-01-2022 10
02-01-2022 15
10-01-2022 01
11-01-2022 12
20-01-2022 09
25-01-2022 08

I want a window function to calculate the average of the value column for the first week (starting 01-01-2022) of 2022, and the subsequent weeks.

Currently creating a new column with the week number for each entry and partitioning on that new column is the only way I can think of solving this. Is there a more elegant way that I’m not familiar with within the window functions in Postgres.

Tags:

2

Answers


  1. 0

    Something like this?

    SELECT  date
    ,   value
    ,   TO_CHAR(date, 'WW')::INT AS weeknumber
    ,   AVG(value) OVER(PARTITION BY TO_CHAR(date, 'WW')::INT)
FROM    table_name
ORDER BY date;

    Usually I don’t use WW, but IW the ISO week numbering. But that’s up to you.

    Login or Signup to reply.
  2. 0

    Using PG14+ date_bin function is a good option. A pre-PG14 date_bin implementation here.

    select date_bin('1 week', "Date", '2022-01-01') as "Week", 
       avg("Value") as "Avg"
from the_table 
group by "Week";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search