Redshift中如何用SQL高效判断日期是否在多组日期区间内

我在Redshift有一张约500万行的表，其中Period_Starts和Period_Ends是varchar类型，存储了任意数量的日期对，每组起始日期和对应结束日期构成一个区间（比如某一行的区间是2020-03-02 ~ 2021-02-06和2021-02-07 ~ 2022-01-01）。现在需要实现一个功能：判断Date列的日期是否落在任意一个区间内，若匹配则返回对应的区间格式（比如匹配第一个区间就返回"2020-03-02:2021-02-06"）。

目前我用Python UDF实现了这个功能，但运行速度很慢，想知道能不能只用SQL实现更高效的方案。

原Python UDF代码 #

create function f_check_period(date_to_check Date, starts varchar, ends varchar)
  returns varchar
stable
as $$
  import datetime
  starts_list = [datetime.datetime.strptime(x, "%Y-%m-%d") for x in starts.split(",")]
  ends_list = [datetime.datetime.strptime(x, "%Y-%m-%d") for x in ends.split(",")]
  
  for i in range(0,len(starts_list)):
      if starts_list[i] <= date_to_check <= ends_list[i]:
          return starts_list[i].strftime("%Y-%m-%d") + ":" + ends_list[i].strftime("%Y-%m-%d")

$$ language plpythonu;

核心逻辑 #

借助Redshift内置的REGEXP_SPLIT_TO_TABLE函数将逗号分隔的日期串拆分为行，通过行号保证起始/结束日期的对应关系，再匹配日期区间，最后聚合得到结果。这种方案利用Redshift的分布式计算引擎优化，比Python UDF逐行解析的效率高得多。

完整SQL代码 #

WITH split_periods AS (
    SELECT
        -- 替换为你的表主键或唯一标识字段，用于关联回原表
        id,
        date_col,
        CAST(s_start.val AS DATE) AS period_start,
        CAST(s_end.val AS DATE) AS period_end,
        -- 生成行号，确保起始和结束日期一一对应
        s_start.rn AS period_idx
    FROM your_table
    -- 拆分起始日期串，带行号
    LEFT JOIN REGEXP_SPLIT_TO_TABLE(Period_Starts, ',') WITH ORDINALITY AS s_start(val, rn) ON 1=1
    -- 通过行号关联对应位置的结束日期
    LEFT JOIN REGEXP_SPLIT_TO_TABLE(Period_Ends, ',') WITH ORDINALITY AS s_end(val, rn) ON s_start.rn = s_end.rn
    -- 过滤无效日期（可选，根据实际数据情况调整）
    WHERE s_start.val ~ '^\d{4}-\d{2}-\d{2}$' 
      AND s_end.val ~ '^\d{4}-\d{2}-\d{2}$'
),
matched AS (
    SELECT
        id,
        date_col,
        CONCAT(period_start, ':', period_end) AS matched_interval,
        period_idx
    FROM split_periods
    WHERE date_col BETWEEN period_start AND period_end
)
SELECT
    t.*,
    COALESCE(m.matched_interval, '') AS matched_interval
FROM your_table t
LEFT JOIN matched m 
    ON t.id = m.id 
    AND t.date_col = m.date_col
-- 取第一个匹配的区间（按拆分顺序），若需所有匹配可改用STRING_AGG
QUALIFY ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY m.period_idx) = 1;

性能优化要点 #

1. 主键/排序键：确保id是表的主键或排序键，Redshift会利用排序键加速关联操作。
2. 过滤无效数据：在split_periods中添加日期格式校验，避免无效数据占用计算资源。
3. 减少数据扫描：如果只需要处理特定日期范围的数据，添加WHERE date_col BETWEEN 'xxxx-xx-xx' AND 'xxxx-xx-xx'缩小处理范围。
4. 避免冗余计算：使用QUALIFY直接筛选每个主行的第一个匹配结果，减少后续数据处理量。

注意事项 #

• 必须保证Period_Starts和Period_Ends的逗号分隔日期数量完全一致，否则会出现日期对不匹配；若存在不一致情况，可在split_periods中添加校验（比如统计两个字段拆分后的行数是否相等）。
• 如果一行有多个匹配区间，上述SQL返回第一个匹配的；若需要返回所有匹配区间，可将最后一步改为STRING_AGG(m.matched_interval, ',') AS matched_intervals，并去掉QUALIFY子句。

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