文章目录
- 前言
- 一、列转行
- 题目:将表Student
- 转化为下面的形式展示
- 先放答案
- 逐步剖析
- 二、row_number() over() 的使用
- 题目:统计订单交易表(orders)每个商品交易金额最高的那一条数据
- 先放答案
- 逐步剖析
- 三、逐行累加
- 题目:还是订单交易表(orders),对商品按照月份累加汇总。比如,一月份显示一月的交易金额,二月份显示一月份+二月份的交易金额
- 先放答案
- 逐步剖析
- 四、rank() over() 开窗函数的使用(以下三道都是滴滴面试过程中手撕)
- 表:订单表t,(网约车订单明细表,每日增量)
- 题目:查询近7天内上一单是取消单的,近7天完单量top100的司机。
- 题目解读:上一单 = 最近一单的上一单,最近一单 = 当前单
- 先放答案
- 逐步剖析
- 五、hive中列转行函数 collect_set 的使用
- 表:表结构和上面那题的一样
- 题目:查询近30天内,司机完单的品类集合、完单天数。产出3个字段:司机id, 近30日完单品类列表,近30日完单天数。
- 函数科普:
- 先放答案
- 逐步剖析
- 六、sql不光要写出来,也要注意效率——调优
- 表:订单表t1,(订单明细表,每日增量)
- 司机表t2,(每日全量)
- 题目:查询近7天内,各品类中,完单量top100的全职司机。
- 先放答案
前言
本篇文章将总结博主在笔试或面试过程中碰到的比较有难度或经典的sql题目。笔试考察的大都不是很难,不像工作中那样好几个嵌套子查询,很多个表关联…可能行数很少,但绝对得多想。如果之前没碰见过,第一次做肯定会漏洞百出。会持续更新的~
一、列转行
题目:将表Student
name | subject | score |
---|---|---|
张三 | 语文 | 78.0 |
张三 | 数学 | 88.0 |
张三 | 英语 | 98.0 |
李四 | 语文 | 89.0 |
李四 | 数学 | 76.0 |
李四 | 英语 | 90.0 |
王五 | 语文 | 66.0 |
转化为下面的形式展示
name | 语文 | 数学 | 英语 | 总分 |
---|---|---|---|---|
张三 | 78 | 88 | 98 | 264 |
李四 | 89 | 76 | 90 | 255 |
王五 | 99 | 0 | 0 | 66 |
先放答案
SELECT `name`,语文,数学,英语,总分
FROM(
SELECT
`name`,
SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
SUM(IF(SUBJECT = '英语',score,0)) AS 英语
FROM student
GROUP BY `name`)t1
LEFT JOIN(
SELECT `name`,SUM(score) AS 总分
FROM student
GROUP BY `name`)t2
USING(`name`);
逐步剖析
1、先看总分,计算很简单,就是按名字group by 之后 sum(分数) 即可
SELECT `name`,SUM(score) AS 总分
FROM student
GROUP BY `name`
name | 总分 |
---|---|
张三 | 264 |
李四 | 255 |
王五 | 66 |
得出这样的结果
2、本题精髓就是怎么把 subject 这一列数据转化成一行列名。可以拿 subject 列中的数据做 if 判断的条件,创建一张只有 name列 不变,后几列是 语数英 的新表
SELECT
`name`,
IF(SUBJECT = '语文',score,0) AS 语文,
IF(SUBJECT = '数学',score,0) AS 数学,
IF(SUBJECT = '英语',score,0) AS 英语
FROM student
name | 语文 | 数学 | 英语 |
---|---|---|---|
张三 | 78.0 | 0 | 0 |
张三 | 0 | 88.0 | 0 |
张三 | 0 | 0 | 98.0 |
李四 | 89.0 | 0 | 0 |
李四 | 0 | 76.0 | 0 |
李四 | 0 | 0 | 90.0 |
王五 | 66.0 | 0 | 0 |
3、离最终答案不远了,用 group by + sum 就能变成没人一行且语数英三列数据合并
SELECT
`name`,
SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
SUM(IF(SUBJECT = '英语',score,0)) AS 英语
FROM student
GROUP BY `name`
name | 语文 | 数学 | 英语 |
---|---|---|---|
张三 | 78 | 88 | 98 |
李四 | 89 | 76 | 90 |
王五 | 99 | 0 | 0 |
这条sql就是本题精髓!!! 怕直接看看不懂,所以拆开分两步写了。
4、最后一步简单,把各科表和总分表关联起来就行,刚才那俩分离的sql写一起
SELECT `name`,语文,数学,英语,总分
FROM(
SELECT
`name`,
SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
SUM(IF(SUBJECT = '英语',score,0)) AS 英语
FROM student
GROUP BY `name`)t1
LEFT JOIN(
SELECT `name`,SUM(score) AS 总分
FROM student
GROUP BY `name`)t2
USING(`name`);
二、row_number() over() 的使用
row_number() over() 在面试中经常和 rank() over()、dense_rank() over() 一起被问,在笔试中也经常需要用到,甚至在工作中也经常见到,反正是相当的重要!
在工作中其实用的是 row_number() over() 的变种,用 @ 。因为公司大都是MySQL5,但窗口函数在MySQL8中才出现,所以工作中都是 row_number 的思想,@的写法。关于@的使用有时间我写写。
题目:统计订单交易表(orders)每个商品交易金额最高的那一条数据
item_id | trade_date | trade_amount |
---|---|---|
A | 2021/3/1 | 1500 |
A | 2021/3/9 | 2500 |
A | 2021/2/22 | 1000 |
B | 2021/3/5 | 3000 |
B | 2021/5/9 | 5000 |
B | 2021/6/22 | 800 |
item_id=商品代码 trade_date=交易日期 trade_amount=交易金额
先放答案
SELECT
item_id,
trade_date,
trade_amount
FROM(
SELECT
item_id,
trade_date,
trade_amount,
row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
FROM orders)t
WHERE sort = 1
item_id | trade_date | trade_amount |
---|---|---|
A | 2021/3/9 | 2500 |
B | 2021/5/9 | 5000 |
逐步剖析
1、因为要求每个
商品的金额最高,所以分组排序必不可少。为什么不能用 group by 然后 max 呢?因为要求的是金额最高的那一条数据
,而不是求最高金额,那一行数据的其他信息得保留下来。而group by 后的其他字段不用聚合函数是没法保存的,就丢失了,跟题意不符。
举个栗子吧:
SELECT item_id,MAX(trade_amount)
FROM orders
GROUP BY item_id
虽然能找到最高金额,但这样就缺字段了
还有这样:
SELECT item_id,trade_date,MAX(trade_amount)
FROM orders
GROUP BY item_id
这样写直接报错,因为 group by 了 item_id 这一列,那么它之外的列如果不用聚合函数直接报错
这样也是不对:
SELECT item_id,trade_date,MAX(trade_amount)
FROM orders
GROUP BY item_id,trade_date
虽然不报错,但是
只有 item_id 和 trade_date 都相同的才能分为一组,不符合题意
所以要用到 row_number() over() 进行分组排序:
SELECT
item_id,
trade_date,
trade_amount,
row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
FROM orders
item_id | trade_date | trade_amount | sort |
---|---|---|---|
A | 2021/3/9 | 2500 | 1 |
A | 2021/3/1 | 1500 | 2 |
A | 2021/2/22 | 1000 | 3 |
B | 2021/5/9 | 5000 | 1 |
B | 2021/3/5 | 3000 | 2 |
B | 2021/6/22 | 800 | 3 |
2、最后子查询即可
SELECT
item_id,
trade_date,
trade_amount
FROM(
SELECT
item_id,
trade_date,
trade_amount,
row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
FROM orders)t
WHERE sort = 1
三、逐行累加
题目:还是订单交易表(orders),对商品按照月份累加汇总。比如,一月份显示一月的交易金额,二月份显示一月份+二月份的交易金额
展示结果应该为:
item_id | trade_date | trade_amount |
---|---|---|
A | 2021/2 | 1000 |
A | 2021/3 | 5000 |
B | 2021/3 | 3000 |
B | 2021/5 | 8000 |
B | 2021/6 | 8800 |
先放答案
SELECT
item_id,
`month`,
SUM(trade_amount) over(PARTITION BY item_id ORDER BY `month`) sums
FROM (
SELECT
item_id,
DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
SUM(trade_amount) trade_amount
FROM orders
GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
ORDER BY item_id,`month`)t
GROUP BY item_id,`month`
逐步剖析
1、题目说:按照月份累加汇总。怎么按照月份呢,按月份分组,把相同月份的金额加起来
SELECT
item_id,
DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
SUM(trade_amount) trade_amount
FROM orders
GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
ORDER BY item_id,`month`
item_id | trade_date | trade_amount |
---|---|---|
A | 2021/2 | 1000 |
A | 2021/3 | 4000 |
B | 2021/3 | 3000 |
B | 2021/5 | 5000 |
B | 2021/6 | 800 |
因为要求不同商品不同月份汇总,所以 group by 后要用两个字段分组。
2、到这里已经跟结果很接近了,只要能按照 item_id(商品代码)分组然后累加即可,我首先想到了sum() over() 窗口函数
SELECT
item_id,
`month`,
SUM(trade_amount) over(PARTITION BY item_id ORDER BY `month`) sums
FROM (
SELECT
item_id,
DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
SUM(trade_amount) trade_amount
FROM orders
GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
ORDER BY item_id,`month`)t
GROUP BY item_id,`month`
四、rank() over() 开窗函数的使用(以下三道都是滴滴面试过程中手撕)
表:订单表t,(网约车订单明细表,每日增量)
dt(分区格式yyyy-MM-dd), product_line, order_id,car_id, driver_id,is_finish,is_cancel,start_time,end_time。
其中,is_finish=1代表订单完成。is_cancel=1代表订单取消。product_line:品类(如快车、专车等)。
题目:查询近7天内上一单是取消单的,近7天完单量top100的司机。
题目解读:上一单 = 最近一单的上一单,最近一单 = 当前单
先放答案
select driver_id,count(*) as top
from t
where datediff(current_date(),dt) < 7 and is_finish=1 and driver_id in (
select driver_id
from(
select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
from t
where datediff(current_date(),dt) < 7
)t1
where sort = 2 AND is_cancel = 1
)
group by driver_id
order by top desc
limit 100
逐步剖析
1、先查出来近7天内上一单是取消单的司机
select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
from t
where datediff(current_date(),dt) < 7
按司机分组,按时间排序。求出来每个司机所有订单放一起,并按时间降序排序。最后加个where条件限制七天内
结果类似这样:
driver_id | is_cancel | dt | sort |
---|---|---|---|
102 | 0 | 2023-03-06 | 1 |
102 | 1 | 2023-03-05 | 2 |
102 | 0 | 2023-03-04 | 3 |
101 | 0 | 2023-03-06 | 1 |
101 | 0 | 2023-03-04 | 2 |
103 | 1 | 2023-03-06 | 1 |
103 | 0 | 2023-03-04 | 2 |
103 | 0 | 2023-03-03 | 3 |
在刚才的基础上子查询,即可求出上一单是取消单的司机
select driver_id
from(
select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
from t
where datediff(current_date(),dt) < 7
)t1
where sort = 2 AND is_cancel = 1
这块是精髓!
2、将刚才查到的符合要求的driver_id写进where条件,再加上时间和完单的限制,最后聚合即可求出完单量top100的司机
select driver_id,count(*) as top
from t
where datediff(current_date(),dt) < 7 and is_finish=1 and driver_id in (
# 刚才开窗的符合条件的sql
)
group by driver_id
order by top desc
limit 100
最后就能拼接完整代码了!
五、hive中列转行函数 collect_set 的使用
表:表结构和上面那题的一样
题目:查询近30天内,司机完单的品类集合、完单天数。产出3个字段:司机id, 近30日完单品类列表,近30日完单天数。
函数科普:
-
collect_set:group by后,将分组中的某列转为一个数组返回,set去重
-
collect_list:group by后,将分组中的某列转为一个数组返回,list不去重
先放答案
select driver_id,collect_set(product_line),count(distinct(dt))
from t
where datediff(current_date(),dt) < 30 and is_finish=1
group by driver_id
逐步剖析
1、题目让产出3个字段:司机id,品类列表,完单天数。可以发现,题目可以按照司机id分组,后面两个字段用函数聚合
select driver_id,collect_set(product_line),count(distinct(dt))
from t
group by driver_id
2、还有两个条件:近30天内、完单,写在where条件中
where datediff(current_date(),dt) < 30 and is_finish=1
拼接起来即可。
六、sql不光要写出来,也要注意效率——调优
表:订单表t1,(订单明细表,每日增量)
dt(分区格式yyyy-MM-dd), product_line, order_id,car_id, driver_id,is_finish,is_cancel,start_time,end_time。
其中,is_finish=1代表订单完成。is_cancel=1代表订单取消。product_line:品类(如快车、专车、拼车等)。
司机表t2,(每日全量)
dt(分区格式yyyy-MM-dd), driver_id, work_type。
其中,work_type=1代表全职
题目:查询近7天内,各品类中,完单量top100的全职司机。
先放答案
SELECT product_line,driver_id,num
FROM(
SELECT product_line,driver_id,num,rank() over(PARTITION BY product_line ORDER BY num DESC) AS sort
FROM(
# 各品类中各全职司机的完单量
SELECT product_line,driver_id,num
FROM(
SELECT product_line,driver_id,COUNT(*) num
FROM t1
WHERE DATEDIFF(CURRENT_DATE,dt)<7 AND is_finish=1
GROUP BY product_line,driver_id
)a
LEFT JOIN
(
SELECT *
FROM t2
WHERE DATEDIFF(CURRENT_DATE,dt)<7 AND work_type=1
)b
ON a.driver_id = b.driver_id
)t1
)t2
WHERE sort <= 100
更多推荐
SQL笔试题总结
发布评论