如何使用SQL将压缩存储的时序采样数据展开为原始明细形式

完全可以通过纯SQL实现该需求，不需要编写存储过程逐行循环，基于集合关联的写法性能、可维护性都远高于游标循环逻辑。

核心实现思路 #

核心逻辑是借助连续整数序列，把单条压缩记录按采样间隔拆分为多行，不需要逐行遍历：

• 生成从0开始的连续整数序列，序列最大长度覆盖单条压缩记录最多可拆分的采样点数量即可
• 将压缩表与整数序列做关联，匹配所有落在压缩记录时间范围内的序列值
• 通过序列值、采样间隔直接计算每个采样点的起止时间，还原原始表结构

代码示例 #

以下写法兼容MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的主流数据库，假设压缩表名为compressed_data，字段为periodStart、periodEnd、variable、samplingInterval（单位：分钟）：

WITH RECURSIVE number_seq(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM number_seq WHERE n < 100000 -- 上限根据业务最大拆分点数调整
)
SELECT
    DATE_ADD(c.periodStart, INTERVAL n.n * c.samplingInterval MINUTE) AS periodStart,
    LEAST(
        DATE_ADD(c.periodStart, INTERVAL (n.n + 1) * c.samplingInterval MINUTE),
        c.periodEnd
    ) AS periodEnd,
    c.variable
FROM compressed_data c
INNER JOIN number_seq n
    ON DATE_ADD(c.periodStart, INTERVAL n.n * c.samplingInterval MINUTE) < c.periodEnd
-- SQL Server用户需要放开递归深度限制，追加下方注释的参数
-- OPTION (MAXRECURSION 32767)
;

优化与注意事项 #

• 如果使用不支持递归CTE的旧版数据库（如MySQL 5.x），可以提前创建一张持久化数字辅助表，存储0~100万的连续整数，关联逻辑和上述写法完全一致，查询性能比递归CTE更高。
• 整数序列的上限需要提前根据业务场景评估：比如单条压缩记录最长跨度为1年、采样间隔最小为1分钟，把上限设为600000即可覆盖所有场景，避免漏数。
• 最后一个采样点的结束时间必须用LEAST函数截断，避免最后一段不足一个采样间隔时，生成的periodEnd超出原始压缩记录的结束时间，导致还原数据失真。

方案优势 #

对比存储过程循环的实现方式，该纯SQL写法有三个明显优势：

• 基于数据库集合运算逻辑执行，数据量大时性能是逐行循环的数十到上百倍
• 输出为标准SELECT结果集，可以直接作为子查询嵌套到其他统计逻辑中，不需要额外调用存储过程
• 不需要手动维护游标、循环变量的状态，不会出现迭代边界错误、变量污染等问题

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