DM8部分函数的功能分别举例说明

• 1 环境说明
• 2 函数功能使用示例
• 2.1 AVG OVER
• 2.2 COUNT OVER
• 2.3 MIN OVER，MAX OVER，SUM OVER
• 2.4 DENSE_RANK
• 2.5 ROW_NUMBER
• 2.6 FIRST
• 2.7 LAG
• 2.8 WM_CONCAT
• 3 更多达梦数据库全方位指南:安装 优化 与实战教程

1 环境说明

• Cpu x86
• Os Kylin V10
• Dm8 1-3-100-2023.12.26-213242-20081-ENT
• 测试数据 下载

2 函数功能使用示例

2.1 AVG OVER

--AVG OVER
--按部门计算平均薪资
--每行显示每个员工的薪资、部门平均薪资及全公司薪资排名。
SELECT
	DEPARTMENT_ID,	--部门ID
	EMPLOYEE_ID,	--员工ID
	SALARY,			--员工工资
	AVG(SALARY)OVER (PARTITION BY DEPARTMENT_ID)AS DEPTAVG,	--部门平均工资
	RANK() OVER(ORDER BY SALARY DESC)AS SALARY_RANK 		--工资排名
	FROM DMHR.EMPLOYEE;

2.2 COUNT OVER

--COUNT OVER
--按部门统计员工数
--每行显示员工所属部门的员工总数
SELECT
	DEPARTMENT_ID,	--部门ID
	EMPLOYEE_ID,	--员工ID
	COUNT(*) OVER(PARTITION BY DEPARTMENT_ID) AS DEPTEMP_COUNT	--部门总人数
FROM DMHR.EMPLOYEE;

2.3 MIN OVER，MAX OVER，SUM OVER

--MIN OVER，MAX OVER，SUM OVER
--每行显示员工薪资及其所属部门的最低薪资，最高薪资，薪资总和，平均工资，总人数，工资排名

SELECT 
  DEPARTMENT_ID,	--部门ID
  EMPLOYEE_ID,		--员工ID
  SALARY,			--员工薪资
  MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPTMIN_SALARY,	--部门最低薪资
  MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPTMAX_SALARY,	--部门最高薪资
  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPTSUM_SALARY,	--部门薪资总和
  AVG(SALARY)OVER (PARTITION BY DEPARTMENT_ID)AS DEPTAVG,			--部门平均工资
  COUNT(*) OVER(PARTITION BY DEPARTMENT_ID) AS DEPTEMP_COUNT,		--部门总人数
  RANK() OVER(ORDER BY SALARY DESC)AS SALARY_RANK 					--工资排名
FROM DMHR.EMPLOYEE ;

2.4 DENSE_RANK

--DENSE_RANK
--按部门统计工资出现次数，并生成密集排名
WITH SALARY_COUNT AS (
  SELECT 
    DEPARTMENT_ID,
    SALARY,
    COUNT(*) AS OCCURRENCE  -- 统计工资出现次数
  FROM DMHR.EMPLOYEE
  GROUP BY DEPARTMENT_ID, SALARY
)
SELECT 
  DEPARTMENT_ID,
  SALARY,
  OCCURRENCE,
  DENSE_RANK() OVER (
    PARTITION BY DEPARTMENT_ID 
    ORDER BY OCCURRENCE DESC
  ) AS DENSERANKNUM  -- 按出现次数降序生成密集排名
FROM SALARY_COUNT;

2.5 ROW_NUMBER

--ROW_NUMBER
--按部门统计工资出现次数，并生成顺序编号

WITH SALARY_COUNT AS (
  SELECT 
    DEPARTMENT_ID,
    SALARY,
    COUNT(*) AS OCCURRENCE  -- 统计工资出现次数
  FROM DMHR.EMPLOYEE
  GROUP BY DEPARTMENT_ID, SALARY
)
SELECT 
  DEPARTMENT_ID,
  SALARY,
  OCCURRENCE,
  ROW_NUMBER() OVER (
    PARTITION BY DEPARTMENT_ID 
    ORDER BY SALARY DESC
  ) AS DENSERANKNUM  -- 按照顺序编号，不区分相同值，即从 1 开始编号
FROM SALARY_COUNT ORDER BY DEPARTMENT_ID ASC,SALARY DESC;

2.6 FIRST

--FIRST
--同时获取部门最低和最高薪资员工
SELECT 
  DEPARTMENT_ID,
  MAX(EMPLOYEE_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY) AS LOWEST_EMP,
  MAX(EMPLOYEE_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY) AS HIGHEST_EMP,
  MIN(SALARY) AS MIN_SALARY,
  MAX(SALARY) AS MAX_SALARY
FROM DMHR.EMPLOYEE
GROUP BY DEPARTMENT_ID;

2.7 LAG

--LAG
--按部门分区，获取部门内前一位员工薪资
SELECT 
  DEPARTMENT_ID,
  EMPLOYEE_NAME,
  SALARY,
  LAG(SALARY) OVER (
    PARTITION BY DEPARTMENT_ID 
    ORDER BY SALARY
  ) AS PREV_SALARY
FROM DMHR.EMPLOYEE;

2.8 WM_CONCAT

--WM_CONCAT
--列转行，合并姓名列
SELECT 
  DEPARTMENT_ID,
  TO_CHAR(WM_CONCAT(NVL(EMPLOYEE_NAME, '未知'))) AS EMPLOYEES
FROM DMHR.EMPLOYEE
GROUP BY DEPARTMENT_ID;

3 更多达梦数据库全方位指南:安装 优化 与实战教程

• 更多达梦数据库全方位指南:安装 优化 与实战教程 - -点击跳转