数据分析sql面试必会6题经典_数据分析之SQL面试题

作为数据分析师，使用的最多的莫过于SQL语句，这里拿几道典型的SQL面试题为例，学习一下数据库知识。

自己搭建MySQL服务器太麻烦，可以使用在线数据库

发现sqlzoo是个不错的SQL学习网站

【2018-12-19更新】

sqlfiddle在线数据库的MySQL版本是5.6，不支持row_number()

于是又在电脑上装了最新的MySQL8.0

练习1 计算连续登陆天数 关键词 row_number

百度数据部门的面试题，主要考察row_number

题目：SQL语句如何查询各个用户最长的连续登陆天数？如图左边是源表User,右边是需要达到的查询结果。

左边源表，右边期望结果

首先在在线数据库中创建表，插入上图左边源表数据

CREATE TABLE IF NOT EXISTS `loadrecord` (

`uid` int(6) unsigned NOT NULL,

`loadtime` varchar(200) NOT NULL,

PRIMARY KEY (`uid`,`loadtime`)

) DEFAULT CHARSET=utf8;

INSERT INTO `loadrecord` (`uid`, `loadtime`) VALUES

('201', '2017/1/1'),

('201', '2017/1/2'),

('202', '2017/1/2'),

('202', '2017/1/3'),

('203', '2017/1/3'),

('201', '2017/1/4'),

('202', '2017/1/4'),

('201', '2017/1/5'),

('202', '2017/1/5'),

('201', '2017/1/6'),

('203', '2017/1/6'),

('203', '2017/1/7');

#运行成功

Select UID,max(cnt) as cnt

From (

Select UID,Grp_No,count(*) as cnt

From (

Select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as Grp_No

From loadrecord

) a

Group By UID,Grp_No

) a

Group By UID

运行成功

过程分析：

#第一步

#判断连续的核心是row_number

#因为row_number是连续的

#所以day-row_number，如果值是恒定的，说明也是连续的，反之一定会变化

select uid,(day(loadtime)-row_number() over(partition by uid order by uid) ) AS cnt

from loadrecord;

#第二步

#第一步得到的结果还不是很明显，需要分组用count()计数，取得不同连续值的次数

SELECT uid,count(*) FROM

(

SELECT uid,(DAY(loadtime)-row_number() over(PARTITION BY uid ORDER BY uid) ) AS cnt

FROM loadrecord

) AS a

GROUP BY uid,cnt;

#第三步

#取得最大连续值

#采用类似部门最高薪的方法 select max() group by

SELECT uid, MAX(cnt) FROM

(

SELECT uid,count(*) AS cnt FROM

(

SELECT uid,(DAY(loadtime)-row_number() over(PARTITION BY uid ORDER BY uid) ) AS cnt

FROM loadrecord

) AS a

GROUP BY uid,cnt

) AS b

GROUP BY uid;

Tips1 @和:=

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

Day()用法: 将日期中的Day提取出来，比如Day(2017/01/05) = 5

练习2 选出每个科目成绩排名前三的学生

快手数据部门的面试题~

主要因该是考察row_number

环境：

MAC

MySQL8.0.13

Sequel Pro

创建数据

#创建表

CREATE TABLE IF NOT EXISTS `dataSubject` (

`id` int(6) unsigned NOT NULL,

`name` varchar(200) NOT NULL,

`subject` varchar(200) NOT NULL,

`score` varchar(200) NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8;

#插入数据

INSERT INTO dataSubject VALUES (1,'小黄','数学',99), (2,'小黄','语文',89),(3,'小黄','英语',79), (4,'小黄','物理',99), (5,'小黄','化学',98), (6,'小红','数学',89), (7,'小红','语文',99), (8,'小红','英语',79), (9,'小红','物理',89), (10,'小红','化学',69),(11,'小绿','数学',89), (12,'小绿','语文',91), (13,'小绿','英语',92),(14,'小绿','物理',93), (15,'小绿','化学',94), (16,'小黄','数学',100), (17,'小黄','语文',100),(18,'小黄','英语',100), (19,'小黄','物理',60), (20,'小黄','化学',66);

select看一下

ROW_NUMBER用一下

本来MySQL 8.0引入row_number很好的

但是为什么我用的时候，分数是两位数排序没问题

分数是100分的时候，排序竟然会排到后面去了，好奇怪

求各位大神解答~

#row_number 无视相同值

SELECT * FROM(

SELECT *,row_number() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t

WHERE t.rank < 4;

#rank() 遇到相同值跳跃式排名

SELECT * FROM(

SELECT *,rank() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t

WHERE t.rank < 4;

#dense_rank() 遇到相同值连续式排名

SELECT * FROM(

SELECT *,dense_rank() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t

WHERE t.rank < 4;

找到原因了

之前创建表的时候score字段用的是char类型，所以MySQL把两位数作作为一个字符类别，把三位数作为一个字符类别，所以看上去两位数能排序，但是三位数却不对。

修改方法：建表语句中吧score的类型改为decimal(10)就好啦~

练习3 各部门员工薪水排名

create table employee (empid int ,deptid int ,salary decimal(10,2));

insert into employee values(1,10,5500.00);

insert into employee values(2,10,4500.00);

insert into employee values(3,20,1900.00);

insert into employee values(4,20,4800.00);

insert into employee values(5,40,6500.00);

insert into employee values(6,40,14500.00);

insert into employee values(7,40,44500.00);

insert into employee values(8,50,6500.00);

insert into employee values(9,50,7500.00);

select * from employee;

select *,row_number() over(partition by deptid order by salary desc) as 'rank_rownumber' from `employee`;

按部门薪水排名

这个例子中数值的建表语句是正确的，所以薪水的顺序没问题~