数据分析面试｜SQL真题持续更新
一、手撕代码
1.查询次日留存用户数、三日留存用户数、七日留存用户数、次日留存率、三日留存率、七日留存率
2.用户消费周期间隔
3.给定表1(月份，部门ID，业绩)和表2（部门ID，部门名称），要求查出每个部门一月份,二月份的业绩（部门ID，部门名称，一月份业绩，二月份业绩）
4.每一行代表了这个订单的id、完成这个订单司机的id，这个订单的金额，订单完成时间，想要一个sql：如果某一天中，任何一个司机完成了5单及以上，且5单的总金额大于50元，把这天和对应的司机id输出。
5.每一行代表这个用户是否在当天活跃过，如果一个用户在当天活跃过，且在未来的第2到第30天又活跃过，则称其为当天的活跃30天留存用户，比如表中a和b用户都在2019/1/1活跃了，a在2019/1/3活跃了，所以满足在2019/1/1的活跃30天留存条件，b没有在第2到第30天活跃过，因此不满足活跃30天留存。我想要每天的活跃用户数和活跃30天留存用户数
6.每行代表司机开始玩游戏的时间（start_time）和游戏结束时间（end_time），请问每个司机结束一场游戏后，平均多久时间内会开始下场游戏？如果司机只玩过一次游戏，就不计算该司机。(用户平均消费间隔)
二、理论知识
1.on作为筛选条件，和where有什么区别？
2.sql怎么避免笛卡尔积
3.Sql四大排名函数 rank、dense rank、row_number、ntile
4.count()
5.怎么判断你写的sql能不能运行，效率如何
6. sql 窗口函数 执行顺序
7.sql中的左连接、右链接、内连接和全连接
一、手撕代码
1.查询次日留存用户数、三日留存用户数、七日留存用户数、次日留存率、三日留存率、七日留存率
-- 用户注册表
create table user_info
(
user_id varchar(10) primary key,
reg_time datetime
);
insert into user_info
values ('u_01', '2020-01-01 09:15:00'),
('u_02', '2020-01-01 00:04:00'),
('u_03', '2020-01-01 22:16:00'),
('u_04', '2020-01-01 20:32:00'),
('u_05', '2020-01-01 13:59:00'),
('u_06', '2020-01-01 21:28:00'),
('u_07', '2020-01-01 14:03:00'),
('u_08', '2020-01-01 11:00:00'),
('u_09', '2020-01-01 23:57:00'),
('u_10', '2020-01-01 04:46:00'),
('u_11', '2020-01-02 14:21:00'),
('u_12', '2020-01-02 11:15:00'),
('u_13', '2020-01-02 07:26:00'),
('u_14', '2020-01-02 10:34:00'),
('u_15', '2020-01-02 08:22:00'),
('u_16', '2020-01-02 14:23:00'),
('u_17', '2020-01-03 09:20:00'),
('u_18', '2020-01-03 11:21:00'),
('u_19', '2020-01-03 12:17:00'),
('u_20', '2020-01-03 15:26:00');
-- 登陆日志表
create table login_log
(
user_id varchar(10),
login_time datetime,
primary key (user_id, login_time)
);
insert into login_log
values ('u_02', '2020-01-02 00:14:00'),
('u_10', '2020-01-02 08:32:00'),
('u_03', '2020-01-02 09:20:00'),
('u_08', '2020-01-02 10:07:00'),
('u_04', '2020-01-02 10:29:00'),
('u_09', '2020-01-02 11:45:00'),
('u_05', '2020-01-02 12:19:00'),
('u_01', '2020-01-02 14:29:00'),
('u_15', '2020-01-03 00:26:00'),
('u_14', '2020-01-03 11:18:00'),
('u_11', '2020-01-03 13:18:00'),
('u_16', '2020-01-03 14:33:00'),
('u_06', '2020-01-04 07:51:00'),
('u_18', '2020-01-04 08:11:00'),
('u_07', '2020-01-04 09:27:00'),
('u_10', '2020-01-04 10:59:00'),
('u_20', '2020-01-04 11:51:00'),
('u_03', '2020-01-04 12:37:00'),
('u_17', '2020-01-04 15:07:00'),
('u_08', '2020-01-04 16:35:00'),
('u_01', '2020-01-04 19:29:00'),
('u_14', '2020-01-05 08:03:00'),
('u_12', '2020-01-05 10:27:00'),
('u_15', '2020-01-05 16:33:00'),
('u_19', '2020-01-06 09:03:00'),
('u_20', '2020-01-06 15:26:00'),
('u_04', '2020-01-08 11:03:00'),
('u_05', '2020-01-08 12:54:00'),
('u_06', '2020-01-08 19:22:00'),
('u_13', '2020-01-09 10:20:00'),
('u_15', '2020-01-09 16:40:00'),
('u_18', '2020-01-10 21:34:00');
-- 查询次日留存用户数、三日留存用户数、七日留存用户数、次日留存率、三日留存率、七日留存率
select date(ui.reg_time) dt, '日期',
count(distinct ui.user_id) 'DNU',
sum(datediff(ll.login_time,ui.reg_time)=1) as '次日留存用户数',
sum(datediff(ll.login_time,ui.reg_time)=3) as '三日留存用户数',
sum(datediff(ll.login_time,ui.reg_time)=7) as '七日留存用户数',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=1) / count(distinct ui.user_id)*100,2) ,'%')'次日留存率',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=3) / count(distinct ui.user_id)*100,2) ,'%') '三日留存率',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=7) / count(distinct ui.user_id)*100,2) ,'%') '七日留存率'
from user_info ui
left join login_log ll on ui.user_id = ll.user_id
group by date(ui.reg_time);注意事项：
1.原时间数据类型是datetime，包含了年月日时分秒，在分组时需要将其转换为date格式进行分组。
2.datediff返回两个日期之间的天数（只有值的日期部分参与计算，而且是前减后的日期天数）。
3.sum忽略Null对数据求和，count忽略Null对数据计数（0也会被计数）。
4.round函数用于把数值字段四舍五日为指定的小数位数。先把小数乘以100，然后round函数保留两位小数
5.concat函数对数据添加百分号，concat_ws（分隔符,字符串,...,字符串），两个函数若有任意参数为Null，则整体结果为Null！
2.用户消费周期间隔
3.给定表1(月份，部门ID，业绩)和表2（部门ID，部门名称），要求查出每个部门一月份,二月份的业绩（部门ID，部门名称，一月份业绩，二月份业绩）
查询结果如图所示：
-- 建表
create table table1
(
month varchar(14),
dept int(4),
sale int(4)
);
insert into table1
values ('一月份', 1, 10);
insert into table1
values ('一月份', 2, 10);
insert into table1
values ('一月份', 3, 5);
insert into table1
values ('二月份', 2, 8);
insert into table1
values ('二月份', 4, 9);
insert into table1
values ('二月份', 3, 8);
create table table2
(
dept int(4),
dname varchar(30)
);
insert into table2
values (1, '国内业务一部');
insert into table2
values (2, '国内业务二部');
insert into table2
values (3, '国内业务三部');
insert into table2
values (4, '国际业务部');
-- 要求查出每个部门一月份,二月份的业绩
select t2.dept,t2.dname,
sum(case when month='一月份' then t1.sale end) as '一月份业绩',
sum(case when month='二月份' then t1.sale end) as '二月份业绩'
from table2 t2 left join table1 t1 on t2.dept = t1.dept
group by t2.dept, t2.dname
order by t2.dept
4.每一行代表了这个订单的id、完成这个订单司机的id，这个订单的金额，订单完成时间，想要一个sql：如果某一天中，任何一个司机完成了5单及以上，且5单的总金额大于50元，把这天和对应的司机id输出。
select date,Driver_id 'driver_id'
from table
group by date,Driver_id
having count(*)>=5 and sum(Amount)>50
5.每一行代表这个用户是否在当天活跃过，如果一个用户在当天活跃过，且在未来的第2到第30天又活跃过，则称其为当天的活跃30天留存用户，比如表中a和b用户都在2019/1/1活跃了，a在2019/1/3活跃了，所以满足在2019/1/1的活跃30天留存条件，b没有在第2到第30天活跃过，因此不满足活跃30天留存。我想要每天的活跃用户数和活跃30天留存用户数
create table Active
(
User_id varchar(10),
date date
);
insert into Active
values ('a', '2019/01/01'),
('a', '2019/01/03'),
('b', '2019/01/01'),
('b', '2019/05/01');
select a1.date as 'Date',
count(distinct a1.User_id) '活跃用户数',
sum(case when datediff(a2.date, a1.date) < 30 and datediff(a2.date, a1.date) > 0 then 1 else 0 end) '活跃30天留存用户数'
from Active a1
inner join Active a2 on a1.User_id = a2.User_id
group by a1.date;6.每行代表司机开始玩游戏的时间（start_time）和游戏结束时间（end_time），请问每个司机结束一场游戏后，平均多久时间内会开始下场游戏？如果司机只玩过一次游戏，就不计算该司机。(用户平均消费间隔)
create table Driver_game
(
Driver_id varchar(10),
Start_time datetime,
end_time datetime
);
insert into Driver_game
values ('A', '2019/01/01 00:00:00', '2019/01/01 00:20:00'),
('A', '2019/01/01 01:15:00', '2019/01/01 01:30:00'),
('A', '2019/01/01 02:00:00', '2019/01/01 02:15:00'),
('B', '2019/01/01 01:15:00', '2019/01/01 01:30:00');
select Driver_id,avg(TimeStampDiff(minute,end_time,next_start_time)) as '游戏平均间隔（分钟）'
from(
select *,lead(start_time) over(partition by driver_id order by start_time)as next_start_time
from Driver_game
)as t1
where next_start_time is not null
group by Driver_id;注意：
1.lead和lag函数，这两个函数一般用于计算差值，最适用的场景是计算花费时间。举个例子，有数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。lead是用于统计窗口内往下第n行值，lag是用于统计窗口内往上第n行值。
2.MySql计算两日期时间之间相差的天数,秒数,分钟数,周数,小时数可以使用TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)，其中unit可以为
FRAC_SECOND 表示间隔是毫秒、SECOND 秒、MINUTE 分钟、HOUR 小时、DAY 天、WEEK 星期、MONTH 月、QUARTER 季度、YEAR 年
二、理论知识
1.on作为筛选条件，和where有什么区别？
答：sql的执行顺序 on>join>where，先通过on条件生成临时表，再进行join操作，若是outer join 则on无法对outter join的主表进行条件筛选（这里说的主表就是left join的左表，right join的右表），而where在join之后，对整张表进行筛选。
【细节】：sql中的连接查询分为3种， cross join，inner join和outer join。on筛选和where筛选的差别只是针对outer join，也就是平时最常使用的left join和right join。因为outer join 的执行过程分为4步1、先对两个表执行交叉连接(笛卡尔积)2、应用on筛选器3、添加外部行（outer join）4、应用where筛选器。因此，以left jion为例，在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。（先on连接生成临时表，再outter join添加外部行）
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义了，条件不为真的就全部过滤掉。
2.sql怎么避免笛卡尔积
3.Sql四大排名函数 rank、dense rank、row_number、ntile
- row_number：对select查询到的数据排序，每一条数据加一个序号。不能用做学生成绩排名，一般多用于分页查询。
- rank：排名函数，相同成绩排名一样，排名跳跃。
- dense_rank：排名函数，排名连续。
- NTILE(group_num)：将所有记录分成group_num个组，每组序号一样（注意：三条数据分 2个区，不可能平均分配。则优先分配较小的区）
4.count()
- count(*)：返回select语句返回结果集的行数，包含Null值和非Null值的所有行。如果用count(*)函数对表中数字进行计数，而不使用Where子句选择其他列，则其执行速度非常块（该优化仅适用MyISAM表，因为MyISAM表的行数存储在
information_schema数据库的tables表的table_rows列中，MySQL可以很快地检索它） - count(1)：和count(*)结果一样，包含Null值和非Null值的所有行
- count(column)：返回不包含
NULL值的所有行数 - count(distinct column)：返回不包含
NULL值的唯一行数 - count(expression)：返回不包含
NULL值的行数，expression 是表达式，意思是在count中我们可以得到符合特点条件的记录数。
效率对比：count(1)和count(*)
- 如果表没有主键，那么count（1）比count（*）快；
- 如果有主键，那么count（主键，联合主键）比count（*）快；
- 如果表只有一个字段，count（*）最快。
- count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。
性能问题：
- 任何情况下SELECT COUNT(*) FROM tablename是最优选择(指没有where的情况）；
- 尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；
- 杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
以下排行是按照效率，而不是时间
count（*） > count（1） > count（id） > count（字段）
参考链接：
5.怎么判断你写的sql能不能运行，效率如何
6. sql 窗口函数 执行顺序
窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。
作用：
- 解决排名问题，e.g.每个班级按成绩排名
- 解决TOPN问题，e.g.每个班级前两名的学生
语法：select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名)
分类：
- 专用窗口函数：rank()，dense_rank()，row_number()
- 汇总函数：max()，min()，count()，sum()，avg()
注意：窗口函数是对where或者group by子句处理后的结果进行操作，因此按照SQL语句的运行顺序，窗口函数一般放在select子句中。
7.sql中的左连接、右链接、内连接和全连接
内连接：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
左连接：左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右连接：右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
全连接：union/union all
union语句注意事项：
列数必须相同；
2.不要求合并的表列名称相同，以第一个sql 表列名为准；
3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；
(select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果
(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果
练习：m 表(即Match表) 的 hostTeamID 与 guestTeamID 都与 t 表(即Team表) 中的 teamID 关联。请查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出： 拜仁 2：0 不来梅 2006-6-21.
select t2.teamName, a.matchResult, a.teamName, a.matchTime
from t t2
right join (
select *
from m m1
left join t t1 on m1.guestTeamID = t1.teamID) a on t2.teamID = a.hostTeamID
where a.matime between '2006-6-1' and '2006-7-1'8.数据库的DDL、DML和DCL的区别与理解
- DML（data manipulation language，记录）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，数据库操作语言
- DDL（data definition language，表）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
- DCL（Data Control Language，数据库对象）： 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
- TCL - Transaction Control Language：事务控制语言，COMMIT - 保存已完成的工作，SAVEPOINT - 在事务中设置保存点，可以回滚到此处，ROLLBACK - 回滚，SET TRANSACTION - 改变事务选项
