如何在SQL中按不同业务月周期统计各账户类型借方余额

需求说明 #

需统计过去6个月内，各自定义业务月对应的三类账户借方总余额，不同账户类型的业务月规则如下：

• Normal类型：每月2日至次月2日为一个业务月（例：1月2日至2月2日属于1月业务月）
• Premimum类型：每月5日至次月5日为一个业务月
• Super_prem类型：每月10日至次月10日为一个业务月

原方案通过DATEADD拆分各账户类型处理后关联，复杂度高且执行效率低，以下是更优的单扫描聚合实现方案。

测试交易数据 #

account  createddate  closed_date   account_type  Debit_Amount  txn_date

1234      01/02/2023   01/01/2099   Normal         100           01/02/2023
7892      02/02/2023   01/01/2099   Premimum       200           01/02/2023 
4567      03/02/2023   01/01/2099   Normal         500           01/02/2023 
8790      05/02/2023   01/01/2099   Normal         500           05/02/2023
8890      05/02/2023   01/01/2099   Super_prem     500           05/03/2023
8330      06/02/2023   01/01/2099   Normal         500           05/02/2023
8990      08/02/2023   01/01/2099   Normal         500           04/02/2023
8490      04/02/2023   01/01/2099   Premimum       500           05/03/2023
8550      05/02/2023   01/01/2099   Normal         500           05/03/2023
8660      05/02/2023   01/01/2099   Super_prem     500           05/03/2023
8340      06/02/2023   01/01/2099   Normal         500           05/02/2023
8120      08/02/2023   01/01/2099   Normal         500           02/02/2023
8890      04/02/2023   01/01/2099   Premimum       500           05/03/2023

优化SQL实现 #

WITH business_month_calc AS (
    SELECT
        account_type,
        Debit_Amount,
        txn_date,
        -- 计算业务月起始日期，用于排序和归属判断
        CASE account_type
            WHEN 'Normal' THEN 
                DATEFROMPARTS(
                    YEAR(CASE WHEN DAY(txn_date) < 2 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    MONTH(CASE WHEN DAY(txn_date) < 2 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    2
                )
            WHEN 'Premimum' THEN
                DATEFROMPARTS(
                    YEAR(CASE WHEN DAY(txn_date) < 5 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    MONTH(CASE WHEN DAY(txn_date) < 5 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    5
                )
            WHEN 'Super_prem' THEN
                DATEFROMPARTS(
                    YEAR(CASE WHEN DAY(txn_date) < 10 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    MONTH(CASE WHEN DAY(txn_date) < 10 THEN DATEADD(MONTH, -1, txn_date) ELSE txn_date END),
                    10
                )
        END AS business_month_start,
        -- 生成易读的业务月标签（如"2023-01业务月"）
        CONCAT(
            FORMAT(
                CASE account_type
                    WHEN 'Normal' THEN DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 2))
                    WHEN 'Premimum' THEN DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 5))
                    WHEN 'Super_prem' THEN DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 10))
                END, 'yyyy-MM'
            ), '业务月'
        ) AS business_month_label
    FROM your_table_name
    -- 筛选过去6个月的交易，可根据实际需求调整时间范围逻辑
    WHERE txn_date >= DATEADD(MONTH, -6, GETDATE())
)
SELECT
    business_month_label,
    account_type,
    SUM(Debit_Amount) AS total_debit
FROM business_month_calc
GROUP BY business_month_label, account_type, business_month_start
ORDER BY business_month_start DESC, account_type;

方案思路 #

1. 单表扫描计算业务月：通过CTE一次性为每条交易计算对应的业务月归属，无需拆分多表关联，减少IO开销
2. 动态业务月判断：利用CASE WHEN根据账户类型的规则，判断交易所属的业务月：
   • 若交易日期早于当月的业务月起始日，则归属到上月的业务月
   • 否则归属当月业务月
3. 高效聚合：直接按业务月标签和账户类型聚合求和，逻辑简洁且执行效率高

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