Sql刷题日志(day3)
一、笔试
1、min(date_time):求最早日期
2、mysql中distinct不能与order by 连用,可以用group by去重
二、面试
1、SQL中如何利用replace函数统计给定重复字段在字符串中的出现次数
(length(all_string)-length(all_string,目标字符串,''))/length(target_string)
2、讲一下Union和Join的区别
- UNION是两张表进行上下拼接,产生的两个记录集(字段要一样的)并在一起,成为一个新的记录集,分为UNION(去重)和UNION ALL两种方法;
- JOIN 是两张表进行左右连接,条件匹配的记录将合并产生一个记录集,有LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN等多种方法。
3、说一下SQL窗口函数并举例
(1)聚合函数与窗口函数区别
①聚合函数是将多条记录聚合为⼀条;窗⼝函数是每条记录都会执行,有几条记录执行完还是几条。
②聚合窗口函数:sum、count、avg、max、min
聚合函数也可以⽤于窗⼝函数。(原因就在于窗⼝函数的执⾏顺序(逻辑上的)是在FROM,JOIN,WHERE, GROUP BY,HAVING之后,在ORDER BY,LIMIT,SELECT DISTINCT之前。它执⾏时GROUP BY的聚合过程已经完成了,所以不会再产⽣数据聚合。)
注:窗口函数是在where之后执行的,所以如果where子句需要用窗口函数作为条件,需要多⼀层查询,在子查询外面进行
(2)窗口函数的基本用法
over关键字用来指定函数执⾏的窗⼝范围,若后⾯括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算;如果不为空,则⽀持以下4中语法来设置窗⼝。
①window_name:给窗口指定⼀个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
②partition by子句:窗口按照哪些字段进⾏分组,窗⼝函数在不同的分组上分别执⾏
③order by子句:按照哪些字段进⾏排序,窗⼝函数将按照排序后的记录顺序进⾏编号
④frame子句:frame是当前分区的⼀个子集,子句⽤来定义子集的规则,通常⽤来作为滑动窗⼝使⽤
(3)(面试考点)序号函数:row_number(),rank(),dense_rank()的区别
①ROW_NUMBER():顺序排序——1、2、3
②RANK():并列排序,跳过重复序号——1、1、3
③DENSE_RANK():并列排序,不跳过重复序号——1、1、2
(4)前后函数:lag(expr,n),lead(expr,n)
用途:返回位于当前行的前n行( LAG(expr,n) )或后n⾏ ( LEAD(expr,n) )的expr的值
应用场景:查询前1名同学的成绩和当前同学成绩的差值
(5)头尾函数:FIRST_VALUE(expr),LAST_VALUE(expr)
⽤途:返回第⼀个( FIRST_VALUE(expr) )或最后⼀个( LAST_VALUE(expr) )expr的值
应用场景:截⽌到当前成绩,按照⽇期排序查询第1个和最后1个同学的分数
4、说一下SQL里面的like的用法
like:模糊查询,'-'匹配单个字符,'%'匹配多个字符
用处:LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式
5、SQL语句求单日留存及一个月的每日留存
/*1.统计每个用户首次访问的日期;2.按用户首次访问的日期分组,统计每个日期的单日留存用户数和30日留存用户数及当前日期的新增用户数; 3.单日留存率=单日留存用户数/当前日期下的新增用户数,30日留存率=30留存用户数/当前日期下的新增用户数*/
with t as
(select user_id, min(log_date) as first_date
from user_log)
select first_date '日期', count(distinct user_id) '新增用户数', round(count(distinct if(datediff(log_date, first_date )=1, user_id, null))/count(distinct user_id), 2) '单日留存率', round(count(distinct if(datediff(log_date, first_date)=29,user_id, null))/count(distinct user_id), 2) '30日留存率'
from
( select l.user_id, l.log_date, t.first_date from user_log l left join t on l.user_id=t.user_id
) a
group by first_date;
6、sql如何进行优化
①用group by代替distinct 去重
②关联之前先用where过滤
③建立合适的索引
④少使用like
⑤用临时表with as
⑥如果是并集,多使用union all(不去重)
⑦在Oracle中可以使用decode代替case when
7、是否会SQL、Python、R等分析工具,分别有什么用?
引申:数据分析师通常会使用EXCEL、SQL、Python和R进行数据处理及数据分析的工作
- EXCEL:EXCEL用于小样本量中基本的数据处理操作。
- SQL:从数据库中取数操作,做一些简单的数据处理工作,通过表连接、嵌套查询等动作完成最终的数据统计工作。SQL基本上可以完成大部分的数据分析工作,对当前公司运营的成效进行数据呈现及分析。
- Python、R:Python和R则属于更高阶的分析工作,可以借助多种多样的工具库,可以通过数据建模,可用于有监督或无监督模型的训练,解决分类或预测问题。
8、说一下count()和count(distinct)用法
- count(column_name):返回非NULL值的数量;
- count(*):返回总行数,NULL也计算进去
- count(distinct):是去重字段后,统计非NULL的值
9、SQL掌握运用的程度?学过sql吗?
①sql 的定义、类别
Structured Query Language(结构化查询语句)。主要包括五类语言:查询语言DQL,操作语言DML,定义语言DDL,事务控制TCL,数据控制语言DCL。
- 数据查询语言 DQL基本结构:是由SELECT子句,FROM子句,WHERE 子句组成的查询块,简称DQL,Data Query Language。**代表关键字为select**。
- 数据库操作语言 DML:可以实现对数据库的基本操作。代表关键字为insert、delete 、update。
- 数据定义语言DDL:用来创建数据库中的各种对象,创建、删除、修改表的结构,比如表、视图、索引、同义词、聚簇等,简称DDL,Data Denifition Language。代表关键字为create、drop、alter。(和DML相比,DML是修改数据库表中的**数据**,而 DDL 是修改数据中表的**结构**)。
- 事务控制语言 TCL:经常被用于快速原型开发、脚本编程、GUI和测试等方面。**代表关键字为commit、rollback。**
- 数据控制语言 DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。**代表关键字为grant、revoke。**
(2)sql 的基本语法上衍生出的其它知识点:
- 嵌套查询:WHERE筛选条件中使用嵌套查询,将(SELECT-FROM-WHERE)的查询语句作为子查询嵌套进去;
- 组合查询:使用UNION/UNION ALL对多个查询结果进行组合,其中UNION将对结果进行去重;
- 表连接:表连接中根据使用场景选择INEER/LEFT/RIGHT/FULL JOIN;
- 聚合函数:使用MAX/MIN/SUM/AVG/COUNT对查询数据进行聚合;
- 窗口函数:多查询数据排序或多样性聚合,xxx over(partition by ... order by ...)