Snowflake循环插入聚合数据报错：语法错误/单行子查询返回多行

问题背景 #

需要通过循环移动时间窗口完成以下操作：

• 统计1小时时间窗口内的记录数
• 与过去7天对应时段的平均值做对比
• 每次循环将时间窗口起始点后移15分钟

现有代码运行时出现两类错误：

1. 语法错误：syntax error line 7 at position 17 unexpected '<'
2. 子查询错误：未捕获STATEMENT_ERROR：单行子查询返回多行

错误原因分析 #

1. 语法错误根源：

   • t1子查询错误地用括号包裹标量子查询(SELECT MAX(ID) + 1 AS ID ...)并与其他聚合字段并列，违反SQL语法规范
   • t2子查询中AVG(number)未配合合理的GROUP BY逻辑，内层UNION后重复GROUP BY导致语法混乱
   • SELECT字段与GROUP BY字段不匹配，如Merchant未在SELECT中正确关联

2. 单行子查询返回多行根源：

   • 部分子查询逻辑不合理，导致返回多条结果
   • t2子查询的关联字段未对齐，引发多对多关联导致数据膨胀

修复后的完整代码 #

1. 创建结果表（优化字段逻辑） #

CREATE OR REPLACE TABLE WHILE_LOOP_count_TEST__RESULTS (
  ID INT NULL,
  WINDOW_START TIMESTAMP_NTZ NULL, -- 存储当前统计窗口的起始时间
  WINDOW_END TIMESTAMP_NTZ NULL, -- 存储当前统计窗口的结束时间
  ID_COUNT INT NULL,
  PREV_7D_AVG FLOAT NULL, -- 均值用FLOAT更合理
  CREATE_DT TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP(),
  CREATE_USER VARCHAR(50) NOT NULL DEFAULT CURRENT_USER()
);
-- 初始化初始数据（用于生成ID起始值）
INSERT INTO WHILE_LOOP_count_TEST__RESULTS (ID, ID_COUNT, WINDOW_START) 
VALUES (1, 1, '2025-02-15 12:00'::TIMESTAMP_NTZ);

2. 修复后的循环插入代码 #

EXECUTE IMMEDIATE $$
DECLARE
  counter INTEGER := 1;
  current_window_start TIMESTAMP_NTZ := '2025-02-19 14:30:20.706000000Z'::TIMESTAMP_NTZ;
  current_window_end TIMESTAMP_NTZ := DATEADD(HOUR, 1, current_window_start);
BEGIN
  WHILE counter < 5 DO
    -- 插入统计结果
    INSERT INTO WHILE_LOOP_count_TEST__RESULTS 
      (ID, WINDOW_START, WINDOW_END, ID_COUNT, PREV_7D_AVG)
    SELECT 
      -- 生成自增ID
      (SELECT COALESCE(MAX(ID), 0) + 1 FROM WHILE_LOOP_count_TEST__RESULTS) AS ID,
      current_window_start AS WINDOW_START,
      current_window_end AS WINDOW_END,
      t1.ID_COUNT,
      COALESCE(t2.PREV_7D_AVG, 0) AS PREV_7D_AVG
    FROM (
      -- 统计当前1小时窗口的记录数
      SELECT 
        COUNT(DB1.TRANS_ID) AS ID_COUNT,
        DB1.MERCHANT
      FROM DB1 
      LEFT JOIN DB2 ON DB2.EXTERNAL_IDENTIFIER = DB1.TRANS_ID
      LEFT JOIN DB3 ON DB2.COUNTRY_CODE = DB3.COUNTRY_CODE
      WHERE
        DB2.TS > current_window_start
        AND DB2.TS < current_window_end
        AND DB2.TYPE = 'INI'
      GROUP BY DB1.MERCHANT
    ) t1
    LEFT JOIN (
      -- 计算过去7天对应时段的平均值（用GENERATE_SERIES简化重复UNION）
      SELECT 
        MERCHANT1,
        AVG(record_count) AS PREV_7D_AVG
      FROM (
        SELECT 
          COUNT(DB1.TRANS_ID) AS record_count,
          DB1.MERCHANT AS MERCHANT1
        FROM DB1
        LEFT JOIN DB2 ON DB2.EXTERNAL_IDENTIFIER = DB1.TRANS_ID
        LEFT JOIN DB3 ON DB2.COUNTRY_CODE = DB3.COUNTRY_CODE
        CROSS JOIN TABLE(GENERATE_SERIES(1, 7)) AS days(d)
        WHERE
          DB2.TS > DATEADD(DAY, -days.d, current_window_start)
          AND DB2.TS < DATEADD(DAY, -days.d, current_window_end)
          AND DB2.TYPE = 'INI'
        GROUP BY DB1.MERCHANT, days.d
        HAVING COUNT(DB1.TRANS_ID) > 30
      ) past_days
      GROUP BY MERCHANT1
    ) t2 ON t1.MERCHANT = t2.MERCHANT1;

    -- 移动时间窗口，后移15分钟
    current_window_start := DATEADD(MINUTE, 15, current_window_start);
    current_window_end := DATEADD(HOUR, 1, current_window_start);
    counter := counter + 1;
  END WHILE;
  RETURN counter;
END;
$$;

关键修复点说明 #

• 语法修正：移除子查询中多余的括号，将标量子查询正确放在SELECT列表中，确保SELECT与GROUP BY字段严格匹配
• 简化历史均值计算：用GENERATE_SERIES代替重复的UNION ALL，避免代码冗余和语法错误
• 解决单行子查询问题：用COALESCE处理可能的空值，确保子查询返回单行结果，同时优化关联逻辑避免多对多关联
• 字段逻辑优化：新增WINDOW_START和WINDOW_END字段明确存储统计窗口时间，替换原不合理的DT字段

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