海量用户每日状态表压缩为状态变更周期表的实现方案咨询

方案1：MySQL 实现（推荐，无需导出数据，性能最优） #

前提：MySQL 版本 ≥ 8.0 支持窗口函数，提前给原表建立(user_id, date)联合索引加速查询。
首先创建结果表：

CREATE TABLE user_status_period (
    user_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY (user_id, start_date)
);

插入归并后的数据：

INSERT INTO user_status_period (user_id, status, start_date, end_date)
SELECT 
    user_id,
    status,
    MIN(date) AS start_date,
    MAX(date) AS end_date
FROM (
    SELECT 
        user_id,
        status,
        date,
        -- 同连续周期的日期差值相同，作为分组标记
        DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id, status ORDER BY date) DAY) AS group_flag
    FROM user_daily_status
) t
GROUP BY user_id, status, group_flag
ORDER BY user_id, start_date;

如果是MySQL 5.x不支持窗口函数，可改用用户变量实现相同逻辑。

方案2：Python 实现（适合导出为CSV的场景，内存占用极低） #

前提：导出CSV前先按user_id升序、date升序排序，无需加载全量数据到内存，逐行处理即可，支持任意大小的CSV文件。

import csv

with open('input.csv', 'r', encoding='utf-8') as in_f, open('output.csv', 'w', encoding='utf-8', newline='') as out_f:
    reader = csv.reader(in_f)
    writer = csv.writer(out_f)
    # 写入输出表头
    writer.writerow(['user_id', 'status', 'start_date', 'end_date'])
    next(reader) # 跳过原表表头
    
    # 初始化状态变量
    prev_user, prev_status, prev_start, prev_end = None, None, None, None
    
    for row in reader:
        user_id, status, date = [x.strip() for x in row]
        if prev_user is None:
            prev_user, prev_status, prev_start, prev_end = user_id, status, date, date
            continue
        # 用户和状态都没有变化，更新周期结束日期
        if user_id == prev_user and status == prev_status:
            prev_end = date
        # 状态或用户变化，结算上一个周期
        else:
            writer.writerow([prev_user, prev_status, prev_start, prev_end])
            prev_user, prev_status, prev_start, prev_end = user_id, status, date, date
    # 写入最后一个未结算的周期
    writer.writerow([prev_user, prev_status, prev_start, prev_end])

方案3：Mac 终端实现（最快的文本处理方案，无需额外安装依赖） #

前提：同Python方案，CSV已按user_id升序、date升序排序，用awk直接在终端处理，性能远高于Python脚本。
终端直接执行命令：

awk 'BEGIN{FS=",";OFS=",";print "user_id,status,start_date,end_date"}
NR==1{next}
{
    gsub(/ /,"",$0); # 去除字段多余空格
    if ($1 == prev_user && $2 == prev_status) {
        prev_end = $3
    } else {
        if (prev_user != "") {
            print prev_user, prev_status, prev_start, prev_end
        }
        prev_user = $1
        prev_status = $2
        prev_start = $3
        prev_end = $3
    }
}
END{
    print prev_user, prev_status, prev_start, prev_end
}' 输入文件.csv > 输出文件.csv

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