前言

  我又断更了！自己好多东西没搞明白，所以也就没有分享，接下来一定笔耕不辍！！今天继续上一次的必会的常用函数！

首先回顾一下知识点：
1、CASE函数
CASE
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
2、日期函数
2021年8月，写法有很多种，比如用year/month函数的
year(date)=2021 and month(date)=8
每天：按天分组group by date

SLogan:利用有限的时间，撸起袖子加油干！既要面包也要玫瑰！！！

一、学习过程

2、日期函数

【SQL29】计算用户的平均次日留存率

解题思路：
  对现有关系数据表的列进行拆解重构，目的是实现当日用户与次日用户的对应匹配，通过对question_practice_detail表利用from …left outer join … on …建立自联结,这里重点指出通过on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1实现联结匹配，datediff(次日，当日)=1即次日-当日=1（相隔一天）发挥构建桥梁作用； DATEDIFF()函数返回两个日期之间的差异
  分母count(distinct q1.device_id,q1.date) 通过DISTINCT 对当日的device_id，date进行双项剔重，利用COUNT()统计用户数；同样的分子count(distinct q2.device_id,q2.date)对次日的device_id，date进行双项剔重。

代码如下：

SELECT 
count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
FROM question_practice_detail as q1 
left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1

结果：

3、文本函数

【SQL30】统计每种性别的人数

解题思路：
select 语句中 if 的用法
IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3
代码如下：

SELECT 
IF(profile LIKE '%female','female','male') AS gender,
COUNT(*) number
FROM user_submit
GROUP BY gender;

结果：

【SQL32】截取出年龄

解题思路：
substring_index用法
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
如果count是负数，那么就是从右往左数，第N个分隔符的右边的全部内容
如果需要中间内容，需要嵌套使用
注意：如果count为1(而不是0)，表示从第一个位置开始【数据库的记录都是从1开始，没有从0开始】。

先截取至年龄，在把年龄单独拎出来！substring_index(SUBSTRING_INDEX(profile,‘,’,3),‘,’,-1)
代码如下：

SELECT substring_index(SUBSTRING_INDEX(profile,',',3),',',-1) AS age,
COUNT(device_id) number
FROM user_submit
GROUP BY age

结果：

【SQL31】提取博客URL中的用户名

解题思路：
和上一题同理，只要搞懂上一题substring_index的用法，这一题就迎刃而解了。
代码如下：

SELECT device_id,
SUBSTRING_INDEX(blog_url,"/",-1) AS user_name
FROM user_submit

结果：

4、窗口函数

【SQL33】找出每个学校GPA最低的同学

解题思路：
<窗口函数>() over (partition by <用于分组的字段> order by <用于排序的字段>)
<窗口函数>的位置可以放两种函数
1 专用窗口函数rank， dense_rank， row_number 等等
2 聚合函数， 如sum, avg, count, max, min 等等
代码如下：

SELECT device_id, university,gpa FROM
(SELECT device_id, university,gpa,
RANK() over (PARTITION BY university ORDER BY gpa) as rk FROM user_profile) as a
WHERE a.rk=1;

结果：

总结

 今天所学的内容有很多知识点，需要我们慢慢的去消化，理解，很多题目都是一个综合的考查，所以我们需要掌握并且熟练使用。知识点一定要配合练习才能够知道自己是否真正掌握，也能检验自己的水平能力，所以大家一定要多多练习！