前言
我又断更了!自己好多东西没搞明白,所以也就没有分享,接下来一定笔耕不辍!!今天继续上一次的必会的常用函数!
首先回顾一下知识点:
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;
结果:
总结
今天所学的内容有很多知识点,需要我们慢慢的去消化,理解,很多题目都是一个综合的考查,所以我们需要掌握并且熟练使用。知识点一定要配合练习才能够知道自己是否真正掌握,也能检验自己的水平能力,所以大家一定要多多练习!