2021-02-24 字节sql面试题

1. 算成绩
   表名：subject_scores

输入
Name subject score
王建国 数学 95
王建国 语文 89
李雪琴 数学 100
李雪琴 语文 100
李雪琴 英语 100

输出
Name math chinese English
王建国 95 89 0
李雪琴 100 100 100

解答：
所涉知识点：GROUP BY 和 CASE WHEN 实现行变列
注意:(1)空的数据这里判断为0；(2)CASE WHEN 前要使用聚合函数，不然报错）

SELECT 
name,
MAX(CASE subject WHEN '数学' THEN score ELSE 0 END) as math,
MAX(CASE subject WHEN '语文' THEN score ELSE 0 END) as chinese,
MAX(CASE subject WHEN '英语' THEN score ELSE 0 END) as English
FROM subject_scores 
GROUP BY name;

2.算成绩
输入
Name math chinese English
王建国 95 89 0
李雪琴 100 100 100
输出
Name subject score
王建国 数学 95
王建国 语文 89
李雪琴 数学 100
李雪琴 语文 100
李雪琴 英语 100

-- 解答
知识点：通过使用UNION ALL 列变行;
注意：0值处理；最后根据姓名排序

SELECT name ,'数学' as subject,math as 'score' FROM subject_scores_2 WHERE math>0
UNION ALL
SELECT name ,'语文' as subject,chinese as 'score' FROM subject_scores_2 WHERE chinese>0
UNION ALL
SELECT name ,'英语' as subject,english as 'score' FROM subject_scores_2 WHERE English>0
ORDER BY name DESC;

3.算昨天每个城市top 10消费金额的用户，输出city_id,city_name,uid, 消费总金额
表名：orders
每次消费记录一条
city_id,city_name,uid,order_id,amount,pay_order_time, pay_date
解答：(窗口函数)

SELECT a.city_id,
a.city_name,
a.uid,
a.pay_amount as '消费总金额'
FROM 
(
SELECT city_id,city_name,uid,SUM(amount) as pay_amount,RANK()over(PARTITION BY city_id ORDER BY SUM(amount) DESC) as rank_no
FROM orders
WHERE pay_date='2020-01-01'
GROUP BY city_id,city_name,uid 
) a 
WHERE a.rank_no<=10;

4.sql解析出json数组中的 fruit_id, fruit_name

[{'fruit_id':1, 'fruit_name':'apple'},
{'fruit_id':2, 'fruit_name':'pear'},
{'fruit_id':3, 'fruit_name':'peach'}]

解答：

SELECT * FROM JSON_TABLE('[
{"fruit_id":1,"fruit_name":"apple"},
{"fruit_id":2,"fruit_name":"pear"},
{"fruit_id":3,"fruit_name":"peach"}
]','$[*]' COLUMNS( fruit_id INT PATH '$.fruit_id' ,fruit_name VARCHAR(25) PATH '$.fruit_name')) as jt;

5.计算end_date
输入：
begin_date, 开始日期
unit_num, --单元 12个单元
week_num, --周序号 1～4，每个单元4周
day_num, --day的序号 1～7，每周7天
输出
end_date 结束日期（12）
解答：(也不知道有没有读懂题意？乱写的)

set @begin_date='2010-01-01',@unit_num=12;
SELECT DATE_ADD(@begin_date,INTERVAL @unit_num*4*7 DAY) AS end_date;

6. 计算占比和同比增长
   t_user记录了用户注册时间和平台，统计2018年1月份
   每天各平台("ios","android","h5")注册用户总量占所有平台总用户的比例，以及各平台注册用户按周同比增长（与一周前相比）的比例
   建表语句
   create table t_user
   (
   uid BIGINT COMMENT "用户id"
   , reg_time STRING COMMENT "注册时间,如2018-07-01 08:11:39"
   , platform STRING COMMENT "注册平台，包括app ios h5"
   );
   解答：
   知识点：窗口函数。
   注意：如果存在某天的缺失数据，偏移函数会有错误

SELECT a.reg_date
,a.platform
,ROUND(a.reg_num/sum(a.reg_num)over(PARTITION BY a.reg_date),4) as rate
,ROUND((a.reg_num-a.reg_num_7)/a.reg_num_7,4) as rate_week
FROM(
SELECT 
DATE(reg_time) as reg_date
,platform
,COUNT(uid) as reg_num
,lag(COUNT(uid),7)over(PARTITION BY platform ORDER BY DATE(reg_time)) as reg_num_7
FROM t_user
WHERE SUBSTR(reg_time,1,7)='2018-01'
GROUP BY DATE(reg_time),platform
) a ;

二、分析题
1.扑克牌54张，里面有4个2，随机抽取10张，其中有1个2的概率。
需要写出分析过程

解答：

2.指标定义：
参课渗透率--参加课程学习人数/整体报名人数
对比上个招生期，本期大龄儿童组参课渗透率增加，低龄儿童组参课渗透率也增加，本期是否参课渗透率整体会提升？为什么
（知识点：辛普森悖论）