如何将小时级与日级汇总插入合并为单个存储过程？

嘿，这个需求完全可以实现，而且思路很清晰——先搞定小时级数据插入，再基于刚插的这批数据做日级汇总，咱们一步步拆解来看：

• 要不要提交第一个事务，核心看你对数据一致性的要求：
  • 如果希望小时级和日级数据必须同时成功/失败（比如日级汇总失败的话，小时级数据也得回滚），那把整个流程放在同一个事务里就行，不用提前提交。这样能保证两组数据的完整性，不会出现只有小时数据没有日汇总的情况。
  • 如果业务允许小时级数据先落地（哪怕日汇总失败，小时数据也保留），那可以先提交小时插入的事务，再开启新事务处理日级汇总。但这种场景要考虑后续补跑日汇总的逻辑，避免数据缺失。

完全可以用游标来处理刚插入的小时数据，但直接靠时间范围筛选可能会有并发干扰（比如其他进程同时插入同小时的数据）。更稳妥的做法是给这批插入的小时数据标记一个批次ID：

1. 插入小时数据前，生成一个唯一的批次ID（比如用NEWID()或者自增序列）；
2. 插入小时数据时，把这个批次ID写入value表的一个额外字段（比如batch_id）；
3. 之后用游标查询时，直接过滤batch_id = @your_batch_id，就能精准定位到自己刚插入的这批数据，不会误操作其他数据。

CREATE PROCEDURE InsertHourlyAndDailyData
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @batch_id UNIQUEIDENTIFIER = NEWID();
    DECLARE @current_date DATE;
    DECLARE @daily_sum DECIMAL(18,2);

    -- 开启事务（保证数据原子性）
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 第一步：插入小时级汇总数据到value表
        INSERT INTO value_table (hour_time, value, batch_id)
        SELECT 
            DATEADD(HOUR, DATEPART(HOUR, t.create_time), CAST(t.create_time AS DATE)) AS hour_time,
            SUM(t.amount) AS value,
            @batch_id
        FROM t
        -- 可根据业务添加时间筛选，比如处理最近未汇总的小时数据
        WHERE t.create_time >= DATEADD(HOUR, -24, GETDATE())
        GROUP BY DATEADD(HOUR, DATEPART(HOUR, t.create_time), CAST(t.create_time AS DATE));

        -- 第二步：用游标处理刚插入的小时数据，生成日级汇总
        DECLARE hourly_cursor CURSOR FOR
        SELECT 
            CAST(hour_time AS DATE) AS stat_date,
            SUM(value) AS daily_sum
        FROM value_table
        WHERE batch_id = @batch_id
        GROUP BY CAST(hour_time AS DATE);

        -- 打开游标并开始循环
        OPEN hourly_cursor;
        FETCH NEXT FROM hourly_cursor INTO @current_date, @daily_sum;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 插入/更新日级汇总表
            MERGE INTO daily_summary_table dst
            USING (SELECT @current_date AS stat_date, @daily_sum AS total_value) src
            ON dst.stat_date = src.stat_date
            WHEN MATCHED THEN
                UPDATE SET dst.total_value = dst.total_value + src.total_value
            WHEN NOT MATCHED THEN
                INSERT (stat_date, total_value) VALUES (src.stat_date, src.total_value);

            FETCH NEXT FROM hourly_cursor INTO @current_date, @daily_sum;
        END;

        -- 关闭并释放游标
        CLOSE hourly_cursor;
        DEALLOCATE hourly_cursor;

        -- 提交事务
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 出错则回滚事务并抛出错误
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

游标是逐行处理，效率不如集合操作高。如果不需要复杂的逐行逻辑，完全可以跳过游标，直接用INSERT...SELECT来生成日级汇总：

-- 替代游标的日级插入逻辑（SQL Server版本）
MERGE INTO daily_summary_table dst
USING (
    SELECT 
        CAST(hour_time AS DATE) AS stat_date,
        SUM(value) AS total_value
    FROM value_table
    WHERE batch_id = @batch_id
    GROUP BY CAST(hour_time AS DATE)
) src
ON dst.stat_date = src.stat_date
WHEN MATCHED THEN
    UPDATE SET dst.total_value = dst.total_value + src.total_value
WHEN NOT MATCHED THEN
    INSERT (stat_date, total_value) VALUES (src.stat_date, src.total_value);

这种方式代码更简洁，执行效率也更高，适合大部分汇总场景。

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