Postgresql - How to count a records before current record date?

volama1699
November 26, 2023
287 views
0 votes
2 Answers

I have a table like this:

-----------------
id | date
-----------------
1  | 2023-10-07
2  | 2023-10-08
3  | 2023-10-14

and I want to get the record count up to the current record where the date is less than Sunday of the current record. So I want the output to look like this:

---------------------
week_number | count
---------------------
2           | 2
3           | 3

I tried different approaches, but the result is a counter for entries within the week. It’s as if he doesn’t look at all records before the current one, but only at those that are in the same week.

WITH weekly AS (
    SELECT
        get_week_number(date::DATE) AS week_number,
        COUNT(*) AS count
    FROM
        inventory
    WHERE
        date <= date_trunc('week', date)::date + INTERVAL '6 days'
    GROUP BY
        week_number
)

SELECT
    week_number, count
FROM
    inventory
        JOIN weekly ON get_week_number(date::DATE) = weekly.week_number
WHERE
    date <= date_trunc('week', date)::date + INTERVAL '6 days'
GROUP BY
    week_number, count
ORDER BY
    week_number;

Answers

- Zegarek
- November 26, 2023 at 10:31 pm
- 0 votes
0
You’re probably looking for window functions. Demo at db<>fiddle
```
select id, 
       date, 
       extract(week from date) AS week_number,
       count(*)over(order by date) AS count
from inventory order by id;
```
id date week_number count

1 2023-10-07 40 1

2 2023-10-08 40 2

3 2023-10-14 41 3
```
select week_number, 
       sum(count)over(order by week_number) 
from( select extract(week from date::DATE) AS week_number,
             count(*) AS count
       from inventory
       group by week_number) AS subquery;
```
week_number sum

40 2

41 3

Default window frame clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning count()over() is going to get the record count up to the current record the way you wanted.
Login or Signup to reply.

- ErwinBrandstetter
- November 26, 2023 at 11:13 pm
- 0 votes
0
This computes

the record count up to the current record where the date is less than Sunday of the current record
```
SELECT *
     , extract(week FROM date + 1) AS week_nr  -- fix off-by-1
     , count(*) OVER (ORDER BY date_bin('7 days', date, timestamp '2023-1-1')
                      RANGE BETWEEN UNBOUNDED PRECEDING AND '1 day' PRECEDING) AS count_til_last_week
FROM   inventory;
```
I assume by "Sunday of the current record" you mean weeks starting on Sunday. ISO weeks start on Monday. date_trunc() truncates weeks accordingly. Use date_bin() to "truncate" to Sunday. See:
- Make date_trunc() start on Sunday instead of Monday
Add a day to your date before extracting the week number to fix the same off-by-1 error. (There may be corner-case issues around the turn of the year.)

To get the count of rows with a "date is less than Sunday of the current record", you have to stop the count at "last week". RANGE BETWEEN UNBOUNDED PRECEDING AND '1 day' PRECEDING achieves that, after reducing every date to the Sunday of the week.
Details in the manual about window functions.

To aggregate per week:
```
SELECT extract(year FROM sun) AS year
     , extract(week FROM sun + interval '1 day') AS week_number
     , sum(row_count) OVER (ORDER BY sun
                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS count_til_last_week
FROM  (
   SELECT date_bin('7 days', date, timestamp '2023-1-1') AS sun
        , count(*) AS row_count
   FROM   inventory
   GROUP  BY 1
   ) sub;
```
Note that a week number without year results in confusing or wrong aggregations quickly. So I based counts on full dates and display the year additionally. (No off-by-1 there.)

fiddle
Login or Signup to reply.