如何基于最新4条数据计算GINI系数？附分组计算参考代码

原始表结构与数据 #

现有表包含id、date、balance三个字段，示例数据如下：

现有SQL（按id分组计算整体GINI系数） #

已有一段按id分组计算全量数据GINI系数的SQL：

with cte_1 as (
select *,  ROW_NUMBER() over(partition by id order by balance) as  rank
from #t6
)

SELECT id,
    1 - 2 * sum((cast(balance as float) * (rank - 1) + balance / 2)) / count(*) / sum(balance) AS gini
FROM cte_1
GROUP BY id
ORDER BY id ASC 

需求 #

针对表中每一行，计算包含该行在内的最新4条数据的GINI系数（即基于date排序的滚动窗口，窗口范围为当前行及往前3行，若不足4条则取窗口内现有所有数据）。

解决方案SQL #

以下是实现滚动窗口GINI系数计算的SQL代码：

WITH ranked_data AS (
    -- 按id分组、date排序，生成行号用于确定滚动窗口范围
    SELECT 
        id,
        date,
        balance,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) AS row_num
    FROM #t6
),
window_data AS (
    -- 关联自身获取当前行及往前3行的滚动窗口数据，同时计算窗口内统计值
    SELECT 
        rd.id,
        rd.date,
        rd.balance,
        rd.row_num,
        w.balance AS window_balance,
        COUNT(w.balance) OVER(PARTITION BY rd.id, rd.row_num) AS window_count,
        SUM(w.balance) OVER(PARTITION BY rd.id, rd.row_num) AS window_sum_balance
    FROM ranked_data rd
    JOIN ranked_data w 
        ON w.id = rd.id 
        AND w.row_num BETWEEN rd.row_num - 3 AND rd.row_num
),
gini_calculation AS (
    -- 对每个滚动窗口内的balance排序，生成窗口内排名
    SELECT 
        id,
        date,
        balance,
        window_count,
        window_sum_balance,
        window_balance,
        ROW_NUMBER() OVER(PARTITION BY id, row_num ORDER BY window_balance) AS window_rank
    FROM window_data
)
-- 计算每行对应的滚动窗口GINI系数
SELECT 
    id,
    date,
    balance,
    1 - 2 * SUM((CAST(window_balance AS FLOAT) * (window_rank - 1) + window_balance / 2)) 
        OVER(PARTITION BY id, row_num) 
        / window_count 
        / window_sum_balance AS rolling_gini
FROM gini_calculation
GROUP BY id, date, balance, window_count, window_sum_balance
ORDER BY id, date;

代码说明 #

1. ranked_data：为每个id下的记录按date排序并生成行号，用于快速定位滚动窗口的范围。
2. window_data：通过自连接获取当前行及往前3行的所有数据，同时计算窗口内的总记录数和balance总和，为后续GINI计算提供基础统计值。
3. gini_calculation：对每个滚动窗口内的balance进行排序，生成窗口内的排名，这是GINI系数计算的必要步骤。
4. 最终查询：应用原有的GINI计算公式，按每个滚动窗口（即每行对应的窗口）计算GINI系数，得到每行的结果。

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