为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)

这道题在面试中出现频次较高,因为一道题既可以考核case when 用法,也可以考核窗口函数用法,还可以考核聚合函数和运算符用法,一举三得,完成这道题需要有清晰的思路,对题目的理解。

首先,理解中位数的概念:是按顺序排列的一组数据中居于中间位置的数。

奇数情况:由低到高排序,取中间那个值。
偶数情况:由低到高排序,取最中间的两个数值的平均数。

因此sql写法要一次性考虑到两种情况,完整代码如下:

select sum(case when flag1=flag2 then num else null end) num1
,sum(case when flag2-flag1 =1 or flag2-flag1 =-1 then num else null end)/2 num2
from(
select num,row_number() over(order by num) flag1
,row_number() over(order by num desc) flag2
from t ) a

讨论:排序问题除了用order by,其次还要想到row_number() over() 函数;使用窗口函数解题较为巧妙;count()函数也可以解题,但是可能会产生笛卡尔集,倾向于减少数据量,提升取数效率,有些数据库也不支持产生笛卡尔集,同时也不能体现对多个知识点的掌握。

第一步:排序,正序和倒序

select num,row_number() over(order by num) flag1
,row_number() over(order by num desc) flag2
from t 

如果是奇数情况,则
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)
如果是偶数情况,则
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)
第二步,flag相减寻找规律:

select case when flag1=flag2 then num else null end num1
,case when flag2-flag1 =1 or flag2-flag1 =-1 then num else null end num2
from(
select num,row_number() over(order by num) flag1
,row_number() over(order by num desc) flag2
from t ) a

如果是奇数情况,则0对应的值为中位数值
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)

如果是偶数情况,则1和-1对应的和的平均值就是中位数值
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)
第三步:计算中位数值

select sum(case when flag1=flag2 then num else null end) num1
,sum(case when flag2-flag1 =1 or flag2-flag1 =-1 then num else null end)/2 num2
from(
select num,row_number() over(order by num) flag1
,row_number() over(order by num desc) flag2
from t ) a

如果是奇数情况,则
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)
如果是偶数情况,则
为什么中位数sql提取可以考核多种知识点用法,row_number() over(),sum(case when ...end)

上一篇:嵌入式基础


下一篇:Git分支管理