Ich habe eine Tabelle mit solchen Online-Sitzungen (leere Zeilen dienen nur der besseren Übersicht):

ip_address  | start_time       | stop_time
------------|------------------|------------------
10.10.10.10 | 2016-04-02 08:00 | 2016-04-02 08:12
10.10.10.10 | 2016-04-02 08:11 | 2016-04-02 08:20

10.10.10.10 | 2016-04-02 09:00 | 2016-04-02 09:10
10.10.10.10 | 2016-04-02 09:05 | 2016-04-02 09:08
10.10.10.10 | 2016-04-02 09:05 | 2016-04-02 09:11
10.10.10.10 | 2016-04-02 09:02 | 2016-04-02 09:15
10.10.10.10 | 2016-04-02 09:10 | 2016-04-02 09:12

10.66.44.22 | 2016-04-02 08:05 | 2016-04-02 08:07
10.66.44.22 | 2016-04-02 08:03 | 2016-04-02 08:11

Und ich brauche die "Envelop" -Online-Zeitspannen:

ip_address  | full_start_time  | full_stop_time
------------|------------------|------------------
10.10.10.10 | 2016-04-02 08:00 | 2016-04-02 08:20
10.10.10.10 | 2016-04-02 09:00 | 2016-04-02 09:15
10.66.44.22 | 2016-04-02 08:03 | 2016-04-02 08:11

Ich habe diese Abfrage, die das gewünschte Ergebnis zurückgibt:

WITH t AS 
    -- Determine full time-range of each IP
    (SELECT ip_address, MIN(start_time) AS min_start_time, MAX(stop_time) AS max_stop_time FROM IP_SESSIONS GROUP BY ip_address),
t2 AS
    -- compose ticks
    (SELECT DISTINCT ip_address, min_start_time + (LEVEL-1) * INTERVAL '1' MINUTE AS ts
    FROM t
    CONNECT BY min_start_time + (LEVEL-1) * INTERVAL '1' MINUTE <= max_stop_time),
t3 AS 
    -- get all "online" ticks
    (SELECT DISTINCT ip_address, ts
    FROM t2
        JOIN IP_SESSIONS USING (ip_address)
    WHERE ts BETWEEN start_time AND stop_time),
t4 AS
    (SELECT ip_address, ts,
        LAG(ts) OVER (PARTITION BY ip_address ORDER BY ts) AS previous_ts
    FROM t3),
t5 AS 
    (SELECT ip_address, ts, 
        SUM(DECODE(previous_ts,NULL,1,0 + (CASE WHEN previous_ts + INTERVAL '1' MINUTE <> ts THEN 1 ELSE 0 END))) 
            OVER (PARTITION BY ip_address ORDER BY ts ROWS UNBOUNDED PRECEDING) session_no
    FROM t4)
SELECT ip_address, MIN(ts) AS full_start_time, MAX(ts) AS full_stop_time
FROM t5
GROUP BY ip_address, session_no
ORDER BY 1,2;

Ich bin jedoch besorgt über die Leistung. Die Tabelle enthält Hunderte von Millionen Zeilen und die Zeitauflösung beträgt Millisekunden (nicht eine Minute, wie im Beispiel angegeben). Also CTEt3 wird riesig. Hat jemand eine Lösung, die das Self-Join und "CONNECT BY" vermeidet?

A single smartAnalytische Funktion wäre großartig