Presto SQL中使用contains数组函数计算工作日的问题

你的代码存在两个核心问题：

• dataset 和 holidays 直接做笛卡尔积，会导致每条业务记录重复关联节假日数据，计算结果失真
• array_agg(date) 是聚合函数，只能用于汇总多行数据，无法在 filter 的行级 lambda 表达式中用来判断单个日期是否属于节假日

以下是两种可行的修正方案：

方案一：预聚合节假日为数组，再过滤日期序列 #

先将所有节假日合并成一个数组，再关联业务数据集，最后在日期序列过滤时判断日期是否不在节假日数组中：

WITH dataset(start_date, end_date) AS (
 values     (date '2021-10-01', date '2021-10-05'),
    (date '2021-10-01', date '2021-10-03'),
    (date '2021-10-02', date '2021-10-10'),
    (date '2021-10-02', date '2021-10-08'),
    (date '2021-10-02', date '2021-10-05')
),
holidays_arr AS (
    SELECT array_agg(date) AS holiday_list
    FROM (values (date '2021-10-08')) AS t(date)
)

select 
    start_date,
    end_date,
    cardinality(filter(
            sequence(start_date, end_date, interval '1' day),
             d -> day_of_week(d) not in (6,7) 
                 and not contains(holiday_list, d)
        )) business_days
from dataset, holidays_arr

方案二：用 EXISTS 子查询判断节假日 #

通过子查询直接判断当前日期是否存在于节假日表中，无需聚合数组：

WITH dataset(start_date, end_date) AS (
 values     (date '2021-10-01', date '2021-10-05'),
    (date '2021-10-01', date '2021-10-03'),
    (date '2021-10-02', date '2021-10-10'),
    (date '2021-10-02', date '2021-10-08'),
    (date '2021-10-02', date '2021-10-05')
),
holidays (date) AS (
    values (date '2021-10-08')
)

select 
    start_date,
    end_date,
    cardinality(filter(
            sequence(start_date, end_date, interval '1' day),
             d -> day_of_week(d) not in (6,7) 
                 and not exists (select 1 from holidays where date = d)
        )) business_days
from dataset

说明 #

• day_of_week(d) 返回值中，6代表周六，7代表周日，符合你的过滤逻辑
• 方案一适合节假日数量较少的场景，数组查询效率较高；方案二更适合节假日表数据量较大的场景，避免生成过大的数组

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