sql经典案例3：求中位数

 在阅读《sql进阶教程》时，Having的章节，有一个案例是求中位数，书中给的案例我认为过于复杂了，可以用更简单的函数实现

书上给的：

 

--按照书上用having的方法

    --求中位数的SQL语句：在HAVING子句中使用非等值自连接

    SELECT AVG(DISTINCT income)

      FROM (SELECT T1.income

              FROM Graduates T1, Graduates T2

            GROUP BY T1.income

            --S1的条件

      HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)

                >= COUNT(*) / 2

            --S2的条件

        AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)

                >= COUNT(*) / 2 ) TMP;

--没怎么看的懂这个

思考：仔细想想，求中位数，是先将所要求中位数的那个字段从小到大排序，然后取出中间的，需要有两个条件：

第一：先得知道有多少行

第二：确定行的奇偶，若是奇数行，则取出中间的那一个，若是偶数行，取中间那两个数的平均值

通用做法：

• 想一想：如果知道了行数，有一列字段是关于行数的标记，那么我可以直接用where条件来确定中间那一行来取中位数了
• 而用row_number刚好可以创造出一列“行数”

select avg(a.income) from 
(
select name,income,row_number()over(order by income) as rn,
count(1)over() as card--总行数
from graduates
) a
where a.rn = ceiling(a.total/2.0)  or a.rn = floor(a.total/2.0)+1
--如果是偶数行如10，则取得是中间那一行与前一行的平均值 ceiling(10/2.0) =5行和floor(10/2.0)+1=6行的平均值
--如果是奇数行为9，则取得是ceiling(9/2.0) =5 和floor(a.total/2.0)+1 =4+1=5，即第五行

 补充：

1、在 MySQL 中计算中位数可以使用 SELECT AVG(column_name) AS median_value FROM table_name ORDER BY column_name LIMIT 1 OFFSET (SELECT COUNT(*) FROM table_name) / 2，

2、在 Oracle 中可以使用 SELECT MEDIAN(column_name) FROM table_name

3、在hive中可以使用ntile窗口函数