基于时间戳按数据老化程度合并SQL表行以缩减存储

我有多张存储不规则高频时间戳数据的SQL表，有时每秒会生成多条记录。为缩减主SQL服务器存储容量，希望创建查询合并数据，并根据数据老化程度设置不同合并粒度：

• 数据不足24小时时，保留秒级粒度
• 数据超过1个月时，调整为小时级粒度

日志表结构及示例数据 #

期望合并结果 #

示例数据均不足24小时，需按秒合并：同一秒内的多条记录合并为一条，Count字段求和，最终得到9条记录（11:24:04对应Count=1，其余每秒对应Count=2）；若数据超过1个月，则按小时合并，同一小时内的所有记录合并为一条并求和Count。

核心思路是根据Time字段与当前时间的差值，动态选择时间分组粒度，再对Count求和。以下是主流SQL数据库的实现方式：

1. SQL Server #

SELECT
    EquID,
    CellID,
    StnID,
    CASE
        -- 不足24小时，保留到秒
        WHEN DATEDIFF(HOUR, Time, GETDATE()) < 24 THEN DATEADD(SECOND, DATEDIFF(SECOND, 0, Time), 0)
        -- 超过1个月，保留到小时
        WHEN DATEDIFF(MONTH, Time, GETDATE()) >= 1 THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, Time), 0)
        -- 24小时到1个月的中间区间，默认按分钟合并（可按需调整）
        ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Time), 0)
    END AS GroupedTime,
    SUM(Count) AS TotalCount
FROM YourLogTable
GROUP BY
    EquID,
    CellID,
    StnID,
    CASE
        WHEN DATEDIFF(HOUR, Time, GETDATE()) < 24 THEN DATEADD(SECOND, DATEDIFF(SECOND, 0, Time), 0)
        WHEN DATEDIFF(MONTH, Time, GETDATE()) >= 1 THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, Time), 0)
        ELSE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Time), 0)
    END
ORDER BY GroupedTime;

2. MySQL #

SELECT
    EquID,
    CellID,
    StnID,
    CASE
        -- 不足24小时，保留到秒
        WHEN TIMESTAMPDIFF(HOUR, Time, NOW()) < 24 THEN DATE_FORMAT(Time, '%Y-%m-%d %H:%i:%s')
        -- 超过1个月，保留到小时
        WHEN TIMESTAMPDIFF(MONTH, Time, NOW()) >= 1 THEN DATE_FORMAT(Time, '%Y-%m-%d %H:00:00')
        -- 24小时到1个月的中间区间，默认按分钟合并（可按需调整）
        ELSE DATE_FORMAT(Time, '%Y-%m-%d %H:%i:00')
    END AS GroupedTime,
    SUM(`Count`) AS TotalCount
FROM YourLogTable
GROUP BY
    EquID,
    CellID,
    StnID,
    CASE
        WHEN TIMESTAMPDIFF(HOUR, Time, NOW()) < 24 THEN DATE_FORMAT(Time, '%Y-%m-%d %H:%i:%s')
        WHEN TIMESTAMPDIFF(MONTH, Time, NOW()) >= 1 THEN DATE_FORMAT(Time, '%Y-%m-%d %H:00:00')
        ELSE DATE_FORMAT(Time, '%Y-%m-%d %H:%i:00')
    END
ORDER BY GroupedTime;

3. PostgreSQL #

SELECT
    EquID,
    CellID,
    StnID,
    CASE
        -- 不足24小时，保留到秒
        WHEN NOW() - Time < INTERVAL '24 hours' THEN DATE_TRUNC('second', Time)
        -- 超过1个月，保留到小时
        WHEN NOW() - Time >= INTERVAL '1 month' THEN DATE_TRUNC('hour', Time)
        -- 24小时到1个月的中间区间，默认按分钟合并（可按需调整）
        ELSE DATE_TRUNC('minute', Time)
    END AS GroupedTime,
    SUM("Count") AS TotalCount
FROM YourLogTable
GROUP BY
    EquID,
    CellID,
    StnID,
    CASE
        WHEN NOW() - Time < INTERVAL '24 hours' THEN DATE_TRUNC('second', Time)
        WHEN NOW() - Time >= INTERVAL '1 month' THEN DATE_TRUNC('hour', Time)
        ELSE DATE_TRUNC('minute', Time)
    END
ORDER BY GroupedTime;

注意事项 #

• 替换YourLogTable为实际表名
• 24小时到1个月的中间区间粒度可按需调整，比如改为5分钟、15分钟级
• 若需将合并后的数据写入新表，可配合INSERT INTO ... SELECT语句实现
• 建议通过数据库定时任务定期执行合并逻辑，将旧数据归档或替换原表数据，持续节省存储

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