最近看了几篇特赞的分析笔面试题,我将链接放在文末了,大家有需要自取。
这篇文章,总结下面试中关于NULL和中位数相关的问题。
1
关于中位数:
要知道,MySQL没有关于中位数计算的函数,所以需要我们自己动手写SQL实现中位数的需求。
另,Hive提供了中位数函数:
percentile(字段,0.5):当数据是整数时;将0.5换成0.25就是第一分位数了;percentile_approx(字段,0.5):当数据是double型数据;将0.5换成0.25就是第一分位数了;
1、找出一堆数中的中位数;
计算逻辑:
1)先对所有数据从小到大排序;
2)计算(n+1)/2;如果,n是奇数,那么对应值就是中位数;如果,n是偶数,就求中间两个数的平均值;
selectfeature1 * (1 - float_part) + next_feature1 * (float_part-0) as medianfrom (selectfeature1,row_number() over(order by feature1 asc) as rank,lead(feature1,1) over(order by feature1 asc) as next_feature1from iris) ainner join(selectcast((count(feature1) + 1) / 2 as int) as int_part,(count(feature1)+1) / 2 % 1 as float_partfrom iris) bon a.rank = b.int_part;
代码的逻辑,比较简单,举例:1,2,3,4,5,6;
对应的中位数:3*(1-0.5)+4*(0.5-0)=3.5
2、给出一堆数和频数的表格,统计这一堆数中位数
表table中字段为id,number,frequency
建表语句如下:
create table table_a (id VARCHAR(2),number int(12),frequency int(12));
insert into table_a values ('b',2,4),('c',3,3),('d',4,5),('a',1,2);
思路:
union 前:统计n为奇数时的中位数;
union后:统计n为偶数时的中位数;
采用union语句,总能且只能满足其中一种情况,将最终的中位数输出。
select (t1.number+lead(t1.number,1)over(order by t1.number))/2 from (select number,frequency,sum(frequency) over( order by number) as ranfrom table_a)t1inner join (select floor(((sum(frequency)+1) / 2) ) as fre_intfrom table_a)t2on t1.ran =t2.fre_intunion select t3.numberfrom (select number,frequency,sum(frequency) over( order by number) as ranfrom table_a)t3inner join (select floor(((sum(frequency)+1) / 2 ) ) as fre_intfrom table_a)t4on t3.ran >t4.fre_intlimit 1
3、三个班级合在一起的一张成绩单,统计每个班级成绩中位数
设表table中字段为id,class,score
create table all_scores (id VARCHAR(12),class VARCHAR(12),score int(10));
insert into all_scores VALUES('zhang shan','A',96),('li si','C',68),('lao wang','B',100),('zhao liu','A',98),('wu xiao','C',86),('xiao ma','A',84),('xiao pang','B',89);
select * from all_scores
代码如下:(oracle,hive中可以很方便使用row_number()over())
select a.class,
a.score * (1 - b.float_part) + a.next_feature1 * (b.float_part-0) as median
from
(select
class,score
row_number() over( partition by class order by score asc) as rank,
lead(score,1) over(partition by class order by score asc) as next_feature1
from
all_scores
) a
inner join
(
select class,
floor((count(score) + 1) / 2) int_part,
(count(score)+1) / 2 % 1 as float_part
from
all_scores
group by class
) b
on a.rank = b.int_part and a.class=b.class
group by a.class
Mysql 5.6版本(没有row_number()over()):
select a.class,
a.score * (1 - b.float_part) + a.next_feature1 * (b.float_part-0) as median
from
(select
if(@class=class,@score:=@score+1,@score:=1) as ran,
@class:=class as class,
score,
lead(score,1) over(partition by class order by score asc) as next_feature1
from
all_scores as a,(select @class:='',@score:=0) as c
order by class
) a
inner join
(
select class,
floor((count(score) + 1) / 2) int_part,
(count(score)+1) / 2 % 1 as float_part
from
all_scores
group by class
) b
on a.ran = b.int_part and a.class=b.class
group by a.class
row_number的mysql改写:有set 与case when的结合;也有本文if的写法,二者效果相同;两种方法我都尝试了,个人感觉if写法更加容易理解!
2
关于NULL:
NULL是一种特殊的值,对某字段使用distinct 关键字时,NULL和一般值一样,都会排重,只保留一个值。
不能对NULL值使用比较运算符:直白地讲,不能对null值使用等号(=)或者不等号(!=)进行比较,要使用is null 和is not null。
有一种情况需要注意,假设我们需要取col不为2的所有col值,包括null。不能只写where col <> '2',因为这样的写法不会包括NULL值。我们需要写成where col <> '2' or col is null。
count(*)会统计null值,count(列名)不包括null值。
含NULL值的运算结果都为NULL。
使用sum函数和avg函数时,相应列中包含NULL的,会发生什么?
sum和avg函数作用于含有NULL的列,NULL值不参与计算,尤其注意使用average时,不将null值记录在分母中。如果需要将NULL值当作0值参与到运算中,可以用case when的方式进行判断赋值。
如果某列含有null,使用group by 进行聚合时,null值会单独保留一行。
不同数据库,有的将null放在最前面,有的放后面。
NULL多用在字段约束中,如非空约束可以用NOT NULL表示。NULL经常用在case表达式中的ELSE子句中:case when <条件> else NULL end,else的部分也可以不写,但为了易读性,还是建议写。
引用 超哥的杂货铺关于NULL的一篇文章:
SQL中这些与NULL有关的细节,你知道吗?
代码,仅供参考,如有错误请拍砖。
推荐阅读: