SQL Athena计算两个日期之间的工作日数

原始表结构 #

WITH my_table (start_date, end_date) AS (
    values 
    ('2021-10-01','2021-10-05'),
    ('2021-10-01','2021-10-03'),
    ('2021-10-02','2021-10-10'),
    ('2021-10-02','2021-10-08'),
    ('2021-10-02','2021-10-05')
) 
SELECT *
FROM my_table

原始表数据 #

需求 #

统计每个start_date到end_date区间内周一至周五的工作日数量，期望结果如下：

期望结果 #

解决方案 #

使用generate_series生成日期区间内的所有日期，筛选出周一至周五的日期并计数：

WITH my_table (start_date, end_date) AS (
    values 
    ('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'::date)
)
SELECT 
    start_date,
    end_date,
    COUNT(CASE WHEN EXTRACT(DOW FROM dt) BETWEEN 1 AND 5 THEN 1 END) AS business_days
FROM my_table
CROSS JOIN LATERAL generate_series(start_date, end_date, '1 day'::interval) dt
GROUP BY start_date, end_date
ORDER BY start_date, end_date;

逻辑说明 #

1. generate_series(start_date, end_date, '1 day'::interval)：生成从start_date到end_date的所有日期序列
2. EXTRACT(DOW FROM dt)：获取日期对应的星期几（周日为0，周六为6），筛选1-5（周一至周五）的日期
3. COUNT(...)：统计符合条件的工作日数量
4. 按start_date和end_date分组，还原原表的每一行数据

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