来几个比较有意思的笔试题，比较注重灵活性；

1、我们把用户对商品的评分用稀疏向量表示，保存在数据库表t里面： t的字段有：uid，goods_id，star； uid是用户id；goods_id是商品id；star是用户对该商品的评分，值为1-5。 现在我们想要计算向量两两之间的内积，内积在这里的语义为：对于不同的用户，如果他们都对同样的一批商品打了分，那么对于这里面的每个人的分数乘起来，并对这些乘积求和。比如g0：2*2+2*4+2*5+4*2+4*4+4*5+5*2+5*4+5*5=121

建表语句：

create table B(uid VARCHAR(12),good_id VARCHAR(12),star int(10)); INSERT INTO B (uid,good_id,star) VALUES(u0,g0,2),(u0,g1,3),(u1,g0,4),(u1,g1,3),(u2,g0,5),(u2,g1,1);

表结构如下：

select t.good_id,sum(xiangchen) from ( select t1.uid,t1.good_id,t1.star*t2.star as xiangchen --3*6 from ( select * from B )t1 inner join ( select * from B )t2 on t1.good_id=t2.good_id )t group by t.good_id;

2、员工表，宿舍表，部门表，统计出宿舍楼各部门人数表

设员工表为employee，字段为id，employee_name，belong_dormitory_id，belong_department_id；

宿舍表为dormitory，字段为id，dormitory_number；

部门表为department，字段为id，department_name

思路：

select t3.dormitory_name,t2.department_number,count(distinct t1.employee_name) from ( select * from employee )t1 inner join ( select * from department )t2 on t1.belong_department_id=t2.id inner join ( select * from dormitory )t3 on t3.id=t1.belong_dormitory_id group by t3.dormitory_name,t2.department_number

3、给出一堆数和频数的表格，统计这一堆数中位数

设表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);建立表格时，注意将number，frequency的顺序打乱，以免出现歧义！

思路：

2+3+4+5)+1)/2=7.5;我们需要知道排序在第7，第8位的数字，然后求其平均数，即为需要求的中位数！

分两种情况考虑：

a：1，1，2，2的情况，中位数是1+2）/2=1.5

b：1，1，2，2，3的情况，中位数是2！

select (t1.number+lead(t1.number,1)over(order by t1.number))/2 from ( select number,frequency,sum(frequency) over( order by number) as ran from table_a )t1 inner join ( select floor(((sum(frequency)+1) / 2) ) as fre_int from table_a )t2 on t1.ran =t2.fre_int union select t3.number from ( select number,frequency,sum(frequency) over( order by number) as ran from table_a )t3 inner join ( select floor(((sum(frequency)+1) / 2 ) ) as fre_int from table_a )t4 on t3.ran >t4.fre_int limit 1

union 或者union all是用来求并集合的，但是不能与order by 连用！所以我们采用sum()over()借助over中的order by函数完成对原始number的排序！

floor（）向下取整！

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版本：

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

此种写法，有一个瑕疵：当B班只有一个学生的时候，最终结果显示null（实际上B班不可能只有一个学生；当初测试的时候，意外发现这个bug）

各位看官，如果有兴趣可以研究下；

为了对Mysql版本写法有更清晰的认识，我将比较陌生的部分单独抽出来进行说明：

select if(@class=class,@score:=@score+1,@score:=1) as ran,--class 是分组键（不能有：），score是排序键 @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 --必不可少

row_number的mysql改写：

有set 与case when的结合；也有本文if的写法，二者效果相同；

两种方法我都尝试了，个人感觉if写法更加容易理解！

4、给定如下表结构，请用SQL写出中位数：（四分位数改写？）

自己练习一下吧（比上述两题都简单啦）

参考答案如下：

求知鸟：数据分析|容易被忽视的统计值22 赞同 · 2 评论文章

5、交易表结构为user_id,id,paid_time,amount

1、写sql查询过去一个月付款用户量（提示 用户量需去重）最高的3天分别是哪几天

2、写sql查询做昨天每个用户最后付款的订单ID及金额

表结构：

create table order_record (id int, user_id int,paid_time datetime,amount bigint); insert into order_record(id,user_id,paid_time,amount) values(1,1028898,2018-01-04 22:32:07.0,9600); insert into order_record(id,user_id,paid_time,amount) values(2,1030621,2018-02-24 23:04:58.0,5400);

第一问：

Select date(paid_time),amount From order_record Where month(paid_time)=07 Group by date(paid_time) Order by count(distinct user_id) desc Limit 3

第二问：

Select t2.id,t2.amount From ( Select user_id,max(paid_time) as max_date From order_record Where datediff(now(),paid_time)=1 Group by user_id )t1 Inner join ( Select user_id,amount,id,paid_time From order_record Where datediff(now(),paid_time)=1 )t2 On t1.user_id=t2.user_id And t1.max_date=t2.paid_time

看的不尽兴，那就关注我的专栏，第一时间知道笔试题状态

数据分析​zhuanlan.zhihu.com/c_1101426662830436352

看完，点赞再走@假装看到你！！！