基于ETL更新触发创建RTM_SO_Totals统计SQL表的技术咨询

1. 创建目标统计表RTM_SO_Totals #

先创建包含所需字段的统计表，时间戳字段可设置默认值自动获取当前时间，也可后续插入时指定：

MySQL 版本 #

CREATE TABLE RTM_SO_Totals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    refresh_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    so_total INT NOT NULL,
    pallet_total INT NOT NULL
);

SQL Server 版本 #

CREATE TABLE RTM_SO_Totals (
    id INT IDENTITY(1,1) PRIMARY KEY,
    refresh_timestamp DATETIME NOT NULL DEFAULT GETDATE(),
    so_total INT NOT NULL,
    pallet_total INT NOT NULL
);

2. 两种自动触发统计的实现方案 #

方案一：数据库触发器（无需修改ETL管道） #

针对你的ETL流程（先截断RTM_SO，再批量插入新数据），配置语句级触发器，在批量插入完成后自动执行统计，避免单条数据插入重复触发：

MySQL 触发器

DELIMITER //
CREATE TRIGGER trg_rtm_so_after_insert
AFTER INSERT ON RTM_SO
FOR EACH STATEMENT
BEGIN
    -- 可选：清空旧统计数据，只保留最新结果；若需历史记录则删除此句
    TRUNCATE TABLE RTM_SO_Totals;
    -- 插入统计结果，时间戳自动取当前时间
    INSERT INTO RTM_SO_Totals (so_total, pallet_total)
    SELECT 
        COUNT(DISTINCT so_number), -- 替换为你的SO唯一标识字段
        SUM(pallet_count) -- 替换为你的托盘数字段
    FROM RTM_SO;
END //
DELIMITER ;

SQL Server 触发器

CREATE TRIGGER trg_rtm_so_after_insert
ON RTM_SO
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 可选：清空旧统计数据
    TRUNCATE TABLE RTM_SO_Totals;
    -- 插入统计结果
    INSERT INTO RTM_SO_Totals (so_total, pallet_total)
    SELECT 
        COUNT(DISTINCT so_number),
        SUM(pallet_count)
    FROM RTM_SO;
END;

方案二：修改ETL管道（更可控） #

如果之前修改ETL未成功，大概率是统计语句的执行时机错误，需将统计步骤放在数据完全上传完成后：

调整后的ETL步骤：

1. 截断RTM_SO表
2. 批量上传新数据到RTM_SO（确保事务提交或数据写入完成）
3. 执行统计插入语句：

-- 可选：清空旧统计数据
TRUNCATE TABLE RTM_SO_Totals;
-- 手动指定时间戳插入统计结果
INSERT INTO RTM_SO_Totals (refresh_timestamp, so_total, pallet_total)
SELECT 
    CURRENT_TIMESTAMP, -- MySQL/SQL Server 通用；SQL Server 也可用 GETDATE()
    COUNT(DISTINCT so_number),
    SUM(pallet_count)
FROM RTM_SO;

排查ETL失败原因：

• 确认统计语句在数据插入事务提交后执行
• 检查执行统计语句的账号是否有RTM_SO的查询权限和RTM_SO_Totals的写入权限
• 验证统计逻辑中的字段名与RTM_SO表实际字段完全匹配

3. 额外建议 #

• 若需保留历史统计记录，删除TRUNCATE语句，每次刷新新增一条统计数据即可
• 批量插入数据时，语句级触发器比行级触发器性能更优，避免重复计算
• 若ETL使用事务，确保统计操作在同一事务内，保证数据一致性

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