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

Iterate through data and calculate the sum of the last three days of each month – Postgresql

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    sales_volume INT
);

INSERT INTO sales
(event_date, sales_volume)
VALUES 
('2023-01-27', '900'),
('2023-02-28', '500'),
('2023-01-29', '100'),
('2023-01-30', '600'),
('2023-01-31', '450'),
  
('2023-02-25', '300'),
('2023-02-26', '250'),
('2023-02-27', '845'),
('2023-02-28', '520'), 

('2023-03-26', '750'),
('2023-03-27', '750'),
('2023-03-28', '625'),
('2023-03-29', '885'),
('2023-03-30', '120'),
('2023-03-31', '400');

Expected Result:

Row event_time sales_volume
1 1 1150
2 2 2115
3 3 1405

I want to iterate through the table and calculate the sum of the sales_volume for each month.
Currently, I am able to achieve this using this query:

SELECT
DATE_PART('month', s.event_date) AS event_time,
SUM(s.sales_volume) AS sales_volume
FROM sales s
WHERE s.event_date BETWEEN '2023-01-29' AND '2023-01-31'
OR s.event_date BETWEEN '2023-02-26' AND '2023-02-28'
OR s.event_date BETWEEN '2023-03-29' AND '2023-03-31'
GROUP BY 1
ORDER BY 1;

However, my original database is much bigger than the example so I would need a lot of these OR-conditions.

How do I need to modify the query so it iterates through the table without using the OR-conditions?

Tags:

2

Answers


  1. 0

    demo:db<>fiddle

    SELECT
    date_trunc('month', event_date) as month,
    SUM(sales_volume)
FROM (
    SELECT
        *,
        row_number() OVER (              -- 1
            PARTITION BY date_trunc('month', event_date) 
            ORDER BY event_date DESC
        )
    FROM sales 
) s
WHERE row_number <= 3                    -- 2
GROUP BY 1                               -- 3
    1. Use row_number() window function to enumerate your days of month by descending day order. So last day gets number 1, last but one day number 2 and so on.
    2. Filter records with row_number values <= 3, which represent the last three days of the month.
    3. Group these records by the the month calculated by the date_trunc() function
    Login or Signup to reply.
  2. 0

    The query below finds the first day of the month following each date range, and then uses a subquery to find the sum of the volume three days prior to that date:

    select row_number() over (order by t.d), (select sum(case when s.event_date >= t.d - interval '3 day' and s.event_date < t.d then s.sales_volume else 0 end) from sales s)
from (select distinct (extract(year from event_date)||'-'||lpad(extract(month from event_date)::text, 2, '0'::text)||'-'||'01')::date + interval '1 month' d from sales) t

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search