数据库查询：逾期90天缺失付款及补缴付款覆盖周期问题

我来帮你搞定这两个数据库查询需求，咱们分场景拆解：

一、查找逾期90天的缺失付款 #

这里的核心是找到未付款的缴款，同时该员工距离上次成功缴款的时间已经超过90天。我们可以用CTE先预计算每个员工的最后一次付款日期，再关联筛选未付款且逾期的记录：

WITH employee_last_paid AS (
    SELECT 
        c.employees_id,
        MAX(p.paid_at) AS last_paid_date
    FROM contributions c
    JOIN payments p ON c.payment_id = p.id
    WHERE p.paid_at IS NOT NULL
    GROUP BY c.employees_id
)
SELECT 
    c.id AS contribution_id,
    c.employees_id,
    c.starts_on AS contribution_start,
    c.ends_on AS contribution_end,
    elp.last_paid_date,
    CURRENT_DATE - elp.last_paid_date AS days_since_last_payment
FROM contributions c
LEFT JOIN payments p ON c.payment_id = p.id
JOIN employee_last_paid elp ON c.employees_id = elp.employees_id
WHERE p.paid_at IS NULL  -- 筛选未付款的缴款
  AND CURRENT_DATE - elp.last_paid_date > 90  -- 距离上次付款超90天
ORDER BY c.employees_id, c.starts_on;

说明： #

• employee_last_paid 这个CTE会先统计每个员工最后一次成功付款的日期；
• 主查询关联未付款的缴款记录，计算并筛选出逾期90天以上的条目；
• 如果你想以缴款的起始日期作为逾期计算节点，可以把 CURRENT_DATE 换成 c.starts_on，根据业务需求调整即可。

二、获取补缴付款对应的覆盖周期 #

补缴付款通常是指一次付款覆盖了多个缴款周期（或单个长周期），这里分两种常见场景处理：

场景1：单个缴款周期超过1个月的补缴 #

如果你的业务中“覆盖超过一个月”指单个缴款的周期跨度大于30天，用这个查询：

SELECT 
    p.id AS payment_id,
    p.amount_pennies,
    p.paid_at AS payment_date,
    c.id AS contribution_id,
    c.starts_on AS coverage_start,
    c.ends_on AS coverage_end,
    (c.ends_on - c.starts_on) AS coverage_days
FROM payments p
JOIN contributions c ON p.id = c.payment_id
WHERE (c.ends_on - c.starts_on) > 30  -- 筛选周期超30天的缴款
ORDER BY p.id, c.starts_on;

场景2：一次付款覆盖多个周期总时长超1个月 #

如果是一次付款对应多个缴款，总覆盖跨度超过30天（比如补缴了3个月的费用），用这个聚合查询：

SELECT 
    p.id AS payment_id,
    p.amount_pennies,
    p.paid_at AS payment_date,
    MIN(c.starts_on) AS overall_coverage_start,
    MAX(c.ends_on) AS overall_coverage_end,
    (MAX(c.ends_on) - MIN(c.starts_on)) AS total_coverage_days
FROM payments p
JOIN contributions c ON p.id = c.payment_id
GROUP BY p.id, p.amount_pennies, p.paid_at
HAVING (MAX(c.ends_on) - MIN(c.starts_on)) > 30  -- 总覆盖时长超30天
ORDER BY p.id;

说明： #

• 两个场景可以根据你的实际业务定义选择，前者聚焦单个长周期缴款，后者聚焦多周期的补缴汇总；
• 可以把 30 换成 INTERVAL '1 month'（如果你的数据库支持 interval 类型，比如PostgreSQL），这样更贴合“一个月”的语义。

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