Postgresql – Gap Finding – Dates

gaps-and-islandspostgresql-9.5

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps.

Data is timestamp without timezone formatted and an example is the following 10 dates:

CREATE TABLE test
(
    measurement_timestamp timestamp without time zone
)
WITH (
    OIDS = FALSE
);

INSERT INTO test (measurement_timestamp ) VALUES

'2019-11-14 08:22:00',
'2019-11-14 11:10:00',
'2019-11-14 11:11:00',
'2019-11-14 11:12:00',
'2019-11-14 11:13:00',
'2019-11-14 11:15:00',
'2019-11-14 11:16:00',
'2019-11-14 11:17:00',
'2019-11-14 11:18:00',
'2019-11-14 11:19:00';

I need to find the following elements: gap > 1 minute

GAPID (can be any sequential number or ignored if needed) StartDateTime,EndDateTime,Gapsize in minutes

Example:

(1,'2019-11-14 08:22:00','2019-11-14 11:10:00', 168)

previous questions asked commonly have a grouping based on a member or other but i simply need the gap in a sequence to find my datagaps. Data attached to the date cannot be used for grouping and cannot be shared nor do i really care. i just need to find the gaps > 1 minute.

EDIT:

Im now looking for ISLAND size:

so assuming i have same list as before i now would like the following output:

'GAP','2019-11-14 08:22:00','2019-11-14 11:10:00', 168
'ISLAND','2019-11-14 11:10:00','2019-11-14 11:13:00', 3
'GAP','2019-11-14 11:13:00','2019-11-14 11:15:00',2
'ISLAND','2019-11-14 11:15:00','2019-11-14 11:19:00',5

can that be done via sql as well?

Best Answer

You need to first calculate the gap and then use that in a second level to calculate the longest gap (this can't be done in a single statement, as you can't nest window function calls):

select *
from (
  select rn, 
         prev_measurement,
         measurement_timestamp,
         gap,
         dense_rank() over (order by gap desc) as rnk
  from (
    select row_number() over w as rn,
           measurement_timestamp,
           lag(measurement_timestamp) over w as prev_measurement,
           measurement_timestamp - lag(measurement_timestamp) over w as gap
    from test
    window w as (order by measurement_timestamp)
  ) t1
  where gap > interval '1 minute'
) t2
where rnk = 1;

Online example

Related Solutions

Sql-server – Performance tuning for gap analysis of table

Use a different method.

For a start, don't populate a temporary table with 2.7M rows - that's not going to want to return in under ten seconds. You could use a CTE instead, and that might work much better.

WITH taps as (
SELECT 
RowID = ROW_NUMBER() OVER (ORDER BY DeviceESN, CreatedDateUTC, [Counter]),  
DeviceESN, 
TapDateUTC,
CreatedDateUTC,
[Counter]
FROM TapGapDetail
WHERE CreatedDateUTC >= @FilterDateUtc
)

--results
select  
    t.DeviceESN as Validator
    ,sum(
        case 
            --They are in sequence
            when t2.[Counter] = t.[Counter]+ 1 then 0 
            --A reset has occured
            when t2.[Counter] < t.[Counter] then 0 
            --A gap exists. Find the difference
            else (t2.[Counter] - t.[Counter] - 1) 
        end) 
        as TapGaps
    ,case 
        --gets the last tap date per validator
        when MAX(t.TapDateUTC) > MAX(t2.TapDateUTC) THEN MAX(T.TapDateUTC) 
        ELSE MAX(t2.TapDateUTC) 
    END 
    AS MaxTapDate
    ,case 
        --gets the last tap date per validator
        when MAX(t.CreatedDateUTC) > MAX(t2.CreatedDateUTC) THEN MAX(T.CreatedDateUTC) 
        ELSE MAX(t2.CreatedDateUTC) 
    END 
    AS MaxCreatedDate
from taps t 
    inner join taps t2 on t.DeviceESN = t2.DeviceESN and t2.RowID = t.RowID + 1
where t2.[Counter] != t.[Counter] + 1
 and t2.[Counter] > t.[Counter]
 And t.CreatedDateUTC >= @FilterDateUtc
 And t2.CreatedDateUTC >= @FilterDateUtc
 And (t.DeviceESN = @Validator Or @Validator = '')
 And Not Exists --edge case for when there is a gap at the end, tried with left join and stats are the same, so this is easier to read I think.
        (Select Top 1 Null From TapGapDetail tgd Where tgd.DeviceEsn = t.DeviceESN And (t.Counter + 1) = tgd.Counter 
            And tgd.CreatedDateUTC >= DateAdd(day, -1 * @ToleranceHours, t.CreatedDateUTC)
            And tgd.CreatedDateUTC <= DateAdd(day, @ToleranceHours, t.CreatedDateUTC))
group by t.DeviceESN
Order By MaxCreatedDate Desc, t.DeviceEsn

I'm not saying this is necessarily terrific (the "NOT EXISTS" bit could be a pain), but it's almost certainly an improvement.

Mysql – Running out of disk space running MySQL query

Your problem is due to 2 reported bugs:

When using UNION or UNION ALL MySQL creates a temporary working table. While it is justifiable for UNION, it is unnecessary for UNION ALL

mysqld uses a temp table for both UNION and UNION ALL processing. The temp table for UNION ALL is not needed as results can be immediately returned to the client. That change would save the cost of writing the temp table and possibly spilling it to disk. It would also return the first-N rows to the client much faster.

Bug #50674: Do not create temporary tables for UNION ALL

This bug was scheduled to be fixed in ver. 5.7.3.

Furthermore, self-join tables are reopened for every call:

In self joins, unions, etc. - tables are re-opened for each mention.

If a UNION (or self-joins) have e.g. 1000 mentions of same table, then 1000 internal structures will be created, with additional file descriptors and buffers per each.

This leads to a simple 100k-sized query to allocate 500MB of memory just by mentioning same (even empty) table in it (e.g. SELECT * FROM table UNION SELECT * FROM table ...).

Bug #44626: Tables are reopened multiple times if used in same query, buffers allocated too

So in your case it creates 38 temporary tables consuming lots of resources.

One possible solution

is to use dynamic SQL and CURSOR to go through every of 38 columns. This way you don't need a view with 38 UNION ALL statements.

Best Answer

Related Solutions

Sql-server – Performance tuning for gap analysis of table

Mysql – Running out of disk space running MySQL query

Related Question