pwF66tjE

1
2DROP TABLE IF EXISTS segment_ios_sessions_info;
3CREATE TABLE segment_ios_sessions_info AS
4SELECT
5   ROW_NUMBER() OVER(PARTITION BY lag.distinct_id ORDER BY lag.event_time) || '-' || lag.distinct_id AS session_id
6 , lag.distinct_id
7 , lag.event_time AS session_start
8 , NULL::timestamp AS session_end
9 , NULL::integer AS num_events
10 , ROW_NUMBER() OVER(PARTITION BY lag.distinct_id ORDER BY lag.event_time) AS session_seq_number
11 , COALESCE(LEAD(lag.event_time) OVER(PARTITION BY lag.distinct_id ORDER BY lag.event_time), '3000-01-01') AS next_session_start
12FROM (SELECT
13         seg.id_all AS event_id
14       , seg.event_name
15       , seg.distinct_id AS distinct_id
16       , seg.segment_timestamp AS event_time
17       , DATEDIFF(seconds,
18                  LAG(seg.new_segment_timestamp) OVER(PARTITION BY seg.distinct_id ORDER BY seg.new_segment_timestamp),
19                  seg.new_segment_timestamp) AS idle_time
20      FROM (
21        SELECT *,
22               CASE WHEN segment_timestamp = LAG(segment_timestamp) OVER
23                    (PARTITION BY distinct_id ORDER BY segment_timestamp)
24                    THEN segment_timestamp + interval '0.00001 seconds' * ROW_NUMBER() OVER
25                        (PARTITION BY distinct_id ORDER BY segment_timestamp)
26                    ELSE segment_timestamp END
27                AS new_segment_timestamp
28        FROM segment_ios_all
29    ) AS seg
30    ) AS lag
31WHERE (lag.idle_time > 1800 OR lag.idle_time IS NULL)
32order by 2, 3 asc;
33
34CREATE TABLE IF NOT EXISTS "public"."segment_ios_sessions"
35(
36   "id_all" VARCHAR(35)   ENCODE lzo
37  ,"distinct_id" VARCHAR(255)
38  ,"session_id" VARCHAR(255)   ENCODE lzo
39  ,"session_num" INT   ENCODE bytedict
40  ,"segment_timestamp" TIMESTAMP WITHOUT TIME ZONE   ENCODE lzo
41)
42  DISTSTYLE KEY
43  DISTKEY ("distinct_id")
44  SORTKEY ("id_all")
45;
46
47TRUNCATE TABLE segment_ios_sessions;
48INSERT INTO segment_ios_sessions (
49SELECT e.id_all,
50       e.distinct_id,
51       s.session_id,
52       s.session_seq_number AS session_num,
53       e.segment_timestamp
54FROM
55    segment_ios_all e
56INNER JOIN segment_ios_sessions_info s
57    ON e.distinct_id = s.distinct_id
58    AND e.segment_timestamp >= s.session_start
59    AND e.segment_timestamp < s.next_session_start);
60
61UPDATE segment_ios_sessions_info
62SET session_end = comp.session_end,
63    num_events = comp.num_events
64FROM
65    (SELECT s.session_id AS session_id,                                                                                                                      
66           LEAST(MAX(e.segment_timestamp) + INTERVAL '5 minutes', MIN(s.next_session_start)) AS session_end,
67           count(distinct e.id_all) AS num_events
68    FROM
69        segment_ios_sessions_info s
70        LEFT JOIN segment_ios_sessions e ON (s.session_id = e.session_id)
71    GROUP BY s.session_id) AS comp
72WHERE comp.session_id = segment_ios_sessions_info.session_id;