Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product_id INTEGER,
  sales_date DATE,
  quantity INTEGER,
  price NUMERIC
);

INSERT INTO sales (product_id, sales_date, quantity, price) VALUES
   (1, '2023-01-01', 10, 10.00),
   (1, '2023-01-02', 12, 12.00),
   (1, '2023-01-03', 15, 15.00),
   (2, '2023-01-01', 8, 8.00),
   (2, '2023-01-02', 10, 10.00),
   (2, '2023-01-03', 12, 12.00);

CREATE TABLE

INSERT 0 6

SELECT *,SUM(quantity)OVER w1 AS increasing_sum
     FROM sales
     WINDOW w1 AS (PARTITION BY product_id 
                   ORDER BY sales_date RANGE 
                   BETWEEN INTERVAL '2 days' PRECEDING 
                       AND CURRENT ROW)

id
product_id
sales_date
quantity
price
increasing_sum

1
1
2023-01-01
10
10.00
10

2
1
2023-01-02
12
12.00
22

3
1
2023-01-03
15
15.00
37

4
2
2023-01-01
8
8.00
8

5
2
2023-01-02
10
10.00
18

6
2
2023-01-03
12
12.00
30

SELECT 6

select product_id, max(increasing_sum) as quantity_last_3_days
from(SELECT product_id,
            SUM(quantity)OVER w1 AS increasing_sum
     FROM sales
     WINDOW w1 AS (PARTITION BY product_id 
                   ORDER BY sales_date RANGE 
                   BETWEEN INTERVAL '2 days' PRECEDING 
                       AND CURRENT ROW) ) as s
group by product_id;

product_id
quantity_last_3_days

1
37

2
30

SELECT 2

select product_id,sum(quantity) as quantity_last_3_days
from(select *,row_number()over(PARTITION BY product_id ORDER BY sales_date)
     from sales)_
where 3>=row_number
group by product_id;

product_id
quantity_last_3_days

1
37

2
30

SELECT 2

select now(),version(),setseed(.42);

insert into sales (product_id, sales_date, quantity, price)
select random()*100 as product_id,
       now()-random()*'1 month'::interval as sales_date,
       random()*100 as quantity,
       (random()*1e3)::numeric(7,2) as price
from generate_series(1,4e5);

now
version
setseed

2024-05-04 20:47:39.019106+01
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

INSERT 0 400000

explain analyze verbose 
select product_id, max(increasing_sum) as quantity_last_3_days
from(SELECT product_id,
            SUM(quantity)OVER w1 AS increasing_sum
     FROM sales
     WINDOW w1 AS (PARTITION BY product_id 
                   ORDER BY sales_date RANGE 
                   BETWEEN INTERVAL '2 days' PRECEDING 
                       AND CURRENT ROW) ) as s
group by product_id;

QUERY PLAN

GroupAggregate  (cost=34550.78..44095.04 rows=200 width=12) (actual time=377.339..1028.153 rows=101 loops=1)

  Output: sales.product_id, max((sum(sales.quantity) OVER (?)))

  Group Key: sales.product_id

  ->  WindowAgg  (cost=34550.78..40003.50 rows=272636 width=16) (actual time=373.794..982.749 rows=400006 loops=1)

        Output: sales.product_id, sum(sales.quantity) OVER (?), sales.sales_date

        ->  Sort  (cost=34550.78..35232.37 rows=272636 width=12) (actual time=373.766..464.972 rows=400006 loops=1)

              Output: sales.product_id, sales.sales_date, sales.quantity

              Sort Key: sales.product_id, sales.sales_date

              Sort Method: external merge  Disk: 8664kB

              ->  Seq Scan on public.sales  (cost=0.00..5274.36 rows=272636 width=12) (actual time=0.026..93.722 rows=400006 loops=1)

                    Output: sales.product_id, sales.sales_date, sales.quantity

Planning Time: 0.436 ms

Execution Time: 1029.953 ms

EXPLAIN

explain analyze verbose 
select product_id,sum(quantity) as quantity_last_3_days
from(select *,row_number()over(PARTITION BY product_id ORDER BY sales_date DESC)
     from sales)_
where 3>=row_number
group by product_id;

QUERY PLAN

GroupAggregate  (cost=34550.78..44095.04 rows=200 width=12) (actual time=295.796..408.865 rows=101 loops=1)

  Output: sales.product_id, sum(sales.quantity)

  Group Key: sales.product_id

  ->  WindowAgg  (cost=34550.78..40003.50 rows=272636 width=56) (actual time=295.005..408.627 rows=303 loops=1)

        Output: NULL::integer, sales.product_id, sales.sales_date, sales.quantity, NULL::numeric, row_number() OVER (?)

        Run Condition: (3 >= row_number() OVER (?))

        ->  Sort  (cost=34550.78..35232.37 rows=272636 width=12) (actual time=294.973..369.203 rows=400006 loops=1)

              Output: sales.product_id, sales.sales_date, sales.quantity

              Sort Key: sales.product_id, sales.sales_date DESC

              Sort Method: external merge  Disk: 8664kB

              ->  Seq Scan on public.sales  (cost=0.00..5274.36 rows=272636 width=12) (actual time=0.017..68.364 rows=400006 loops=1)

                    Output: sales.product_id, sales.sales_date, sales.quantity

Planning Time: 0.182 ms

Execution Time: 410.345 ms

EXPLAIN

create index idx1_asc on sales(product_id,sales_date)
  include(quantity)with(fillfactor=100);

CREATE INDEX

create index idx1_desc on sales(product_id,sales_date desc)
  include(quantity)with(fillfactor=100);

CREATE INDEX

vacuum analyze sales;

VACUUM

explain analyze verbose 
select product_id, max(increasing_sum) as quantity_last_3_days
from(SELECT product_id,
            SUM(quantity)OVER w1 AS increasing_sum
     FROM sales
     WINDOW w1 AS (PARTITION BY product_id 
                   ORDER BY sales_date RANGE 
                   BETWEEN INTERVAL '2 days' PRECEDING 
                       AND CURRENT ROW) ) as s
group by product_id;

QUERY PLAN

GroupAggregate  (cost=0.42..24577.72 rows=101 width=12) (actual time=3.531..726.925 rows=101 loops=1)

  Output: sales.product_id, max((sum(sales.quantity) OVER (?)))

  Group Key: sales.product_id

  ->  WindowAgg  (cost=0.42..18576.62 rows=400006 width=16) (actual time=0.072..671.064 rows=400006 loops=1)

        Output: sales.product_id, sum(sales.quantity) OVER (?), sales.sales_date

        ->  Index Only Scan using idx1_asc on public.sales  (cost=0.42..11576.51 rows=400006 width=12) (actual time=0.050..130.609 rows=400006 loops=1)

              Output: sales.product_id, sales.sales_date, sales.quantity

              Heap Fetches: 0

Planning Time: 0.304 ms

Execution Time: 727.002 ms

EXPLAIN

explain analyze verbose 
select product_id,sum(quantity) as quantity_last_3_days
from(select *,row_number()over(PARTITION BY product_id ORDER BY sales_date DESC)
     from sales)_
where 3>=row_number
group by product_id;

QUERY PLAN

GroupAggregate  (cost=0.42..24577.72 rows=101 width=12) (actual time=0.573..101.750 rows=101 loops=1)

  Output: sales.product_id, sum(sales.quantity)

  Group Key: sales.product_id

  ->  WindowAgg  (cost=0.42..18576.62 rows=400006 width=56) (actual time=0.075..101.554 rows=303 loops=1)

        Output: NULL::integer, sales.product_id, sales.sales_date, sales.quantity, NULL::numeric, row_number() OVER (?)

        Run Condition: (3 >= row_number() OVER (?))

        ->  Index Only Scan using idx1_desc on public.sales  (cost=0.42..11576.51 rows=400006 width=12) (actual time=0.052..68.647 rows=400006 loops=1)

              Output: sales.product_id, sales.sales_date, sales.quantity

              Heap Fetches: 0

Planning Time: 0.176 ms

Execution Time: 101.817 ms

EXPLAIN
id	product_id	sales_date	quantity	price	increasing_sum
1	1	2023-01-01	10	10.00	10
2	1	2023-01-02	12	12.00	22
3	1	2023-01-03	15	15.00	37
4	2	2023-01-01	8	8.00	8
5	2	2023-01-02	10	10.00	18
6	2	2023-01-03	12	12.00	30