sql 中位数_23岁|【学习】SQL中关于中位数求解的思路

= abs((select sum(Frequency) from Numbers where Number<=n.Number) -"],[20,"\n","24:\"ec3j\"|36:177"],[20,"(select sum(Frequency) from Numbers where Number>=n.Number))"],[20,"\n","24:\"cwnd\"|36:177"],[20,"\n","24:\"ytQW\""],[20,"二、给一些数求中位数","27:\"13\"|8:1"],[20,"\n","24:\"Avoz\""],[20,"leetcode上569题是让求员工薪水中位数："],[20,"\n","24:\"bmEz\""],[30,[{"A1":[40,[[20,"Id","26:\"21868134\""],[20,"\n","24:\"ZOXR\""]],"25:\"CqZT\""],"B1":[40,[[20,"Company","26:\"21868134\""],[20,"\n","24:\"wgTA\""]],"25:\"2N8m\""],"C1":[40,[[20,"Salary","26:\"21868134\""],[20,"\n","24:\"X1or\""]],"25:\"ibR6\""],"A2":[40,[[20,"1","26:\"21868134\""],[20,"\n","24:\"THbz\""]],"25:\"GPLI\""],"B2":[40,[[20,"A","26:\"21868134\""],[20,"\n","24:\"uBdK\""]],"25:\"eSYh\""],"C2":[40,[[20,"1111","26:\"21868134\""],[20,"\n","24:\"1wlZ\""]],"25:\"bcp8\""],"A3":[40,[[20,"2","26:\"21868134\""],[20,"\n","24:\"Kv4z\""]],"25:\"8utM\""],"B3":[40,[[20,"B","26:\"21868134\""],[20,"\n","24:\"Xgzq\""]],"25:\"WqrT\""],"C3":[40,[[20,"3456","26:\"21868134\""],[20,"\n","24:\"I5BG\""]],"25:\"Hc3K\""],"A4":[40,[[20,"3","26:\"21868134\""],[20,"\n","24:\"POag\""]],"25:\"ANk8\""],"B4":[40,[[20,"C","26:\"21868134\""],[20,"\n","24:\"BtrM\""]],"25:\"S7zZ\""],"C4":[40,[[20,"4456","26:\"21868134\""],[20,"\n","24:\"l8an\""]],"25:\"OSwQ\""],"A5":[40,[[20,"4","26:\"21868134\""],[20,"\n","24:\"FrJc\""]],"25:\"DfmM\""],"B5":[40,[[20,"A","26:\"21868134\""],[20,"\n","24:\"NzWw\""]],"25:\"lLv3\""],"C5":[40,[[20,"1213","26:\"21868134\""],[20,"\n","24:\"c1qU\""]],"25:\"wkxy\""]},[[10,5]],[[10,1,"3:206"],[10,2,"3:205"]]],"25:\"4zBopH\""],[20,"\n","24:\"Io2c\""],[20,"这里相比于前一题不同之处在于，“频数”被展开了，直接上最的明细数据，思路是以我们的某个明细数据为基准，大于等于它以上的频数和小于等于它以下的频数个数相减绝对值一定是比1 小的。最后聚合一下即可。比如："],[20,"\n","24:\"KdIs\""],[20,"1 1 2 3 4 4 5"],[20,"\n","24:\"rkb1\"|blockquote:true"],[20,"中位数是3，比3大的个数是3，比3小的个数也是3，两者相减是0。"],[20,"\n","24:\"HfG3\"|blockquote:true"],[20,"1 1 1 2 3 4 4 5"],[20,"\n","24:\"zEco\"|blockquote:true"],[20,"中位数是(2+3)/2=2.5 以2为基准比2大的有4个，比2小的有3个，两者相减是1。"],[20,"\n","24:\"nsum\"|blockquote:true"],[20,"SELECT e.*"],[20,"\n","24:\"mATP\"|36:177"],[20,"from Employee e"],[20,"\n","24:\"pGPQ\"|36:177"],[20,"where"],[20,"\n","24:\"5qBv\"|36:177"],[20,"1 >= ABS("],[20,"\n","24:\"3VSv\"|36:177"],[20,"(SELECT COUNT(*) from Employee e1 where e1.Company = e.Company and e.Salary >= e1.Salary) –"],[20,"\n","24:\"heS2\"|36:177"],[20,"(SELECT COUNT(*) from Employee e2 where e2.Company = e.Company and e.Salary <= e2.Salary)"],[20,"\n","24:\"REdZ\"|36:177"],[20,")"],[20,"\n","24:\"TiKw\"|36:177"],[20,"group by Company, Salary"],[20,"\n","24:\"dWFp\"|36:177"],[20,"\n","24:\"mT65\""],[20,"三、总结","27:\"13\"|8:1"],[20,"\n","24:\"s8nV\""],[20,"求解中位数的套路get,回顾一下无非两点，明细数据其上下界限的统计总数(利用count函数)相减绝对值小于等于1，然后再分组选出平均数即可；频数数据其上下界限的统计总数(利用sum函数)相减绝对值小于等于当前频数，然后再avg该频数即可。"]]">

今天是2020年10月11日，下午4点36分，今天去补牙了(痛)，复习一下关于SQL中的中位数的一些取法。

通常与中位数相关的SQL取法有两种，一种是给一堆频数，求其中的中位数；一种是直接给一些数，让求其中的中位数。(分别对应leetcode上面的571和569题)

-----本文大纲------------------

一、给一堆频数求中位数

二、给一些数求中位数

三、总结

--------------------------------------

一、给一堆频数求中位数

这个题目之前看拼多多面经时候有考过，类似下面这个表，求中位数。

Number	Frequency
0	1
1	23
2	33
3	13
4	45

有频数求解中位数关键是，中位数的频数一定是会大于等于它上半部分频数总和与下半部分频数综合之差。然后求得中位数频数后再avg一下就可以得到中位数了。

select avg(n.Number) medianfrom Numbers nwhere n.Frequency >= abs((select sum(Frequency) from Numbers where Number<=n.Number) -(select sum(Frequency) from Numbers where Number>=n.Number))

二、给一些数求中位数

leetcode上569题是让求员工薪水中位数：

Id	Company	Salary
1	A	1111
2	B	3456
3	C	4456
4	A	1213

这里相比于前一题不同之处在于，“频数”被展开了，直接上最的明细数据，思路是以我们的某个明细数据为基准，大于等于它以上的频数和小于等于它以下的频数个数相减绝对值一定是比1 小的。最后聚合一下即可。比如：

1 1 2 3 4 4 5

中位数是3，比3大的个数是3，比3小的个数也是3，两者相减是0。

1 1 1 2 3 4 4 5

中位数是(2+3)/2=2.5  以2为基准比2大的有4个，比2小的有3个，两者相减是1。

SELECT e.*from Employee ewhere1 >= ABS((SELECT COUNT(*) from Employee e1 where e1.Company = e.Company and e.Salary >= e1.Salary) –(SELECT COUNT(*) from Employee e2 where e2.Company = e.Company and e.Salary <= e2.Salary))group by Company, Salary

三、总结

求解中位数的套路get,回顾一下无非两点，明细数据其上下界限的统计总数(利用count函数)相减绝对值小于等于1，然后再分组选出平均数即可；频数数据其上下界限的统计总数(利用sum函数)相减绝对值小于等于当前频数，然后再avg该频数即可。