基于折扣历史计算发票合格天数的SQL查询逻辑需求

问题描述 #

现有两张业务表：

• 发票表：存储发票核心信息，字段包含invoiceid（发票ID）、datefrom（发票生效起始日期）、dateto（发票生效结束日期）
• 折扣历史表：存储折扣生效的时间段，核心字段为discount_from（折扣起始日期）、discount_to（折扣结束日期）

需求：编写SQL查询，计算每个invoiceid对应的发票时间段与折扣时间段的重叠总天数。

示例说明：
针对invoiceid 229，其发票时间段与两段折扣时间重叠：

• 重叠段1：01-01-23 至 10-01-2023，共计10天
• 重叠段2：19-01-23 至 27-01-2023，共计9天
  最终该发票的合格折扣天数为19天。

通用SQL解决方案 #

SELECT
    i.invoiceid,
    SUM(
        DATEDIFF(
            LEAST(i.dateto, d.discount_to),
            GREATEST(i.datefrom, d.discount_from)
        ) + 1
    ) AS eligible_days
FROM
    invoices i
JOIN
    discount_history d
ON
    -- 筛选存在时间段重叠的记录对
    i.datefrom <= d.discount_to
    AND i.dateto >= d.discount_from
GROUP BY
    i.invoiceid;

逻辑说明 #

1. 关联筛选：通过JOIN条件仅保留发票时间段与折扣时间段有重叠的记录对，避免无效计算
2. 确定重叠区间：
   • GREATEST(i.datefrom, d.discount_from)：取发票起始日期和折扣起始日期的较大值，作为重叠区间的实际开始
   • LEAST(i.dateto, d.discount_to)：取发票结束日期和折扣结束日期的较小值，作为重叠区间的实际结束
3. 计算单段天数：用DATEDIFF计算日期差后加1，是为了包含起始和结束当天（例如01-01至10-01的日期差为9，加1后得到正确的10天）
4. 聚合求和：按invoiceid分组，将所有重叠段的天数累加，得到该发票的总合格折扣天数

数据库适配调整 #

不同数据库的日期计算函数存在差异，可按需替换：

• PostgreSQL：将DATEDIFF替换为DATE_PART('day', LEAST(i.dateto, d.discount_to) - GREATEST(i.datefrom, d.discount_from)) + 1
• SQL Server：使用DATEDIFF(day, GREATEST(i.datefrom, d.discount_from), LEAST(i.dateto, d.discount_to)) + 1
• Oracle：使用TRUNC(LEAST(i.dateto, d.discount_to)) - TRUNC(GREATEST(i.datefrom, d.discount_from)) + 1

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