Snowflake SQL优化：自动获取非空最新有效日期数据

以下查询会自动获取最新的有效工作日数据，自动跳过无数据的周末及节假日：

-- 先定义CTE，获取所有符合条件的有效日期（非周末、非节假日、有数据）
WITH valid_dates AS (
    SELECT 
        DATE_TRUNC('day', CONVERT_TIMEZONE('America/Los_Angeles','Europe/Amsterdam', 
            TRY_TO_TIMESTAMP_NTZ(CONCAT(TRY_TO_DATE(TO_CHAR(dt), 'YYYYMMDD'), ' ', 
                CAST(TO_TIMESTAMP(LPAD(TO_CHAR(tm, 'FM000000'), 6, '0'), 'HH24MISS') AS TIME))
        ))::DATE AS work_date
    FROM Table2
    WHERE xtp = '743' AND xcf = '003' AND adc = '12'
    -- 过滤周末：Snowflake中DAYOFWEEK返回1=周日，7=周六，工作日为2-6
    AND DAYOFWEEK(work_date) BETWEEN 2 AND 6
    -- 过滤节假日：如果有自定义节假日表，取消下面注释并替换表名
    -- AND work_date NOT IN (SELECT holiday_date FROM HOLIDAYS_TABLE)
    GROUP BY work_date
    HAVING COUNT(*) > 0 -- 确保该日期有数据
),
-- 获取最新的有效日期
latest_valid_date AS (
    SELECT MAX(work_date) AS target_date
    FROM valid_dates
)

SELECT 
    whse,
    SUM(pspq) AS total_pspq
FROM Table1
WHERE 
    rwd IN ('3','17','21','40') 
    AND xone <> 'J'
    AND pln NOT LIKE 'H%'
    AND rwsd IN (
        SELECT DISTINCT asw
        FROM Table2, latest_valid_date
        WHERE 
            xtp = '743' AND xcf = '003' AND adc = '12'
            AND CONVERT_TIMEZONE('America/Los_Angeles','Europe/Amsterdam', 
                TRY_TO_TIMESTAMP_NTZ(CONCAT(TRY_TO_DATE(TO_CHAR(dt), 'YYYYMMDD'), ' ', 
                    CAST(TO_TIMESTAMP(LPAD(TO_CHAR(tm, 'FM000000'), 6, '0'), 'HH24MISS') AS TIME))
            )::TIMESTAMP_NTZ 
            BETWEEN target_date || ' 00:01:00' AND target_date || ' 23:59:00'
    )
GROUP BY whse;

说明： #

• 如果你的环境有官方/自定义节假日表，请取消valid_dates中节假日过滤的注释，替换为实际的节假日表名和字段名
• DAYOFWEEK的判断逻辑可根据你的地区调整（部分地区周日是工作日的话，需修改范围）

• 时区转换逻辑优化：把Table2中dt和tm的转换逻辑封装成UDF（用户自定义函数），避免重复写复杂的转换代码，提升可读性和维护性。示例：

  CREATE OR REPLACE FUNCTION CONVERT_TO_AMSTERDAM_TS(dt INT, tm INT)
  RETURNS TIMESTAMP_NTZ
  AS $$
      CONVERT_TIMEZONE('America/Los_Angeles','Europe/Amsterdam', 
          TRY_TO_TIMESTAMP_NTZ(CONCAT(TRY_TO_DATE(TO_CHAR(dt), 'YYYYMMDD'), ' ', 
              CAST(TO_TIMESTAMP(LPAD(TO_CHAR(tm, 'FM000000'), 6, '0'), 'HH24MISS') AS TIME))
      )::TIMESTAMP_NTZ
  $$;
  

  之后查询中直接调用CONVERT_TO_AMSTERDAM_TS(dt, tm)即可。

• 子查询性能优化：原查询中SELECT DISTINCT asw可以改成用GROUP BY去重，再用INNER JOIN替代IN，大数据量下性能更优：

  -- 替换原rwsd IN (...)的部分
  INNER JOIN (
      SELECT asw
      FROM Table2, latest_valid_date
      WHERE 
          xtp = '743' AND xcf = '003' AND adc = '12'
          AND CONVERT_TO_AMSTERDAM_TS(dt, tm) BETWEEN target_date || ' 00:01:00' AND target_date || ' 23:59:00'
      GROUP BY asw
  ) t2 ON Table1.rwsd = t2.asw
  

• 索引/聚类键建议：

  • 给Table2的xtp、xcf、adc字段创建联合索引，或者设置为聚类键，加速过滤条件的查询
  • 给Table1的rwd、xone、pln、rwsd字段创建联合索引，提升WHERE子句的过滤效率

• 日期处理优化：如果Table2中的dt和tm可以提前合并成标准时间戳字段存储，避免每次查询都做字符串拼接和转换，能大幅提升查询速度。

• 节假日管理：维护一个统一的节假日表，定期更新节假日数据，确保查询能准确跳过节假日。

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