基于发票收付数据的时间段债务最值、平均值查询及二月统计需求

首先明确下问题中的债务金额定义：某一天的债务总额 = 所有满足「发票应付日期 ≤ 当天 ≤ 发票实付日期」的发票金额之和。基于这个定义，我们可以通过生成日期序列并关联发票数据来计算每日债务，再做聚合统计。

先把你提供的发票数据整理成清晰的表格：

一、通用思路说明 #

要计算每日债务总额，核心步骤是：

1. 生成目标时间段内的所有日期（确保每天都被统计，哪怕当天债务为0）
2. 关联发票数据，计算每个日期对应的未付发票总金额
3. 对每日债务总额做聚合，得到最大值和平均值

下面分两种常见数据库（PostgreSQL 和 MySQL）给出具体实现代码。

二、PostgreSQL 实现代码 #

1. 查询指定时间段的最大/平均债务金额 #

假设指定时间段为 @start_date 到 @end_date（你可以替换成实际的日期范围，比如 '2019-01-01' 到 '2019-04-30'）：

WITH date_series AS (
    -- 生成指定时间段内的所有日期
    SELECT generate_series(@start_date::date, @end_date::date, '1 day'::interval) AS date_day
),
daily_debt AS (
    -- 计算每日债务总额
    SELECT
        ds.date_day::date,
        COALESCE(SUM(i.Amount), 0) AS daily_total_debt
    FROM date_series ds
    LEFT JOIN invoices i
        ON ds.date_day BETWEEN i.InvoiceDate AND i.InvoicePayment
    GROUP BY ds.date_day
)
-- 统计最大和平均债务
SELECT
    MAX(daily_total_debt) AS max_debt,
    AVG(daily_total_debt) AS avg_debt
FROM daily_debt;

2. 统计2019年2月的最大/平均债务金额 #

只需把日期范围固定为2019年2月：

WITH date_series AS (
    SELECT generate_series('2019-02-01'::date, '2019-02-28'::date, '1 day'::interval) AS date_day
),
daily_debt AS (
    SELECT
        ds.date_day::date,
        COALESCE(SUM(i.Amount), 0) AS daily_total_debt
    FROM date_series ds
    LEFT JOIN invoices i
        ON ds.date_day BETWEEN i.InvoiceDate AND i.InvoicePayment
    GROUP BY ds.date_day
)
SELECT
    MAX(daily_total_debt) AS max_debt_feb_2019,
    AVG(daily_total_debt) AS avg_debt_feb_2019
FROM daily_debt;

三、MySQL 实现代码 #

MySQL没有内置的generate_series函数，我们需要用递归CTE生成日期序列：

1. 查询指定时间段的最大/平均债务金额 #

WITH RECURSIVE date_series AS (
    SELECT @start_date AS date_day
    UNION ALL
    SELECT DATE_ADD(date_day, INTERVAL 1 DAY)
    FROM date_series
    WHERE date_day < @end_date
),
daily_debt AS (
    SELECT
        ds.date_day,
        COALESCE(SUM(i.Amount), 0) AS daily_total_debt
    FROM date_series ds
    LEFT JOIN invoices i
        ON ds.date_day BETWEEN i.InvoiceDate AND i.InvoicePayment
    GROUP BY ds.date_day
)
SELECT
    MAX(daily_total_debt) AS max_debt,
    AVG(daily_total_debt) AS avg_debt
FROM daily_debt;

2. 统计2019年2月的最大/平均债务金额 #

WITH RECURSIVE date_series AS (
    SELECT '2019-02-01' AS date_day
    UNION ALL
    SELECT DATE_ADD(date_day, INTERVAL 1 DAY)
    FROM date_series
    WHERE date_day < '2019-02-28'
),
daily_debt AS (
    SELECT
        ds.date_day,
        COALESCE(SUM(i.Amount), 0) AS daily_total_debt
    FROM date_series ds
    LEFT JOIN invoices i
        ON ds.date_day BETWEEN i.InvoiceDate AND i.InvoicePayment
    GROUP BY ds.date_day
)
SELECT
    MAX(daily_total_debt) AS max_debt_feb_2019,
    AVG(daily_total_debt) AS avg_debt_feb_2019
FROM daily_debt;

四、结果验证（基于你的数据） #

以2019年2月为例，手动计算几个关键日期的债务：

• 2019-02-01：Bill1(314) + Bill2(205) + Bill4(456) = 975
• 2019-02-05：Bill1(314) + Bill2(205) + Bill3(90) + Bill4(456) = 1065（这是2月的最大值）
• 2019-02-19：Bill1(314) + Bill4(456) = 770
• 2019-02-28：Bill1(314) + Bill4(456) = 770

运行代码后，2019年2月的最大债务应为1065，平均债务则是28天的每日总额之和除以28。

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