Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/q/78147985/5298879
/*
I have a table that has daily data and thousands of rows per day. 
However, some days are not present due to holidays.
I need a query that would check if the initial date has data 
and if it does retrieve the data for the previous 5 days of data. 
Example: If I put in todays date 2024/3/12, 
I would need to check if 2024/3/12 has data, 
if yes, retrieve data for March 7,8,9,10,11 and 12. 
However, if let's say the 8th is a holiday and there was no data, 
I would need data for 6,7,9,10,11,12.
*/
create table my_table(visitors_count int, mydate date);

select setseed(.42);

insert into my_table 
select (random()*100)::int, 
       current_date+(-random()*180)::int
from generate_series(1,9e5);

delete from my_table where mydate = '2024-03-08';

create index on my_table (mydate);

select * from my_table tablesample bernoulli(.42)repeatable(.42)limit 12;

CREATE TABLE

setseed

SELECT 1

INSERT 0 900000

DELETE 5058

CREATE INDEX

visitors_count
mydate

80
2023-12-12

28
2023-11-07

37
2024-02-15

0
2024-03-03

95
2024-01-26

27
2023-12-13

41
2024-02-04

86
2023-10-01

94
2023-11-03

12
2023-12-11

54
2024-02-29

19
2024-02-04

SELECT 12

vacuum analyze my_table;

VACUUM

--https://stackoverflow.com/a/78148773/5298879
prepare last5dates as
with last5dates as (
    select distinct mydate
    from my_table mdt
    where mdt.mydate <= '2024-03-12'
    order by mdt.mydate desc
    limit 6
    )    
select distinct mydate
from my_table mdt
where exists(select from last5dates last5
             where last5.mydate = mdt.mydate)
order by mydate;

explain analyze verbose execute last5dates;

execute last5dates;

PREPARE

QUERY PLAN

Sort  (cost=1726.17..1726.62 rows=180 width=4) (actual time=11.983..11.985 rows=6 loops=1)

  Output: mdt.mydate

  Sort Key: mdt.mydate

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=1717.63..1719.43 rows=180 width=4) (actual time=11.976..11.979 rows=6 loops=1)

        Output: mdt.mydate

        Group Key: mdt.mydate

        Batches: 1  Memory Usage: 40kB

        ->  Nested Loop  (cost=0.85..1643.05 rows=29831 width=4) (actual time=0.018..8.349 rows=27638 loops=1)

              Output: mdt.mydate

              ->  Limit  (cost=0.42..700.12 rows=6 width=4) (actual time=0.011..3.144 rows=6 loops=1)

                    Output: mdt_1.mydate

                    ->  Unique  (cost=0.42..20991.26 rows=180 width=4) (actual time=0.010..3.141 rows=6 loops=1)

                          Output: mdt_1.mydate

                          ->  Index Only Scan Backward using my_table_mydate_idx on public.my_table mdt_1  (cost=0.42..18753.91 rows=894942 width=4) (actual time=0.009..1.940 rows=22529 loops=1)

                                Output: mdt_1.mydate

                                Index Cond: (mdt_1.mydate <= '2024-03-12'::date)

                                Heap Fetches: 0

              ->  Index Only Scan using my_table_mydate_idx on public.my_table mdt  (cost=0.42..107.43 rows=4972 width=4) (actual time=0.004..0.455 rows=4606 loops=6)

                    Output: mdt.mydate

                    Index Cond: (mdt.mydate = mdt_1.mydate)

                    Heap Fetches: 0

Planning Time: 0.375 ms

Execution Time: 12.045 ms

EXPLAIN

mydate

2024-03-06

2024-03-07

2024-03-09

2024-03-10

2024-03-11

2024-03-12

SELECT 6

--https://stackoverflow.com/a/78148773/5298879
prepare last5dates2 as
with last5dates as (
    select distinct mydate
    from my_table mdt
    where mdt.mydate <= '2024-03-12'
    order by mdt.mydate desc
    limit 6 )    
select distinct mydate
from my_table 
inner join last5dates using (mydate)
order by mydate;

explain analyze verbose execute last5dates;

execute last5dates;

PREPARE

QUERY PLAN

Sort  (cost=1726.17..1726.62 rows=180 width=4) (actual time=12.434..12.435 rows=6 loops=1)

  Output: mdt.mydate

  Sort Key: mdt.mydate

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=1717.63..1719.43 rows=180 width=4) (actual time=12.429..12.432 rows=6 loops=1)

        Output: mdt.mydate

        Group Key: mdt.mydate

        Batches: 1  Memory Usage: 40kB

        ->  Nested Loop  (cost=0.85..1643.05 rows=29831 width=4) (actual time=0.016..8.662 rows=27638 loops=1)

              Output: mdt.mydate

              ->  Limit  (cost=0.42..700.12 rows=6 width=4) (actual time=0.010..3.260 rows=6 loops=1)

                    Output: mdt_1.mydate

                    ->  Unique  (cost=0.42..20991.26 rows=180 width=4) (actual time=0.010..3.258 rows=6 loops=1)

                          Output: mdt_1.mydate

                          ->  Index Only Scan Backward using my_table_mydate_idx on public.my_table mdt_1  (cost=0.42..18753.91 rows=894942 width=4) (actual time=0.009..2.035 rows=22529 loops=1)

                                Output: mdt_1.mydate

                                Index Cond: (mdt_1.mydate <= '2024-03-12'::date)

                                Heap Fetches: 0

              ->  Index Only Scan using my_table_mydate_idx on public.my_table mdt  (cost=0.42..107.43 rows=4972 width=4) (actual time=0.004..0.475 rows=4606 loops=6)

                    Output: mdt.mydate

                    Index Cond: (mdt.mydate = mdt_1.mydate)

                    Heap Fetches: 0

Planning Time: 0.002 ms

Execution Time: 12.461 ms

EXPLAIN

mydate

2024-03-06

2024-03-07

2024-03-09

2024-03-10

2024-03-11

2024-03-12

SELECT 6

--https://stackoverflow.com/a/78148666/5298879
--check which dates it took
prepare data_by_days_back as
with data_by_days_back as (
  select *,dense_rank()over w1 as days_back 
  from my_table 
  where mydate<='2024-03-12' --start date
  window w1 as (order by mydate desc) )
select distinct mydate, days_back --checking which dates got caught
from data_by_days_back
where days_back<=6 --number of non-empty days back from the start 
order by mydate;

explain analyze verbose execute data_by_days_back;

execute data_by_days_back;

PREPARE

QUERY PLAN

Sort  (cost=48686.60..48776.60 rows=36000 width=12) (actual time=22.690..22.692 rows=6 loops=1)

  Output: my_table.mydate, (dense_rank() OVER (?))

  Sort Key: my_table.mydate

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=45602.17..45962.17 rows=36000 width=12) (actual time=22.451..22.684 rows=6 loops=1)

        Output: my_table.mydate, (dense_rank() OVER (?))

        Group Key: my_table.mydate, dense_rank() OVER (?)

        Batches: 1  Memory Usage: 1561kB

        ->  WindowAgg  (cost=0.42..32178.04 rows=894942 width=16) (actual time=0.025..16.999 rows=27638 loops=1)

              Output: NULL::integer, my_table.mydate, dense_rank() OVER (?)

              Run Condition: (dense_rank() OVER (?) <= 6)

              ->  Index Only Scan Backward using my_table_mydate_idx on public.my_table  (cost=0.42..18753.91 rows=894942 width=4) (actual time=0.010..2.860 rows=27639 loops=1)

                    Output: my_table.mydate

                    Index Cond: (my_table.mydate <= '2024-03-12'::date)

                    Heap Fetches: 0

Planning Time: 0.181 ms

Execution Time: 22.884 ms

EXPLAIN

mydate
days_back

2024-03-06
6

2024-03-07
5

2024-03-09
4

2024-03-10
3

2024-03-11
2

2024-03-12
1

SELECT 6
visitors_count	mydate
80	2023-12-12
28	2023-11-07
37	2024-02-15
0	2024-03-03
95	2024-01-26
27	2023-12-13
41	2024-02-04
86	2023-10-01
94	2023-11-03
12	2023-12-11
54	2024-02-29
19	2024-02-04
mydate	days_back
2024-03-06	6
2024-03-07	5
2024-03-09	4
2024-03-10	3
2024-03-11	2
2024-03-12	1