信贷项目 连续逾期用SQL怎么实现

在SQL中，要查询信贷项目的连续逾期情况，通常需要处理用户的历史还款数据，可能涉及到日期比较和计算逾期次数。这是一个基本示例，假设我们有一个名为`credit_transactions`的表，其中包含`transaction_id`（交易ID）、`user_id`（用户ID）、`loan_id`（贷款ID）、`payment_date`（还款日期）等字段：

CREATE TABLE credit_transactions (
    id INT PRIMARY KEY,
    user_id INT,
    loan_id INT,
    payment_date DATE
);

-- 假设逾期状态在当前日期之后，比如超过30天
WITH overdue_transactions AS (
    SELECT 
        user_id, 
        loan_id, 
        t1.payment_date as first_overdue_date,
        ROW_NUMBER() OVER (PARTITION BY user_id, loan_id ORDER BY payment_date DESC) as row_num
    FROM 
        credit_transactions t1
    LEFT JOIN 
        credit_transactions t2 ON t1.user_id = t2.user_id AND t1.loan_id = t2.loan_id AND DATEDIFF(t1.payment_date, t2.payment_date) > 30
    WHERE 
        t2.transaction_id IS NULL
)
SELECT 
    user_id, 
    loan_id,
    first_overdue_date,
    CASE 
        WHEN row_num = 1 THEN 'First Overdue'
        WHEN row_num <= 2 THEN 'Second Overdue'
        ELSE 'Continuous Overdue'
    END as consecutive_oversight_status
FROM 
    overdue_transactions;

这个查询首先找出每个用户的首次逾期交易，然后基于连续的逾期情况进行分类。实际应用中可能需要根据业务需求调整逾期天数阈值和连续逾期的定义。