如何在SQLite中基于活动会话时间窗口构建多表聚合查询

要解决这个按活动会话聚合统计的问题，我们可以借助SQLite的窗口函数来识别会话边界，再通过分组聚合得到所需的统计结果。以下是具体的实现思路和完整查询语句：

核心思路 #

1. 标记会话边界：把table1和table2的所有时间窗口合并，按时间排序后，用LAG()函数获取每条记录的前一个时间窗口，计算两者的时间差。当间隔≥10分钟（600秒）时，视为新会话的起点，通过累计求和生成唯一的会话ID。
2. 分表聚合统计：基于生成的会话ID，分别对table1和table2聚合counter和value的总和，同时记录每个会话的开始/结束时间。
3. 合并结果展示：将两个表的聚合结果按会话ID关联，输出完整的会话统计数据。

完整SQL查询 #

WITH all_windows AS (
    -- 合并table1和table2的所有时间窗口，确保会话划分覆盖所有数据
    SELECT window FROM table1
    UNION
    SELECT window FROM table2
),
window_session_mapping AS (
    -- 为每个时间窗口分配会话ID：间隔≥10分钟则开启新会话
    SELECT
        window,
        -- 累计计算会话ID：前一个窗口间隔≥600秒时加1，否则继承
        SUM(
            CASE 
                WHEN strftime('%s', window) - strftime('%s', LAG(window) OVER (ORDER BY window)) >= 600 
                THEN 1 
                ELSE 0 
            END
        ) OVER (ORDER BY window) + 1 AS session_id
    FROM all_windows
),
table1_session_stats AS (
    -- 聚合table1的会话统计数据
    SELECT
        wsm.session_id,
        MIN(t1.window) AS session_start,
        MAX(t1.window) AS session_end,
        SUM(t1.counter) AS table1_counter_sum,
        SUM(t1.value) AS table1_value_sum
    FROM table1 t1
    JOIN window_session_mapping wsm ON t1.window = wsm.window
    GROUP BY wsm.session_id
),
table2_session_stats AS (
    -- 聚合table2的会话统计数据
    SELECT
        wsm.session_id,
        SUM(t2.counter) AS table2_counter_sum,
        SUM(t2.value) AS table2_value_sum
    FROM table2 t2
    JOIN window_session_mapping wsm ON t2.window = wsm.window
    GROUP BY wsm.session_id
)
-- 合并两个表的会话统计结果，展示最终输出
SELECT
    '会话' || t1ss.session_id AS session_name,
    t1ss.session_start,
    t1ss.session_end,
    t1ss.table1_counter_sum,
    t1ss.table1_value_sum,
    -- 处理table2无对应会话数据的情况，默认填充0
    COALESCE(t2ss.table2_counter_sum, 0) AS table2_counter_sum,
    COALESCE(t2ss.table2_value_sum, 0) AS table2_value_sum
FROM table1_session_stats t1ss
LEFT JOIN table2_session_stats t2ss ON t1ss.session_id = t2ss.session_id
ORDER BY t1ss.session_id;

关键细节说明 #

• 时间差计算：用strftime('%s', window)将时间窗口转换为 Unix 时间戳（秒），差值≥600即表示间隔超过10分钟。
• 会话ID生成：通过SUM() OVER (ORDER BY window)累计会话切换的次数，再加1得到从1开始的会话ID。
• 空值处理：用COALESCE()确保当某会话只有table1数据时，table2的统计值显示为0，避免出现NULL。

针对你的示例数据的输出 #

基于你提供的table1示例数据，假设table2无对应数据，查询输出会是：

注：你的插入逻辑中，table1的ON CONFLICT语句里写的是count = count + 1，但表结构字段是counter，这是个笔误，需要修正为counter = counter + 1才能正确更新统计值。

内容的提问来源于stack exchange，提问作者Avba