请选择 进入手机版 | 继续访问电脑版

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

[复制链接]
冰宇 发表于 2020-12-31 19:00:02 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
 

数据分析口试|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.查询次日留存用户数、三日留存用户数、七日留存用户数、次日留存率、三日留存率、七日留存率

  1. -- 用户注册表create table user_info(    user_id  varchar(10) primary key,    reg_time datetime);insert into user_infovalues ('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_logvalues ('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_idgroup 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,部门名称,一月份业绩,二月份业绩)

查询效果如图所示:

  1. -- 建表create table table1(    month varchar(14),    dept  int(4),    sale  int(4));insert into table1values ('一月份', 1, 10);insert into table1values ('一月份', 2, 10);insert into table1values ('一月份', 3, 5);insert into table1values ('二月份', 2, 8);insert into table1values ('二月份', 4, 9);insert into table1values ('二月份', 3, 8);create table table2(    dept int(4),    dname varchar(30));insert into table2values (1, '国内业务一部');insert into table2values (2, '国内业务二部');insert into table2values (3, '国内业务三部');insert into table2values (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.deptgroup by t2.dept, t2.dnameorder by t2.dept
复制代码
 
4.每一行代表了这个订单的id、完成这个订单司机的id,这个订单的金额,订单完成时间,想要一个sql:如果某一天中,任何一个司机完成了5单及以上,且5单的总金额大于50元,把这天和对应的司机id输出。


  1. select date,Driver_id 'driver_id'from tablegroup by date,Driver_idhaving 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天留存用户数


  1. create table Active(    User_id varchar(10),    date    date);insert into Activevalues (&#39;a&#39;, &#39;2019/01/01&#39;),       (&#39;a&#39;, &#39;2019/01/03&#39;),       (&#39;b&#39;, &#39;2019/01/01&#39;),       (&#39;b&#39;, &#39;2019/05/01&#39;);select a1.date as                                                                                          &#39;Date&#39;,       count(distinct a1.User_id)                                                                          &#39;生动用户数&#39;,       sum(case when datediff(a2.date, a1.date) < 30 and datediff(a2.date, a1.date) > 0 then 1 else 0 end) &#39;生动30天留存用户数&#39;from Active a1         inner join Active a2 on a1.User_id = a2.User_idgroup by a1.date;
复制代码
 

6.每行代表司机开始玩游戏的时间(start_time)和游戏竣事时间(end_time),请问每个司机竣事一场游戏后,均匀多久时间内会开始下场游戏?如果司机只玩过一次游戏,就不盘算该司机。(用户均匀消费隔断)


  1. create table Driver_game(    Driver_id  varchar(10),    Start_time datetime,    end_time   datetime);insert into Driver_gamevalues (&#39;A&#39;, &#39;2019/01/01 00:00:00&#39;, &#39;2019/01/01 00:20:00&#39;),       (&#39;A&#39;, &#39;2019/01/01 01:15:00&#39;, &#39;2019/01/01 01:30:00&#39;),       (&#39;A&#39;, &#39;2019/01/01 02:00:00&#39;, &#39;2019/01/01 02:15:00&#39;),       (&#39;B&#39;, &#39;2019/01/01 01:15:00&#39;, &#39;2019/01/01 01:30:00&#39;);select Driver_id,avg(TimeStampDiff(minute,end_time,next_start_time)) as &#39;游戏均匀隔断(分钟)&#39;from(    select *,lead(start_time) over(partition by driver_id order by start_time)as next_start_time    from Driver_game        )as t1where next_start_time is not nullgroup 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 窗口函数 执行顺序

 

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.
  1. select t2.teamName, a.matchResult, a.teamName, a.matchTimefrom t t2         right join (    select *    from m m1             left join t t1 on m1.guestTeamID = t1.teamID) a on t2.teamID = a.hostTeamIDwhere a.matime between &#39;2006-6-1&#39; and &#39;2006-7-1&#39;
复制代码
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 - 改变事务选项

 
 
 

来源:https://blog.csdn.net/cjx_cqupt/article/details/111772462
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )