openGauss / 磐维数据库分析函数实用示例
本文主要介绍了 openGauss / 磐维数据库中分析函数的使用，涵盖聚合函数、排名函数、窗口函数、文本聚合函数、行列转换等多种函数，希望能对大家有所帮助。
本文主要介绍了 openGauss / 磐维数据库中分析函数的使用，涵盖聚合函数、排名函数、窗口函数、文本聚合函数、行列转换等多种函数，希望能对大家有所帮助。
下面举例说明：
聚合函数
示例1、查询特定部门（10和20）的员工信息，并分析特定部门（10和20）内每个人的薪水和部门平均薪水的情况：
postgres=# SELECT deptno, empno, sal, round(avg(sal) OVER (PARTITION BY deptno),2) as "avg_sal" FROM emp where deptno in (10,20);
deptno | empno | sal | avg_sal
--------+-------+---------+---------
10 | 7934 | 1300.00 | 2916.67
10 | 7782 | 2450.00 | 2916.67
10 | 7839 | 5000.00 | 2916.67
20 | 7788 | 3000.00 | 2175.00
20 | 7369 | 800.00 | 2175.00
20 | 7876 | 1100.00 | 2175.00
20 | 7566 | 2975.00 | 2175.00
20 | 7902 | 3000.00 | 2175.00
(8 rows)示例2、查询特定部门（10和20）的员工信息，并分析两个部门内所有员工的总薪资、按部门分区的薪水总和、部门内按员工编号排序的累加薪水：
postgres=# select ename,empno,sal,deptno,
postgres-# sum(sal) over() total_sal,
postgres-# sum(sal) over(partition by deptno) dept_sal,
postgres-# sum(sal) over(partition by deptno order by empno) add_sal
postgres-# from emp
postgres-# where deptno in (10,20);
ename | empno | sal | deptno | total_sal | dept_sal | add_sal
--------+-------+---------+--------+-----------+----------+----------
CLARK | 7782 | 2450.00 | 10 | 19625.00 | 8750.00 | 2450.00
KING | 7839 | 5000.00 | 10 | 19625.00 | 8750.00 | 7450.00
MILLER | 7934 | 1300.00 | 10 | 19625.00 | 8750.00 | 8750.00
SMITH | 7369 | 800.00 | 20 | 19625.00 | 10875.00 | 800.00
JONES | 7566 | 2975.00 | 20 | 19625.00 | 10875.00 | 3775.00
SCOTT | 7788 | 3000.00 | 20 | 19625.00 | 10875.00 | 6775.00
ADAMS | 7876 | 1100.00 | 20 | 19625.00 | 10875.00 | 7875.00
FORD | 7902 | 3000.00 | 20 | 19625.00 | 10875.00 | 10875.00
(8 rows)排名函数
示例3、查询特定部门（10和20）的员工信息，并分析两个部门内所有员工的薪资排名情况。
函数使用说明：
row_number() 为每个员工分配一个唯一行号（从 1 开始递增）。
rank（）为员工分配非连续的排名。
dense_rank() 为员工分配连续的排名。
postgres=# select ename,empno,sal,deptno,
postgres-# row_number() over(order by sal) as rn,
postgres-# rank() over(order by sal) as rank,
postgres-# dense_rank() over(order by sal) as dense_rank
postgres-# from emp
postgres-# where deptno in (10,20);
ename | empno | sal | deptno | rn | rank | dense_rank
--------+-------+---------+--------+----+------+------------
SMITH | 7369 | 800.00 | 20 | 1 | 1 | 1
ADAMS | 7876 | 1100.00 | 20 | 2 | 2 | 2
MILLER | 7934 | 1300.00 | 10 | 3 | 3 | 3
CLARK | 7782 | 2450.00 | 10 | 4 | 4 | 4
JONES | 7566 | 2975.00 | 20 | 5 | 5 | 5
SCOTT | 7788 | 3000.00 | 20 | 6 | 6 | 6
FORD | 7902 | 3000.00 | 20 | 7 | 6 | 6
KING | 7839 | 5000.00 | 10 | 8 | 8 | 7
(8 rows)
窗口函数
示例4、查询特定部门（10和20）的员工信息，并利用窗口函数获取每个员工在部门内按员工编号排序后的上下行工资值、所在部门第一个员工的工资值以及所在部门最后一个员工的工资值。
函数使用说明：
lead(sal)显示当前员工下一个员工的工资（按empno排序）。
lag(sal)显示当前员工前一个员工的工资（按empno排序）。
first_value(sal)显示部门内第一个员工的工资（按empno排序）。
last_value(sal)显示部门内最后一个员工的工资（按empno排序）。
postgres=# select ename,empno,sal,deptno,
postgres-# lead(sal) over(partition by deptno order by empno) as lead,
postgres-# lag(sal) over(partition by deptno order by empno) as lag,
postgres-# first_value(sal) over(partition by deptno order by empno) as first_value,
postgres-# last_value(sal) over(partition by deptno order by empno rows between unbounded preceding and unbounded following) as last_value
postgres-# from emp
postgres-# where deptno in (10,20);
ename | empno | sal | deptno | lead | lag | first_value | last_value
--------+-------+---------+--------+---------+---------+-------------+------------
CLARK | 7782 | 2450.00 | 10 | 5000.00 | | 2450.00 | 1300.00
KING | 7839 | 5000.00 | 10 | 1300.00 | 2450.00 | 2450.00 | 1300.00
MILLER | 7934 | 1300.00 | 10 | | 5000.00 | 2450.00 | 1300.00
SMITH | 7369 | 800.00 | 20 | 2975.00 | | 800.00 | 3000.00
JONES | 7566 | 2975.00 | 20 | 3000.00 | 800.00 | 800.00 | 3000.00
SCOTT | 7788 | 3000.00 | 20 | 1100.00 | 2975.00 | 800.00 | 3000.00
ADAMS | 7876 | 1100.00 | 20 | 3000.00 | 3000.00 | 800.00 | 3000.00
FORD | 7902 | 3000.00 | 20 | | 1100.00 | 800.00 | 3000.00
(8 rows)
postgres=# keep函数
注意：只能在oracle兼容模式下使用。
示例5：从emp表中筛选出部门编号为 10 和 20 的员工中工资（sal）最低的员工姓名（first）和工资最高的员工姓名（last）。
函数使用说明：
DENSE_RANK FIRST ORDER BY sal：按工资（sal）升序排序（默认），取排名第一（即工资最低）的记录。
DENSE_RANK LAST ORDER BY sal：按工资升序排序，取排名最后（即工资最高）的记录。
KEEP：保留这些工资最低、最高的记录。
postgres=> SELECTmax(ename) keep(dense_rankfirstorderby sal) asfirst,
postgres-> min(ename) keep(dense_ranklastorderby sal) aslast
postgres-> FROM emp
postgres-> where deptno in (10,20);
first | last
-------+------
SMITH | KING
(1 row)文本聚合函数
示例6：将部门（10和20）的员工姓名按部门分组，并通过两种不同的字符串聚合函数合并成字符串，主要目的是对比 wm_concat 和 listagg 函数的行为差异。
函数使用说明：
wm_concat(ename)：将部门内所有员工姓名合并为一个字符串，默认无明确排序（依赖数据存储顺序）。
listagg(ename, ‘,’) WITHIN GROUP (ORDER BY ename)：将部门内所有员工姓名按字母顺序排序后合并为字符串。
postgres=# select wm_concat(ename) over (partition by deptno) enames1,
postgres-# listagg(ename,',') within group(order by ename) over (partition by deptno) enames2
postgres-# from emp where deptno in (10,20);
enames1 | enames2
------------------------------+------------------------------
MILLER,CLARK,KING | CLARK,KING,MILLER
MILLER,CLARK,KING | CLARK,KING,MILLER
MILLER,CLARK,KING | CLARK,KING,MILLER
SCOTT,SMITH,ADAMS,JONES,FORD | ADAMS,FORD,JONES,SCOTT,SMITH
SCOTT,SMITH,ADAMS,JONES,FORD | ADAMS,FORD,JONES,SCOTT,SMITH
SCOTT,SMITH,ADAMS,JONES,FORD | ADAMS,FORD,JONES,SCOTT,SMITH
SCOTT,SMITH,ADAMS,JONES,FORD | ADAMS,FORD,JONES,SCOTT,SMITH
SCOTT,SMITH,ADAMS,JONES,FORD | ADAMS,FORD,JONES,SCOTT,SMITH
(8 rows)
示例7：将部门（10和20）的员工姓名按部门分组，并通过两种不同的字符串聚合函数合并成字符串，主要目的是对比 wm_concat 和 string_agg 函数的行为差异。
函数使用说明：
string_agg(ename, ‘,’)：将部门内所有员工姓名合并为逗号分隔的字符串，默认无显式排序。
postgres=# select wm_concat(ename) enames1, string_agg(ename,',') enames2
postgres-# from emp where deptno in (10,20)
postgres-# group by deptno;
enames1 | enames2
------------------------------+------------------------------
CLARK,KING,MILLER | CLARK,KING,MILLER
SMITH,JONES,SCOTT,ADAMS,FORD | SMITH,JONES,SCOTT,ADAMS,FORD
(2 rows)递归函数
示例8：构建基于组织结构（在这里是员工的管理关系）的层次树。通过递归地查找员工及其下属，展示员工之间的上下级关系。
注意：只能在oracle兼容模式下使用。
postgres=> selectlevelas lv,
postgres-> connect_by_root(empno) as root_no,
postgres-> ltrim(sys_connect_by_path(ename, '->'), '->') as ename_path,
postgres-> decode(level, 1, 1) as root_node,
postgres-> connect_by_isleafas isleaf,
postgres-> empno,
postgres-> ename,
postgres-> prior ename as prior_name
postgres-> from emp
postgres-> startwith mgr = 7839
postgres-> connectbynocycle mgr = (prior empno)
postgres-> ordersiblingsby empno;
lv | root_no | ename_path | root_node | isleaf | empno | ename | prior_name
----+---------+---------------------+-----------+--------+-------+--------+------------
1 | 7566 | JONES | 1 | 0 | 7566 | JONES |
2 | 7566 | JONES->SCOTT | | 0 | 7788 | SCOTT | JONES
3 | 7566 | JONES->SCOTT->ADAMS | | 1 | 7876 | ADAMS | SCOTT
2 | 7566 | JONES->FORD | | 0 | 7902 | FORD | JONES
3 | 7566 | JONES->FORD->SMITH | | 1 | 7369 | SMITH | FORD
1 | 7698 | BLAKE | 1 | 0 | 7698 | BLAKE |
2 | 7698 | BLAKE->ALLEN | | 1 | 7499 | ALLEN | BLAKE
2 | 7698 | BLAKE->WARD | | 1 | 7521 | WARD | BLAKE
2 | 7698 | BLAKE->MARTIN | | 1 | 7654 | MARTIN | BLAKE
2 | 7698 | BLAKE->TURNER | | 1 | 7844 | TURNER | BLAKE
2 | 7698 | BLAKE->JAMES | | 1 | 7900 | JAMES | BLAKE
1 | 7782 | CLARK | 1 | 0 | 7782 | CLARK |
2 | 7782 | CLARK->MILLER | | 1 | 7934 | MILLER | CLARK
(13 rows)行列转换函数
pivot函数
示例9： 通过 PIVOT函数将部门工资数据从行结构转换为列结构，显示各部门工资总额。
postgres=# select *
postgres-# from (select deptno, sal from emp) a
postgres-# pivot(sum(sal) as sal
postgres(# for deptno in(10 as d10, 20 as d20, 30 as d30, 40 as d40));
d10_sal | d20_sal | d30_sal | d40_sal
---------+----------+---------+---------
8750.00 | 10875.00 | 9400.00 |
(1 row)pivot函数的等效sql语句如下（case when…）：
postgres=# SELECT
postgres-# SUM(CASE WHEN deptno = 10 THEN sal END) AS d10_sal,
postgres-# SUM(CASE WHEN deptno = 20 THEN sal END) AS d20_sal,
postgres-# SUM(CASE WHEN deptno = 30 THEN sal END) AS d30_sal,
postgres-# SUM(CASE WHEN deptno = 40 THEN sal END) AS d40_sal
postgres-# FROM emp;
d10_sal | d20_sal | d30_sal | d40_sal
---------+----------+---------+---------
8750.00 | 10875.00 | 9400.00 |
(1 row)unpivot函数
示例10：通过 unpivot函数将部门工资汇总数据从列结构转换为行结构，显示各部门工资总额。
postgres=# create table emp_sum(d10_sal numeric(8,0),
postgres(# d20_sal numeric(8,0),d30_sal numeric(8,0),d40_sal numeric(8,0));
CREATETABLE
postgres=# insert into emp_sum values(8750,10875,9400,null);
INSERT01
postgres=# select * from emp_sum ;
d10_sal | d20_sal | d30_sal | d40_sal
---------+---------+---------+---------
8750 | 10875 | 9400 |
(1row)
postgres=# select *
postgres-# from emp_sum unpivot include nulls
postgres-# (sal for dept in(d10_sal,d20_sal,d30_sal,d40_sal));
dept | sal
---------+-------
d10_sal | 8750
d20_sal | 10875
d30_sal | 9400
d40_sal |
(4rows)总结
在实际工作中是否使用这些分析函数，需要结合explain【analyze】查看执行计划进行分析而定，不要盲目使用。
本文作者：飞天，微信公众号：飞天online。墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证以及OBCA、KCP、KCSM、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
欢迎加入华为云数据库社区，在这里可以找到志同道合的朋友，共同探索数据库的奥秘
更多推荐
19
0- 0
已为社区贡献17条内容
所有评论(0)