PostgreSQL 12.8如何统计两个日期间各星期几的天数

适用场景 #

• 表中存在start_date日期字段，需统计每行记录从start_date到current_date（当前日期）区间内，周一到周日每个星期几的累计出现天数
• 环境为PostgreSQL 12.8，配套工具不支持部分高版本语法（如负索引），要求仅用内置函数实现，无需创建自定义函数
• 预期返回字段：start_date（起始日期）、day_of_week（星期几编号，1=周一、7=周日）、no_of_days（对应星期几累计天数），验证基准：2022-04-01至2022-06-06区间内周一累计10天
• 此前执行DATEPART('day', start - stop) AS days失败属于语法适配问题，PostgreSQL中取日期部分建议用原生EXTRACT函数

实现代码 #

版本1：逻辑直观易读（适合数据量中等场景） #

利用PostgreSQL原生GENERATE_SERIES函数生成连续日期序列后分组计数，所有语法在PG12版本全兼容，无自定义函数依赖：

-- 替换your_table为实际业务表名
WITH continuous_date AS (
    SELECT
        t.start_date,
        GENERATE_SERIES(t.start_date, CURRENT_DATE, '1 day'::interval)::date AS stat_date
    FROM your_table t
)
SELECT
    start_date,
    EXTRACT(ISODOW FROM stat_date)::integer AS day_of_week,
    COUNT(*) AS no_of_days
FROM continuous_date
GROUP BY start_date, day_of_week
ORDER BY start_date, day_of_week;

逻辑说明：

• 针对每一行的start_date，生成从起始日到当前日期的所有连续日期，无需手写循环或自定义函数
• 用EXTRACT(ISODOW FROM 日期)获取星期编号，返回值1对应周一、7对应周日，符合常规计数习惯
• 分组计数后直接得到每个星期几的累计出现天数，经测试2022-04-01至2022-06-06区间的周一计数为10，和预期一致

版本2：高性能计算版（适合大数据量/长周期场景） #

不展开逐天日期，通过数学计算直接得到结果，哪怕时间跨度超过百年也能毫秒级返回，同样兼容PG12所有语法：

SELECT
    t.start_date,
    dow::integer AS day_of_week,
    (total_days / 7) + 
    CASE
        WHEN start_dow <= end_dow AND dow BETWEEN start_dow AND end_dow THEN 1
        WHEN start_dow > end_dow AND (dow >= start_dow OR dow <= end_dow) THEN 1
        ELSE 0
    END AS no_of_days
FROM (
    SELECT
        start_date,
        (CURRENT_DATE - start_date) + 1 AS total_days,
        EXTRACT(ISODOW FROM start_date)::integer AS start_dow,
        EXTRACT(ISODOW FROM CURRENT_DATE)::integer AS end_dow
    FROM your_table t
) t
CROSS JOIN GENERATE_SERIES(1,7) dow
ORDER BY start_date, day_of_week;

逻辑说明：

• 先计算区间总天数，除以7得到整周带来的固定各星期几天数
• 再判断剩余不足一周的时间段内是否包含对应星期几，存在则额外加1
• 关联GENERATE_SERIES(1,7)直接生成周一到周日的编号，无需额外建维表

注：两个版本都没有用到PostgreSQL 12之后的新增语法，不存在工具兼容问题，也不需要创建任何自定义函数。

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