统计每个月及以前的有订单的用户，如何优化？

手头没有 MySQL，拿俩小型数据库试一试。

SQL 流程优化

1. 先找出所有用户首次交易月份
2. 分组统计这些月份的用户数量
3. 使用窗口函数，累计历史以来，每个月的用户总数
4. 挑出自己需要的月份即可（有空缺的月份，可以用 LEFT JOIN 等补齐）

测试结果（千万用户、一亿交易记录）

DuckDB 完整测试 SQL 代码

-- 测试发现，建索引帮助不大？
CREATE TABLE data(user_id INT, purchase_date TIMESTAMP);

-- 添加一亿条交易记录。字段取值：
-- user_id：[0, 1KW) 随机值
-- purchase_date：[1970-01-01 00:00:00, now) 随机值
INSERT INTO data(user_id, purchase_date)
SELECT
    CAST(random() * 10000000 AS INT),
    to_timestamp(CAST(random() * (SELECT epoch(now())) AS INT))
FROM generate_series(1, 100000000);

-- 启动计时
.timer on

-- 计算
WITH
    step1 AS (
        SELECT date_trunc('month', min(purchase_date)) date
        FROM data
        GROUP BY user_id
    ),
    step2 AS (
        SELECT date, count(*) cnt
        FROM step1
        GROUP BY date
    ),
    step3 as (
        SELECT date, sum(cnt) OVER win cnt
        FROM step2
        WINDOW win AS (ORDER BY date)
    )
SELECT *
FROM step3
WHERE date >= '2023-01-01'::DATE;

运行结果

┌────────────┬─────────┐
│    date    │   cnt   │
│    date    │ int128  │
├────────────┼─────────┤
│ 2023-01-01 │ 9999566 │
│ 2023-02-01 │ 9999569 │
│ 2023-03-01 │ 9999579 │
│ 2023-04-01 │ 9999583 │
│ 2023-05-01 │ 9999590 │
│ 2023-06-01 │ 9999594 │
└────────────┴─────────┘
Run Time (s): real 9.859 user 69.752993 sys 4.206752

SQLite 完整测试 SQL 代码

-- 偷个懒，直接建索引，不建原始表了（反正起作用的也是索引）
CREATE TABLE data(
    user_id INT,
    purchase_date TIMESTAMP,
    PRIMARY KEY(user_id, purchase_date)
) WITHOUT ROWID;

-- 添加一亿条交易记录。字段取值同上
INSERT OR IGNORE INTO data(user_id, purchase_date)
SELECT
    abs(random() % 10000000),
    abs(random() % (SELECT unixepoch()))
FROM generate_series(1, 100000000);

-- 启动计时
.timer on

-- SQLite 有个毛病，GROUP BY 是先排序表，再根据键计算聚合值。
-- 这里改成手动根据键，直接计算聚合值，
-- 免去排序 step1 一亿行表，省内存（几百MB）和时间（5秒左右）。
DROP TABLE IF EXISTS mid_step2;
CREATE TEMP TABLE mid_step2(date INTEGER PRIMARY KEY, count INT);
WITH
    step1(date) AS (
        SELECT CAST(strftime('%Y%m', min(purchase_date), 'unixepoch') AS INT)
        FROM data
        GROUP BY user_id
    )
INSERT INTO mid_step2
SELECT date, 1
FROM step1
WHERE true
ON CONFLICT(date) DO UPDATE
SET count = count + 1;

-- 继续后续步骤
WITH
    step3 AS (
        SELECT date, sum(count) OVER win cnt
        FROM mid_step2
        WINDOW win AS (ORDER BY date)
    )
SELECT *
FROM step3
WHERE date >= 202301;

运行结果

202301|9999492
202302|9999496
202303|9999510
202304|9999521
202305|9999527
202306|9999528
Run Time: real 21.655 user 21.640625 sys 0.000000