SQL必会的常用函数
目录
条件函数
if
IF(条件表达式,值1,值2)
如果条件表达式为True,返回值1,为False,返回值2.
返回值可以是任何值,比如:数值,文本,日期,空值,NULL,数学表达式,函数等。
当然if函数也可以嵌套。
case
case函数是一种多分支的函数,可以根据条件返回多个可能的结果表达式中的一个,可用在任何允许使用表达式的地方,但不能作为一个单独语句执行。
分为:
简单case函数
搜素case函数
简单case函数:
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
计算测试表达式,按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等,则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等,
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值;
若没有指定ELSE子句,则返回NULL。
举例:询班级表中的学生的班号、班名、系号和班主任号,并对系号作如下处理:
当系号为1时,显示 “计算机系”;
当系号为2时,显示 “软件工程系”;
当系号为3时,显示 “物联网系”。
SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表
搜索case函数:
CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式,
则当指定了ELSE子句时,返回ELSE子句中指定的结果;
如果没有指定ELSE子句,则返回NULL。
上个例子如果用搜索case就可以这样写
SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表
现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
本题注意:age为null 也记为 25岁以下
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_cut
日期函数
- DATEADD
作用:时间相加
语法dateadd(datetime date, bigint delta, string datepart)
参数说明:
date:Datetime类型,日期值。若输入为String类型会隐式转换为Datetime类型后参与运算,其它类型抛异常。
delta:Bigint类型,修改幅度。若输入为String类型或Double型会隐式转换到Bigint类型后参与运算,其他类型会引发异常。若delta大于0,则增,否则减。
datepart:String类型常量。此字段的取值遵循String与Datetime类型转换的约定,即yyyy表示年,mm表示月。 - DATEDIFF
作用:时间相减
datediff(datetime date1, datetime date2, string datepart)
参数说明:
date1:Datetime类型,日期值。若输入为String类型会隐式转换为Datetime类型后参与运算,其它类型抛异常,被减数。
date2:Datetime类型,日期值。若输入为String类型会隐式转换为Datetime类型后参与运算,其它类型抛异常,减数。
datepart:String类型常量。此字段的取值遵循String与Datetime类型转换的约定,即yyyy表示年,mm表示月。
- DATEPART
作用:
datepart(datetime date, string datepart)
参数说明:
date:Datetime类型,若输入为String类型会隐式转换为Datetime类型后参与运算,其它类型抛异常。
datepart:String类型常量,支持扩展的日期格式。若datepart不符合指定格式或者其它类型则会发生异常。
- TO_CHAR
作用:将日期转化为指定格式的字符串
to_char(datetime date, string format)
文本函数
connect
将两个字符串连接
语法:connect(str1,str2)
substring
作用:截取字符串
语法:substring(str,strindex,length)
参数说明:
str:字符串
strindex:想要开始截取的字符串索引
length:想要截取的字符串长度。
substring_index
作用:截取字符串
语法:substring(str,str1,index)
参数说明:
str:字符串
str1:以哪个字符串来切(分隔符)
index:如果为正数,那就是从左往右数,第n个分割符左边的内容。如果为负数,那就是从右边数。
假如给定一个字符串: str=www.Geekerjun.com
那么 substring_index(str,'.',1)
结果就是www
如果把index由1改成-2,那就是Geekerun.com
窗口函数
什么是窗口函数?
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
专用窗口函数
例如我们对一张字段为学号,班级,成绩和ranking的表,我们想要在每个班级内部按成绩进行排名,那么我们就可以执行这句SQL
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表
我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:
1)每个班级内:按班级分组
partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)
2)按成绩排名
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
但是这样子看的话如果先分组再排序,我们可以用group by再排序也能实现,为啥还要用窗口函数呢?
我们再介绍一下其他的专业窗口函数,我们可以举个例子
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位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。
row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
聚合函数作为窗口函数
我们举个简单的例子。
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表
可以看出窗口函数的聚合是针对本行以上的聚合。不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算。
聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。
窗口函数非常适用的场景:排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励。
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!