如何编写SQL查询统计各分类重复访客数及计算复访率

实现逻辑 #

1. 先按category_id和user_id分组，统计单个用户对单个分类的访问次数
2. 再按category_id分组，聚合计算重复访客数、总访客数、复访率三个指标

SQL实现（线上业务场景） #

假设原始行为表名为category_visit_records，查询语句如下：

SELECT
    category_id,
    -- 统计访问次数≥2的去重用户数
    COUNT(DISTINCT CASE WHEN visit_cnt >= 2 THEN user_id END) AS repeat_visitor_count,
    -- 统计总去重访客数
    COUNT(DISTINCT user_id) AS total_visitor_count,
    -- 计算复访率，乘1.0是为了避免整数除法截断，保留4位小数可按需调整
    ROUND(
        COUNT(DISTINCT CASE WHEN visit_cnt >= 2 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id),
        4
    ) AS repeat_visit_rate
FROM (
    -- 内层先统计每个用户对每个分类的访问次数
    SELECT
        category_id,
        user_id,
        COUNT(*) AS visit_cnt
    FROM category_visit_records
    -- 可按需加时间范围过滤条件，比如统计近7天的复访率：WHERE visit_time >= 'xxxx-xx-xx'
    GROUP BY category_id, user_id
) t
GROUP BY category_id;

结果验证 #

针对你给出的示例数据，执行上述SQL后得到的结果如下：

注：你示例说明中提到的113分类总访客数为7属于笔误，实际去重后用户为1、5、6、7共4人，对应复访率为2/4=0.5

Pandas实现（离线分析场景） #

如果是用Python做离线数据分析，可参考如下代码：

import pandas as pd

# df为读取到的原始访问数据DataFrame，包含category_id、user_id两列
# 统计每个用户对每个分类的访问次数
user_visit_cnt = df.groupby(['category_id', 'user_id'], as_index=False).size().rename(columns={'size': 'visit_cnt'})
# 按分类聚合计算指标
result = user_visit_cnt.groupby('category_id', as_index=False).agg(
    repeat_visitor_count=('user_id', lambda s: (user_visit_cnt.loc[s.index, 'visit_cnt'] >= 2).sum()),
    total_visitor_count=('user_id', 'nunique')
)
# 计算复访率
result['repeat_visit_rate'] = (result['repeat_visitor_count'] / result['total_visitor_count']).round(4)

性能优化建议 #

如果数据量超过百万级，可提前做预聚合处理：按天/小时维度提前计算每个用户对每个分类的访问次数并存储，后续统计时直接基于预聚合表计算，无需每次扫描全量原始数据。

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