SQL 条件函数 日期函数 文本函数 窗口函数
玩了几天，劳逸结合，继续复习刷题sql
一、条件函数
1.题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量（age为null 也记为 25岁以下）
user_profile
期望结果：
涉及知识：
需要使用case函数，case函数是一种分支函数，可以根据条件表达式返回多个可能的结果中的一个。可用在人任何允许使用表达式的地方，当不能单独使用一个语句执行。
简单case函数
计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个where子句的简单表达式进行比较。如果某个简单表达式的值与测试表达式的值相等，则返回第一个匹配的when子句，如果所有表达式的值与测试表达式的值都不相等，若指定了else子句，则返回else子句中指定结果的值，若没有指定else子句，则返回NULL
搜索case函数
按上到下的书写顺序计算每个when子句的布尔表达式。返回第一个取值为true的布尔表达式所对应的结果表达式的值。如果没有取值为true的布尔表达式，且当指定了else子句时，返回else子句指定的结果，如果没有指定else子句，则返回null
SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下' WHEN age >= 25 THEN '25岁及以上'END age_cut,COUNT(*)number
FROM user_profile
GROUP BY age_cutselectif (age >= 25, "25岁以上", "25岁以下") AS age_cut,count(*) as number
fromuser_profile
group byage_cut;
二、日期函数
1.题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
question_practice_detail
期望结果：
涉及知识：
由于涉及到时间，可以直接使用day() month() year()函数，由于是计算八月每天练题数，所以需要按照date时间分隔，由于具体指定了月份可以使用where
selectday(date) day,count(question_id) question_cnt
fromquestion_practice_detail
wheremonth(date) = 8and year(date) = 2021
group bydate
三、文本函数
1.题目：统计每种性别的人数
user_submit
期望结果：
涉及知识：
可以使用substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
count如果为正数，则从左到右，第n个分隔符的左边全部内容。如果count为负数，则从右往左数，第n个分隔符的右边所有内容。
例子：str=www.baidu.com
sunstring_index(str,' . ',1)
结果：www
sunstring_index(str,' . ',-2)
结果：baidu.com
selectsubstring_index (profile, ',', -1) gender,count(*) number
fromuser_submit
group bygender使用substring_index来截取最后一个字段，性别 gender,然后统计gender的数量，最后再根据gender分组
涉及知识：
可以使用like函数进行模糊匹配 %表示占位符，再使用if进行判断，如果profile字段中含有female字段，则为female,否则为male 表示为gender，再用count统计数量。因为需要统计每种性别的人数，所以使用gender来分组。
selectif (profile like '%female', 'female', 'male') gender,count(*) number
fromuser_submit
group bygender四、窗口函数
题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
期望结果：
首先可以先获取每个学校最低gpa,可以使用min函数和group分组，分别得到每个学校最低的gpa
解法1：由于还需要获取device_id，所以需要再获取其中的值。再用where字段 和（university和gpa）
selectdevice_id,university,gpa
fromuser_profile
where(university, gpa) in (selectuniversity,min(gap)fromuser_profilegroup byuniversity)
order byuniversity
解法2：
涉及知识：
窗口函数涉及到组内排名需要涉及到sql的高级功能窗口函数。窗口函数也叫OLAP函数
窗口函数的基本语法：
<窗口函数> over (partition by <用于分组的列名>order by <用于排序的列名>)窗口函数可以放两种函数：
1. 专用窗口函数：rank,dense_rank,row_number专用窗口函数
2. 聚合函数，sum,avg,max,min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
partion by用来对表分组
order by子句是对于分组后的结果进行排序
之前已经有group by的子句分组功能，为什么还需要窗口函数。
group by分组汇总后改变了表的行数，一行一个类别。而partion函数不会减少原表的行数。
其他窗口函数：
rank,dense_rank,row_number区别？
select *,rank() over (order by 成绩 desc) as ranking,dense_rank() over (order by 成绩 desc) as dese_rank,row_number() over (order by 成绩 desc) as row_num
from 班级表
rank函数：为5位、5位、5位、8位，也就是如果有并列名次的行，会占用下一名次的位置。
dense_rank：为5位、5位、5位、6位,如果有并列名次，不占用下一名次位置。
row_num函数：为5位、6位、7位、8位,就是不考虑并列名次的情况。
题解：
首先使用row_num函数进行排序，以学校为分组，然后以学校分组进行排名，再用where筛选需要的名次
select*,row_number() over (partition byuniversityorder bygpa) as rn
fromuser_profile
由于题目要求最后需要按照学校排名，所以最后使用oder by,因为是使用最后一名，所以使用cn排名为1的，因为排序默认为升序。
selectdevice_id,university,gpa
from(select*,row_number() over (partition byuniversityorder bygpa) as rnfromuser_profile) as univ_min
wherern = 1
order byuniversity;