背景

需要对用户的访问日志记录进行转化分析，由于日志记录没有业务session，需要物理地对访问记录进行session切分，规则是30分钟内的用户操作为一个会话（session），如果两个操作之间的时间间隔超过30分钟，则判定为两个session。
环境：GreenPlum（postgresql）

数据

/*
用户ID：bid
访问时间：event_time
页面key：event_key
*/
WITH log_data AS(
    SELECT
        bid,
        event_time::TIMESTAMP AS event_time,
        event_key,
        /* 访问序号 */
        ROW_NUMBER() OVER(PARTITION BY bid ORDER BY event_time) AS num
    FROM
    (
        SELECT '123' AS bid, '2017-11-20 10:01:56' AS event_time, 'page1' AS event_key
        UNION ALL
        SELECT '123' AS bid, '2017-11-20 10:05:56' AS event_time, 'page2' AS event_key
        UNION ALL
        SELECT '123' AS bid, '2017-11-20 10:43:56' AS event_time, 'page1' AS event_key
        UNION ALL
        SELECT '123' AS bid, '2017-11-20 10:46:56' AS event_time, 'page2' AS event_key
        UNION ALL
        SELECT '123' AS bid, '2017-11-20 10:49:56' AS event_time, 'page3' AS event_key
    )tmp
)
SELECT * FROM log_data;

图一

切分session过程

需要找到一个记录A的时间减去上一个记录B的时间差值大于30分钟，如下图中，需要找到箭头指向的那条10：43：56访问的记录

图二

问题转化为寻找每个session的起始访问记录，详细sql如下：

SELECT
    t1.*,
    t2.num,
    /* 如果记录为首次或两个记录时间差大于30分钟则打上标记 */
    CASE WHEN t2. event_time is NULL OR t1.event_time - t2.event_time > INTERVAL '30 minute' THEN t1.num ELSE null END AS time_diff
FROM
    log_data t1
LEFT JOIN
    /* 前后记录关联 */
    log_data t2 on t1.bid = t2.bid AND t1.num = t2.num + 1

根据t1.bid = t2.bid AND t1.num = t2.num + 1关联得到切分点的num，如下图中的time_diff

图三

通过time_diff is not null 得到具体的切分点

SELECT
    bid,
    time_diff,
    event_time
FROM
    (
    SELECT
        t1.*,
        t2.num,
        /* 如果记录为首次或两个记录时间差大于30分钟则打上标记 */
        CASE WHEN t2. event_time is NULL OR t1.event_time - t2.event_time > INTERVAL '30 minute' THEN t1.num ELSE null END AS time_diff
    FROM
        log_data t1
    LEFT JOIN
        /* 前后记录关联 */
        log_data t2 on t1.bid = t2.bid AND t1.num = t2.num + 1
    )t02
WHERE 
    t02.time_diff IS NOT NULL

图四

图一跟图四通过t01.bid = t02.bid AND t01.num >= t02.time_diff关联排序过滤最终完成session切分

SELECT
    *
FROM
    (
        SELECT
            t01.*,
            t02.event_time,
            time_diff,
            /* 取分等值连接后的第一条数据 */
            ROW_NUMBER() OVER(PARTITION BY t01.bid,t01.num ORDER BY t02.time_diff desc) AS num_2
        FROM
            log_data t01
        LEFT JOIN
        (
            SELECT
                bid,
                time_diff,
                event_time
            FROM
                (
                SELECT
                    t1.*,
                    t2.num,
                    /* 如果记录为首次或两个记录时间差大于30分钟则打上标记 */
                    CASE WHEN t2. event_time is NULL OR t1.event_time - t2.event_time > INTERVAL '30 minute' THEN t1.num ELSE null END AS time_diff
                FROM
                    log_data t1
                LEFT JOIN
                    /* 前后记录关联 */
                    log_data t2 on t1.bid = t2.bid AND t1.num = t2.num + 1
                )t02
            WHERE 
                t02.time_diff IS NOT NULL
        )t02 ON t01.bid = t02.bid AND t01.num >= t02.time_diff
    )tmp
/* 取num_2为1的记录 */
WHERE num_2 = 1

图五

图六

再添加session_id字段即可，本文采用md5(bid+event_time1)作为session_id