一、条件函数
IF 条件函数
IF函数是最常用到的条件函数,写法为 if(x=n,a,b),x=n代表判断条件,如果x=n时,那么结果返回a,否则返回b。
-- 把非北京大学的用户统一归为其他大学
Select device_id,if(university = ‘北京大学',’北京大学',’其他大学')
as university
From user_profile
Case when
case when与if的作用基本相同,也是按照条件更换列中的内容,区别是case when可以对多个条件进行转换。
举个🌰:
score为A的情况下,值替换为优,B替换为良,C替换为中,其余情况全部替换为不及格。
Select CASE WHEN SCORE = 'A' THEN '优' WHEN SCORE = 'B' THEN '良' WHEN SCORE = 'C' THEN ‘中' ELSE ‘不及格' END --注意这里需要加end作为结束
二、日期函数
在DBMS中日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤。
常见的日期数据格式有两种:'yyyy-MM-dd' 和 'yyyyMMdd'。
时间戳-日期格式转化
时间戳是数据库中自动生成的唯一二进制数字,表明数据库中数据修改发生的相对顺序,其记录形式类似:1627963699 ,在实际工作环境中,对于用户行为发生的时间通常都是用时间戳进行记录,时间戳和日期格式之间可以利用from_unixtime和 unix_timestamp进行转换。
from_unixtime可以将时间戳转换成日期,unix_timestamp可以将日期转换回时间戳。
举个🌰:
-- from_unixtime select from_unixtime(time,'yyyy-MM-dd') as time From question_practice_detail -- unix_timestamp select unix_timestamp('2021-08-01','yyyy-MM-dd') as time
年月日截取
对于常见的日期格式,类似’2021-08-01’,有时候在聚合计算时我们会想将日期中的年、月、日分别提取出来,这时应该怎么做呢?SQL为此提供了对应的年、月、日提取函数,分别为year(), month(), day()。
select year('2021-08-01'),month('2021-08-01'),day('2021-08-01')
日期差计算
-datediff
datediff的作用为计算两个日期之间的天数间隔,语法为datediff(date1,date2),返回起始时间 date1 和结束时间 date2 之间的天数,date1大于date2的情况下,返回的天数为正数,date1小于date2的情况下,返回的天数为负数。
select datediff('2021-08–09','2021-08-01')
-- output : 8
-date_sub
语法为date_sub (string startdate, interval int day) ,返回开始日期startdate减少days天后的日期
select date_sub('2021-08–09',interval 8 day)
-- Output :‘2021-08-01'
-date_add
语法为date_add(string startdate, interval int day) ,返回开始日期startdate增加days天后的日期
select date_add('2021-08–01',interval 8 day)
-- Output : ‘2021-08-09'
三、文本函数
长度 —length
length函数返回文本字段中值的长度
select length(‘abc’)
-- 3
连接 —concat
CONCAT函数用于将两个或多个字符串连接起来,形成一个单一的字符串
select concat(‘abc’,’bcd’,’ff')
-- ‘abcbcdff'
分割 —SUBSTRING_INDEX
SUBSTRING_INDEX函数用于将字符串依据某个指定分隔符进行切分,并返回指定位置分隔符前的字符。(字段分割符,位置)
举个🌰:
假如现在有一列字段以字符串记录了用户的身高体重和性别,如 ‘180,78kg,male',现 在想要分别取出用户的身高和体重,应该怎么做呢?
这时就可以用到 SUBSTRING_INDEX函数,指定逗号作为分隔符,如果想要返回身高的话,位置填1,即 可得到相应结果。
select SUBSTRING_INDEX('180,78kg',',','1') as height
如果想要取出体重怎么办?可以嵌套一次SUBSTRING_INDEX查询,负数位置代表从后向前取,-1代表最后一位,第二层嵌套相当于是取出’180,78kg'的最后一个逗号后的字符,得到我们想要的体重数据
select SUBSTRING_INDEX(SUBSTRING_INDEX('180,78kg',',’,'2’),’,’,-1) as height
定位 —instr
instr(substr,str):返回substr字符串在str里第一次出现的位置,从1开始,没有则返回0
select instr('bacd','a')
-- 2
截取 —substring
substr(string A,int start,int len),返回字符串A从下标start位置开始,长度为len的字符串
substring(string A,int start),在不指定返回字符串长度的情况下,返回字符串A从下标start位置到结尾的字符串
select substring(‘bacda’,2)
-- ’acda’
select substring(‘bacda’,2,2)
-- 'ac'
四、窗口函数
row_number() over partition by
函数的含义为先分组再排序, row_number() over (partition by col1 order by col2),表示根据col1分组,在分组内部根据col2排序
举个🌰:
在每个学校的内部根据gpa进行一次排名,获得每个学生在学校的名次数据,desc代表是按照从大到小降序排列。
Select device_id, university, gpa, row_number() over (partition by university order by gpa desc) as rank. - -desc代表降序排列 From user_profile
注意: 输出结果 rank 会根据排序结果自动赋值
lead()/leg() over (partition by col1 order by col2)
lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接,并且 LAG 和 LEAD 有更高的效率。