如何基于daterange类型统计每位用户今年已获批假期的总天数？

要实现管理员所需的统计需求，核心是聚合用户数据并准确计算本年度内获批假期的天数（包括处理跨年度假期的截断计算），基于你的PostgreSQL环境，可通过以下SQL语句实现：

SELECT
    u.id AS user_id,
    u.firstname,
    u.lastname,
    -- 计算本年度内的累计假期天数
    SUM(
        GREATEST(
            0,
            LEAST(UPPER(h.time_range), (DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year')::DATE)
            - GREATEST(LOWER(h.time_range), DATE_TRUNC('year', CURRENT_DATE)::DATE)
        )
    ) AS approved_holiday_days
FROM holidays h
INNER JOIN users u ON h.user_id = u.id
WHERE
    h.tenant_id = $1
    AND h.accepted_by_admin IS TRUE
    -- 仅保留与本年度有重叠的假期（过滤完全不在本年度的记录）
    AND h.time_range && DATE_TRUNC('year', CURRENT_DATE)::DATE || '/' || (DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year')::DATE
GROUP BY u.id, u.firstname, u.lastname
ORDER BY approved_holiday_days DESC;

关键逻辑说明： #

1. 日期范围过滤：

   • 用DATE_TRUNC('year', CURRENT_DATE)生成本年度的起始时间，结合+ INTERVAL '1 year'得到下一年起始时间，构建本年度的闭开区间[本年度起始, 下一年起始)
   • 通过&&操作符判断假期的time_range是否与本年度区间重叠，直接过滤掉完全不在本年度的无效记录

2. 单条假期的有效天数计算：

   • GREATEST(LOWER(h.time_range), 本年度起始)：取假期实际开始日期和本年度起始的较大值，确保只统计本年度内的假期起始部分
   • LEAST(UPPER(h.time_range), 下一年起始)：取假期的结束边界（因daterange是左闭右开，upper()返回的是不包含的结束日期）和下一年起始的较小值，确保只统计本年度内的假期结束部分
   • 两者相减得到该假期在本年度内的有效天数，用GREATEST(0, ...)避免出现负数（比如假期完全在本年度之前的极端情况）

3. 聚合统计：

   • 按用户ID、姓名分组，用SUM()累加每位用户的有效假期天数，最后按天数降序排序，方便管理员快速查看假期时长排名

补充说明： #

• 如果需要统计固定年份（而非当前年度），只需将CURRENT_DATE替换为目标年份的任意日期即可，比如统计2023年就用DATE_TRUNC('year', '2023-06-01'::DATE)
• 原查询中的LIMIT 50在统计场景中无需保留，我们需要的是所有符合条件用户的汇总数据

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