Psql：获取每个 partner 的 invoice 统计与最后付款

结论

先分别聚合 invoice 和 payment，再按 partner_id 关联，可以避免明细表直接连接后，同一张 invoice 因对应多笔付款而被重复计算。

如果 min number、min date、max number 和 max date 是四个独立的统计值，可以这样查询：

WITH invoice_stats AS (
    SELECT
        partner_id,
        MIN(number)       AS min_number,
        MIN(invoice_date) AS min_date,
        MAX(number)       AS max_number,
        MAX(invoice_date) AS max_date,
        COUNT(*)          AS invoice_count
    FROM invoice
    GROUP BY partner_id
),
payment_stats AS (
    SELECT
        partner_id,
        MAX(payment_date) AS last_pay
    FROM payment
    GROUP BY partner_id
)
SELECT
    i.partner_id,
    i.min_number,
    i.min_date,
    i.max_number,
    i.max_date,
    i.invoice_count,
    p.last_pay
FROM invoice_stats AS i
LEFT JOIN payment_stats AS p
    ON p.partner_id = i.partner_id
ORDER BY i.partner_id;

为什么要先聚合再关联

一个 partner 通常有多张 invoice，也可能有多笔 payment。如果直接连接两个明细表：

FROM invoice AS i
LEFT JOIN payment AS p
    ON p.partner_id = i.partner_id

每张 invoice 都可能匹配该 partner 的每笔 payment。比如某个 partner 有 10 张 invoice 和 3 笔 payment，连接后可能产生 30 行，COUNT(*) 也会随之被放大。

先分别按 partner_id 聚合后，每个子查询中的一个 partner 只占一行。此时再连接，就不会重复计数。

没有付款记录时的结果

这里使用 LEFT JOIN。只要 partner 有 invoice，就会出现在查询结果中；如果没有付款记录，last_pay 为 NULL。

如果只需要返回至少有一笔付款的 partner，可以把 LEFT JOIN 改成 INNER JOIN：

JOIN payment_stats AS p
    ON p.partner_id = i.partner_id

日期的显示格式

如果 invoice_date 和 payment_date 是 PostgreSQL 的 date 或 timestamp 类型，建议保留原始类型，由应用层负责格式化。

如果必须让 SQL 直接输出 DD-MM-YYYY 格式，可以使用 TO_CHAR：

WITH invoice_stats AS (
    SELECT
        partner_id,
        MIN(number)       AS min_number,
        MIN(invoice_date) AS min_date,
        MAX(number)       AS max_number,
        MAX(invoice_date) AS max_date,
        COUNT(*)          AS invoice_count
    FROM invoice
    GROUP BY partner_id
),
payment_stats AS (
    SELECT
        partner_id,
        MAX(payment_date) AS last_pay
    FROM payment
    GROUP BY partner_id
)
SELECT
    i.partner_id,
    i.min_number,
    TO_CHAR(i.min_date, 'DD-MM-YYYY') AS min_date,
    i.max_number,
    TO_CHAR(i.max_date, 'DD-MM-YYYY') AS max_date,
    i.invoice_count,
    TO_CHAR(p.last_pay, 'DD-MM-YYYY') AS last_pay
FROM invoice_stats AS i
LEFT JOIN payment_stats AS p
    ON p.partner_id = i.partner_id
ORDER BY i.partner_id;

TO_CHAR 返回的是文本。如果后续还要按日期排序、筛选或计算，最好不要在统计查询中提前格式化。

invoice number 与日期是否需要来自同一行

上面的查询分别计算：

MIN(number)
MIN(invoice_date)
MAX(number)
MAX(invoice_date)

这四个值相互独立。因此，min_number 不一定来自日期最早的那张 invoice，max_number 也不一定来自日期最新的那张 invoice。

如果 number 是文本类型，MIN() 和 MAX() 会按字符串排序。只有当编号格式固定，并且年份和序号都补齐位数时，例如 INV-2020-005，字符串顺序通常才能反映编号顺序。

如果需要取得“最早一张 invoice 的编号和日期”以及“最新一张 invoice 的编号和日期”，可以使用 DISTINCT ON：

WITH invoice_counts AS (
    SELECT
        partner_id,
        COUNT(*) AS invoice_count
    FROM invoice
    GROUP BY partner_id
),
first_invoice AS (
    SELECT DISTINCT ON (partner_id)
        partner_id,
        number AS min_number,
        invoice_date AS min_date
    FROM invoice
    ORDER BY partner_id, invoice_date ASC, id ASC
),
last_invoice AS (
    SELECT DISTINCT ON (partner_id)
        partner_id,
        number AS max_number,
        invoice_date AS max_date
    FROM invoice
    ORDER BY partner_id, invoice_date DESC, id DESC
),
payment_stats AS (
    SELECT
        partner_id,
        MAX(payment_date) AS last_pay
    FROM payment
    GROUP BY partner_id
)
SELECT
    c.partner_id,
    f.min_number,
    f.min_date,
    l.max_number,
    l.max_date,
    c.invoice_count,
    p.last_pay
FROM invoice_counts AS c
JOIN first_invoice AS f
    ON f.partner_id = c.partner_id
JOIN last_invoice AS l
    ON l.partner_id = c.partner_id
LEFT JOIN payment_stats AS p
    ON p.partner_id = c.partner_id
ORDER BY c.partner_id;

这里用 id 处理日期相同的情况，确保排序结果稳定。前提是 id 能表示明确的先后顺序。如果业务采用其他排序规则，应换成对应的字段。

索引建议

数据量较大时，可以考虑建立以下索引：

CREATE INDEX ON invoice (partner_id);
CREATE INDEX ON payment (partner_id, payment_date DESC);

如果经常查询每个 partner 最早和最新的 invoice，还可以建立：

CREATE INDEX ON invoice (partner_id, invoice_date, id);

具体选择哪种查询，取决于字段的业务含义。如果需要四个字段各自的最小值和最大值，使用第一种聚合查询；如果编号必须与最早、最晚日期来自同一条记录，则使用第二种查询。

备注：内容仅供参考。