【牛客SQL快速入门】SQL基础（三）

一、条件函数

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 有更高的效率。