Question on how to generate row for every time segment if entry not present

user_11019985 0
Viewed 24

I have a table in PostgreSQL that holds user summary for every 15-min segment, but not all the 15-time segments would be populated. I need to run a query that not only pulls data from the table, but also generate new entries for missing 15-min segments.

Is this possible to generate output in a single query? If not, should I go with stored procedures? Any examples? Thanks a lot in advance.

See the example here...

CREATE TABLE access_log_summary (
        user VARCHAR ( 50 ) NOT NULL,
        bucket TIMESTAMP NOT NULL
);      
INSERT INTO access_log_summary(user, bucket) VALUES ('john', '2021-01-01 00:15:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('john', '2021-01-01 00:30:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('john', '2021-01-01 01:00:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('john', '2021-01-01 01:15:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('john', '2021-01-01 02:30:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('mark', '2021-01-01 00:15:00');
INSERT INTO access_log_summary(user, bucket) VALUES ('mark', '2023-01-01 00:30:00');

Notice that both 'john' and 'mark' does not have entries every 15 minutes.

At this point, I want to run a query to not only generate data from the table, but also generate new entries for missing records. For example,

select (values + new data) 
from access_log_summary 
order by bucket 
where bucket between ('2021-01-01', '2021-01-05');

-----------   ------------------------   ------
User        |  bucket                   | Exist
-----------   ------------------------   ------
john           2021-01-01 00:15:00       Y
mark           2021-01-01 00:15:00       Y
john           2021-01-01 00:30:00       Y
mark           2021-01-01 00:30:00       Y
john           2021-01-01 00:45:00       N
john           2021-01-01 01:00:00       Y
john           2021-01-01 01:15:00       Y
john           2021-01-01 01:30:00       N
john           2021-01-01 01:45:00       N
john           2021-01-01 02:00:00       N
john           2021-01-01 02:15:00       N
john           2021-01-01 02:30:00       Y
john           2021-01-01 02:45:00       N
....
....

Once again, thanks for trying to help.

postgresql sql
1 Answers

If you want rows for all names for a time period, you can use cross join with generate_series() and then a left join:

select u.user, gs.bucket, (als.user is not null) as exists_flag
from (select distinct user from access_log_summary
     ) u cross join
     generate_series('2021-01-01 00:15:00'::timestamp,
                     '2021-01-01 01:15:00'::timestamp,
                     interval '15 minute'
                     ) gs(bucket) left join
     access_log_summary als
     on als.user = u.user and
        als.bucket = gs.bucket;

Note: user is a reserved word in Postgres; I assume your real data has a more appropriate name.

Here is a db<>fiddle.

user_11440350
Related