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的员工进行奖励。