数据分析面试｜SQL真题持续更新

最新推荐文章于 2024-06-24 22:17:17 发布

升职加薪

最新推荐文章于 2024-06-24 22:17:17 发布

阅读量1.1k

点赞数

分类专栏： mysql题库大全

本文链接：https://blog.csdn.net/cjx_cqupt/article/details/111772462

版权

mysql题库大全专栏收录该内容

4 篇文章 4 订阅

订阅专栏

数据分析面试｜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的含义了，条件不为真的就全部过滤掉。

详细解释连接：https://www.jb51.net/article/118258.htm

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（字段）

参考链接：https://blog.csdn.net/FeiChangWuRao/article/details/89493516?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control

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语句注意事项：

1.通过union连接的SQL它们分别单独取出的列数必须相同；

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 - 改变事务选项