dateframe取某列数据_数据分析笔试题（五）

最近看了几篇特赞的分析笔面试题，我将链接放在文末了，大家有需要自取。

这篇文章，总结下面试中关于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 medianfrom (selectclass,scorerow_number() over( partition by class order by score asc) as rank,lead(score,1) over(partition by class order by score asc) as next_feature1from all_scores) ainner join(select  class,floor((count(score) + 1) / 2) int_part,(count(score)+1) / 2 % 1 as float_partfrom all_scoresgroup by class ) bon a.rank = b.int_part and a.class=b.classgroup 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 medianfrom (selectif(@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_feature1from all_scores as a,(select @class:='',@score:=0) as corder by class) ainner join(select  class,floor((count(score) + 1) / 2) int_part,(count(score)+1) / 2 % 1 as float_partfrom all_scoresgroup by class) bon a.ran = b.int_part and a.class=b.classgroup 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有关的细节，你知道吗？

代码，仅供参考，如有错误请拍砖。

推荐阅读：

Sql如何统计连续打卡天数

两道腾讯最爱考的数据分析面试题 | 附解题思路

数据分析笔试题(一) 数据分析笔试题(二) 数据分析笔试题(三) 数据分析笔试题(四) -- The End-- 原创不易，您的转发与在看，就是我坚持的动力！