必知！opengauss / 磐维数据库分析函数实用示例

原创飞天 2025-04-24

本文主要介绍了 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=> SELECT max(ename) keep(dense_rank first order by sal) as first,
postgres->        min(ename) keep(dense_rank last order by sal) as last
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=> select level as 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_isleaf as isleaf,
postgres->        empno,
postgres->        ename,
postgres->        prior ename as prior_name
postgres->   from emp
postgres->  start with mgr = 7839
postgres-> connect by nocycle mgr = (prior empno)
postgres->  order siblings by 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));
CREATE TABLE
postgres=# insert into emp_sum values(8750,10875,9400,null);
INSERT 0 1
postgres=# select * from emp_sum ;
 d10_sal | d20_sal | d30_sal | d40_sal 
---------+---------+---------+---------
    8750 |   10875 |    9400 |        
(1 row)

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 |      
(4 rows)
复制

总结

在实际工作中是否使用这些分析函数，需要结合explain【analyze】查看执行计划进行分析而定，不要盲目使用。

关于作者：
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证以及OBCA、KCP、KCSM、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~

墨力计划

最后修改时间：2025-04-25 10:01:37

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

文章被以下合辑收录

磐维数据库（共50篇）

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于华为openGauss开源软件，并进一步提升了系统稳定性。磐维数据库具有高性能、高可靠、高安全、高兼容等特点，能够满足复杂多变的业务需求。磐维数据库提供了自动化、流程化的解决方案，实现了一键式数据迁移。这种高效的数据迁移方式不仅提高了迁移数据的效率，也降低了操作难度，为用户带来了极大的便利。