2021-12-11 拆解一道SQL 算中位数的语句

有如下表：

  name  | income 
--------+--------
 桑普森 | 400000
 迈克   |  30000
 怀特   |  20000
 肯特   |  10000
(4 rows)

请写出计算中位数的SQL语句：

SELECT
    avg(DISTINCT income)
FROM
    (
        SELECT
            t1.income
        FROM
            Graduates t1,
            Graduates t2
        GROUP BY
            t1.income
        HAVING
            sum(
                CASE
                    WHEN t2.income >= t1.income THEN 1
                    ELSE 0
                END
            ) >= COUNT(*) / 2.0
            AND sum(
                CASE
                    WHEN t2.income <= t1.income THEN 1
                    ELSE 0
                END
            ) >= COUNT(*) / 2.0
    ) tmp;

刚看到这种解法时，我是懵逼的，谁能告诉我这是啥？

没办法，拆解大法：先看看他造了个啥表：

SELECT
    t1.income,
    t2.income
FROM
    Graduates t1,
    Graduates t2;
 
 income | income 
--------+--------
 400000 | 400000
 400000 |  30000
 400000 |  20000
 400000 |  10000
  30000 | 400000
  30000 |  30000
  30000 |  20000
  30000 |  10000
  20000 | 400000
  20000 |  30000
  20000 |  20000
  20000 |  10000
  10000 | 400000
  10000 |  30000
  10000 |  20000
  10000 |  10000
(16 rows)

笛卡尔积，让每个元素和集合中所有的元素比较。

把聚合函数和Case表达式加上，看看这个表的变形形式：

SELECT
    t1.income,
    sum(
        CASE
            WHEN t2.income >= t1.income THEN 1
            ELSE 0
        END
    ) AS hi_eq,
    sum(
        CASE
            WHEN t2.income <= t1.income THEN 1
            ELSE 0
        END
    ) AS lo_eq,
    COUNT(*) / 2.0 AS cnt_2
FROM
    Graduates t1,
    Graduates t2
GROUP BY
    t1.income
ORDER BY
    t1.income;

 income | hi_eq | lo_eq | cnt_2 
--------+-------+-------+-------
  10000 |     4 |     1 |     2
  20000 |     3 |     2 |     2
  30000 |     2 |     3 |     2
 400000 |     1 |     4 |     2
(4 rows)

统计每一个元素，大于等于集合中所有元素，小于等于集合中所有元素的数量。

同时计算集合中元素数量的一半，注意不要约去奇数中的1.

SELECT
    t1.income
FROM
    Graduates t1,
    Graduates t2
GROUP BY
    t1.income
HAVING
    sum(
        CASE
            WHEN t2.income >= t1.income THEN 1
            ELSE 0
        END
    ) >= COUNT(*) / 2.0
    AND sum(
        CASE
            WHEN t2.income <= t1.income THEN 1
            ELSE 0
        END
    ) >= COUNT(*) / 2.0;

 income 
--------
  30000
  20000
(2 rows)

为什么能出现两个值？因为集合元素数量是偶数，必然是两个值取平均，如果是奇数，则不必再平均。

SELECT
    avg(DISTINCT income)
FROM
    (
        SELECT
            t1.income
        FROM
            Graduates t1,
            Graduates t2
        GROUP BY
            t1.income
        HAVING
            sum(
                CASE
                    WHEN t2.income >= t1.income THEN 1
                    ELSE 0
                END
            ) >= COUNT(*) / 2.0
            AND sum(
                CASE
                    WHEN t2.income <= t1.income THEN 1
                    ELSE 0
                END
            ) >= COUNT(*) / 2.0
    ) tmp;

        avg         
--------------------
 25000.000000000000
(1 row)

这个sql取中位数的思想很有意思，如果是C++，我大概的做法是：

1. 对集合sort()
2. 判断集合size()的奇偶，if(size() & 1)
3. 如果为奇数，则取第 size() / 2 + 1 的元素
4. 如果为偶数，则取第 size() /2 及 size() / 2 +1 元素的 avg()

但SQL是面向集合的语言，要用集合的思想解决，于是：

1. 让集合中每个元素 和集合中所有元素进行比较，累计大于等于它的个数，和小于等于它的个数。
2. 当这两个累计数都大于等于集合元素数量的二分之一时，意味着这个元素就是中位数，或者是中位数集合中的一个元素，通过求平均值可得中位数。

目测这种sql算法不适合太大的集合，算法时间增量是N^2的。

参考文献：《SQL进阶教程》作者：[日]MICK 译者：吴炎昌 人民邮电出版社 出版时间：2017-11