如何使用SQLite基于县级人口收入数据计算美国各州基尼系数

计算逻辑与SQLite实现代码 #

以下代码可直接适配你提供的县级收入人口数据集完成各州基尼系数计算，默认你的原始数据表名为county_income，字段与你给出的样例完全一致：

WITH state_total AS (
    -- 统计每个州的总人口、总收入作为计算基数
    SELECT 
        State,
        SUM(TotalPop) AS state_total_pop,
        SUM(TotalPop * IncomePerCap) AS state_total_income
    FROM county_income
    GROUP BY State
),
county_metrics AS (
    -- 计算单个县的人口占比、收入占比，按州内人均收入升序排序
    SELECT 
        c.State,
        c.County,
        c.TotalPop,
        c.IncomePerCap,
        1.0 * c.TotalPop / st.state_total_pop AS pop_pct,
        1.0 * (c.TotalPop * c.IncomePerCap) / st.state_total_income AS income_pct
    FROM county_income c
    JOIN state_total st ON c.State = st.State
    ORDER BY c.State, c.IncomePerCap ASC
),
cumulative_metrics AS (
    -- 计算累计人口占比、累计收入占比
    SELECT 
        State,
        pop_pct,
        income_pct,
        SUM(pop_pct) OVER (PARTITION BY State ORDER BY IncomePerCap ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_pop_pct,
        SUM(income_pct) OVER (PARTITION BY State ORDER BY IncomePerCap ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_income_pct
    FROM county_metrics
),
lorenz_area AS (
    -- 用梯形法计算洛伦兹曲线下的总面积
    SELECT 
        State,
        SUM(
            (COALESCE(LAG(cum_income_pct) OVER (PARTITION BY State ORDER BY cum_pop_pct ASC), 0) + cum_income_pct) 
            * pop_pct 
            / 2
        ) AS area_under_lorenz
    FROM cumulative_metrics
    GROUP BY State
)
-- 代入公式计算最终基尼系数
SELECT 
    State,
    ROUND(1 - 2 * area_under_lorenz, 4) AS gini_coefficient
FROM lorenz_area;

新数据实体创建方法 #

如果需要将计算得到的各州基尼系数保存为独立的新表，直接在上述查询前增加建表语句即可：

CREATE TABLE state_gini AS
-- 此处粘贴上面完整的WITH查询代码即可
WITH state_total AS (
    SELECT 
        State,
        SUM(TotalPop) AS state_total_pop,
        SUM(TotalPop * IncomePerCap) AS state_total_income
    FROM county_income
    GROUP BY State
),
county_metrics AS (
    SELECT 
        c.State,
        c.County,
        c.TotalPop,
        c.IncomePerCap,
        1.0 * c.TotalPop / st.state_total_pop AS pop_pct,
        1.0 * (c.TotalPop * c.IncomePerCap) / st.state_total_income AS income_pct
    FROM county_income c
    JOIN state_total st ON c.State = st.State
    ORDER BY c.State, c.IncomePerCap ASC
),
cumulative_metrics AS (
    SELECT 
        State,
        pop_pct,
        income_pct,
        SUM(pop_pct) OVER (PARTITION BY State ORDER BY IncomePerCap ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_pop_pct,
        SUM(income_pct) OVER (PARTITION BY State ORDER BY IncomePerCap ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_income_pct
    FROM county_metrics
),
lorenz_area AS (
    SELECT 
        State,
        SUM(
            (COALESCE(LAG(cum_income_pct) OVER (PARTITION BY State ORDER BY cum_pop_pct ASC), 0) + cum_income_pct) 
            * pop_pct 
            / 2
        ) AS area_under_lorenz
    FROM cumulative_metrics
    GROUP BY State
)
SELECT 
    State,
    ROUND(1 - 2 * area_under_lorenz, 4) AS gini_coefficient
FROM lorenz_area;

执行完成后会生成名为state_gini的新表，每行对应一个州的基尼系数，默认保留4位小数，可根据需求调整精度。

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