数据分析面试常见问题及答案 知名大厂数据分析师24个经典面试题目和答案解析

现有A表，有21个列，第一列id，剩余列为特征字段，列名从d1-d20，共10W条数据！另外一个表B称为模式表，和A表结构一样，共5W条数据 请找到A表中的特征符合B表中模式的数据，并记录下相对应的id。

有两种情况满足要求：

每个特征列都完全匹配的情况下

最多有一个特征列不匹配，其他19个特征列都完全匹配，但哪个列不匹配未知

1 select A.id,2 ((case A.d1 when B.d1 then 1 else 0) +3 (case A.d2 when B.d2 then 1 else 0) +4 ...) as count_match5 from A left join B6 on A.d1 = B.d1

14问：

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

例子，数据库表里有以下的数据：U0 g0 2 U0 g1 4 U1 g0 3 U1 g1 1 计算后的结果为：U0 U1 2*3+4*1=10 ……

1 select uid1, uid2, sum(result) as dot2 from3 (select t1.uid as uid1, t2.uid as uid2, t1.goods_id, t1.star*t2.star as result4 from t as t1, t as t25 on t1.goods_id = t2.goods_id) as t6 group by goods_id

15问：统计教授多门课老师数量并输出每位老师教授课程数统计表

设表class中字段为id，teacher，course

1. 统计教授多门课老师数量

1 select count(*) from class

2. 输出每位老师教授课程数统计

1 select teacher, count(course) as count_course2 from class3 group by teacher

16

问：四个人选举出一个骑士，统计投票数，并输出真正的骑士名字

设表tabe中字段为id，knight，vote_knight

1 select knight from table2 group by vote_knight3 order by count(vote_knight) limit 1

17

问：员工表，宿舍表，部门表，统计出宿舍楼各部门人数表

设：

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

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

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

1 select dormitory_number, department_name, count(employee_name) as count_employee2 from employee as e3 left join dormitory as dor on e.belong_dormitory_id = dor.id4 left join department as dep on e.belong_department_id = dep.id

18问：给出一堆数和频数的表格，统计这一堆数中位数

设表table中字段为id,number,frequency

1 set @sum = (select sum(frequency)+1 as sum from table)2 set @index = 03 set @last_index = 04 select avg(distinct t.frequecy)5 from6 (select @last_index := @index, @index := @index+frequency as index, frequency7 from table) as t8 where t.index in (floor(@sum/2), ceiling(@sum/2))9 or (floor(@sum/2) > t.last_index and ceiling(@sum.2) <= t.index)

19问：中位数，三个班级合在一起的一张成绩单，统计每个班级成绩中位数

设表table中字段为id，class，score

1 select t1.class, avg(distinct t1.score) as median2 from table t1, table t2 on t1.id = t2.id3 group by t1.class, t1.score4 having sum(case when t1.score >= t2.score then 1else 0 end) >=5 (select count(*)/2 from table where table.class = t1.class)6 and7 having sum(case when t1.score <= t2.score then 1else 0 end) >=8 (select count(*)/2 from table where table.class = t1.class)

20问：交易表结构为user_id,order_id,pay_time,order_amount

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

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

1 select count(distinct user_id) as c from table group by month(pay_time

1 select order_id, order_amount from ((select user_id, max(pay_time) as mt from table

21问：

PV表a(表结构为user_id,goods_id),点击表b(user_id,goods_id),数据量各为50万条，在防止数据倾斜的情况下，写一句sql找出两个表共同的user_id和相应的goods_id

1 select * from a2 where a.user_id exsit (select user_id from b)

22

1select * from table order by rand() limit 2000

1 set @target = 02 set @count_user = 03 select @target:=@target+10 as age_right, *4 from table as t15 where t1.age >=@target-10 and t1.age < (@target)6 and t1.id in7 (select floor(count(*)*0.1） from table as t28 where t1.age >=@target-10 and t1.age < (@target)9 order by rand() limit ??)

1 select visit_date, count(distince user_id)

1 select t1.date2 from table t1, table t2, ..., table t73 on t1.visit_date = (t2.visit_date+1) and t2.visit_date = (t3.visit_date+1)4 and ... and t6.visit_date = (t7.visit_date+1）

1 select day(visit_date), count(distinct user_id)2 from table3 where user_id not in4 (select user_id from table5 where day(visit_date) < date_sub(visit_date, interval 7day))

1 # 三日留存2 # 先计算每个平台7日前的新用户数量3 select t1.plat, t1.c/t2.c as retention_34 (select plat, count(distinct user_id)5 from table6 group by plat, user_id7 having day(min(visit_date)) = date_sub(now(), interval 7 day)) as t18 left join9 (select plat, count(distinct user_id) as c10 from table11 group by user_id having count(user_id) > 012 having day(min(visit_date)) = date_sub(now(), interval 7 day)13 and day(max(visit_date)) > date_sub(now(), interval 7 day)14 and day(max(visit_date)) <= date_sub(now(), interval 4day)) as t215 on t1.plat = t2.plat