SQL学习（4）：JD电话面试题目

Algebra幸运E

已于 2024-07-15 16:46:07 修改

阅读量512

点赞数

分类专栏： SQL学习文章标签：面试 sql 学习

于 2022-06-05 20:30:10 首次发布

本文链接：https://blog.csdn.net/UoB1887858/article/details/125084459

版权

SQL学习专栏收录该内容

12 篇文章 0 订阅

订阅专栏

SQL学习（4）：JD电话面试题目

面试复盘、总结和学习

文章目录

SQL学习（4）：JD电话面试题目

1.面试题目描述

本部分为整个面试复盘，包含题目和当时回答。
本次岗位主要内容涉及大数据、HiveSQL、SparkSQL。个人能力涉及MySQL。

流程、问题、回答及复盘

简单自我介绍：

简述院校专业、实习经历。主要讲述熟悉MATLAB和MySQL，Python和R进行过两个课程项目但不算熟练，正在学习。最后提及英文能力和性格。
面试官简述工作涉及技术(任意顺序)：
主要涉及大数据，尤其是HiveSQL，说明接下来的题目为SQL题目
有一个营收表，包括渠道、金额，怎么获得每个渠道的总金额：
回答select sum(金额)…group by 渠道
有一个学生信息表，包括学生、时间、学科和成绩，获得今天成绩比昨天好的学生信息：
回答用自联结后再where成绩更好或datediff筛选昨天再where成绩更好

追问怎么自联结：
卡壳，回答使用子查询

再次追问，不使用子查询怎么做：
回答datediff内参数也想用子查询，没想出不用子查询

复盘：没有回答正确，实际可以不适用子查询。子查询相对来说也会使得代码结构复杂，在有更好选择情况下应当舍弃。
了解行转列和列传行吗：
回答听说过，但还未涉及和学到

复盘：面试官在此后说到工作中会比较多涉及，应当进行学习和掌握
union all 和 union的区别：
回答一个去重再连接，一个直接连接

复盘：应当更准确说明哪一种对应什么效果，自己不够自信且只是不牢固
有一张订单表，包含用户、下单时间，问怎么获取第一笔订单：
回答如果想获取单个用户信息，用where筛选用户，select部分用min或者最后部分用order by 时间 limit 1；如果想获取每个用户第一笔订单，回答方法相同，只是需要添加group by用户

追问说单个用户正确，每个用户会有些问题
回答那可以使用rank() over(partition by 用户 order by time)然后选取rank=1的信息

没有表态，追问rank和row_number的区别
回答排序函数实际有3个。其中一个是顺序连续排序，一个是数值相同顺序相同连续排序，一个是数值相同顺序相同不连续，也就是跳过排序

复盘：分情况讨论不错，回答问题时就应该注意细节。业务中可能更需要兼容高的方案，但面试时分情况能够争取思考时间，也能够保证部分内容正确得分。
询问是否有金融经济相关知识
回答本科有课程，有基础知识，能够快速学习，不困难

复盘：知识性内容如实回答，但没有掌握时需要补充说明使得面试官了解你的优势，比如有基础、学习起来不难、愿意学习等
询问是否处理过企业级别的数据
回答没有，课程项目处理过4000-70000级别的数据
反问有什么问题想问
询问工作内容涉及的大数据知识有什么？
回答还是工具的使用，主要是使用SQL，所以SQL要熟练。大数据内容更多是一些只是了解。

复盘：有技术性问题的面试可以询问先前没打出来或拿不准的问题
再次询问，研究生课程中大数据计算内容是什么，是不是涉及很多大数据的计算知识
回答不是。主要是大数据基础介绍，比如MapReduce、Hadoop，计算部分主要是文本分析、图论、聚类等算法。

2.问题复盘

主要复盘4、7两道实操题目

有一个学生信息表，包括学生、时间、学科和成绩，获得今天成绩比昨天好的学生信息

主要的难点在于，今天和昨天两个概念在一张表上。

最直观的方法是将表格自联结，算出所有所有时间差，选择时间差为1的数据，然后再进行成绩对比。

该方法的难点在于，ON的条件选择。

如果选择name连接，会导致需要再WHERE处添加DATEDIFF函数，整体繁琐。
而实际上ON的条件除了column相同，还能够和WHERE一样，包含运算结果、大小判断等

SELECT a.*
FROM student a JOIN student b 
ON DATEDIFF(a.s_time,b.s_time)=1
WHERE a.grade>b.grade

其他方法还能够使用窗口函数LAG()，等价于自联结

SELECT *
FROM 
	(SELECT *,LAG(grade) OVER(ORDER BY s_time) lag_grades 
	FROM student) t1
WHERE grade>lag_grades

有一张订单表，包含用户、下单时间，问怎么获取第一笔订单

对于多个用户来说，使用limit选取存在问题，因为limit是在其余命令结束的最后进行的，因此只能获得一条数据

最直观的解法是使用MIN函数+多表关联。

注意：

不能仅使用SELECT *, MIN(paid_time)， 这种做法不会得到对应字段，而是会将min()得到的值增加一列和每一个选出的结果直接拼接。
如果使用GROUP BY cust_id，那么SELECT cust_id, MIN(paid_time)能够得到ID和对应MIN。但如果SELECT *, MIN(paid_time)，则只会返回每一组第一列数据和本组MIN 。原因在于，GROUP BY使用的字段实际上就是组名，当然能够和聚类函数一一对应，而其他字段没有一一对应关系，分组就只能默认返回第一行。
如果使用GROUP BY cust_id, HAVING paid_time=MIN(paid_time)，需要存在SELECT paid_time, MIN(paid_time)，但这样的结果显然会存在两个重复的时间列。 没有paid_time会显示Unkown column ‘paid_time’，两个都没有返回结果为NULL。
因此，如果只是想知道每人第一笔交易时间，可以直接使用SELECT cust_id, MIN(paid_time) FROM customer_info。如果还需要其他信息，则通常使用多表关联，这也是更常见的做法。

SELECT c.user_id, mc.pt
FROM 
	(SELECT user_id, MIN(paid_time) AS pt
	 FROM customer_info
	 GROUP BY user_id) AS mc 
JOIN customer_info AS c USING (user_id)
#JOIN条件要尽可能采用独有的字段，例如ID等，避免出现和实际不相符的情况
#user_id是独有的。如果使用时间相同可能存在不同用户同时间的情况。
WHERE c.paid_time=mc.pt

通用的解法是使用窗口函数+OVER PARTITION排序后选取序列为1的数据。

统计函数+OVER()的好处是，每行都会返回一个值，而不会只返回一个值。

此处可以使用MIN(), 也可以使用 ROW_NUMBER() 对时间排序获取序号，也可以使用LAG()选取NULL的行（即没有前一行数据的行就是是第一笔）
【不过由于IS NULL和AND\OR等结合后规则不太一样，在WHERE中使用容易出错，建议避免使用这种方法】

#MIN+OVER
SELECT R.user_id, R.paid_time
FROM 
	(SELECT user_id, paid_time, 
			MIN(paid_time) OVER(PARTITION BY user_id ORDER BY paid_time ASC) AS pt
	 FROM customer_info) AS R
WHERE R.paid_time=R.pt

#ROW_NUMBER+OVER
SELECT R.user_id, R.paid_time
FROM 
	(SELECT user_id, paid_time, 
			ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY paid_time ASC) AS rn
	 FROM custmoer_info) AS R
WHERE R.rn=1

#LAG+OVER
SELECT R.user_id, R.paid_time
FROM 
	(SELECT user_id, paid_time, 
			LAG(paid_time, 1) OVER(PARTITION BY user_id ORDER BY paid_time ASC) as lag_time
	 FROM custmoer_info)
WHERE R.lag_time IS NULL

需要注意的是

子查询生成的表格需要重命名，否则经常会出现“Every derived table must have its own alias”的警告。
使用排序窗口函数的子查询新表尽可能避免和其他表进行JOIN连接（除非很熟悉返回情况和连接键），否则很容易出现笛卡尔积，使用排序进行筛选的想法就会出错。

比如
子查询表1

id rank
001 1
001 2
表2

id price
001 98
001 36
如果使用JOIN之后，会出现

id rank price
001 1 98
001 1 36
001 2 98
001 2 36

id	rank
001	1
001	2

id	price
001	98
001	36

id	rank	price
001	1	98
001	1	36
001	2	98
001	2	36

3.知识点总结

1. 执行顺序

单表顺序为

FROM
ON
JOIN
WHERE
[GROUP BY]
[WITH]
[HAVING]
SELECT
[DISTINCT]
[ORDER BY]
[LIMIT]

每一步生成一张虚拟表，后面的步骤是在上一张虚拟表中进行筛选与查询。

2.别名（alias）：列别名

列别名和表别名命名规则相同，AS可以省略，命名不能使用函数名、保留字等。
MySQL中，不支持列别名作为WHERE查询条件。

列表别名可以在 GROUP BY, ORDER BY, HAVING中被引用，而标准的SQL禁止在WHERE中被引用，因为WHERE语句执行在先。

可以用子查询的方式代替
因此，使用GROUP BY时，尽量使用HAVING语句，而不是WHERE语句。

拓展：

WHERE和HAVING有执行顺序。
开窗函数只能在GROUP BY后，因此做查询只能使用HAVING，且别名可以使用；原列名字段才可以使用WHERE。

3. JOIN

ON条件使用两表字段相同时，如果字段名字一样，可以直接使用 USING (字段)
ON 条件不仅为a.<>=b.<>形式，还可以添加函数、不等式符号。
JOIN 产生笛卡尔积

4. 子查询

命名：
MySQL要求每一个派生出来的表都必须有一个自己的别名（alias）

单值和多值

  只有返回且仅返回一行、一列数据的子查询才能当成单值子查询，结合SELECT和WHERE操作
  
  如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合，当作一个新表使用，通常在FROM处使用

5. 开窗函数：排序函数

row_number()：连续排序。相同数据排名分先后，没有重复值。
rank()：跳跃排序。相同数据排名相同，之后排名跳过重复值，存在重复值。
dense_rank()：连续排序。相同数据排名相同，之后排名不跳过重复值，存在重复值。

例如：

price	row_num	rank_num	dense_num
100	1	1	1
100	2	1	1
99	3	3	2
98	4	4	3

拓展：
NTILE(N)：

将一个有序的数据集按顺序规则划分为N个桶(bucket)，返回值为每行的对应桶数序号（等价概念切片值，第几个切片，第几个分区）。
规则：
1. 原则上划分为相等的小切片，从1开始到N，为每一行分配该小切片的数字序号。
2. 如果切片不均匀，默认增加第一个切片的行数。
3. 不支持ROWS BETWEEN。
4. 通常格式为
  NTILE(N) OVER([PARTITION BY <> ]ORDER BY <> ASC/DESC)

例如：

price	ntile(2)	ntile(3)
100	1	1
100	1	1
99	2	2
98	2	3

6. 开窗函数：LAG和LEAD

可以在同一次查询中取出同一字段的前N行的数据(LAG)和后N行的数据(LEAD)作为独立的列，相当于表的自关联。

格式和运用(LAG为例)
LAG(<列名> [,offset[, default_value]]) OVER (PARTITION BY <> ORDER BY <> ASC/DESC …)

返回<列名>当前行之前offset行的值。如果没有前一行，返回default_value。

  offset必须是零或文字正整数。如果offset为零，则LAG()函数计算<列名>当前行的值。如果未指定offset，默认为1。如果省略default_value，默认为NULL。
  结果时按ORDER排序后的前offset行数据，尤其注意ORDER BY <时间>时不是前offset天。

7.★窗口函数重点★

所有窗口函数和其别名不能直接在AND、ON、GROUP BY、WHERE、HAVING中使用，只能最直接在SELECT、ORDER中使用。
解决方法是将窗口函数放在子查询中作为子表使用，然后别名可以正常使用。
如果查询使用聚合或GROUP BY，请记住窗口函数只能处理分组后的结果，而不是原始的表数据

例如：

SELECT *, 
	LAG(date,2,'NG') OVER(PARTITION BY id ORDER BY u_date ASC) lag_time, 
	LEAD(date,2,'ND') OVER(PARTITION BY id ORDER BY u_date ASC) lead_time
FROM user_info

结果为

id	u_time	lag_time	lead_time
001	10-01	NG	10-02
001	10-02	10-01	10-03
001	10-03	10-02	ND
002	10-01	NG	10-04
002	10-04	10-01	ND
003	10-02	NG	ND

8. 行转列和列转行

数据分析中行列转换的概念和数学中矩阵转换概念不同。数据分析中行列转换是长表和宽表的互相转换，是数据格式化。该概念更常见地出现在EXCEL数据透视表中。

常见的行转列方法为条件聚合，SUM(或其他统计函数)+IF/CASE WHEN+GROUP BY
注意：

判断句使用NULL或0作为条件不成立返回值
不能只用单个IF或CASE WHEN。结合GROUP BY之后只会保留每一组第一行数据，且只返回一列；不使用GROUP BY宽表横轴分类又出现重复情况。
使用嵌套IF语句，如IF(<条件1>,t1,IF(<条件2>,t2,t3))，可以和GROUP BY结合，但是只会返回一列。
宽表的一对数据分类只对应单个数据值时使用SUM(IF)、MAX(IF)都可以，统计函数只是为了在GROUP后能够输出对应数值。考虑多个数据值情况时则可以使用GROUP_CONCAT()进行结合
如果想要增加一行一列进行统计，可以使用with rollup[参考]。

常见的列转行方法为数据聚合，UNION ALL或UNION(自动去重)

实例：

长表(scoreLong)：横轴为数据名称，纵轴存在重复可能性，更接近关系数据库形式

uid	course	score
A	语文	74
A	英语	83
A	数学	81
A	物理	95
B	语文	89
B	英语	90
B	数学	81
B	物理	78

宽表(scoreWide)：横纵变为数据分类（没有重复），score数据直接填入表格，更接近日常中使用的二维表格形式

uid	语文	英语	数学	物理
A	74	83	81	95
B	89	90	81	78

行转列，即长表转宽表

SELECT uid,  
      SUM(IF(course='语文', score, NULL)) AS `语文`,  
      SUM(IF(course='数学', score, NULL)) AS `数学`, 
      SUM(IF(course='英语', score, NULL)) AS `英语`,  
      SUM(IF(course='物理', score, NULL)) AS `物理`,  
      SUM(IF(course='化学', score, NULL)) AS `化学`  
FROM scoreLong  
GROUP BY uid

列转行，即宽表转长表

SELECT uid, '语文' as course, `语文` as score 
#'语文'为长表内字符串类型数据，`语文`为宽表列名字段
FROM scoreWide  
WHERE `语文` IS NOT NULL#语文分数不能为NULL
UNION ALL
SELECT uid, '数学' as course, `数学` as score  
FROM scoreWide  
WHERE `数学` IS NOT NULL  
UNION ALL 
SELECT uid, '英语' as course, `英语` as score  
FROM scoreWide  
WHERE `英语` IS NOT NULL   
UNION ALL
SELECT uid, '物理' as course, `物理` as score  
FROM scoreWide  
WHERE `物理` IS NOT NULL   
UNION ALL
SELECT uid, '化学' as course, `化学` as score  
FROM scoreWide  
WHERE `化学` IS NOT NULL