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

Question

存在一张交易记录表包含用户的id(user_id)以及交易的时间(purchase_date).
需要制作统计图，X轴则代表每一个月，Y轴则代表本月及本月以前有订单交易的用户数.
现在问题就是由于交易数据过多，一但统计的月数过多查询就会超时(5分钟就会timeout)
sql:
SELECT COUNT(DISTINCT user_id) AS user_count, target_month from 记忆记录表 tt JOIN (SELECT 某个月 AS target_month UNION SELECT 某个月 AS target_month UNION ....SELECT 某个月 AS target_month) as tm ON tt.purchase_date < (target_month + 一个月) GROUP BY tm.target_month
大佬们有没有什么办法优化？

Answer 1

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

SQL 流程优化

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

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

数据库	计算用时（秒）
DuckDB v0.8（八线程，未用索引）	9.859
SQLite v3.42（单线程，使用索引）	21.655

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

Answer 2

弄定任务，每个月初1日凌晨计算上个月的数据，汇总成月表记录在数据库中，需要实时查询直接去月表捞数据就行，就不用实时查了