如何修改BigQuery存储过程以支持数组类型的台风/黑雨时间输入

问题说明 #

现有BigQuery存储过程Summary_Typhoon_BlackRain仅支持单次输入一组台风/黑暴雨的起止时间，每次调用只能处理单组范围，需手动多次调用并合并结果。现需将参数改为数组类型，支持一次性传入多组时间范围，自动完成所有匹配逻辑，无需手动合并输出表。

原存储过程代码：

CREATE OR REPLACE PROCEDURE `Summary_Typhoon_BlackRain`(TyphoonStart TIMESTAMP, TyphoonEnd TIMESTAMP, BlackRainStart TIMESTAMP, BlackRainEnd TIMESTAMP)
BEGIN

CREATE OR REPLACE TABLE `Summary_Typhoon_BlackRain` AS(

SELECT -- Enter the time in UTC format not in HKT format
      PairingDutyId, 
      EmployeeCode,
      PairingDutyStartDate, PairingDutyEndDate, -- PairingDutyStartDate = Reporting Time, PairingDutyEndDate = Release Time
      ArrivalActual_UTC, -- Actual In Time (OOOI)
      Flight_No,
      DEPSTN, ARRSTN,
      (CASE 
          WHEN DEPSTN = 'HKG' AND TyphoonStart IS NOT NULL THEN
            CASE
              WHEN PairingDutyStartDate <= TIMESTAMP_ADD(TyphoonStart, INTERVAL -90 MINUTE) THEN 0
              WHEN PairingDutyStartDate >  TIMESTAMP_ADD(TyphoonStart, INTERVAL -90 MINUTE) AND PairingDutyStartDate < TIMESTAMP_ADD(TyphoonEnd, INTERVAL 90 MINUTE) THEN 1 
              WHEN PairingDutyStartDate >= TIMESTAMP_ADD(TyphoonEnd, INTERVAL 90 MINUTE) THEN 0
              ELSE 0
            END
          WHEN ARRSTN = 'HKG' AND TyphoonStart IS NOT NULL THEN
            CASE
              WHEN ArrivalActual_UTC <= TyphoonStart AND PairingDutyEndDate <= TyphoonStart THEN 0
              WHEN ArrivalActual_UTC <= TyphoonStart AND (PairingDutyEndDate > TyphoonStart AND PairingDutyEndDate < TyphoonEnd) THEN 1
              WHEN (ArrivalActual_UTC > TyphoonStart AND ArrivalActual_UTC < TyphoonEnd) AND (PairingDutyEndDate > TyphoonStart AND PairingDutyEndDate < TyphoonEnd) THEN 1
              WHEN (ArrivalActual_UTC > TyphoonStart AND ArrivalActual_UTC < TyphoonEnd) AND PairingDutyEndDate >= TyphoonEnd THEN 1
              WHEN ArrivalActual_UTC >= TyphoonEnd AND PairingDutyEndDate >= TyphoonEnd THEN 0
              ELSE 0
            END
        END) AS Typhoon,
      (CASE
        WHEN DEPSTN = 'HKG' AND BlackRainStart IS NOT NULL THEN
            CASE
              WHEN PairingDutyStartDate <= TIMESTAMP_ADD(BlackRainStart, INTERVAL -90 MINUTE) THEN 0
              WHEN PairingDutyStartDate >  TIMESTAMP_ADD(BlackRainStart, INTERVAL -90 MINUTE) AND PairingDutyStartDate < TIMESTAMP_ADD(BlackRainEnd, INTERVAL 90 MINUTE) THEN 1 
              WHEN PairingDutyStartDate >= TIMESTAMP_ADD(BlackRainEnd, INTERVAL 90 MINUTE) THEN 0
              ELSE 0
            END
      END) AS BlackRain
    FROM 
      `BlockHourAdjustment`);

END;

期望调用方式：

DECLARE TyphoonStarts ARRAY<TIMESTAMP> DEFAULT NULL;
DECLARE TyphoonEnds ARRAY<TIMESTAMP> DEFAULT NULL;
DECLARE BlackRainStarts ARRAY<TIMESTAMP> DEFAULT NULL;
DECLARE BlackRainEnds ARRAY<TIMESTAMP> DEFAULT NULL;
CALL `Summary_Typhoon_BlackRain`([TIMESTAMP '2023-07-16 16:40:00.000 UTC',TIMESTAMP '2023-08-31 18:40:00.000 UTC'], [TIMESTAMP '2023-07-17 08:40:00.000 UTC',TIMESTAMP '2023-09-01 08:20:00.000 UTC'], NULL, NULL);

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE `Summary_Typhoon_BlackRain`(
  TyphoonStarts ARRAY<TIMESTAMP>, 
  TyphoonEnds ARRAY<TIMESTAMP>, 
  BlackRainStarts ARRAY<TIMESTAMP>, 
  BlackRainEnds ARRAY<TIMESTAMP>
)
BEGIN
  -- 校验台风起止时间数组长度一致
  IF TyphoonStarts IS NOT NULL AND TyphoonEnds IS NOT NULL AND ARRAY_LENGTH(TyphoonStarts) != ARRAY_LENGTH(TyphoonEnds) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'TyphoonStarts和TyphoonEnds数组长度必须一致';
  END IF;
  -- 校验黑暴雨起止时间数组长度一致
  IF BlackRainStarts IS NOT NULL AND BlackRainEnds IS NOT NULL AND ARRAY_LENGTH(BlackRainStarts) != ARRAY_LENGTH(BlackRainEnds) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'BlackRainStarts和BlackRainEnds数组长度必须一致';
  END IF;

  CREATE OR REPLACE TABLE `Summary_Typhoon_BlackRain` AS
  SELECT 
    PairingDutyId, 
    EmployeeCode,
    PairingDutyStartDate, PairingDutyEndDate,
    ArrivalActual_UTC,
    Flight_No,
    DEPSTN, ARRSTN,
    -- 判断是否匹配任意一组台风时间范围
    CASE 
      WHEN DEPSTN = 'HKG' AND TyphoonStarts IS NOT NULL THEN
        CASE
          WHEN EXISTS (
            SELECT 1 
            FROM UNNEST(TyphoonStarts) AS ts WITH OFFSET pos
            JOIN UNNEST(TyphoonEnds) AS te WITH OFFSET pos USING(pos)
            WHERE PairingDutyStartDate > TIMESTAMP_ADD(ts, INTERVAL -90 MINUTE) 
              AND PairingDutyStartDate < TIMESTAMP_ADD(te, INTERVAL 90 MINUTE)
          ) THEN 1
          ELSE 0
        END
      WHEN ARRSTN = 'HKG' AND TyphoonStarts IS NOT NULL THEN
        CASE
          WHEN EXISTS (
            SELECT 1 
            FROM UNNEST(TyphoonStarts) AS ts WITH OFFSET pos
            JOIN UNNEST(TyphoonEnds) AS te WITH OFFSET pos USING(pos)
            WHERE NOT (
              (ArrivalActual_UTC <= ts AND PairingDutyEndDate <= ts) 
              OR (ArrivalActual_UTC >= te AND PairingDutyEndDate >= te)
            )
          ) THEN 1
          ELSE 0
        END
      ELSE 0
    END AS Typhoon,
    -- 判断是否匹配任意一组黑暴雨时间范围
    CASE
      WHEN DEPSTN = 'HKG' AND BlackRainStarts IS NOT NULL THEN
        CASE
          WHEN EXISTS (
            SELECT 1 
            FROM UNNEST(BlackRainStarts) AS brs WITH OFFSET pos
            JOIN UNNEST(BlackRainEnds) AS bre WITH OFFSET pos USING(pos)
            WHERE PairingDutyStartDate > TIMESTAMP_ADD(brs, INTERVAL -90 MINUTE) 
              AND PairingDutyStartDate < TIMESTAMP_ADD(bre, INTERVAL 90 MINUTE)
          ) THEN 1
          ELSE 0
        END
      ELSE 0
    END AS BlackRain
  FROM `BlockHourAdjustment`;
END;

关键改动说明 #

• 参数类型调整：将原单个TIMESTAMP参数替换为ARRAY<TIMESTAMP>，支持传入多组起止时间
• 数组一致性校验：添加逻辑校验起止时间数组长度匹配，避免传入不对应的时间组
• 多范围匹配逻辑：使用UNNEST结合WITH OFFSET将数组按位置配对，通过EXISTS判断当前记录是否匹配任意一组时间范围，替代原单组时间判断逻辑
• 输出兼容：保持最终输出表结构与原过程一致，自动完成多组时间的匹配计算，无需手动合并多次调用结果

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