需求：编写SQLite语句每日计算活跃用户数及占比

需求明确 #

按日生成统计数据，每行包含三列：

• date：统计日期
• number_active_users：活跃用户绝对数量（定义：统计日期X的[X-6天, X]区间内至少收听一首歌曲的唯一用户）
• percentage_active_users：活跃用户占总用户的比例

原SQL问题分析 #

你写的SQL存在几个核心问题：

1. count(user_name)没去重，会把同一用户当日多次收听的情况重复计数，得到的不是唯一用户数
2. 用ROW_NUMBER()给日期排号后筛选前6天，只返回了固定6天的数据，没有实现“每个日期对应过去7天窗口”的统计逻辑
3. 占比计算cnt/SUM(cnt)是取前6天用户数总和的占比，不是活跃用户数与总用户数的比值

正确SQL实现 #

-- 第一步：生成所有需要统计的日期（从user_history中提取唯一的日期）
WITH all_dates AS (
    SELECT DISTINCT date(listened_at, 'unixepoch', 'localtime') AS stat_date
    FROM user_history
),
-- 第二步：统计每个日期对应的7天窗口内的活跃用户数
active_users_per_date AS (
    SELECT
        ad.stat_date AS date,
        COUNT(DISTINCT uh.user_name) AS number_active_users
    FROM all_dates ad
    LEFT JOIN user_history uh
        ON date(uh.listened_at, 'unixepoch', 'localtime') 
           BETWEEN date(ad.stat_date, '-6 days') AND ad.stat_date
    GROUP BY ad.stat_date
),
-- 第三步：获取总用户数
total_users AS (
    SELECT COUNT(*) AS total FROM users
)
-- 第四步：计算占比并输出结果
SELECT
    au.date,
    au.number_active_users,
    -- 转换为浮点避免整数除法，保留两位小数可按需调整
    ROUND((au.number_active_users * 1.0 / tu.total) * 100, 2) AS percentage_active_users
FROM active_users_per_date au, total_users tu
ORDER BY au.date;

关键说明 #

• all_dates：从用户收听记录中提取所有存在收听行为的日期，确保每个有数据的日期都被统计
• 关联查询时用BETWEEN date(ad.stat_date, '-6 days') AND ad.stat_date，精准匹配每个统计日期的7天窗口
• COUNT(DISTINCT uh.user_name)确保每个用户只被计数一次，符合活跃用户的定义
• 占比计算时用*1.0将整数转换为浮点型，避免SQLite默认的整数除法导致结果为0
• 如果需要统计所有连续日期（包括无收听记录的日期），可以用递归CTE生成连续日期序列替换all_dates

优化建议 #

为了提升查询效率，建议给user_history表添加以下索引：

CREATE INDEX idx_user_history_listened_at ON user_history(listened_at);
CREATE INDEX idx_user_history_user_date ON user_history(user_name, listened_at);

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