如何调整MySQL查询输出30/60/90天逾期金额占总金额的百分比

问题描述 #

我已编写了一个可输出客户信贷数据（合同、欠款金额等）的SQL查询，现需调整该查询，使其输出30天、60天、90天逾期金额的占比，但目前仅能实现全客户数据的占比计算。

表结构 #

CREATE TABLE mysql_test_a ( 
id_contract INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
id_client int4 NOT NULL, 
dt_start_ep date NOT NULL,  
dt_end_ep date NOT NULL, 
amt_overdue int(4) 
);

现有查询 #

SELECT id_client, count(id_contract) numb_cont,
 
count( CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 30 DAY) then 1 end) as 30_days,
count( CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 60 DAY) then 1 end) as 60_days,
count( CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 90 DAY) then 1 end) as 90_days,

sum(amt_overdue) as amt_sum,
sum(CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 30 DAY) 
        THEN amt_overdue 
            ELSE 0 
        END) AS 30_day_amt,
        
sum(CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 60 DAY) 
                THEN amt_overdue 
                ELSE 0 
            END) AS 60_day_amt,

sum(CASE WHEN dt_end_ep >= date_add(dt_start_ep,INTERVAL 90 DAY) 
            THEN amt_overdue 
            ELSE 0 
        END) AS 90_day_amt,

SUM(amt_overdue) OVER() / SUM(SUM(amt_overdue)) OVER(PARTITION BY id_client ORDER BY id_client ASC) AS percentage

from mysql_test_a

group by id_client,amt_overdue

现有查询问题分析 #

1. 分组逻辑错误：group by id_client,amt_overdue会将同一客户下不同欠款金额的合同拆分成多行统计，导致客户整体逾期数据碎片化，无法得到准确的汇总结果。
2. 占比计算逻辑混乱：原percentage字段的分子是全表逾期总额，分母是客户自身逾期总额的累加（窗口函数在分组后使用逻辑冲突），无法正确输出各区间逾期金额的占比。

修正后的查询方案 #

方案1：各逾期区间金额占客户自身总逾期金额的比例 #

该方案计算每个客户的30/60/90天逾期金额分别占该客户总逾期金额的百分比：

SELECT 
    id_client,
    COUNT(id_contract) AS numb_cont,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN 1 END) AS 30_days_count,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN 1 END) AS 60_days_count,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN 1 END) AS 90_days_count,
    SUM(amt_overdue) AS amt_sum,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN amt_overdue ELSE 0 END) AS 30_day_amt,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN amt_overdue ELSE 0 END) AS 60_day_amt,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN amt_overdue ELSE 0 END) AS 90_day_amt,
    -- 计算各区间占客户自身总逾期的比例，保留两位小数
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue), 0) * 100, 2) AS 30_day_pct,
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue), 0) * 100, 2) AS 60_day_pct,
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue), 0) * 100, 2) AS 90_day_pct
FROM mysql_test_a
GROUP BY id_client
ORDER BY id_client;

方案2：各逾期区间金额占全平台总逾期金额的比例 #

如果需要计算每个客户的各区间逾期金额在全平台总逾期中的占比，可使用窗口函数获取全平台总额：

SELECT 
    id_client,
    COUNT(id_contract) AS numb_cont,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN 1 END) AS 30_days_count,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN 1 END) AS 60_days_count,
    COUNT(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN 1 END) AS 90_days_count,
    SUM(amt_overdue) AS amt_sum,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN amt_overdue ELSE 0 END) AS 30_day_amt,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN amt_overdue ELSE 0 END) AS 60_day_amt,
    SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN amt_overdue ELSE 0 END) AS 90_day_amt,
    -- 计算各区间占全平台总逾期的比例，保留两位小数
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 30 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue) OVER(), 0) * 100, 2) AS 30_day_total_pct,
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 60 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue) OVER(), 0) * 100, 2) AS 60_day_total_pct,
    ROUND(SUM(CASE WHEN dt_end_ep >= DATE_ADD(dt_start_ep, INTERVAL 90 DAY) THEN amt_overdue ELSE 0 END) / NULLIF(SUM(amt_overdue) OVER(), 0) * 100, 2) AS 90_day_total_pct
FROM mysql_test_a
GROUP BY id_client
ORDER BY id_client;

关键调整说明 #

1. 修正分组逻辑：仅按id_client分组，确保每个客户只输出一行汇总数据。
2. 避免除数为0：使用NULLIF处理总逾期金额为0的情况，防止计算报错。
3. 明确字段含义：将原30_days等字段重命名为30_days_count，区分合同数量统计和金额统计，避免混淆。
4. 控制精度：用ROUND函数将百分比结果保留两位小数，提升可读性。

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