如何使用纯SQL实现按自然周统计指定时间段内的活跃用户数量

实现方案 #

核心思路 #

要实现按自然周统计活跃用户，核心是先生成统计周期内所有周的时间区间，再将用户生效区间和周区间做匹配后分组统计：

• 先生成指定统计时间段内所有自然周的周一、周日日期对，确保即使某周没有活跃用户也能输出对应行（数值为0）
• 转换用户表的字符串日期为标准日期类型，预过滤完全不在统计范围内的用户提升查询效率
• 关联周序列和用户表，按给定的活跃规则匹配用户，按周一分组统计去重用户数

示例SQL（PostgreSQL语法） #

WITH RECURSIVE week_series AS (
    -- 这里以统计2023年全年为例，动态生成所有周的起止日期，周一为周首
    SELECT 
        DATE_TRUNC('week', DATE '2023-01-01')::DATE AS week_monday,
        (DATE_TRUNC('week', DATE '2023-01-01') + INTERVAL '6 days')::DATE AS week_sunday
    UNION ALL
    SELECT 
        (week_monday + INTERVAL '7 days')::DATE,
        (week_sunday + INTERVAL '7 days')::DATE
    FROM week_series
    WHERE week_monday < DATE_TRUNC('week', DATE '2023-12-31')::DATE
),
formatted_user AS (
    -- 转换用户表的dd.mm.yyyy格式字符串为日期类型，过滤非活跃用户
    SELECT 
        "user-id",
        TO_DATE("start", 'DD.MM.YYYY') AS start_dt,
        CASE WHEN "end" IS NULL THEN NULL ELSE TO_DATE("end", 'DD.MM.YYYY') END AS end_dt
    FROM your_user_table
    WHERE isActive = 1
    -- 预过滤完全不在2023年范围内的用户
    AND TO_DATE("start", 'DD.MM.YYYY') <= DATE '2023-12-31'
    AND ("end" IS NULL OR TO_DATE("end", 'DD.MM.YYYY') > DATE '2023-01-01')
)
SELECT 
    TO_CHAR(week_monday, 'DD.MM.YYYY') AS "date",
    COUNT(DISTINCT fu."user-id") AS amount
FROM week_series ws
LEFT JOIN formatted_user fu
    ON fu.start_dt <= ws.week_sunday
    AND (fu.end_dt IS NULL OR fu.end_dt > ws.week_sunday)
GROUP BY ws.week_monday
ORDER BY ws.week_monday;

不同数据库适配说明 #

• MySQL：递归CTE语法一致，日期转换替换为STR_TO_DATE(col, '%d.%m.%Y')，日期加减用DATE_ADD，日期格式化用DATE_FORMAT(week_monday, '%d.%m.%Y')，注意设置SET @@session.date_first = 1保证周一为周首
• SQL Server：递归CTE语法一致，日期转换用CONVERT(DATE, col, 104)（104对应dd.mm.yyyy格式），日期加减用DATEADD(DAY, 7, week_monday)，提前执行SET DATEFIRST 1设置周起始为周一
• Oracle：周序列可改用CONNECT BY语法生成，日期转换用内置TO_DATE函数即可

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