概念:
MySQL中没了提高代码的重用性和隐藏实现细节,MySQL提供了很多函数。函数可以理解为别人封装号的模板代码
分类:
- 聚合函数
- 数学函数
- 字符串函数
- 日期函数
- 控制流函数
- 窗口函数
聚合函数
概念:
在MySQL中,聚合函数主要由:count,sum,min,max,avg,这些聚合函数在之前都讲过,不在过多重复。这里主要学习group_concat()。
group_concat()函数首先根据group by指定的列进行分组,并且用分隔符分隔,将同一个分组中的值连接起来,返回一个字符串结果
方法:
group concat ([distinct ] 字段名[order by 排序字段 asc/desc ] [ separator '分隔符 '])
注:
- 使用distinct可以排除重复值
- 如果需要对结果中的值进行排序,可以使用order by子句
- separator 是一个字符串值,默认为逗号
例如:
CREATE TABLE emp(
emp_id INT PRIMARY KEY auto_increment comment '编号',
emp_name CHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '工资',
department CHAR(20) NOT NULL DEFAULT '' COMMENT '部门'
);
INSERT INTO emp(emp_name,salary,department) VALUES('张晶晶',5000,'财务部'),
('王飞飞', 5800.00, '财务部'),( '赵刚', 6200.00, '财务部'),
( '刘小贝', 5700.00, '人事部'),( '王大鹏', 6700.00, '人事部'),
( '张小斐', 5200.00, '人事部'),( '刘云云', 7500.00, '销售部'),
( '刘云鹏', 7200.00, '销售部'),( '刘云鹏', 7800.00, '销售部');
-- 将所有员工的名字合并成一行
SELECT GROUP_CONCAT(emp_name) FROM emp;
-- 指定分隔符合并
SELECT GROUP_CONCAT(emp_name SEPARATOR ';') FROM emp;
-- 指定排序方式和分隔符
SELECT department,GROUP_CONCAT(emp_name SEPARATOR ';')FROM emp GROUP BY department;
SELECT department,GROUP_CONCAT(emp_name ORDER BY salary DESC SEPARATOR ';')FROM emp GROUP BY department;
数学函数
函数名 | 描述 | 实例 |
---|---|---|
ABS(x) | 返回x的绝对值 | 返回-1的绝对值;SELECT ABS(-1)——返回1 |
CEIL(x) | 返回大于或等于x的最小整数 | SELECT CEIL(1.5) ——返回2 |
FLOOR(x) | 返回小于或等于x的最大整数 | 小于或等于1.5的整数:SELECT FLOOR(1.5) ——返回1 |
GREATEST(expr1,expr2,expr3,……) | 返回列表中的最大值 | 返回以下数字列表中的最大值:SELECT GREATEST(3,12,34,8,25);——34 返回以下字符串列表中的最大值:SELECT GREATEST(“Google”,“Runoob”,“Apple”) ;——Runoob |
LEAST(expr1,expr2,expr3……) | 返回列表中的最小值 | 返回以下数字列表中的最小值:SELECT LEAST(3,12,34,8,25);——3 返回以下字符串列表中的最小值:SELECT LEAST(“Google”,“Runoob”,“Apple”);——Apple |
MAX(expression) | 返回字段expression中的最大值 | 返回数据表products中字段price的最大值:ELECT MAX(Price) AS MinPrice FROM Products; |
MIN(expression) | 返回字段expression中的最小值 | 返回数据表Products中字段Price的最小值:SELECT MIN(Price) AS MinPrice FROM Products; |
MOD(x,y) | 返回x除以y以后的余数 | 5除于2的余数: SELECT MOD(5,2) ——1 |
PI()) | 返回圆周率(3.141593) | SELECT PI() ——3.141593 |
POW(x,y) | 返回x的y次方 | 2的3次方: SELECT POW(2,3) —— 8 |
RAND() | 返回0到1的随机数 | SELECT RAND() ——0.93099315644334 |
ROUND(x) | 返回离x最近的整数(遵循四舍五入) | SELECT ROUND(1.23456) ——1 |
ROUND(X,Y) | 返回指定位数的小数(遵循四舍五入) | SELECT ROUND(1.23456,3) ——1.235 |
TRUNCATE(x,y) | 返回数值x保留小数点后y位的值(与ROUND最大的区别是不会进行四舍五入) | SELECT TRUNCATE(1.23456,3) ——1.234 |
例如:
SELECT abs(-10);
SELECT abs(10);
-- 向上取整
SELECT CEIL(1.1);
SELECT CEIL(1.0);
-- 向下取整
SELECT FLOOR(1.1);
SELECT FLOOR(1.9);
-- 取列表最大值
SELECT GREATEST(1,2,3);
-- 取列表最小值
SELECT LEAST(1,2,3);
-- 取模
SELECT MOD(5,2);
-- 取x的y次方
SELECT POWER(2,3);
-- 取随机数3
SELECT RAND();
SELECT FLOOR(RAND()*100);
-- 将小数的四舍五入取整
SELECT ROUND(3.1415);
SELECT ROUND(3.5415);
-- 将小数的四舍五入取指定位数小数
SELECT ROUND(3.5415,3);
-- 将小数直接截取到指定位数
SELECT TRUNCATE(3.1515,3);
字符串函数
函数名 | 描述 | 实例 |
---|---|---|
CHAR_LENGTH(25) | 返回字符串s的字符数 | 返回字符串RUNOOB的字符数SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString; |
CHARACTER_LENGTH(s) | 返回字符串s的字符数 | 返回字符串RUNOOB的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString; |
CONCAT(s1,s2,…sn) | 字符串s1,s2等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT(“SQL”,“Runoob”,“Google”,“Facebook”) AS ConcatenatedString; |
CONCAT_WS(x,s1,s2…sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上想,x可以是分隔符 | 合并多个字符串,并添加分隔符:SELECT CONCAT_WS(" -",“SQL”,“Tutorial”,“is”,“fun!”) AS ConcatenatedString; |
FIELD(s,s1,s2) | 返回第一个字符串s在字符串列表(s1,s2…)中的位置 | 返回字符串c在列表值中的位置:SELECT FIELD(“c”,“a”,“b”,“c”,“d”,“e”); |
LTRIM(s) | 去掉字符串s开始出的空格 | 去掉字符串RUNOOB开始出的空格:SELECT LTRIM(" RUNOOB") AS LeftTrimmedString; |
MID(s,n,len) | 从字符串s的n位置截取长度为len的子字符串,同SUBSTRING(s,n,len) | 从字符串RUNOOB中的第2个位置截取3个字符:SELECT MID(“RUNOOB”,2,3) AS ExtractString; |
POSITION(s1 IN s) | 从字符串s中获取s1的开始位置 | 返回字符串abc中b的位置:SELECT POSITION(‘b’ in ‘abc’) |
REPLACE(s,s1,s2) | 将字符串s2替代字符串s中的字符串s1 | 将字符串abc中的字符a替换为字符x:SELECT REPLACE(‘abc’) |
RIGHT(s,n) | 返回字符串s的后n个字符 | 返回字符串runnoob的后两个字符:SELECT RIGHT(‘runoob’,2) |
RTRIM(s) | 去掉字符串s结尾处的空格 | 去点字符串RUNOOB的末尾空格:SELECT RTRIM("RUNOOB ") AS RightTrimmedString; |
STRCMP(s1,s2) | 比较字符串s1和s2,如果s1与s2相等返回0,如果s1>s2返回1,如果s1<s2返回-1 | 比较字符串:SELECT STRCMP(“runoob”,“runoob”); |
SUBSTR(s,start,length) | 从字符串s的start位置截取长度为length的子字符串 | 从字符串RUNOOB中的第2个位置截取3个字符:SELECT SUBSTR(“RUNOOB”,2,3) AS ExtractString; |
SUBSTRING(s,start,length) | 从字符串s的start位置截取长度为length的子字符串 | 从字符串RUNOOB中的第2个位置截取3个字符:SELECT SUBSTRING(“RUNOOB”,2,3) AS ExtractString; |
TRIM(s) | 去掉字符串s开始和结尾处的空格 | 去掉字符串RUNOOB的首尾空格:SELECT TRIM(’ RUNOOB ') AS TrimmedString; |
UCASE(s) | 将字符串转化为大写 | 将字符串runnob转换为大写:SELECT UCASE(“runoob”); |
UPPER(s) | 将字符串转化为大写 | 将字符串runnob转换为大写:SELECT UPPER(“runoob”); |
LCASE(s) | 将字符串s的所有字母变成小写字母 | 字符串RUNOOB转换为小写:SELECT LCASE(‘RUNOOB’) |
LOWER(s) | 将字符串s的所有字母变成小写字母 | 字符串RUNOOB转换为小写:SELECT LOWER(‘RUNOOB’) |
例如:
-- 获取字符串字符个数
SELECT CHAR_LENGTH('hello');
SELECT CHAR_LENGTH('你好吗');
-- length取长度,返回的单位是字节
SELECT LENGTH('hello');
SELECT LENGTH('你好吗');
-- 字符串合并
SELECT CONCAT('hello','world');
-- 指定分隔符进行字符串合并
SELECT CONCAT_WS('-','hello','world');
-- 返回字符串在列表中的位置
SELECT FIELD('aaa','aaa','bbb','ccc');
-- 去除字符串左边的空格
SELECT LTRIM(' aaaaa');
SELECT LTRIM(' aaaaa ');
SELECT RTRIM(' aaaaa ');
SELECT RTRIM(' aaaaa');
-- 字符串截取
SELECT MID("helloworld",2,3);
-- 获取字符串A在字符串中出现的位置
SELECT POSITION('abc' IN 'helloabcworld');
SELECT POSITION('abc' IN 'habcelloabcworld');
-- 字符串替换
SELECT REPLACE('aaahelloworld','aaa','bbb');
-- 字符串翻转
SELECT REVERSE('hello');
-- 返回字符串的后几个字符
SELECT RIGHT('hello',3);
-- 字符串比较
SELECT STRCMP("hello","world");
-- 字符串截取
SELECT SUBSTR('hello',2,3);
SELECT SUBSTRING('hello',2,3);
-- 将小写转大写
SELECT UCASE("helloWorld");
SELECT UPPER("helloWorld");
-- 将大写转小写
SELECT LCASE("HELLOWORLD");
SELECT LOWER("HELLOWORLD");
日期函数
函数名 | 描述 | 实例 |
---|---|---|
UNIX_TIMESTAMP() | 返回从1970-01-01 00:00:00到当前毫秒值 | select UNIX_TIMESTAMP()->1632729059 |
UNIX_TIMESTAMP(DATE_STRING) | 将制定日期转化为毫秒值时间戳 | SELECT UNIX_TIMESTAMP(‘2011-12-07 13:01:03’); |
FROM_UNIXTIME(BIGINT UNIXTIME[,STRING FORMAT]) | 将毫秒值转化为指定格式日期 | SELECT FROM_UNIXTIME(1598079966,‘%Y-%m-%d %H:%i:%s’);(1598079966,‘%Y-%m-%d %H:%i:%s’); ->2020-02-22 15-06-06 |
CURDATE() | 返回当前日期 | SELECT CURDATE(); ->2018-09-19 |
CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE();->2018-09-19 |
CURRENT_TIME | 返回当前时间 | SELECT CURRENT_TIME(); |
CURTIME() | 返回当前时间 | SELECT CURTIME(); |
CURRENT_TIMESTAMP() | 返回当前日期和时间 | SELCET CURRENT_TIMESTAMP() |
DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE(“2017-06-15”); |
DATEDIFF(d1,d2) | 计算日期d1->d2之间相隔的天数 | SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) |
TIMEDIFF(time1,time2) | 计算时间差值 | SELECT TIMEDIFF(‘13:10:11’,‘13:10:10’); |
DATE_FORMAT(d,f) | 按表达式f的要求显示日期d | SELECT DATE_FORMAT(‘2011-11-11 11:11:11’,‘%M%d%Y’); |
STR_TO_DATE(string,format_mask) | 将字符串转化为日期 | SELCET STR_TO_DATE(“August 10 2017”,“%M%d%Y”); |
DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔 | Orders表中OrderDate字段减去2天:SELECT Orderid,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders |
ADDDATE/DATE_ADD(d,INTERVAL expr type) | 计算起始日期d加上一个时间段后的日期,type值可以是:1.MICROSECOND 2.SECOND 3.MINUTE 4.HOUR 5.DAY 6.WEEK 7.MONTH 8.QUARTER 9.YEAR 10.DAY_MINUTE 11.DAY_HOUR 12.YEAR_MONTH | SELECT DATE_ADD(“2017-06-15”,IN TERVAL 10 DAY) ; SELECT DATE_ADD(“2017-06-15 09:34:21”,INTERVAL -3 HOUR); SELECT DATE_ADD(“2017-06-15 09:34:24”,INTERVAL ) |
EXTRACT(type FROM d) | 从日期d中获取指定的值,type指定返回的值。type可取值与上述一致 | SELECT EXTRACT(MINUTER FROM ‘2011-11-11 11:11:11’) |
LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY(‘2017-06-20’); |
MAKEDATE(year,day-ok-year) | 基于给定参数年份year和所在年中的天数序号day-of-year返回一个日期 | SELECT MAKEDATE(2017,3) |
YEAR(d) | 返回年份 | SELECT YEAR(‘2017-06-15’); |
MONTH(d) | 返回日期d中的月份值,1到12 | SELECT MONTH(‘2011-11-11 11:11:11’); |
DAY(d) | 返回日期d的日期部分 | SELECT DAY(‘2017-06-15’); |
HOUR(t) | 返回t中的小时值 | SELECT HOUR(1:2:3); |
MINUTE(t) | 返回t中的分钟值 | SELECT MINUTE(‘1:2:3’) |
SECOND(t) | 返回t中的秒钟值 | SELECRT SECOND(‘1:2:3’) |
QUARTER(d) | 返回日期d是第几个季节,返回1到4 | SELECT QUARTER(‘2011-11-11 11:11:11’) |
MONTHNAME(d) | 返回日期当中的月份名称,如November | SELECT MONTHNAME(‘2022-12-22 22:22:22’); |
DAYNAME(d) | 返回日期d是星期几,如Monday,Tuesday | SELECT DAYNAME(‘2022-12-22 22:22:22’); |
DAYOFMONTH(d) | 计算日期d是本月的第几天 | SELECT DAYOF MONTH(‘2022-12-22 22:22:22’); |
DAYOFWEEK(d) | 日期d见天是星期几,1星期日,2星期一,以此类推 | SELECT DAYOFWEEK(‘2022-12-22 22:22:22’); |
DAYOFYEAR(d) | 计算日期d是本年的第几天 | SELECT DAYOFYEAR(‘2022-12-22 22:22:22’); |
WEEK(d) | 计算日期d是本年的第几个星期,范围是0到53 | SELECT WEEK(‘2022-12-22 22:22:22’); |
WEEKDAY(d) | 日期d是星期几,0表示星期一,1表示星期二 | SELECT WEEKDAY(‘2022-06-17’); |
WEEKOFYEAY(d) | 就散日期d是本年的第几个星期,范围是0到53 | SELECT WEEKOFYEAR(‘2022-06-17 18:14’); |
YEARWEEK(date,mode) | 返回年份及第几周(0到53),mode中0表示周天,1表示周一,以及类推 | SELECT YEARWEEK(‘2022-06-17’); |
NOW() | 返回当前日期和时间 | SELECT NOW() |
例如:
-- 获取的时间戳(毫秒值)
SELECT UNIX_TIMESTAMP();
-- 将一个日期字符串转化为毫秒值
SELECT UNIX_TIMESTAMP('2022-06-17 17:09:00');
-- 将时间戳毫秒值转化为指定格式的日期
SELECT FROM_UNIXTIME(1655456940,'%Y-%m-%d %H:%i:%s');
-- 获取当前的年月日
SELECT CURDATE();
SELECT CURRENT_DATE();
-- 获取当前时间的时分秒
SELECT CURRENT_TIME;
SELECT CURTIME();
-- 获取年月日和时分秒
SELECT CURRENT_TIMESTAMP();
-- 从日期字符串中获取年月日
SELECT DATE('2022-12-10 12:35:55');
-- 获取日期之间的差值
SELECT DATEDIFF('2022-06-17','2021-12-01');
-- 获取事件的差值(秒级)
SELECT TIMEDIFF('17:34:00','12:12:34');
-- 日期格式化
SELECT DATE_FORMAT('2022-06-17 17:35:0','%Y.%m.%d %H:%i:%s');
-- 将字符串转为日期
SELECT STR_TO_DATE('2022-06-17 11:11:11','%Y-%m-%d %H:%I:%s');
-- 将日期进行减法
SELECT DATE_SUB('2022-06-17',INTERVAL 2 DAY);
SELECT DATE_SUB('2022-06-17',INTERVAL 2 MONTH);
-- 将日期进行加法
SELECT DATE_ADD('2022-06-17',INTERVAL 2 DAY);
SELECT DATE_ADD('2022-06-17',INTERVAL 2 MONTH);
-- 从日期中获取小时
SELECT EXTRACT(HOUR FROM '2022-6-17 17:50:00');
SELECT EXTRACT(DAY FROM '2022-6-17 17:50:00');
SELECT EXTRACT(MONTH FROM '2022-6-17 17:50:00');
-- 获取给定日期的最后一天
SELECT LAST_DAY('2022-02-1');
-- 获取指定年份和天数的日期
SELECT MAKEDATE('2022',100);
-- 根据日期获取年月日,时分秒
SELECT YEAR('2021-12-13 11:56:00');
SELECT MONTH('2021-12-13 11:56:00');
SELECT MINUTE('2021-12-13 11:56:00');
SELECT QUARTER('2021-12-13 11:56:00');
-- 根据日期获取信息
SELECT MONTHNAME('2022-12-22 22:22:22');
SELECT DAYNAME('2022-12-22 22:22:22');
SELECT DAYOFMONTH('2022-12-22 22:22:22');
SELECT DAYOFWEEK('2022-12-22 22:22:22');
SELECT DAYOFYEAR('2022-12-22 22:22:22');
SELECT WEEK('2022-6-17');
SELECT YEARWEEK('2022-06-17');
SELECT NOW();
控制流函数
if逻辑判断语句
函数名 | 描述 | 实例 |
---|---|---|
IF(expr,v1,v2) | 如果表达式expr成立,返回结果v1;否则,返回结果v2。 | SELECT IF(1>0,‘正确’,‘错误’) |
IFNULL(v1,v2) | 如果v1的值不为NULL,则返回v1,否则返回v2。 | SELECT IFNULL(nulll,‘Hello Word’) |
ISNULL(expression) | 判断表达式是否为NULL | SELECT ISNULL(NULL); |
NULLIF(expr1,expr2) | 比较两个字符串,如果字符串expr1与expr2相等,则返回NULL,否则返回expr1 | SELCT NULLIF(25,25); |
例如:
SELECT IF(5>3,'大于','小于');
SELECT IFNULL(5,0);
SELECT IFNULL(NULL,0);
SELECT ISNULL(5);
SELECT ISNULL(NULL);
SELECT NULLIF(12,12);
SELECT NULLIF(12,20);
case when 语句
函数名 | 描述 | 实例 |
---|---|---|
CASE expression WHEN condition1 THEN result1 … WHEN conditionN THEN resultN ELSE result END | CASE表示函数开始,END表示函数结束。如果condition1成立,则返回result1,…,当全部不成立则返回result,而当有一个成立后,后面就不执行了。 | SELECT CASE 100 WHEN 50 THEN ‘TOM’ WHEN 100 THEN 'MARY ELSE ‘TIM’ END; |
例如:
-- CASE WEHN语句
SELECT
CASE 1
WHEN 1 THEN
'你好'
WHEN 2 THEN
'hello'
WHEN 5 THEN
'正确'
ELSE
'其他'
END;
窗口函数(MySQL 8.0以下版本可以不看)
概念:
- MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数,与Oracle函数类似,属于MySQL的一大特点。
- 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变函数。
方法:WINDOW_FUNCTION(expr) OVER(
OARTITION BY …
ORDERR BY …
frame_clause
)
注:window_function是窗口函数的名称;expr是参数,有些函数不需要参数;over子句包含三个选项:
- 分区(partition by)
PARTITION BY 选项用于将数据行拆分成多个分区(组),它的作用类似于GROUP BY分组。如果省略了PARTITION BY,所有的数据作为一个组进行计算 - 排序(order by)
OVER子句中的ORDER BY选项用于指定分区内的排序方式,与ORDER BY子句的作用类似 - 窗口大小(frame_clause)
frame_clause选项用于在当前分区内指定一个计算窗口,也就是一个与当前相关的数据子集
序号函数
序号函数有三个:ROW_NUMBER()、RANK()、DENSE_RANK(),可以用来实现分组排序,并添加序号。
方法:
ROW_NUMBER()|RANK()|DENSE_RANK() OVER(
PARTITION BY…
ORDER BY…
)
例如:
CREATE TABLE employee(
dname VARCHAR(20),
eid VARCHAR(20),
ename VARCHAR(20),
hiredate DATE,
salary DOUBLE
);
INSERT INTO employee VALUES ('研发部', '1001', '刘备', '2021-11-01', 3000);
INSERT INTO employee VALUES ('研发部', '1002', '关羽', '2021-11-02', 5000);
INSERT INTO employee VALUES ('研发部', '1003', '张飞', '2021-11-03', 7000);
INSERT INTO employee VALUES ('研发部', '1004', '赵云', '2021-11-04', 7000);
INSERT INTO employee VALUES ('研发部', '1005', '马超', '2021-11-05', 4000);
INSERT INTO employee VALUES ('研发部', '1006', '黄忠', '2021-11-06', 4000);
INSERT INTO employee VALUES ('销售部', '1007', '曹操', '2021-11-01', 2000);
INSERT INTO employee VALUES ('销售部', '1008', '许褚', '2021-11-02', 3000);
INSERT INTO employee VALUES ('销售部', '1009', '典韦', '2021-11-03', 5000);
INSERT INTO employee VALUES ('销售部', '1010', '张辽', '2021-11-04', 6000);
INSERT INTO employee VALUES ('销售部', '1011', '徐晃', '2021-11-05', 9000);
INSERT INTO employee VALUES ('销售部', '1012', '曹洪', '2021-11-06', 6000);
-- 给每个部门的员工按照薪资排序,并给出排名
SELECT
dname,
ename,
salary,
ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn1
RANK() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn2
DENSE_RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn3
FROM employee;
-- 求出每个部门薪资排在前三名的员工-分组求TOPN
SELECT * FROM(
SELECT
dname,
ename,
salary,
DENSE_RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn
FROM employee
)t
WHERE t.rn <= 3
-- 对所有员工进行全部排序(不分组)
SELECT
dname,
ename,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rn
FROM employee;
开窗聚合函数-SUM,AVG,MIN,MAX
概念:在窗口中每条记录动态的应用聚合函数(SUM()、AVG()、MAX()、MIN()、COUNT()),可以动态计算在指定的窗口内的各种聚合函数值。
例如:
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate) AS c1
FROM employee;
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (ORDER BY hiredate) AS c1
FROM employee;
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c1
FROM employee;
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS c1
FROM employee;
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS c1
FROM employee;
SELECT
dname,
ename,
hiredate,
salary,
SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1
FROM employee;
分布函数-CUME_DIST和PERCENT_RANK
概念:
CUME_DIST
- 用途:分组内小于、等于当前rank值的行数/分组内总行数
- 应用场景:查询小于等于当前薪资(salary)的比例
PERCENT_RANK
- 用途:每行按照公式,(rank-1)/(rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
- 应用场景:不常用
例如:
SELECT
dname,
ename,
salary,
-- 3/12=1/4=0.25
CUME_DIST() OVER (ORDER BY salary) AS rn1,
-- 1/6=0.1667
CUME_DIST() OVER (PARTITION BY dname ORDER BY salary) AS rn2
FROM employee;
SELECT
dname,
ename,
salary,
RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn,
-- 第一行:(1-1)/(6-1)=0 第二行与第一行相同
-- 第三行:(3-1)/(6-1)=0.4
PERCENT_RANK() OVER (PARTITION BY dname ORDER BY salary DESC) AS rn2
FROM employee;
前后函数-LAG和LEAD
概念:
- 用途:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
- 应用场景:查询前1名同学的成绩和当前同学成绩的差值
例如:
SELECT
dname,
ename,
salary,
LAG(hirdate,1,'2000-01-01') OVER (PARTITION BY dname ORDER BY hirdate) AS time1,
LAG(hirdate,2) OVER (PARTITION BY dname ORDER BY hirdate) AS time2
FROM employee;
头尾函数-FIRST_VALUE和LAST_VALUE
概念:
- 用途:返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
- 场景:截止到当前,按照日期排序查询第一个入职和最后一个入职员工的薪资
例如:
SELECT
dname,
ename,
salary,
hiredate,
FIRST_VALUE(salary) OVER (PARTITION BY dname ORDER BY hirdate) AS first,
LAST_VALUE(salary) OVER (PARTITION BY dname ORDER BY hirdate) AS last
FROM employee;
其他函数-NTH_VALUE(expr,n)、NTILE(n)
概念:
NTH_VALUE(expr,n)
- 用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名
- 应用场景:截止到当前薪资,显示每个员工的薪资中排名第二或第三的薪资
NTLE(n)
- 用途:将分区中的有序数据分为n个等级,记录等级数
- 应用场景:将每个部门员工按照入职日期分为3个组
例如:
SELECT
dname,
ename,
salary,
hiredate,
NTH_VALUE(salary,2) OVER (PARTITION BY dname ORDER BY hirdate) AS first_salary,
NTH_VALUE(salary,3) OVER (PARTITION BY dname ORDER BY hirdate) AS second_salary
FROM employee;
SELECT
dname,
ename,
salary,
hiredate,
NTILE(3) OVER (PARTITION BY dname ORDER BY hirdate) AS nt
FROM employee;