SQLite3中按日期统计distinct attcharid的累计唯一值问题

我来帮你搞定这个累计唯一值统计的需求——这在做用户增长图表时确实是个常见的痛点，之前的方法之所以不对，是因为直接累加每日的distinct数会重复计算跨天出现的用户，我们需要的是截至当天所有从未出现过的新用户的总数。

核心思路 #

要实现这个需求，关键是先追踪每个attcharid第一次出现的日期，然后对每个统计日期，计算所有首次出现日期≤当前日期的用户数量，这样得到的就是截至当天的累计唯一用户数。

完整SQL查询语句 #

下面是针对你的场景的可直接运行的SQLite查询，包含了日期范围生成、首次出现日期统计和累计计算三个部分：

SELECT 
    dates.killmaildate,
    COUNT(DISTINCT first_occurrences.attcharid) AS cumulative_unique_users
FROM (
    -- 生成目标日期范围内的所有日期（包括无数据的日期）
    SELECT date('2018-04-12', '+' || (t.i || ' days')) AS killmaildate
    FROM (
        SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
        SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
    ) t
    WHERE date('2018-04-12', '+' || (t.i || ' days')) <= date('2018-04-30')
) dates
LEFT JOIN (
    -- 获取每个符合条件的attcharid首次出现的日期
    SELECT 
        attcharid,
        MIN(killmaildate) AS first_seen_date
    FROM killdata
    WHERE attship = '23913' 
      AND attallianceid = '99007379'
      AND killmaildate BETWEEN date('2018-04-12') AND date('2018-04-30')
    GROUP BY attcharid
) first_occurrences ON dates.killmaildate >= first_occurrences.first_seen_date
GROUP BY dates.killmaildate
ORDER BY dates.killmaildate;

语句拆解说明 #

1. 日期生成子查询dates：
   这个部分会生成从2018-04-12到2018-04-30的所有日期，确保即使某天没有任何数据（比如你示例中的4月13日），也会显示当天的累计值（和前一天保持一致），这样你的增长图表会更完整。

2. 首次出现日期子查询first_occurrences：
   通过MIN(killmaildate)找出每个attcharid第一次符合条件（指定attship和attallianceid）出现的日期，这样我们就能明确每个用户是哪一天新增的。

3. 主查询累计计算：
   将每个日期与所有首次出现日期≤该日期的用户关联，通过COUNT(DISTINCT attcharid)统计这些用户的总数，得到截至当天的累计唯一用户数。

简化版（仅显示有数据的日期） #

如果你不需要显示无数据的日期，可以简化日期生成部分，直接从数据表中提取有数据的日期：

SELECT 
    daily_dates.killmaildate,
    COUNT(DISTINCT first_occurrences.attcharid) AS cumulative_unique_users
FROM (
    SELECT DISTINCT killmaildate
    FROM killdata
    WHERE killmaildate BETWEEN date('2018-04-12') AND date('2018-04-30')
      AND attship = '23913' 
      AND attallianceid = '99007379'
    ORDER BY killmaildate
) daily_dates
LEFT JOIN (
    SELECT 
        attcharid,
        MIN(killmaildate) AS first_seen_date
    FROM killdata
    WHERE attship = '23913' 
      AND attallianceid = '99007379'
      AND killmaildate BETWEEN date('2018-04-12') AND date('2018-04-30')
    GROUP BY attcharid
) first_occurrences ON daily_dates.killmaildate >= first_occurrences.first_seen_date
GROUP BY daily_dates.killmaildate
ORDER BY daily_dates.killmaildate;

这个版本会返回你示例中那些有数据的日期，对应的累计值会准确反映截至当天的新增用户总数，比如4月14日的累计值会是4月12日的27加上4月14日新增的用户数（而非27+13）。

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