如何在Impala SQL中排除两国节假日计算有效交易期限

现有deals表结构及数据如下：

需要为每行计算交易期限，但需排除两个对应国家中任意一个的周末或节假日。配套的Holidays表记录了各国每日的日期类型（1=工作日，2=节假日/周末），数据如下：

使用Impala SQL方言尝试了以下查询，但未成功（未考虑日期重叠，且无法使用UDF）：

SELECT id,
       sub_id,
       deal_start,
       deal_end,
       country_A,
       country_B,
       DATEDIFF(d.deal_end, d.deal_start) AS dirty_term,
       COUNT(hA.date) AS country_A_hd,
       COUNT(hB.date) AS country_B_hd,
       (DATEDIFF(d.deal_end, d.deal_start) - GREATEST(COUNT(hA.date), COUNT(hA.date))
FROM deals AS d
LEFT JOIN holydays AS hA ON hA.country=d.country_A AND hA.date BETWEEN d.deal_start AND d.deal_end AND hA.day_type = 2
LEFT JOIN holydays AS hB ON hB.country=d.country_B AND hB.date BETWEEN d.deal_start AND d.deal_end AND hB.day_type = 2
GROUP BY id,
       sub_id,
       deal_start,
       deal_end,
       country_A,
       country_B

期望结果 #

计算逻辑 #

核心逻辑：仅当交易当天两个国家均为工作日时，才计入有效交易期限；若任意一个国家为节假日/周末，则该天不计入。以示例组合为例：

注：total_AB_hd表示当天至少一个国家为节假日/周末的天数；real_term = datediff - total_AB_hd（或直接统计两国均为工作日的天数）。

使用Impala支持的递归CTE生成交易日期范围，关联节假日表后按天判断有效性，最终聚合计算结果：

WITH date_range AS (
    -- 初始化：每个交易的起始日期
    SELECT id, sub_id, deal_start AS dt, deal_end
    FROM deals
    UNION ALL
    -- 递归生成后续日期，直到到达deal_end
    SELECT id, sub_id, dt + INTERVAL 1 DAY, deal_end
    FROM date_range
    WHERE dt < deal_end
),
deal_dates AS (
    -- 关联原交易表，补充完整交易信息
    SELECT dr.id, dr.sub_id, dr.dt,
           d.deal_start, d.deal_end, d.country_A, d.country_B
    FROM date_range dr
    JOIN deals d ON dr.id = d.id AND dr.sub_id = d.sub_id
),
holiday_check AS (
    -- 按日期获取两个国家的工作日类型，未记录的日期默认视为工作日
    SELECT dd.id, dd.sub_id, dd.dt,
           MAX(CASE WHEN h.country = dd.country_A THEN h.day_type ELSE 1 END) AS a_day_type,
           MAX(CASE WHEN h.country = dd.country_B THEN h.day_type ELSE 1 END) AS b_day_type
    FROM deal_dates dd
    LEFT JOIN holidays h ON h.date = dd.dt AND h.country IN (dd.country_A, dd.country_B)
    GROUP BY dd.id, dd.sub_id, dd.dt
)
-- 聚合计算最终结果
SELECT 
    id,
    sub_id,
    deal_start,
    deal_end,
    country_A,
    country_B,
    DATEDIFF(deal_end, deal_start) AS datediff,
    -- 计算至少一个国家为节假日的天数
    COUNT(*) - SUM(CASE WHEN a_day_type = 1 AND b_day_type = 1 THEN 1 ELSE 0 END) AS total_AB_hd,
    -- 计算两国均为工作日的有效期限
    SUM(CASE WHEN a_day_type = 1 AND b_day_type = 1 THEN 1 ELSE 0 END) AS real_term
FROM holiday_check
GROUP BY id, sub_id, deal_start, deal_end, country_A, country_B
ORDER BY id, sub_id;

说明 #

1. 递归CTE生成日期范围：解决无法使用UDF生成序列的问题，覆盖交易的每一天（从deal_start到deal_end）。
2. 节假日关联与判断：通过CASE语句匹配两个国家的工作日类型，未在Holidays表中出现的日期默认视为工作日。
3. 聚合计算：统计有效工作日数，反向推导节假日天数，最终得到符合要求的交易期限。

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