PAYATHON 2026

如何重构 Postgres 查询以获取当前账单周期内的所有购买记录？

支付老李

结论

不要把月份和日期拆开比较。应先算出每位 customer 当前账单周期的起始日期，再按完整的日期范围筛选 purchases。

判断条件应写成：

EXTRACT(DAY FROM CURRENT_DATE) >= c.billing_date

这里不能漏掉等号。当天正好是 billing day 时，周期应从本月的 billing day 开始，当天的 purchases 也应计入新周期。

重构后的查询

下面的写法假设 billing_date 在 1 到 28 之间，确保每个月都有对应日期：

SELECT
    p.customer_id,
    SUM(p.subtotal) AS total_period_purchases
FROM purchases AS p
JOIN customer AS c
    ON c.id = p.customer_id
WHERE p.date >=
    CASE
        WHEN EXTRACT(DAY FROM CURRENT_DATE) >= c.billing_date THEN
            date_trunc('month', CURRENT_DATE)
                + (c.billing_date - 1) * INTERVAL '1 day'
        ELSE
            date_trunc('month', CURRENT_DATE)
                - INTERVAL '1 month'
                + (c.billing_date - 1) * INTERVAL '1 day'
    END
AND p.date < CURRENT_DATE + INTERVAL '1 day'
GROUP BY p.customer_id;

具体逻辑如下：

  • 今天已经到达或超过 billing day，周期从本月的 billing day 开始。
  • 今天还没到 billing day，周期从上个月的 billing day 开始。
  • p.date < CURRENT_DATE + INTERVAL '1 day' 会包含今天全天，但不会包含之后的日期。

假设 billing day 是 26：

  • 当前日期是 10 月 19 日，周期起点为 9 月 26 日。
  • 当前日期是 10 月 26 日，周期起点为 10 月 26 日。
  • 当前日期是 10 月 30 日，周期起点仍为 10 月 26 日。

为什么不应使用 DATE_PART 分别比较

分别比较 MONTH 和 DAY，会把连续的日期范围拆成多个条件分支。查询会变得更复杂，也更容易漏掉年份边界。例如，只比较月份时，不同年份的同一个月份会被当成相同月份。

另外，在 p.date 上调用：

DATE_PART('MONTH', p.date)

通常会影响 PostgreSQL 使用 p.date 上的普通索引。改用完整日期的范围条件后，可以配合下面的索引：

CREATE INDEX ON purchases (customer_id, date);

如果 billing_date 可能是 29、30 或 31

这时需要先确定业务规则，因为有些月份不存在这些日期。常见处理方式是把 billing day 顺延，或者截取为当月最后一天。

如果采用“截取为当月最后一天”的规则，可以先计算实际有效的账单日期：

WITH customer_periods AS (
    SELECT
        c.id AS customer_id,
        CASE
            WHEN CURRENT_DATE >= current_bill_date THEN current_bill_date
            ELSE previous_bill_date
        END AS period_start
    FROM customer AS c
    CROSS JOIN LATERAL (
        SELECT
            date_trunc('month', CURRENT_DATE)::date AS current_month_start,
            (
                date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
            )::date AS previous_month_start
    ) AS months
    CROSS JOIN LATERAL (
        SELECT
            months.current_month_start
                + LEAST(
                    c.billing_date,
                    EXTRACT(
                        DAY FROM (
                            months.current_month_start
                            + INTERVAL '1 month'
                            - INTERVAL '1 day'
                        )
                    )::integer
                ) - 1 AS current_bill_date,

            months.previous_month_start
                + LEAST(
                    c.billing_date,
                    EXTRACT(
                        DAY FROM (
                            months.current_month_start
                            - INTERVAL '1 day'
                        )
                    )::integer
                ) - 1 AS previous_bill_date
    ) AS billing_dates
)
SELECT
    p.customer_id,
    SUM(p.subtotal) AS total_period_purchases
FROM purchases AS p
JOIN customer_periods AS cp
    ON cp.customer_id = p.customer_id
WHERE p.date >= cp.period_start
  AND p.date < CURRENT_DATE + INTERVAL '1 day'
GROUP BY p.customer_id;

这样，2 月份的 billing day 31 会自动按 2 月最后一天处理。

如果 purchases.date 是 date 类型，结束条件也可以写成：

AND p.date <= CURRENT_DATE

如果字段类型是 timestamp 或 timestamptz，使用半开区间更稳妥：

AND p.date >= period_start
AND p.date < CURRENT_DATE + INTERVAL '1 day'

使用 timestamptz 时，还要确认数据库 session 时区与账单业务时区一致，否则午夜前后的记录可能会被计入错误的账单日期。

备注：内容仅供参考。