SQL如何计算分组内的基尼系数_编写复杂聚合逻辑实现-SQL-PHP中文网

基尼系数在SQL中无内置函数，因标准SQL未定义GINI()，须用窗口函数组合实现：排序、累计求和、比例计算及梯形法求面积，并处理单行、零值、重复值等边界情况。

sql如何计算分组内的基尼系数_编写复杂聚合逻辑实现

基尼系数在SQL里为什么不能直接用内置函数算

因为标准SQL（包括PostgreSQL、MySQL 8.0+、SQL Server）至今没有 GINI() 或类似聚合函数。你查文档会发现，连 PERCENTILE_CONT() 都是较新才加入的，基尼系数这种业务侧定义的统计量，必须手写逻辑。

它本质依赖排序、累计求和、比例计算，而这些在单条SQL里要靠窗口函数组合完成——不是不能做，是容易写错顺序或漏掉归一化步骤。

用窗口函数分四步推导基尼系数

假设有一张用户订单表 orders，字段为 user_id 和 amount，现在要按 region 分组计算各地区订单金额分布的基尼系数。

先对每组内 amount 升序排列，用 ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount)
算每组总金额：SUM(amount) OVER (PARTITION BY region)，这是分母基础
用 SUM(amount) OVER (PARTITION BY region ORDER BY amount ROWS UNBOUNDED PRECEDING) 算累计金额，再除以组总金额，得到洛伦兹曲线横纵坐标
基尼 = 1 − 2 × 洛伦兹曲线下面积，而面积可用梯形法近似：对相邻两行的累计比例做平均后乘以横坐标差（即 1.0 / 组内行数）

关键点：必须用 CAST(... AS DECIMAL) 避免整数除法；组内只有一行时基尼应为0，需加 CASE WHEN COUNT(*) = 1 THEN 0 特判。

PostgreSQL实操示例（带注释）

WITH ranked AS (
  SELECT
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount) AS rn,
    COUNT(*) OVER (PARTITION BY region) AS cnt,
    SUM(amount) OVER (PARTITION BY region) AS total_amt,
    SUM(amount) OVER (PARTITION BY region ORDER BY amount ROWS UNBOUNDED PRECEDING) AS cum_amt
  FROM orders
),
lorenz AS (
  SELECT
    region,
    rn,
    cnt,
    CAST(cum_amt AS DECIMAL) / NULLIF(total_amt, 0) AS cum_ratio,
    CAST(rn AS DECIMAL) / cnt AS rank_ratio
  FROM ranked
),
area AS (
  SELECT
    region,
    SUM(
      (cum_ratio + LAG(cum_ratio, 1, 0) OVER (PARTITION BY region ORDER BY rn))
      * (rank_ratio - LAG(rank_ratio, 1, 0) OVER (PARTITION BY region ORDER BY rn))
      / 2.0
    ) AS lorenz_area
  FROM lorenz
  GROUP BY region
)
SELECT
  region,
  CASE WHEN MAX(cnt) = 1 THEN 0.0
       ELSE 1.0 - 2.0 * MAX(lorenz_area)
  END AS gini_coefficient
FROM area
GROUP BY region;