I want to get starttime minimum and endtime maximum on consecutive dates(must be the same month) , but all dates have to be consecutive.

I want to combine them in one row. How can I get the following table?(Table 2)

This is actually what I want:

CodePudding user response：

Use LAG(endtime) or LEAD(starttime) OVER (PARTITION BY ip_address ORDER BY starttime) on a first pass so that each row can see the date from its neighboring row ( you can go in either direction). Then use a CASE or DECODE to test whether the dates are contiguous or not. If they are not, then emit the date, else emit NULL. Then in a parent block, you can use LAST_VALUE() with IGNORE NULLS option and ROWS BETWEEN UNBOUNDED PRECEDING TO CURRENT ROW to get the most recent of those conditionally computed dates. That can then be used in a GROUP BY in yet a third parent query block to finalize your results.

CodePudding user response：

Or the classical MATCH_RECOGNIZE for the merging of intervals:

select * from data 
match_recognize(
    partition by ipaddress
    order by starttime, endtime
    measures first(starttime) as starttime, max(endtime) as endtime
    pattern( merged* strt )
    define
        merged as endtime = next(starttime) - 1 
);