SQL的逾期发展

Question

尽管我做出了努力，但我无法想到一种方法来满足我的需求：

我有2个表格，分别包含一套贷款要偿还的贷款，以及对这些贷款的一系列偿还（并非所有贷款都有报价，因为其中一些贷款都可以参与其中保持无薪）。

表贷款有列：

• ID
• TABLE
• DUE_DATE

偿还具有列：

• ID
• 债务_id
• paymend_date

我的目标是获得逾期5天以上日期以上的金额过去的。

这是我位于（在Postgres语法中）的位置：

with due_for_5_days_by_date AS (
    select 
    due_date + interval '5' DAY as due_date,
    sum(amount) as amount
    FROM loan
    GROUP BY due_date
),
paid_by_date as (
    select 
        payment_date,
        sum(amount) as amount
    FROM reimbursement
    join loan on loan.id = loan_id
    GROUP BY payment_date
),
cumulated as (
    SELECT
        COALESCE(due_date, payment_date) as date,
        SUM(COALESCE(paid_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as paid,
        SUM(COALESCE(due_for_5_days_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as due_5d
    FROM due_for_5_days_by_date
    FULL OUTER JOIN paid_by_date on due_date = payment_date
)
select 
    DATE,
    due_5d - paid as overdue_5d
from cumulated
order by date

说明：

• 首先，我获得每个日期恰好5天的金额
• ，然后我得到每个日期付款的金额
• ，然后我将所获得的金额计入1。和2。获取累积的应付金额和付费金额
• ，然后按差额我获得逾期金额

，但问题是，通过此任务，按日期支付的金额还包含贷款的报销，该贷款持续了少于5天。换句话说，最终查询中每个日期的付费金额被高估了，因为它应该排除该日期最近到期的贷款金额。我想不出一种解决这个问题的方法。

您能想到一种获得我想要的东西吗？

非常感谢您的帮助！

Answer 1

我刚刚找到了这种方法，但是它使用了一个十字连接，因此不确定它在我的整个数据上是否会很好地扩展，即使它是在BigQuery上托管的：

with all_dates as (
    select distinct payment_date as date
    from reimbursement
    union
    select distinct due_date as date
    from loan
    union
    select distinct due_date + interval '5' day as date
    from loan
), 
joined_data as (
    SELECT
        amount,
        due_date,
        payment_date
    from loan
    LEFT join reimbursement on loan.id = loan_id
)
select 
    date,
    SUM(
      CASE
        WHEN (due_date <= DATE - INTERVAL '5' day) and (payment_date > DATE or payment_date is null) then amount
        else 0
      END
    ) as overdue_5d
FROM all_dates
cross join joined_data
group by date
order by DATE