如何计算各年度累计活跃用户数（含2010及之前年份）

我需要计算每年使用我司应用的累计活跃用户总数（包含新用户与回流用户）。目前编写的SQL脚本仅能统计单一年度的用户数，我希望获取截至2010-12-31及之前所有年份的累计用户数（历史年份可能超过3年）。

现有SQL脚本：

SELECT '2010-12-31' as date, count(*) as year
FROM users AS c 
WHERE FORMAT(c.first_joined, 'yyyyMMdd') <= '2010-12-31 ' 
AND status = 'ACTIVE'

其中first_joined为用户首次进入系统的日期，total为新老用户总数。当前脚本仅返回2010-12-31的累计数（8,617），期望得到各年度的累计结果，示例如下：

方案一：动态生成年度列表+关联统计（通用型） #

该方案适用于多数SQL方言，自动生成从最早有用户加入的年份到2010年的所有年度年末日期，再计算每个日期的累计活跃用户数及当年新增用户：

-- 生成需统计的年度年末日期
WITH year_end_dates AS (
    SELECT DATEFROMPARTS(y, 12, 31) AS year_end
    FROM (
        -- 获取所有早于等于2010年的用户注册年份
        SELECT DISTINCT YEAR(first_joined) AS y
        FROM users
        WHERE YEAR(first_joined) <= 2010
    ) AS years
)
-- 计算累计数并格式化输出
SELECT
    CONCAT(
        CONVERT(VARCHAR, yed.year_end, 23),
        ' (*(', FORMAT(COUNT(DISTINCT CASE WHEN YEAR(u.first_joined) = YEAR(yed.year_end) THEN u.user_id END), 'N0'),
        ' joined in ', YEAR(yed.year_end), ')*)'
    ) AS date,
    FORMAT(COUNT(DISTINCT u.user_id), 'N0') AS total
FROM year_end_dates yed
LEFT JOIN users u 
    ON u.first_joined <= yed.year_end
    AND u.status = 'ACTIVE'
GROUP BY yed.year_end
ORDER BY yed.year_end;

方案二：窗口函数简化版（支持窗口函数的数据库） #

如果你的数据库支持窗口函数（如SQL Server 2012+、MySQL 8+、PostgreSQL等），可先统计每年新增用户，再用窗口函数计算累计值：

-- 统计每年新增的活跃用户
WITH yearly_new_users AS (
    SELECT
        YEAR(first_joined) AS join_year,
        COUNT(DISTINCT user_id) AS new_users
    FROM users
    WHERE status = 'ACTIVE'
        AND YEAR(first_joined) <= 2010
    GROUP BY YEAR(first_joined)
)
-- 计算累计值并格式化输出
SELECT
    CONCAT(
        DATEFROMPARTS(join_year, 12, 31),
        ' (*(', FORMAT(new_users, 'N0'), ' joined in ', join_year, ')*)'
    ) AS date,
    FORMAT(SUM(new_users) OVER (ORDER BY join_year), 'N0') AS total
FROM yearly_new_users
ORDER BY join_year;

注意事项 #

• 脚本假设表存在唯一用户标识字段user_id，若没有可替换为能唯一识别用户的字段组合，避免重复统计同一用户。
• 不同数据库的日期格式化函数略有差异：SQL Server用FORMAT/CONVERT，MySQL用DATE_FORMAT，PostgreSQL用TO_CHAR，可根据实际数据库调整。
• 若需要强制包含某个年份（比如最早年份之前的空年份），可在year_end_dates的子查询中手动添加UNION SELECT 2007这类语句。

内容的提问来源于stack exchange，提问作者java-life-love