Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable
    (record_date date, country varchar(5), objectid int, objectuse int);
--create index on mytable (record_date, country);

INSERT INTO mytable
    (record_date, country, objectid, objectuse)
VALUES
    ('2022-07-20 00:00:00', 'chile', 0, 4),
    ('2022-07-01 00:00:00', 'chile', 1, 4),
    ('2022-07-02 00:00:00', 'chile', 1, 4),
    ('2022-07-03 00:00:00', 'chile', 1, 5),
    ('2022-07-04 00:00:00', 'chile', 1, 4),

    ('2022-07-01 00:00:00', 'chile', 2, 4),
    ('2022-07-02 00:00:00', 'chile', 2, 4),
    ('2022-07-03 00:00:00', 'chile', 2, 5),
    ('2022-07-04 00:00:00', 'chile', 2, 4),
    
    ('2022-07-01 00:00:00', 'chile', 3, 4),
    ('2022-07-02 00:00:00', 'chile', 3, 4),
    ('2022-07-03 00:00:00', 'chile', 3, 5),
    ('2022-07-04 00:00:00', 'chile', 3, 4),

    ('2022-07-01 00:00:00', 'A', 2, 4),
    ('2022-07-02 00:00:00', 'A', 2, 4),
    ('2022-07-03 00:00:00', 'A', 2, 5),
    ('2022-07-04 00:00:00', 'A', 2, 4),
    
    ('2022-07-01 00:00:00', 'A', 3, 4),
    ('2022-07-02 00:00:00', 'A', 3, 4),
    ('2022-07-03 00:00:00', 'A', 3, 5),
    ('2022-07-04 00:00:00', 'A', 3, 4),

    ('2022-07-26 00:00:00', 'peru', 3088, 4),
    ('2022-07-27 00:00:00', 'peru', 3088, 4),
    ('2022-07-28 00:00:00', 'peru', 3088, 4),
    ('2022-07-30 00:00:00', 'peru', 3088, 4),
    ('2022-07-31 00:00:00', 'peru', 3088, 4);

CREATE TABLE



INSERT 0 26


explain WITH daily_lookback AS (
    SELECT
        A.record_date,
        A.country,
        B.objectid,
        MAX(B.objectuse) AS maxuse
    FROM mytable AS A
    LEFT JOIN mytable AS B
        ON A.record_date >= B.record_date
        AND A.country = B.country
        AND DATE_PART('month', A.record_date) = DATE_PART('month', B.record_date)
        AND DATE_PART('year', A.record_date) = DATE_PART('year', B.record_date)
    GROUP BY
        A.record_date,
        A.country,
        B.objectid
)
SELECT
    record_date,
    country,
    SUM(maxuse) AS usetotal
FROM daily_lookback
GROUP BY 
    record_date,
    country
ORDER BY
    record_date;


QUERY PLAN


Sort  (cost=309.83..310.33 rows=200 width=36)

  Sort Key: a.record_date

  ->  HashAggregate  (cost=300.18..302.18 rows=200 width=36)

        Group Key: a.record_date, a.country

        ->  HashAggregate  (cost=265.26..277.96 rows=1270 width=36)

              Group Key: a.record_date, a.country, b.objectid

              ->  Merge Left Join  (cost=176.34..252.56 rows=1270 width=36)

                    Merge Cond: (((a.country)::text = (b.country)::text) AND ((date_part('month'::text, (a.record_date)::timestamp without time zone)) = (date_part('month'::text, (b.record_date)::timestamp without time zone))) AND ((date_part('year'::text, (a.record_date)::timestamp without time zone)) = (date_part('year'::text, (b.record_date)::timestamp without time zone))))

                    Join Filter: (a.record_date >= b.record_date)

                    ->  Sort  (cost=88.17..91.35 rows=1270 width=28)

                          Sort Key: a.country, (date_part('month'::text, (a.record_date)::timestamp without time zone)), (date_part('year'::text, (a.record_date)::timestamp without time zone))

                          ->  Seq Scan on mytable a  (cost=0.00..22.70 rows=1270 width=28)

                    ->  Sort  (cost=88.17..91.35 rows=1270 width=36)

                          Sort Key: b.country, (date_part('month'::text, (b.record_date)::timestamp without time zone)), (date_part('year'::text, (b.record_date)::timestamp without time zone))

                          ->  Seq Scan on mytable b  (cost=0.00..22.70 rows=1270 width=36)



EXPLAIN


explain select record_date, country, min(usetotal) as usetotal from
--(select distinct record_date, country from mytable) d
mytable d inner join lateral
(
    select distinct sum(max(objectuse)) over () as usetotal from mytable a
    where a.record_date between date_trunc('month', d.record_date) and d.record_date
      and a.country = d.country
    group by objectid
) T on 1 = 1
group by record_date, country
order by record_date, country;


QUERY PLAN


GroupAggregate  (cost=49180.02..49194.72 rows=200 width=36)

  Group Key: d.record_date, d.country

  ->  Sort  (cost=49180.02..49183.20 rows=1270 width=36)

        Sort Key: d.record_date, d.country

        ->  Nested Loop  (cost=38.63..49114.55 rows=1270 width=36)

              ->  Seq Scan on mytable d  (cost=0.00..22.70 rows=1270 width=28)

              ->  Unique  (cost=38.63..38.63 rows=1 width=12)

                    ->  Sort  (cost=38.63..38.63 rows=1 width=12)

                          Sort Key: (sum((max(a.objectuse))) OVER (?))

                          ->  WindowAgg  (cost=38.59..38.62 rows=1 width=12)

                                ->  GroupAggregate  (cost=38.59..38.60 rows=1 width=8)

                                      Group Key: a.objectid

                                      ->  Sort  (cost=38.59..38.59 rows=1 width=8)

                                            Sort Key: a.objectid

                                            ->  Seq Scan on mytable a  (cost=0.00..38.58 rows=1 width=8)

                                                  Filter: ((record_date <= d.record_date) AND ((country)::text = (d.country)::text) AND (record_date >= date_trunc('month'::text, (d.record_date)::timestamp with time zone)))



EXPLAIN


select record_date, country, min(usetotal) as usetotal from
--(select distinct record_date, country from mytable) d
mytable d inner join lateral
(
    select distinct sum(max(objectuse)) over () as usetotal from mytable a
    where a.record_date between date_trunc('month', d.record_date) and d.record_date
      and a.country = d.country
    group by objectid
) T on 1 = 1
group by record_date, country
order by record_date, country;


record_date
country
usetotal


2022-07-01
A
8

2022-07-01
chile
12

2022-07-02
A
8

2022-07-02
chile
12

2022-07-03
A
10

2022-07-03
chile
15

2022-07-04
A
10

2022-07-04
chile
15

2022-07-20
chile
19

2022-07-26
peru
4

2022-07-27
peru
4

2022-07-28
peru
4

2022-07-30
peru
4

2022-07-31
peru
4



SELECT 14
record_date	country	usetotal
2022-07-01	A	8
2022-07-01	chile	12
2022-07-02	A	8
2022-07-02	chile	12
2022-07-03	A	10
2022-07-03	chile	15
2022-07-04	A	10
2022-07-04	chile	15
2022-07-20	chile	19
2022-07-26	peru	4
2022-07-27	peru	4
2022-07-28	peru	4
2022-07-30	peru	4
2022-07-31	peru	4