如何在SQL中计算每日未结清发票的条件运行总计

核心逻辑 #

单日未结清发票金额统计规则：对任意统计日终，汇总满足以下全部条件的发票金额即可：

• 开票日期 ≤ 统计日（发票已开出）
• 还款日期为空 或 还款日期 > 统计日（当日未完成还款）
  统计日及之前已还款的发票，不计入当日总额。

实现代码 #

以MySQL 8.0+版本为例，通过递归CTE生成连续日期序列，避免无业务发生的日期漏统：

-- 假设源表名为invoices，对带空格的字段名用反引号做标识符转义
WITH RECURSIVE date_series AS (
    -- 起始日期取表中最早的开票日期
    SELECT MIN(`creation day`) AS stat_date
    FROM invoices
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    -- 截止日期取表中最晚的业务日期（开票/还款），可按需替换为CURDATE()统计到当日
    WHERE stat_date < (
        SELECT GREATEST(
            MAX(`creation day`),
            MAX(IFNULL(`repayment day`, `creation day`))
        ) FROM invoices
    )
)
SELECT
    ds.stat_date AS `Date`,
    SUM(iv.amount) AS `Outstanding invoice`
FROM date_series ds
LEFT JOIN invoices iv
    ON iv.`creation day` <= ds.stat_date
    AND (iv.`repayment day` IS NULL OR iv.`repayment day` > ds.stat_date)
GROUP BY ds.stat_date
ORDER BY ds.stat_date;

结果验证 #

用提供的样例数据运行上述代码，返回结果和期望值完全匹配：

• 2022-07-02：200
• 2022-07-03：400
• 2022-07-04：500
• 2022-07-05：1100
• 2022-07-06：1000

注意事项 #

• 不同数据库生成连续日期的语法存在差异：PostgreSQL可直接用generate_series函数生成日期序列，大数据组件（Hive/SparkSQL）可使用explode(sequence())实现，日期生成后的关联汇总逻辑完全通用
• 如果库中字段名包含空格（如creation day），需要使用对应数据库的标识符包裹（MySQL用反引号、SQL Server用方括号、PostgreSQL用双引号），避免语法报错
• 如果需要统计截止到当前日期的全部未结清数据，将递归CTE中的截止日期条件替换为CURDATE()即可

内容的提问来源于stack exchange，提问作者joe.somewhere