這是一個基于用戶點擊信息進行會話時長分析的案例，常見于互聯網 App 使用分析。
問題描述
用戶的訪問記錄存儲在 user_access 表中，包含用戶編號（user_id）以及訪問時間（access_time）等信息。以下是一個示例數據：
-- 創建示例表
CREATE TABLE user_access(user_id bigint, access_time timestamp);-- 生成示例數據
INSERT INTO user_access VALUES
(1, '2025-04-30 07:00:00'),
(2, '2025-04-30 07:05:00'),
(2, '2025-04-30 07:10:00'),
(1, '2025-04-30 07:15:00'),
(1, '2025-04-30 07:20:00'),
(1, '2025-04-30 08:00:00'),
(3, '2025-04-30 08:00:00'),
(1, '2025-04-30 08:10:00');
對于同一個用戶，如果前后兩次操作時間間隔超過 30 分鐘，則認為它們屬于不同的會話。要求分析每個用戶每次會話的開始時間和結束時間，輸出結果如下：
以用戶 1 為例，他有兩次會話，第一次會話從 7 點開始到 7 點 20 分結束（持續 20 分鐘），第二次會話從 8 點開始到 8 點 10 分結束（持續 10 分鐘）。
問題解析
基于上面的描述，我們首先需要分析用戶每次點擊的時間和上一次時間是否間隔超過 30 分鐘，超過了表示新會話開始，否則仍然屬于上一次會話。為此，我們需要使用一個窗口函數 lag，它可以返回上一條數據的信息：
SELECT user_id, access_time,lag(access_time) OVER (PARTITION BY user_id ORDER BY access_time) AS pre_access_time, -- 上次點擊時間CASE WHEN access_time - INTERVAL '30' MINUTE <= lag(access_time) OVER (PARTITION BY user_id ORDER BY access_time) THEN 0ELSE 1END AS new_session -- 基于每次點擊和上次點擊時間間隔判斷是否新的會話
FROM user_access;user_id|access_time |pre_access_time |new_session|
-------+-----------------------+-----------------------+-----------+1|2025-04-30 07:00:00.000| | 1|1|2025-04-30 07:15:00.000|2025-04-30 07:00:00.000| 0|1|2025-04-30 07:20:00.000|2025-04-30 07:15:00.000| 0|1|2025-04-30 08:00:00.000|2025-04-30 07:20:00.000| 1|1|2025-04-30 08:10:00.000|2025-04-30 08:00:00.000| 0|2|2025-04-30 07:05:00.000| | 1|2|2025-04-30 07:10:00.000|2025-04-30 07:05:00.000| 0|3|2025-04-30 08:00:00.000| | 1|
其中，lag 函數分析了每個用戶（PARTITION BY user_id）每次點擊對應的上一次點擊時間（ORDER BY access_time）；CASE 表達式基于時間間隔判斷是否新的會話開始。
考慮到一個用戶可能存在多次會話，我們需要把它們進行編號。基于上面的查詢結果，每次新會話開始對應的 new_session 字段都等于 1，其他都等于 0，可以基于這個字段求和生成會話編號：
WITH user_access_session_flag AS ( SELECT user_id, access_time,CASE WHEN access_time - INTERVAL '30' MINUTE <= lag(access_time) OVER (PARTITION BY user_id ORDER BY access_time) THEN 0ELSE 1END AS new_session -- 基于每次點擊和上次點擊時間間隔判斷是否新的會話FROM user_access
)
SELECT user_id, access_time,sum(new_session) OVER (PARTITION BY user_id ORDER BY access_time) AS session_num -- 基于new_session標識生成每次會話的編號
FROM user_access_session_flag;user_id|access_time |session_num|
-------+-----------------------+-----------+1|2025-04-30 07:00:00.000| 1|1|2025-04-30 07:15:00.000| 1|1|2025-04-30 07:20:00.000| 1|1|2025-04-30 08:00:00.000| 2|1|2025-04-30 08:10:00.000| 2|2|2025-04-30 07:05:00.000| 1|2|2025-04-30 07:10:00.000| 1|3|2025-04-30 08:00:00.000| 1|
其中，WITH 用于定義通用表表達式，我們可以簡單把它理解為一個臨時表（user_access_session_flag）。在這里主要是將查詢語句模塊化，便于我們閱讀，否則要使用子查詢。
此時，我們已經可以比較清晰地看到每個用戶的每次會話信息了。為了生成最終的效果，得到每次會話的開始時間和結束時間，可以基于用戶和會話編號再執行一次分組操作：
WITH user_access_session_flag AS ( SELECT user_id, access_time,CASE WHEN access_time - INTERVAL '30' MINUTE <= lag(access_time) OVER (PARTITION BY user_id ORDER BY access_time) THEN 0ELSE 1END AS new_session -- 基于每次點擊和上次點擊時間間隔判斷是否新的會話FROM user_access
),
user_sessions AS (SELECT user_id, access_time,sum(new_session) OVER (PARTITION BY user_id ORDER BY access_time) AS session_num -- 基于new_session標識生成每次會話的編號FROM user_access_session_flag
)
SELECT user_id, session_num, min(access_time) AS start_time, max(access_time) AS end_time
FROM user_sessions
GROUP BY user_id, session_num
ORDER BY user_id, session_num; -- 分組獲取每個用戶每次會話的會話開始時間和結束時間user_id|session_num|start_time |end_time |
-------+-----------+-----------------------+-----------------------+1| 1|2025-04-30 07:00:00.000|2025-04-30 07:20:00.000|1| 2|2025-04-30 08:00:00.000|2025-04-30 08:10:00.000|2| 1|2025-04-30 07:05:00.000|2025-04-30 07:10:00.000|3| 1|2025-04-30 08:00:00.000|2025-04-30 08:00:00.000|
在以上查詢中，我們基于原始數據定義了臨時表 user_access_session_flag，然后又基于它定義了臨時表 user_sessions，接著基于這個臨時表進行分組分析，得到最終結果。
通過這個案例也可以看出，通用表表達式（WITH）編寫的代碼非常符號我們的閱讀理解習慣，推薦大家使用。
