Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE EXTENSION btree_gist;

CREATE EXTENSION


CREATE TABLE stock (
  stock_id   serial PRIMARY KEY
, store_id   integer NOT NULL
, product_id integer NOT NULL
, day        date NOT NULL
, value      integer NOT NULL
, UNIQUE (store_id, product_id, day)
);

INSERT INTO stock(store_id, product_id, day, value)
SELECT * FROM (
   SELECT DISTINCT ON (s,p,d)
          (random() * 25)::int   AS s   -- 25 stores
        , (random() * 500)::int   AS p  -- 500 products
        , '1987-3-1'::date + g/20 AS d  -- 28 years of history
        , (random() * 100)::int   AS v
   FROM   generate_series(1, 100000) g   -- ~ 100k rows 
   ) sub
ORDER BY d, random();

CREATE TABLE



INSERT 0 99917


VACUUM ANALYZE stock;

VACUUM


CREATE MATERIALIZED VIEW mv_stock AS
SELECT store_id, product_id, value
     , daterange (day, lead(day, 1, now()::date)
          OVER (PARTITION BY store_id, product_id ORDER BY day)) AS day_range
FROM   stock;

CREATE INDEX mv_stock_range_idx ON mv_stock USING gist (day_range);

SELECT 99917



CREATE INDEX


VACUUM ANALYZE mv_stock;

VACUUM


-- Erwin
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inclusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT store_id, product_id, value, s.day_range * x.day_range AS days
   FROM  (
      SELECT store_id, product_id, value
           , daterange (day, lead(day, 1, CURRENT_DATE)
                OVER (PARTITION BY store_id, product_id ORDER BY day)) AS day_range
      FROM   stock
      ) s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2
ORDER  BY 1, 2
LIMIT  10;


store_id
product_id
days_in_range
my_result


0
0
15
68.00

0
1
15
44.00

0
2
15
41.00

0
3
15
61.00

0
4
15
57.00

0
5
15
49.00

0
6
15
18.00

0
7
15
92.00

0
8
15
1.00

0
9
15
16.00



SELECT 10


-- Erwin, building on MV
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inlcusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT s.store_id, s.product_id, s.value, s.day_range * x.day_range AS days
   FROM   mv_stock s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2
ORDER  BY 1, 2
LIMIT 10;


store_id
product_id
days_in_range
my_result


0
0
15
68.00

0
1
15
44.00

0
2
15
41.00

0
3
15
61.00

0
4
15
57.00

0
5
15
49.00

0
6
15
18.00

0
7
15
92.00

0
8
15
1.00

0
9
15
16.00



SELECT 10


-- joop
WITH calendar(day) AS ( SELECT generate_series('2013-01-01'::date, '2013-01-15'::date, '1 d'::interval)::date )
SELECT st.store_id,st.product_id
      , (SUM(st.value)::decimal(8,2) / COUNT(*) )::decimal(8,2) AS wval
FROM calendar
JOIN stock st ON calendar.day >= st.day
        AND NOT EXISTS ( -- this calendar entry belongs to the next stock entry 
                SELECT * FROM stock nx
                WHERE nx.store_id = st.store_id AND nx.product_id = st.product_id
                AND nx.day > st.day AND nx.day <= calendar.day
        )
GROUP BY st.store_id,st.product_id
ORDER BY st.store_id,st.product_id
LIMIT 10;


store_id
product_id
wval


0
0
68.00

0
1
44.00

0
2
41.00

0
3
61.00

0
4
57.00

0
5
49.00

0
6
18.00

0
7
92.00

0
8
1.00

0
9
16.00



SELECT 10


-- Jaaz
select store_id, product_id, CASE WHEN sum(nextday-day) > 0 THEN sum(Value*(nextday-day)) / sum(nextday-day) END as Avg_Value
from (
  select *
      , (
        select value
        from stock b
        where a.store_id = b.store_id
          and a.product_id = b.product_id
          and a.day >= b.day
        order by b.day
        limit 1
      )*1.0 "value"
      , coalesce((
        select day
        from stock b
        where a.store_id = b.store_id
          and a.product_id = b.product_id
          and a.day < b.day
        order by b.day
        limit 1
      ),case when current_date > '2013-01-01' then '2013-01-15' else current_date end) nextday
  from (
    select store_id, product_id, min(case when day < '2013-01-01' then '2013-01-15' else day end) AS day
    from stock z
    where day < '2013-01-15'
    group by store_id, product_id
    ) a
  union all
  select store_id, product_id, day, value*1.0 "value"
    , coalesce((
      select day
      from stock b
      where a.store_id = b.store_id
        and a.product_id = b.product_id
        and a.day < b.day
      order by b.day
      limit 1
    ),case when current_date > '2013-01-01' then '2013-01-15' else current_date end) nextday
  from stock a
  where a.day between '2013-01-01' and '2013-01-15'
) t
group by store_id, product_id
order by store_id, product_id
LIMIT 10;


store_id
product_id
avg_value


0
0
null

0
1
null

0
2
null

0
3
null

0
4
null

0
5
null

0
6
null

0
7
null

0
8
null

0
9
null



SELECT 10


-- Erwin query plan (wihtout LIMIT!)
EXPLAIN ANALYZE
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inclusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT store_id, product_id, value, s.day_range * x.day_range AS days
   FROM  (
      SELECT store_id, product_id, value
           , daterange (day, lead(day, 1, CURRENT_DATE)
                OVER (PARTITION BY store_id, product_id ORDER BY day)) AS day_range
      FROM   stock
      ) s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2
ORDER  BY 1, 2;


QUERY PLAN


GroupAggregate  (cost=0.42..9992.55 rows=955 width=48) (actual time=0.037..210.467 rows=12998 loops=1)

  Group Key: s.store_id, s.product_id

  ->  Subquery Scan on s  (cost=0.42..9935.98 rows=999 width=44) (actual time=0.021..175.447 rows=12998 loops=1)

        Filter: (s.day_range && '[2013-01-01,2013-01-16)'::daterange)

        Rows Removed by Filter: 86919

        ->  WindowAgg  (cost=0.42..8687.02 rows=99917 width=48) (actual time=0.020..158.387 rows=99917 loops=1)

              ->  Index Scan using stock_store_id_product_id_day_key on stock  (cost=0.42..6189.10 rows=99917 width=16) (actual time=0.009..57.255 rows=99917 loops=1)

Planning Time: 0.175 ms

Execution Time: 211.266 ms



EXPLAIN


-- Erwin MV query plan (wihtout LIMIT!)
EXPLAIN ANALYZE
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inlcusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT s.store_id, s.product_id, s.value, s.day_range * x.day_range AS days
   FROM   mv_stock s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2
ORDER  BY 1, 2;


QUERY PLAN


Sort  (cost=2607.30..2626.40 rows=7641 width=48) (actual time=38.900..39.616 rows=12998 loops=1)

  Sort Key: s.store_id, s.product_id

  Sort Method: quicksort  Memory: 994kB

  ->  HashAggregate  (cost=1961.65..2114.47 rows=7641 width=48) (actual time=27.178..33.434 rows=12998 loops=1)

        Group Key: s.store_id, s.product_id

        Batches: 1  Memory Usage: 1425kB

        ->  Bitmap Heap Scan on mv_stock s  (cost=520.60..1456.87 rows=13461 width=26) (actual time=4.843..7.776 rows=12998 loops=1)

              Recheck Cond: (day_range && '[2013-01-01,2013-01-16)'::daterange)

              Heap Blocks: exact=735

              ->  Bitmap Index Scan on mv_stock_range_idx  (cost=0.00..517.24 rows=13461 width=0) (actual time=4.753..4.754 rows=12998 loops=1)

                    Index Cond: (day_range && '[2013-01-01,2013-01-16)'::daterange)

Planning Time: 0.182 ms

Execution Time: 40.152 ms



EXPLAIN


CREATE INDEX mv_stock_range_plus_idx ON mv_stock USING gist (day_range) INCLUDE (store_id, product_id, value);

CREATE INDEX


-- Erwin MV query plan (wihtout LIMIT!)
EXPLAIN ANALYZE
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inlcusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT s.store_id, s.product_id, s.value, s.day_range * x.day_range AS days
   FROM   mv_stock s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2
ORDER  BY 1, 2;


QUERY PLAN


Sort  (cost=2174.28..2193.38 rows=7641 width=48) (actual time=40.515..41.230 rows=12998 loops=1)

  Sort Key: s.store_id, s.product_id

  Sort Method: quicksort  Memory: 994kB

  ->  HashAggregate  (cost=1528.63..1681.46 rows=7641 width=48) (actual time=28.874..35.080 rows=12998 loops=1)

        Group Key: s.store_id, s.product_id

        Batches: 1  Memory Usage: 1425kB

        ->  Index Only Scan using mv_stock_range_plus_idx on mv_stock s  (cost=0.28..1023.85 rows=13461 width=26) (actual time=0.147..9.272 rows=12998 loops=1)

              Index Cond: (day_range && '[2013-01-01,2013-01-16)'::daterange)

              Heap Fetches: 0

Planning Time: 0.262 ms

Execution Time: 41.763 ms



EXPLAIN


-- Erwin MV query plan (wihtout LIMIT!)
EXPLAIN ANALYZE
WITH input(a,b) AS (SELECT '2013-01-01'::date   -- your time frame here
                         , '2013-01-15'::date)  -- inlcusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT s.store_id, s.product_id, s.value, s.day_range * x.day_range AS days
   FROM   mv_stock s
   JOIN  (
      SELECT daterange(a, b+1) FROM input
      ) x(day_range) ON s.day_range && x.day_range
   ) sub
GROUP  BY 1, 2


QUERY PLAN


HashAggregate  (cost=1528.63..1681.46 rows=7641 width=48) (actual time=28.980..35.163 rows=12998 loops=1)

  Group Key: s.store_id, s.product_id

  Batches: 1  Memory Usage: 1425kB

  ->  Index Only Scan using mv_stock_range_plus_idx on mv_stock s  (cost=0.28..1023.85 rows=13461 width=26) (actual time=0.132..9.379 rows=12998 loops=1)

        Index Cond: (day_range && '[2013-01-01,2013-01-16)'::daterange)

        Heap Fetches: 0

Planning Time: 0.181 ms

Execution Time: 35.676 ms



EXPLAIN


-- joop query plan (wihtout LIMIT!)
EXPLAIN ANALYZE
WITH calendar(day) AS ( SELECT generate_series('2013-01-01'::date, '2013-01-15'::date, '1 d'::interval)::date )
SELECT st.store_id,st.product_id
      , (SUM(st.value)::decimal(8,2) / COUNT(*) )::decimal(8,2) AS wval
FROM calendar
JOIN stock st ON calendar.day >= st.day
        AND NOT EXISTS ( -- this calendar entry belongs to the next stock entry 
                SELECT * FROM stock nx
                WHERE nx.store_id = st.store_id AND nx.product_id = st.product_id
                AND nx.day > st.day AND nx.day <= calendar.day
        )
GROUP BY st.store_id,st.product_id
ORDER BY st.store_id,st.product_id;


QUERY PLAN


Sort  (cost=2930811.88..2930836.86 rows=9992 width=22) (actual time=1712.592..1713.323 rows=12998 loops=1)

  Sort Key: st.store_id, st.product_id

  Sort Method: quicksort  Memory: 892kB

  ->  HashAggregate  (cost=2929923.27..2930148.09 rows=9992 width=22) (actual time=1700.089..1706.724 rows=12998 loops=1)

        Group Key: st.store_id, st.product_id

        Batches: 1  Memory Usage: 1425kB

        ->  Hash Anti Join  (cost=3135.34..2633872.90 rows=29605037 width=12) (actual time=376.388..1643.413 rows=194970 loops=1)

              Hash Cond: ((st.store_id = nx.store_id) AND (st.product_id = nx.product_id))

              Join Filter: ((nx.day > st.day) AND (nx.day <= (((generate_series(('2013-01-01'::date)::timestamp with time zone, ('2013-01-15'::date)::timestamp with time zone, '1 day'::interval)))::date)))

              Rows Removed by Join Filter: 1498755

              ->  Nested Loop  (cost=0.42..1420632.02 rows=33305667 width=20) (actual time=340.663..1196.145 rows=1498755 loops=1)

                    ->  Result  (cost=0.00..25.02 rows=1000 width=4) (actual time=340.618..340.718 rows=15 loops=1)

                          ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=8) (actual time=340.614..340.698 rows=15 loops=1)

                                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=340.581..340.582 rows=1 loops=1)

                    ->  Index Scan using stock_store_id_product_id_day_key on stock st  (cost=0.42..1087.55 rows=33306 width=16) (actual time=0.011..45.852 rows=99917 loops=15)

                          Index Cond: (day <= (((generate_series(('2013-01-01'::date)::timestamp with time zone, ('2013-01-15'::date)::timestamp with time zone, '1 day'::interval)))::date))

              ->  Hash  (cost=1636.17..1636.17 rows=99917 width=12) (actual time=34.445..34.446 rows=99917 loops=1)

                    Buckets: 131072  Batches: 1  Memory Usage: 5318kB

                    ->  Seq Scan on stock nx  (cost=0.00..1636.17 rows=99917 width=12) (actual time=0.019..10.117 rows=99917 loops=1)

Planning Time: 0.298 ms

JIT:

  Functions: 26

  Options: Inlining true, Optimization true, Expressions true, Deforming true

  Timing: Generation 1.948 ms, Inlining 106.791 ms, Optimization 127.281 ms, Emission 106.635 ms, Total 342.654 ms

Execution Time: 1932.971 ms



EXPLAIN
store_id	product_id	avg_value
0	0	null
0	1	null
0	2	null
0	3	null
0	4	null
0	5	null
0	6	null
0	7	null
0	8	null
0	9	null
product_id	days_in_range	my_result
0	15	68.00
1	15	44.00
2	15	41.00
3	15	61.00
4	15	57.00
5	15	49.00
6	15	18.00
7	15	92.00
8	15	1.00
9	15	16.00