PostgreSQL常用函数使用整理
程序员文章站
2024-03-21 10:56:46
...
获取当前date
--结果:2020-12-28
select current_date;
获取当前timestamp
--结果:2020-12-31 03:38:17.598313
select current_timestamp;
--结果:2020-12-31 03:38:17.598313
select now();
data和timestamp的相互转换
--结果:2020-12-31
select current_timestamp::date;
--结果:2020-12-31
select now()::date;
--结果:2020-12-31 00:00:00.000000
select current_date::timestamp;
--结果:2020-12-31 00:00:00
select current_date::timestamp(0);
date和字符串之间的转换
--结果:2020-12-31
select date('2020-12-31 09:12:23.12345');
select date('2020-12-31 09:12:23');
select date('2020-12-31');
--结果:2020/12/31,类型text
select to_char(current_date, 'yyyy/MM/dd');
--结果:2020-12-31,类型varchar
select current_date::varchar;
--结果:2020-12-31,类型text
select current_date::text;
timestamp和字符串之间的转换
--保留到秒数,结果:2020-12-31 09:12:23.000000
select to_timestamp('2020-12-31 09:12:23.123456', 'yyyy-MM-dd hh24:mi:ss');
--保留到毫秒数,结果:2020-12-31 09:12:23.123000
select to_timestamp('2020-12-31 09:12:23.123', 'yyyy-MM-dd hh24:mi:ss.ms');
--保留到微妙数,结果:2020-12-31 09:12:23.123456
select to_timestamp('2020-12-31 09:12:23.123456', 'yyyy-MM-dd hh24:mi:ss.us');
--简约写法，结果：2020-12-31 09:12:23.123456
select '2020-12-31 09:12:23.123456'::timestamp;
--保留到秒数，结果：2020-12-31 03:49:08
select to_char(current_timestamp, 'yyyy-MM-dd hh24:mi:ss');
--保留到毫秒数,结果:2020-12-31 03:49:18.932
select to_char(current_timestamp, 'yyyy-MM-dd hh24:mi:ss.ms');
--保留到微妙数,结果:2020-12-31 03:50:31.441009
select to_char(current_timestamp, 'yyyy-MM-dd hh24:mi:ss.us');
获取时间戳
--结果:1609391312
select extract(epoch from current_timestamp(0));
--结果:1609391355.618436
select extract(epoch from current_timestamp);
--时间戳运算,结果:122.922817
select extract(epoch from current_timestamp - '2020-12-31 14:06:00'::timestamp);
--结果:18
select extract(epoch from current_timestamp(0) - '2020-12-31 14:09:00'::timestamp(0));
日期运算
--获取年份,类型numeric
select extract(year from current_date);
select extract(year from current_timestamp);
--结果:2020,类型text
select extract(year from current_date)::text;
select extract(year from current_timestamp)::text;
--获取月份,类型numeric
select extract(month from current_date);
select extract(month from current_timestamp);
--结果:12,类型text
select extract(month from current_date)::text;
select extract(month from current_timestamp)::text;
--获取当天,类型numeric
select extract(day from current_date);
select extract(day from current_timestamp);
--结果:31,类型text
select extract(day from current_date)::text;
select extract(day from current_timestamp)::text;
--引申,获取每周的星期号，星期天(0)到星期六(6)
select extract(dow from timestamp '2020-12-31 12:23:12.234123');
/*
extract(field FROM source)函数从日期/时间数值里抽取子域，比如年、小时等。 source必须是一个timestamp, time, interval类型的值表达式(类型为date的表达式转换为 timestamp，因此也可以用)。field 是一个标识符或者字符串，它指定从源数据中抽取的域。extract 函数返回类型为double precision的数值。
*/
--当前日期加一天,结果:2021-01-01,类型:date
select current_date + 1;
--结果:2021-01-01 00:00:00.000000,类型:date
select current_date + interval '1 day';
--当前日期减一天,结果:2020-12-30,类型:date
select current_date - 1;
--结果:2020-12-30 00:00:00.000000,类型:date
select current_date + interval '-1 day';
select current_date - interval '1 day';
--当前日期加一年,结果:2021-12-31 00:00:00.000000,类型:date
select current_date + interval '1 year';
--当前日期减一年,结果:2019-12-31 00:00:00.000000,类型date
select current_date + interval '-1 year';
select current_date - interval '1 year';
--当前日期加一月,结果:2021-01-31 00:00:00.000000,类型date
select current_date + interval '1 month';
--当前日期减一月,结果:2020-11-30 00:00:00.000000,类型:date
select current_date + interval '-1 month';
select current_date - interval '1 month';
/*
备注: current_timestamp也可以进行加减运算且类型为timestamp;
*/
日期截取date_trunc(‘field’, source)函数
--结果:2020-01-01 00:00:00.000000,类型timestamp
select date_trunc('year', current_timestamp);
--结果:2020-12-01 00:00:00.000000,类型timestamp
select date_trunc('month', current_timestamp);
--结果:2020-12-13 00:00:00.000000,类型timestamp
select date_trunc('day', timestamp '2020-12-13 12:23:24.123456');
--结果:2020-12-13 16:00:00.000000,类型timestamp
select date_trunc('hour', timestamp '2020-12-13 16:23:24.123456');
select date_trunc('h', timestamp '2020-12-13 16:23:24.123456');
--结果:2020-12-13 16:23:00.000000,类型timestamp
select date_trunc('minute', timestamp '2020-12-13 16:23:24.123456');
select date_trunc('m', timestamp '2020-12-13 16:23:24.123456');
--结果:2020-12-13 16:23:24.000000,类型timestamp
select date_trunc('second', timestamp '2020-12-13 16:23:24.123456');
select date_trunc('s', timestamp '2020-12-13 16:23:24.123456');
--结果:2020-12-13 16:23:24.123000,类型timestamp
select date_trunc('milliseconds', timestamp '2020-12-13 16:23:24.123456');
select date_trunc('ms', timestamp '2020-12-13 16:23:24.123456');
字符串操作常用函数
--字符串位bit_length(string)
--结果:8
select bit_length('s');
--结果:24
select bit_length('中');
--字符串中字符个数char_length(string)
--结果:6
select char_length('string');
--把字符串转化为小写lower(string)
--结果:string
select lower('STRING');
--把字符串转化为大写upper(string)
--结果:STRING
select upper('string');
--替换子串overlay(string placing string from int [for int]),第一个int表示从第几个字符开始,后面一个表示共替换多少个字符
--结果:hello world
select overlay('hxxxx world' placing 'ello' from 2 for 4)
--字符串截取substring(string [from int] [for int]或substring(string from pattern)或substring(string from pattern for escape)
--结果:ello,从第二位开始截取后面4个字符串
select substring('Hello World' from 2 for 4);
select substring('Hello World', 2, 4);
--结果:mas,截取末尾3个字符
select substring('Thomas' from '...$');
--结果:trin,表示获取从t到n的所有字符串
select substring('string' from '%#"t%n#"%' for '#');
--结果:Tomxxxx,表示截掉开头的x字符串
select trim(leading 'x' from 'xxxxTomxxxx');
--结果:xxxxTom,表示截掉末尾的x字符串
select trim(trailing 'x' from 'xxxxTomxxxx');
--结果:Tom,表示截掉前后的x字符串
select trim(both 'x' from 'xxxxTomxxxx');
--连接所有参数NULL参数被忽略。concat(str "any" [, str "any" [, ...]])
--结果:abcde222
select concat('abcde', 2, NULL, 22);
--连接所有参数，但是第一个参数是分隔符，用于将所有参数分隔。NULL 参数被忽略。concat_ws(sep text, str "any" [, str "any" [, ...] ])
--结果:abcde,2,22
select concat_ws(',', 'abcde', 2, NULL, 22);
--string中字符的数目,length(string)
--结果:3
select length('abc');
--返回字符串的前n个字符。当n是负数时，返回除最后|n|个字符以外的所有字符。left(str text, n int)
--结果:hell
select left('hello world', 4);
--返回字符串的后n个字符。当n是负数时，返回除前面|n|个字符以外的所有字符。right(str text, n int)
--结果:orld
select right('hello world', 4);
--用POSIX正则表达式作为分隔符，分隔字符串,regexp_split_to_table(string text, pattern text [, flags text])
--结果:值为1 2 3 4 5的一列数据
select regexp_split_to_table('1,2,3,4,5', ',');
--把字符串string里出现地所有子字符串from 替换成子字符串to,replace(string text, from text, to text)
--结果:abXXefabXXef
select replace('abcdefabcdef', 'cd', 'XX');
模糊匹配
| 操作符 | 描述 | 例子 |
|---|---|---|
| ~ | 匹配正则表达式，大小写相关 | 'thomas' ~ '.*thomas.*' |
| ~* | 匹配正则表达式，大小写无关 | 'thomas' ~* '.*Thomas.*' |
| !~ | 不匹配正则表达式，大小写相关 | 'thomas' !~ '.*Thomas.*' |
| !~* | 不匹配正则表达式，大小写无关 | 'thomas' !~* '.*vadim.*' |
替换null值
--COALESCE(value [, ...]),返回它的第一个非 NULL 的参数值。如果所有参数都是null那么返回null
--结果:str1
select coalesce(null, 'str1', 'str2');
--结果:null
select coalesce(null, null);
--NULLIF(value1, value2),当且仅当value1等于value2时，NULLIF才返回null。否则它返回value1
--结果:str1
SELECT NULLIF('str1', 'str');
--结果:null
SELECT NULLIF('str', 'str');
uuid函数(高版本支持)
--结果: 966c5263-3d9b-4ec1-8a80-72ea3eec9b7e
select gen_random_uuid();
分析函数和开窗函数(over()函数)
分析函数它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。分析函数用于计算基于组的某种聚合值。许多分析函数同时也是聚合函数。分析函数有时候被称为窗口函数。分析函数以一定的方法在一个与当前行相关的结果集子集中进行计算。这个子集可以被称为窗口。
分析函数和聚合函数不同之处：分析函数和聚合函数很多是同名的，意思也一样(例如sum(), max()等即是分析函数又是聚合函数,只是作为分析函数时,必须和开窗函数over()函数一起使用)，只是聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。简单的说就是聚合函数返回统计结果，分析函数返回明细加统计结果。
使用格式： 函数名(列) OVER(选项)
分析函数带有一个开窗函数over()
开窗函数over()
开窗函数(over())包含三个分析子句：
分组子句(partition by)
排序子句(order by)
窗口子句(rows 方式、range方式、滑动窗口)
开窗函数里的"窗"，即"窗口"，表示分析函数分析时要处理的数据范围
比如分析函数sum()，它所针对的数据范围为窗口中的记录，而不是整个表的记录
要获取某个范围的sum值，则需在窗口指定具体的数据范围
比如指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和
窗口的大小是由Over 的Partition By子句界定; 窗口滑动的顺序是由Over的Order by子句指定;
rows方式和range方式的区别:
rows是物理行，就是按行的位置，根据位置计算窗口范围
range是逻辑行，是按单元格值和偏移量计算窗口范围
排序子句(order by)
排序子句的使用方法跟sql中的order by一样，如：order by colA desc, colB asc nulls first, colC nulls last
开窗函数的order by和sql语句的order by的执行时机
分析及开窗函数是在整个sql查询结束后再进行的, 即sql语句的order by也会影响分析函数的执行结果，有以下两种情况：
1) 两者一致，即sql语句中的order by语句与开窗函数的order by一致，则sql语句中的order by先执行,分析函数在分析时就不必再排序
2) 两者不一致，即sql语句中的order by语句与开窗函数的order by不一致，则分析及开窗函数先分析排序，sql语句中的order by再最后执行
窗口子句(rows)
如果没有窗口子句(rows),则默认当前组的第一行到当前行
无论是否省略分组子句，都有：
窗口子句(rows)不能单独存在，必须有order by子句时才能出现
相反，有order by子句，可以没有窗口子句(rows)
当省略窗口子句时
如果存在order by，则默认的窗口是unbounded preceding and current row，即当前组的第一行到当前行
如果不存在order by，则默认的窗口是unbounded preceding and unbounded following，即整个组
例如：lag(sal) over(order by sal)
over(order by salary)表示意义如下：
1) 由于省略分组子句，所以当前组的范围为整个表的数据行
2) 在当前组(此时为整个表的数据行)这个范围里执行排序，即order by salary
3) 分析函数lag(sal)在当前组(此时为整个表的数据行)这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行
窗口子句(rows)的相关关键字
preceding：表示在…之前
1 preceding：表示当前记录的前1条记录
2 preceding：表示当前记录的前2条记录
n preceding：表示当前记录的前n条记录
unbounded preceding：不受控制的，无限的,
若无分组，则表示所有记录的第1条记录
若有分组，则表示分组后，组内的第1条记录
following：表示在…之后
1 following：表示当前记录的后一条记录
2 following：表示当前记录的后两条记录
n following：表示当前记录的后n条记录
unbounded following：不受控制的，无限的,
若无分组，则表示所有记录的最后一条记录
若有分组，则表示分组后，组内的最后一条记录
相关用例：
rows between unbounded preceding and unbounded following：针对所有记录
rows between unbounded preceding and current row：针对第一条记录到当前记录
rows between current row and unbounded following：针对当前记录到最后一条记录
rows between 1 preceding and current row：针对当前记录的上一行记录到当前行记录
rows between current row and 3 following：针对当前记录到当前行记录的后三条记录
rows between 1 preceding and 2 following：针对当前记录的上一条记录 ~~ 当前行记录的后两条记录
当开窗函数over()出现分组(partition by)子句时，unbounded preceding即表中一个分组里的第一行，unbounded following即表中一个分组里的最后一行
当开窗函数over()省略了分组(partition by)子句时，unbounded preceding即表中的第一行， unbounded following即表中的最后一行
函数名（ [ 参数 ] ） over（ [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ]）
| 函数名 | 返回类型 | 描述 |
|---|---|---|
| row_number() | bigint | 在其分区中的当前行号，从1计 |
| rank() | bigint | 将数据行值按照排序后的顺序进行排名, 在有并列的情况下排名值将被跳跃。即跳跃排序，有两个第二名时接下来就是第四名 |
| dense_rank() | bigint | 将数据行值按照排序后的顺序进行排名, 在有并列的情况下也不跳过排名值。即连续排序，有两个第二名时仍然跟着第三名 |
| lag() | 访问一个分区或结果集中之前的一行 | |
| lead() | 访问一个分区或结果集中之后的一行 | |
| first_value() | 访问一个分区或结果集中第一行 | |
| last_value() | 访问一个分区或结果集中最后一行 | |
| nth_value() | 访问一个分区或结果集中的任意一行 | |
| ratio_to_report() | 计算报告中值得比例 | |
| percent_rank() | 将计算得到的排名值标准化为0到1之间的值 | |
| percentile_cont() | 取出与指定的排名百分比相匹配的值, 是percent_rank的反函数 | |
| ntile() | 将数据行分组为单元 | |
| listagg() | 将来自不同行的列转化为列表格式 | |
| array_agg() | 列转行 |
准备数据：
create table employee_test(
id int4 not null primary key,
emp_name varchar(10),
group_name varchar(20),
salary numeric(6, 2)
);
insert into employee_test(id, emp_name, group_name, salary)
values (1, '小郑', '开发部', 1000.01),
(2, '小明', '开发部', 1000.02),
(3, '小白', '开发部', 1000.03),
(4, '小王', '采购部', 1000.04),
(5, '小李', '财务部', 1000.05),
(6, '小东', '采购部', 1000.06),
(7, '小南', '财务部', 1000.07);
- 与聚合函数一起使用
使用格式: 聚合函数(列) over(选项), 其中选项可以是如下等
(partition by 列)子句
(order by 列)子句
(partition by 列 order by 列)子句
(partition by 列 order by 列 range between n preceding and m following)
(partition by 列 order by 列 rows between n preceding and m following)
--A (partition by 列)子句: 意思是按照其后面的列来进行分区(分组)后聚合
select *, max(salary) over(partition by group_name) from employee_test;
--结果
id emp_name group_name salary max
5 小李 财务部 1000.05 1000.07
7 小南 财务部 1000.07 1000.07
4 小王 采购部 1000.04 1000.06
6 小东 采购部 1000.06 1000.06
3 小白 开发部 1000.03 1000.03
2 小明 开发部 1000.02 1000.03
1 小郑 开发部 1000.01 1000.03
--B (order by 列)子句: 开窗函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行;没有ORDER BY时,默认的窗口是全部的分区
select *, max(salary) over(order by salary asc) from employee_test;
id emp_name group_name salary max
1 小郑 开发部 1000.01 1000.01
2 小明 开发部 1000.02 1000.02
3 小白 开发部 1000.03 1000.03
4 小王 采购部 1000.04 1000.04
5 小李 财务部 1000.05 1000.05
6 小东 采购部 1000.06 1000.06
7 小南 财务部 1000.07 1000.07
--与前面对比理解
select *, max(salary) over(order by salary desc) from employee_test;
id emp_name group_name salary max
7 小南 财务部 1000.07 1000.07
6 小东 采购部 1000.06 1000.07
5 小李 财务部 1000.05 1000.07
4 小王 采购部 1000.04 1000.07
3 小白 开发部 1000.03 1000.07
2 小明 开发部 1000.02 1000.07
1 小郑 开发部 1000.01 1000.07
--C (partition by 列 order by 列)子句
select *, max(salary) over(partition by group_name order by salary asc) from employee_test;
--partition by group_name意思就是按照group_name进行分区
--order by salary asc表示在分区里将当前行的salary与前面所有行的salary进行对比求出聚合函数的值
id emp_name group_name salary max
5 小李 财务部 1000.05 1000.05
7 小南 财务部 1000.07 1000.07
4 小王 采购部 1000.04 1000.04
6 小东 采购部 1000.06 1000.06
1 小郑 开发部 1000.01 1000.01
2 小明 开发部 1000.02 1000.02
3 小白 开发部 1000.03 1000.03
--对比理解
select *, max(salary) over(partition by group_name order by salary desc) from employee_test;
id emp_name group_name salary max
7 小南 财务部 1000.07 1000.07
5 小李 财务部 1000.05 1000.07
6 小东 采购部 1000.06 1000.06
4 小王 采购部 1000.04 1000.06
3 小白 开发部 1000.03 1000.03
2 小明 开发部 1000.02 1000.03
1 小郑 开发部 1000.01 1000.03
--扩展例子
select *, sum(salary) over(partition by group_name order by salary asc) from employee_test;
id emp_name group_name salary sum
5 小李 财务部 1000.05 1000.05
7 小南 财务部 1000.07 2000.12
4 小王 采购部 1000.04 1000.04
6 小东 采购部 1000.06 2000.1
1 小郑 开发部 1000.01 1000.01
2 小明 开发部 1000.02 2000.03
3 小白 开发部 1000.03 3000.06
--扩展对比例子
select *, sum(salary) over(partition by group_name order by salary desc) from employee_test;
id emp_name group_name salary sum
7 小南 财务部 1000.07 1000.07
5 小李 财务部 1000.05 2000.12
6 小东 采购部 1000.06 1000.06
4 小王 采购部 1000.04 2000.1
3 小白 开发部 1000.03 1000.03
2 小明 开发部 1000.02 2000.05
1 小郑 开发部 1000.01 3000.06
--添加数据
INSERT INTO public.employee_test (id, emp_name, group_name, salary) VALUES (8, '小西', '开发部', 1000.08);
INSERT INTO public.employee_test (id, emp_name, group_name, salary) VALUES (9, '小北', '开发部', 1000.08);
--D (partition by 列 order by 列 rows between n preceding and m following)子句
--rows: 使用固定的行数来限制分区中的数据行数量
--n preceding: 表示从分区中前面n行开始
--m following: 表示从分区中后面m行结束
select *, sum(salary) over(partition by group_name order by salary asc rows between 0 preceding and 1 following) from employee_test;
id emp_name group_name salary sum
5 小李 财务部 1000.05 2000.12
7 小南 财务部 1000.07 1000.07
4 小王 采购部 1000.04 2000.1
6 小东 采购部 1000.06 1000.06
1 小郑 开发部 1000.01 2000.03
2 小明 开发部 1000.02 2000.05
3 小白 开发部 1000.03 2000.11
8 小西 开发部 1000.08 2000.16
9 小北 开发部 1000.08 1000.08
--等效写法: select *, sum(salary) over(partition by group_name order by salary asc rows between current row and 1 following) from employee_test;
select *, sum(salary) over(partition by group_name order by salary asc rows between 1 preceding and 2 following) from employee_test;
id emp_name group_name salary sum
5 小李 财务部 1000.05 2000.12
7 小南 财务部 1000.07 2000.12
4 小王 采购部 1000.04 2000.1
6 小东 采购部 1000.06 2000.1
1 小郑 开发部 1000.01 3000.06
2 小明 开发部 1000.02 4000.14
3 小白 开发部 1000.03 4000.21
8 小西 开发部 1000.08 3000.19
9 小北 开发部 1000.08 2000.16
--添加数据
INSERT INTO public.employee_test (id, emp_name, group_name, salary) VALUES (10, '小中', '开发部', 1000.03);
INSERT INTO public.employee_test (id, emp_name, group_name, salary) VALUES (11, '小冬', '开发部', 1000.04);
--E (partition by 列 order by 列 range between n preceding and m following)子句
--range: 是逻辑行的范围,要经过计算,一般range后面是数值或时间间隔等,这样根据当前行和range的表达式就能就算当前行对应的窗口范围
--n preceding: 表示从分区中最小比当前行的值小n
--m following: 表示从分区中最大比当前行的值大m
select *, sum(salary) over(partition by group_name order by salary asc range between 0.01 preceding and 0.02 following) from employee_test;
--range between 0.01 preceding and 0.02 following的意思就是最小比当前行的salary小0.01, 最大比当前行的salary大0.02的范围
id emp_name group_name salary sum
5 小李 财务部 1000.05 2000.12
7 小南 财务部 1000.07 1000.07
4 小王 采购部 1000.04 2000.1
6 小东 采购部 1000.06 1000.06
1 小郑 开发部 1000.01 4000.09
2 小明 开发部 1000.02 5000.13
3 小白 开发部 1000.03 4000.12
10 小中 开发部 1000.03 4000.12
11 小冬 开发部 1000.04 3000.1
8 小西 开发部 1000.08 2000.16
9 小北 开发部 1000.08 2000.16
select *, sum(salary) over(partition by group_name order by salary asc range between current row and 0.02 following) from employee_test;
--range between current row and 0.02 following的意思是当前行到比当前行最大大0.02的范围
id emp_name group_name salary sum
5 小李 财务部 1000.05 2000.12
7 小南 财务部 1000.07 1000.07
4 小王 采购部 1000.04 2000.1
6 小东 采购部 1000.06 1000.06
1 小郑 开发部 1000.01 4000.09
2 小明 开发部 1000.02 4000.12
3 小白 开发部 1000.03 3000.1
10 小中 开发部 1000.03 3000.1
11 小冬 开发部 1000.04 1000.04
8 小西 开发部 1000.08 2000.16
9 小北 开发部 1000.08 2000.16
- 与排序函数一起使用
--row_number() over(): 在其分区中的当前行号，从1计
select *, row_number() over(partition by group_name order by salary asc) from employee_test;
id emp_name group_name salary row_number
5 小李 财务部 1000.05 1
7 小南 财务部 1000.07 2
4 小王 采购部 1000.04 1
6 小东 采购部 1000.06 2
1 小郑 开发部 1000.01 1
2 小明 开发部 1000.02 2
3 小白 开发部 1000.03 3
10 小中 开发部 1000.03 4
11 小冬 开发部 1000.04 5
8 小西 开发部 1000.08 6
9 小北 开发部 1000.08 7
--对比
select *, row_number() over(partition by group_name order by salary desc) from employee_test;
id emp_name group_name salary row_number
7 小南 财务部 1000.07 1
5 小李 财务部 1000.05 2
6 小东 采购部 1000.06 1
4 小王 采购部 1000.04 2
9 小北 开发部 1000.08 1
8 小西 开发部 1000.08 2
11 小冬 开发部 1000.04 3
3 小白 开发部 1000.03 4
10 小中 开发部 1000.03 5
2 小明 开发部 1000.02 6
1 小郑 开发部 1000.01 7
--rank() over(): 跳跃排序，有两个第二名时接下来就是第四名
select *, rank() over(partition by group_name order by salary asc) from employee_test;
id emp_name group_name salary rank
5 小李 财务部 1000.05 1
7 小南 财务部 1000.07 2
4 小王 采购部 1000.04 1
6 小东 采购部 1000.06 2
1 小郑 开发部 1000.01 1
2 小明 开发部 1000.02 2
3 小白 开发部 1000.03 3
10 小中 开发部 1000.03 3
11 小冬 开发部 1000.04 5
8 小西 开发部 1000.08 6
9 小北 开发部 1000.08 6
--对比
select *, rank() over(partition by group_name order by salary desc) from employee_test;
id emp_name group_name salary rank
7 小南 财务部 1000.07 1
5 小李 财务部 1000.05 2
6 小东 采购部 1000.06 1
4 小王 采购部 1000.04 2
9 小北 开发部 1000.08 1
8 小西 开发部 1000.08 1
11 小冬 开发部 1000.04 3
3 小白 开发部 1000.03 4
10 小中 开发部 1000.03 4
2 小明 开发部 1000.02 6
1 小郑 开发部 1000.01 7
--dense_rank() over(): 连续排序，有两个第二名时仍然跟着第三名
select *, dense_rank() over(partition by group_name order by salary asc) from employee_test;
id emp_name group_name salary dense_rank
5 小李 财务部 1000.05 1
7 小南 财务部 1000.07 2
4 小王 采购部 1000.04 1
6 小东 采购部 1000.06 2
1 小郑 开发部 1000.01 1
2 小明 开发部 1000.02 2
3 小白 开发部 1000.03 3
10 小中 开发部 1000.03 3
11 小冬 开发部 1000.04 4
8 小西 开发部 1000.08 5
9 小北 开发部 1000.08 5
--对比
select *, dense_rank() over(partition by group_name order by salary desc) from employee_test;
id emp_name group_name salary dense_rank
7 小南 财务部 1000.07 1
5 小李 财务部 1000.05 2
6 小东 采购部 1000.06 1
4 小王 采购部 1000.04 2
9 小北 开发部 1000.08 1
8 小西 开发部 1000.08 1
11 小冬 开发部 1000.04 2
3 小白 开发部 1000.03 3
10 小中 开发部 1000.03 3
2 小明 开发部 1000.02 4
1 小郑 开发部 1000.01 5
上一篇： MySQL高可用MHA配置与故障切换
下一篇： LSTM输入结构