SQLite中按pid聚合唯一tid并计算连续分组访问次数的查询问题

需求拆解与核心逻辑 #

• 统计唯一tid数量：需基于去重后的pid+tid维度统计，用COUNT(tid)即可（先聚合去重后）
• 按唯一tid求和duration：必须先对pid+tid分组计算每个tid的duration总和，再按pid汇总，不能用SUM(DISTINCT duration)（后者会去重duration值，而非按tid维度求和）
• 计算访问次数：对每个pid下的唯一tid排序，用窗口函数计算相邻tid的间隔，间隔>5则视为新访问，统计这类新访问的标记值总和（初始访问默认算1次）

完整SQL实现 #

假设你的表名为user_table，以下是整合所有需求的查询：

WITH tid_agg AS (
    -- 按pid+tid聚合，得到每个tid的duration总和（自动去重tid）
    SELECT 
        pid,
        tid,
        SUM(duration) AS tid_total_duration
    FROM user_table
    GROUP BY pid, tid
),
tid_sequence AS (
    -- 对每个pid的tid排序，获取前一个tid用于计算间隔
    SELECT 
        pid,
        tid,
        LAG(tid) OVER (PARTITION BY pid ORDER BY tid) AS previous_tid
    FROM tid_agg
),
visit_flags AS (
    -- 标记新访问：第一个tid或间隔超5的tid算新访问
    SELECT 
        pid,
        CASE
            WHEN previous_tid IS NULL THEN 1
            WHEN tid - previous_tid > 5 THEN 1
            ELSE 0
        END AS new_visit
    FROM tid_sequence
),
visit_counts AS (
    -- 统计每个pid的总访问次数
    SELECT 
        pid,
        SUM(new_visit) AS visit_count
    FROM visit_flags
    GROUP BY pid
)
-- 最终整合所有结果
SELECT 
    ta.pid,
    COUNT(ta.tid) AS unique_tid_count,
    SUM(ta.tid_total_duration) AS total_duration,
    vc.visit_count
FROM tid_agg ta
JOIN visit_counts vc ON ta.pid = vc.pid
GROUP BY ta.pid
ORDER BY ta.pid;

关键错误点修正 #

你之前用SUM(DISTINCT duration)的问题在于：它会把所有重复的duration值去重后求和，比如同一个tid下有多条duration=10的记录，只会计算一次10，但你的需求是每个tid对应的所有duration之和，所以必须先按pid+tid分组计算每个tid的总和，再按pid汇总。

访问次数逻辑说明 #

1. 先通过tid_agg拿到每个pid下的唯一tid及其duration总和
2. 用LAG()窗口函数为每个tid获取同pid下的上一个tid
3. 对比当前tid与上一个tid的差值，超过5则标记为新访问
4. 对每个pid的新访问标记求和，得到总访问次数（第一个tid会被标记为1，后续符合条件的加1，正好是实际访问次数）

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