PostgreSQL中基于表日期列计算每行工作日天数的问题

我之前用固定日期计算指定区间的工作日天数，代码是这样的：

SELECT SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) 
FROM (
    SELECT ('2007-04-01'::date + (generate_series(0,'2007-04-30'::date - '2007-04-01'::date)||'days')::interval) AS foo
) foo 

现在我想把固定日期换成myTable（代码里我写成了pto）表中的start_date和end_date字段，为每行输出对应的工作日天数。示例表数据如下：

我写了下面的代码，但输出结果不符合预期：

SELECT pto.start_date, pto.end_date, 
       SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) as theDIFF 
FROM ( 
    SELECT start_date, (start_date::date + (generate_series(0,end_date::date - start_date::date)||'days')::interval) AS foo 
    FROM pto 
) foo 
inner join pto pto on pto.start_date = foo.start_date 
group by pto.start_date, pto.end_date 

实际错误输出： #

预期输出： #

问题分析 #

你的代码问题出在不必要的表关联和generate_series的用法不够严谨：

1. 子查询里已经为每行生成了对应的日期序列，但后续又和原表pto做了inner join，导致每行的日期序列被重复关联，统计结果被放大。
2. 手动拼接interval的方式容易出错，PostgreSQL的generate_series其实支持直接传入日期范围生成序列，不用自己计算天数差。

修正后的代码 #

我们可以用LATERAL JOIN（PostgreSQL 9.3+支持）来为原表的每一行生成对应的日期序列，然后直接统计工作日数，这样逻辑更清晰，结果也准确：

SELECT 
    pto.start_date, 
    pto.end_date,
    COUNT(*) AS theDIFF
FROM pto
LATERAL (
    SELECT generate_series(pto.start_date::date, pto.end_date::date, '1 day'::interval) AS day
    WHERE pto.start_date <= pto.end_date -- 避免start_date大于end_date的异常情况
) AS date_series
WHERE extract(dow FROM date_series.day) IN (1,2,3,4,5) -- 1=周一，5=周五
GROUP BY pto.start_date, pto.end_date
ORDER BY pto.start_date;

代码说明 #

1. LATERAL JOIN会遍历pto表的每一行，为每行的start_date到end_date生成连续的日期序列。
2. 用COUNT(*)替代SUM(CASE...)，因为我们已经通过WHERE过滤掉了周末，剩下的都是工作日，直接计数即可。
3. 增加了WHERE pto.start_date <= pto.end_date的判断，避免出现开始日期晚于结束日期的错误情况。

测试验证 #

用你给出的测试数据：

运行修正后的代码会得到预期结果：

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