PostgreSQL常用函数使用整理

最新推荐文章于 2024-03-07 18:35:42 发布
最新推荐文章于 2024-03-07 18:35:42 发布
阅读量2k 收藏 26
点赞数 2
分类专栏： 笔记 文章标签： postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/LG_L_YM/article/details/112356704
版权

获取当前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);
  1. 与聚合函数一起使用

​ 使用格式: 聚合函数(列) 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
  1. 与排序函数一起使用
--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
DOIT工匠
关注
  • 2
    点赞
  • 踩
  • 26
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
PostgreSQL 10 基础课程第四章 常用函数数学函数使用
11-04
PostgreSQL 10 基础课程第四章 常用函数数学函数使用
regexp_extract用法
jtpython666的博客
11-12 1831
版权声明：本文为CSDN博主「weixin_43383676」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/weixin_43383676/article/details/101020406。函数：regexp_extract(str, regexp[, idx]) ----字符串正则表达式解析函数。idx是返回结果 取表达式的哪一部分 默认值为1。要注意的是idx的数字不能大于表达式中()的个数。
Hive窗口边界ROWS BETWEEN 和 RANGE BETWEEN使用示例
最新发布
qq_36382892的博客
03-07 607
总而言之， ROWS BETWEEN 和 RANGE BETWEEN 提供了不同的窗口边界定义方式， ROWS BETWEEN 基于行数，而 RANGE BETWEEN 基于值范围。根据具体需求和数据分析目的，选择合适的窗口边界定义方式来执行窗口函数操作。
postgreSQL窗口函数总结
01-20
postgreSQL窗口函数总结 1 窗口函数说明 2 row_number/rank/dense_rank的区别 2 窗口函数语句 2 1 准备数据 3 1.1 创建测试的表test1 3 1.2 插入数据到test1表中 3 2 rank over 窗口函数使用 3 2.1 按照分区查看每行的个数 3 2.2 按照分区和排序查看每行的数据 4 2.3 查看每个部门最高的数据 4 3 row_number over 窗口函数的使用 5 3.1 显示数据的行号 5 3.1.1 顺序显示行号 5 3.1.2 获取一段内的数据 5 3.2 显示分区的个数 6 3.3 按照department分组w
PostgreSQL 正则表达式 常用函数的总结
10-19
主要介绍了PostgreSQL 正则表达式 常用函数的总结的相关资料,对那些需要进行复杂数据处理的程序来说，正则表达式无疑是一个非常有用的工具，这里就介绍下如何使用，需要的朋友可以参考下
HIVE获取时间函数, regexp_extract正则提取用法
jlulxg的博客
06-16 5186
Hive获取时间函数 regexp_replace(date_add(from_unixtime(unix_timestamp(), "yyyy-MM-dd") , -1), '-','') regexp_extract函数: regexp_extract( str, regexp[, idx] ) - extracts a group that matches regexp 字符...
Postgresql：REGEXP开头的正则函数用法
m0_71559454的博客
04-21 4968
以字符串‘你好-Hello,123_世界World,456’来介绍下REGEXP_MATCHES()、REGEXP_REPLACE()、REGEXP_SPLIT_TO_ARRAY()、REGEXP_SPLIT_TO_TABLE() 四个函数用法。
sql正则提取固定内容
查数菇乐园
02-20 1万+
regexp_extract 语法: regexp_extract(string subject, string pattern, int index) 返回值: string 说明： 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。 第一参数string subject： 要处理的字段 第二参数string pattern: 需要匹配的正则表达式 第三个参数int index: 0是显示与之匹配的整个字符串 1 是显示第一个括号里面的 2 是
PostgreSQL数组实现多字段拼接、传入多条件查询
tttzzzqqq2018的博客
08-29 662
【代码】PostgreSQL多字段拼接、传入多条件查询。
sql中模糊查询同一个字段的多个条件
qq_50280782的博客
12-22 2992
使用REGEXP关键字 使用|实现or效果 sql语句： SELECT f_sample_code,f_item_code from td_lims_sample_item WHERE f_sample_code REGEXP "TWT202109010024p|T2021-08-10_000057p" and f_item_code in('000133','000060') 运行结果 java参数传递 接口测试结果 ...
【PostgreSQL-9.6.3】extract函数
热门推荐
Just Do IT
07-31 1万+
extract函数格式： extract （field from source） extract函数是从日期或者时间数值里面抽取子域，比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串，是从源数据中的抽取的域。 1. century （世纪） test=# select extract (century from
postgresql 兼容 oracle 函数
05-10
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
最近一个项目用到PostgresSQl数据库，有些不同于oracle数据库，尤其时间处理上，结合PostgreSQL手册以及网上转载些资料，如下，方便开发中用到
guangfei1987的专栏
12-14 1187
PostgreSQL手册学习笔记   最近，项目要从Oracle 迁移到PostgreSQL上（主要针对Java server端查询语句可能遇到问题的一些思考）。 这里没有牵扯到 表结构，存数过程，函数，Job的迁移 早上把PostgreSQL8.0 手册 1 2 部分看了一遍，总结了一些知识点（主要是PostgreSQL自己的特殊的部分和我自己认为重要的部分）： 1.创建一个数据库
ORACLE获取字符串中数字部分
weixin_34390105的博客
04-29 675
select translate('1212中国2323','0123456789'||'1212中国2323','0123456789') from dual; select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual;标签：regexp_replace regexp replace oracle 正则函数的参数说明：一共6个...
PostgreSQL 基本数据类型及常用SQL 函数操作
huang714的专栏
12-08 1799
PostgreSQL 基本数据类型及常用SQL 函数操作
PostgreSQL常用字符串函数和时间函数：
木头大左的博客
06-11 2102
一、逻辑操作符： 常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。二、比较操作符： 下面是PostgreSQL中提供的比较操作符列表： 操作符 描述 < 小于 > 大于 &lt...
PostgreSQL时间函数和操作符
fantasywith的博客
12-18 882
【代码】PostgreSQL时间函数和操作符。
postgresql（pg）数据库regexp_replace函数的使用
qq_42213727的博客
03-22 7814
例如： 使用空字符替换：select regexp_replace('你好hello世界abc', '[A-Za-z]+',''),* from A 输出结果："你好世界abc" 注意：没加参数，默认只会替换第一正则表达式出现的字符串使用空字符替换：select regexp_replace('你好hello世界abc', '[A-Za-z]+','','g'),* from A 输出结果："你好世界" 注意：第4个参数为g,代表替换所有匹配到的字符串 ...
postgresql 常用小语法
杰姆小生的博客
02-21 306
值判断CASE name WHEN '' THEN '-' ELSE name END name为空则返回-，否则返回name 替换nullCOALESCE(name, '0') name为null时用0代替 格式化日期为时间戳EXTRACT(epoch FROM CAST(logged_at AS TIMESTAMP)) 格式化logged_at为时间戳 占位LPAD(id::VARCHAR,30,'0') id不足30位的左边补0RPAD(id::VARCHAR..
postgresql常用函数
09-13
PostgreSQL常用函数包括字符串函数和聚合函数。在字符串函数方面，有一个常用的函数是"||"，它可以用于字符串的连接，即将两个字符串连接在一起。在正则表达式方面，PostgreSQL也提供了一些常用的函数用于复杂数据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值