I have a table representing files and the time spans they cover like so:
filename | start_time | end_time | format | ----------+---------------------+---------------------+--------| file1.tar | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | raw | file2.nc | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | nc | file3.raw | 2017-07-19 06:00:36 | 2017-07-19 06:01:44 | raw | file4.tar | 2018-08-20 00:00:01 | 2018-08-20 23:59:59 | raw | file5.cdf | 2018-08-20 00:00:01 | 2018-08-20 11:59:59 | cdf |
I'm trying to find the timespans for which we have files in raw format, but no file in a non-raw format (basically, any
format value except "raw").
Given the example data above, the result would look like this:
start_time | end_time | --------------------+---------------------+ 2017-07-19 06:00:36 | 2017-07-19 06:01:44 | 2018-08-20 12:00:00 | 2018-08-20 23:59:59 |
Here is the logic behind this:
- The timeframe from raw file1 is entirely covered by non-raw file2, so do not include it
- The timeframe from raw file3 has no non-raw files, so include it entirely
- The timeframe from raw file4 is partially covered by non-raw file 5, so include the uncovered portion
This is a small part of a large hairy query (involving versions, soft-delete status, location, and etc) that I'm trying to narrow down to just the part I'm having trouble with so please let me know if anything else seems strange or unclear.
From the comments:
- There are ~29 million rows in the source table
- I've removed to version information to make it more clear what the problem is
- The format column will be either 'raw' or some other string. For the purposes of the question, we only care about raw or not-raw
- The filename will not always contain 'raw' but the format column will for raw files
- A result of
tsranges is also fine, maybe even preferable
This is pretty close, and produces the two "not-fully-covered" rows, but produces the whole time span from 2018-08-20 when I want only the 12 uncovered hours.
with base as ( --create tsranges and collapse the formats select filename, case when format = 'raw' then 'raw' else 'not_raw' end as format, tsrange(start_time, end_time, '[)') as time_span from datafiles ), a as ( --compute the running max of the upper bound select time_span, format, coalesce(lower(time_span), '-infinity') as range_start, max(coalesce(upper(time_span), 'infinity')) over (partition by format order by time_span) as range_end from base ), b as ( --if the previous range_end is earlier than the range_start, there is a gap, start a new range select *, lag(range_end) over (partition by format order by time_span) < range_start or null as next_range_start from a ), c as ( --tag group numbers by counting how many next_range_starts have passed select *, count(next_range_start) over (partition by format order by time_span) as grp from b ), d as ( --group by group number, make the final range from the highest and lowest in each range select format, tsrange(min(range_start), max(range_end)) as time_span from c group by format, grp ) select * from d raw where format = 'raw' and not exists ( select 1 from d not_raw where format = 'not_raw' and raw.time_span <@ not_raw.time_span )