达梦(DM) SQL聚集函数及日期运算操作

2024-04-30 13:36
文章标签 sql dm 函数 操作 运算 日期 database 达梦 聚集

本文主要是介绍达梦(DM) SQL聚集函数及日期运算操作，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

达梦DM SQL聚集函数及日期运算操作

  • 聚集函数
    • MAX、MIN、SUM、AVG、COUNT
    • 使用分析函数 sum (…) over (order by…) 可以生成累计和
    • 更改累计和的值
    • 计算出现次数最多的值
  • 日期运算
    • 加减日、月、年
    • 加减时、分、秒
    • 日期间隔之时、分、秒
    • 日期间隔之日、月、年
    • 求两个日期间的工作天数
    • 确定当前记录和下一条记录之间相差的天数

这里讲解DM数据库的操作，主要涉及聚集函数、分析函数、日期运算、日期操作等操作。

聚集函数

MAX、MIN、SUM、AVG、COUNT

SQL 中的聚集函数共包括 5 个 (MAX、MIN、SUM、AVG、COUNT)，可以帮我们求某列的最大值、最小值及平均值等

-- 查询每个部门员工的平均薪资、最小薪资、最大薪资、总工资及总记录SELECT deptno,AVG (salary) AS 平均值,MIN (salary) AS 最小值,MAX (salary) AS 最大值,SUM (salary) AS 工资合计,COUNT (*) AS 总行数FROM employee GROUP BY deptno;

查询结果如图
在这里插入图片描述
当表中没有数据时，不加 group by 会返回一行数据，加了 group by 无数据返回，先建空表

CREATE TABLE employee2 AS SELECT * FROM employee WHERE 1 = 2;

不加 group by

SELECT COUNT (*) AS cnt, SUM (salary) AS sum_sal FROM employee2 WHERE deptno = 11;

执行结果如图
在这里插入图片描述
增加 group by

SELECT COUNT (*) AS cnt, SUM (salary) AS sum_sal FROM employee2 WHERE deptno = 11 group by deptno;

执行结果如图
在这里插入图片描述
清空表数据

truncate table TEST

使用分析函数 sum (…) over (order by…) 可以生成累计和

使用分析函数 sum (…) over (order by…) 可以生成累计和，查询部门1下的工资累计

-- 按员工编号排序对员工的工资进行累加SELECT empno AS 编号,ename AS 姓名,salary AS 人工成本,SUM (salary) OVER (ORDER BY empno) AS 成本累计FROM employeeWHERE deptno = 1;

执行结果如图
在这里插入图片描述
结果是当前部门下按照empno排序从第一行到当前行的所有工资之和，为了形象地说明这一点，我们用 listagg 模拟出每一行是哪些值相加

-- 使用 listagg 函数模拟员工总成本的累加值SELECT empno                                                AS 编号,ename                                              AS 姓名,salary                                                     AS 人工成本,SUM (salary) OVER (ORDER BY empno)                   AS 成本累计,(SELECT LISTAGG (salary, '+') WITHIN GROUP (ORDER BY empno)FROM employee bWHERE b.deptno = 1 AND b.empno <= a.empno)  计算公式FROM employee aWHERE deptno = 1ORDER BY empno;

执行结果如图
在这里插入图片描述

更改累计和的值

更改累计和的值，为了方便测试，创建视图

CREATE OR REPLACE VIEW v (id,amt,trx) ASSELECT 1,100,'PR' FROM dual UNION ALLSELECT 2,100,'PR' FROM dual UNION ALLSELECT 3,50,'PY' FROM dual UNION ALLSELECT 4,100,'PR' FROM dual UNION ALLSELECT 5,200,'PY' FROM dual UNION ALLSELECT 6,50,'PY' FROM dual;SELECT * FROM v;

创建完成后结果如图
在这里插入图片描述
• id 是唯一列。
• amt 列表示每次事务处理（存款或取款）涉及到的金额。
• trx 定义了事务的类型，取款是 PY，存款是 PR。
先要求计算每次存/取款后的余额，如果 trx 是 PR，则加上 amt 值代表的金额；否则减去 amt 值代表的金额。这实际上是一个累加问题，我们可以把取款的值先变成负数。

 SELECT id,CASE WHEN trx = 'PY' THEN '取款' ELSE '存款' END 存取类型,amt 金额,(CASE WHEN trx = 'PY' THEN -amt ELSE amt END) AS 余额FROM vORDER BY id;

在这里插入图片描述
累加处理后的结果

  SELECT id,CASE WHEN trx = 'PY' THEN '取款' ELSE '存款' END 存取类型,amt 金额,SUM (CASE WHEN trx = 'PY' THEN -amt ELSE amt END) OVER (ORDER BY id)AS 余额FROM vORDER BY id;

执行结果如图
在这里插入图片描述

计算出现次数最多的值

使用 partition by 子句查看部门中哪个工资等级的员工最多
1.计算不同工资出现的次数

SELECT salary, COUNT (*) AS 出现次数 FROM employee GROUP BY salary;

执行结果如图
在这里插入图片描述
2.按次数排序生成序号

SELECT salary,DENSE_RANK () OVER (ORDER BY 出现次数 DESC) AS 次数排序 FROM (  SELECT salary, COUNT (*) AS 出现次数 FROM employee GROUP BY salary);

执行结果如图
在这里插入图片描述
3.根据序号过滤得到需要的结果

SELECT salary FROM (SELECT salary, DENSE_RANK () OVER (ORDER BY 出现次数 DESC) AS 次数排序FROM (  SELECT salary, COUNT (*) AS 出现次数 FROM employee GROUP BY salary) x) yWHERE 次数排序 = 1;

执行结果如图
在这里插入图片描述
4.利用 partition by 子句查询各部门哪个工资等级的员工最多

SELECT deptno, salary FROM (SELECT deptno,salary,DENSE_RANK () OVER (PARTITION BY deptno ORDER BY 出现次数 DESC)AS 次数排序FROM (  SELECT salary, deptno, COUNT (*) AS 出现次数 FROM employee GROUP BY deptno, salary) x) yWHERE 次数排序 = 1;

执行结果如图
在这里插入图片描述
部门 1 、 2 中各工资档次出现次数都为 1，所以返回所有的数据。

日期运算

加减日、月、年

date 类型的数据可以直接加减天数，加减月份需要使用 add_months 函数，同时也可以使用 add_days 加减天数

SELECT hiredate AS 聘用日期,add_days (hiredate, -5) AS 减5天,add_days (hiredate, 5) AS 加5天,add_months (hiredate, -5) AS 减5月,add_months (hiredate, 5) AS 加5月,add_months (hiredate, -5 * 12) AS 减5年,add_months (hiredate, 5 * 12) AS 加5年FROM employeeWHERE ROWNUM <= 1;

执行结果如图
在这里插入图片描述

加减时、分、秒

时间类型的数据可以直接加减时、分、秒，1/24 指的是 1 小时

select sysdate as 当前日期,sysdate - 5.0 / 24 as 减5小时,sysdate + 5.0 / 24 as 加5小时,sysdate - 5.0 / 24 / 60 as 减5分钟,sysdate + 5.0 / 24 / 60 as 加5分钟from dual;

执行结果如图
在这里插入图片描述

日期间隔之时、分、秒

两个 date 相减，得到的是天数，乘以 24 即为小时，以此类推可计算出秒。employee 表数据如下
在这里插入图片描述
计算日期间隔

SELECT 间隔天数,间隔天数 * 24 AS 间隔小时,间隔天数 * 24 * 60 AS 间隔分,间隔天数 * 24 * 60 * 60 AS 间隔秒FROM (SELECT MAX (hiredate) - MIN (hiredate) AS 间隔天数FROM employeeWHERE empno IN (1,2)) x;

执行结果如图
在这里插入图片描述

日期间隔之日、月、年

使用 months_between 函数计算间隔月份，以此类推计算出间隔年

SELECT max_hd - min_hd 间隔天,MONTHS_BETWEEN (max_hd, min_hd) 间隔月,MONTHS_BETWEEN (max_hd, min_hd) / 12 间隔年FROM (SELECT MAX (hiredate) max_hd, MIN (hiredate) min_hdFROM employee);

执行结果如图
在这里插入图片描述

求两个日期间的工作天数

查询原始数据

SELECT empno, hiredate FROM employee WHERE empno IN (1,2);

执行结果如图
在这里插入图片描述
将原始数据转为一行

SELECT MIN (hiredate) AS min_hd, MAX (hiredate) AS max_hd FROM employee WHERE empno IN (1,2);

在这里插入图片描述
枚举两个日期之间的天数

select (max_hd-min_hd)+1 as 天数 from (SELECT MIN (hiredate) AS min_hd, MAX (hiredate) AS max_hd FROM employee WHERE empno IN (1,2));

在这里插入图片描述
创建中间表t500

CREATE TABLE t500 AS SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <= 500;

在这里插入图片描述
与 t500 做笛卡尔积枚举 30 天的所有日期

SELECT min_hd + (t500.id - 1) AS 日期FROM (SELECT MIN (hiredate) AS min_hd, MAX (hiredate) AS max_hd FROM employee WHERE empno IN (1,2)) x,t500WHERE t500.id <= ( (max_hd - min_hd) + 1);

在这里插入图片描述
根据日期得到对应的工作日信息

SELECT 日期, TO_CHAR (日期, 'DY', 'NLS_DATE_LANGUAGE = American') AS dy FROM (SELECT min_hd + (t500.id - 1) AS 日期FROM (SELECT MIN (hiredate) AS min_hd, MAX (hiredate) AS max_hd FROM employee WHERE empno IN (1,2)) x,t500WHERE t500.id <= ( (max_hd - min_hd) + 1));

在这里插入图片描述
过滤工作日数据汇总

SELECT COUNT(*) FROM (SELECT 日期, TO_CHAR (日期, 'DY', 'NLS_DATE_LANGUAGE = American') AS dy FROM (SELECT min_hd + (t500.id - 1) AS 日期FROM (SELECT MIN (hiredate) AS min_hd, MAX (hiredate) AS max_hd FROM employee WHERE empno IN (1,2)) x,t500WHERE t500.id <= ( (max_hd - min_hd) + 1))) WHERE dy NOT IN ('SAT','SUN');

在这里插入图片描述
得到10个工作日。
求一年中周内各日期的天数
可以按照以下步骤分析
• 取得大当前年度信息。
• 计算一年有多少天。
• 生成日期列表。
• 转换为对应的星期标识。
• 汇总统计。

WITH x0 AS (SELECT TO_DATE ('2023-01-01', 'yyyy-mm-dd') AS 年初 FROM DUAL),x1 AS (SELECT 年初, ADD_MONTHS (年初, 12) AS 下年初 FROM x0),x2 AS (SELECT 年初, 下年初, 下年初 - 年初 AS 天数 FROM x1),x3 AS (SELECT 年初 + (LEVEL - 1) AS 日期 FROM x2 CONNECT BY LEVEL <= 天数),x4 AS (SELECT 日期, TO_CHAR (日期, 'DY') AS 星期 FROM x3)SELECT 星期, COUNT (*) AS 天数 FROM x4 GROUP BY 星期;

在这里插入图片描述

确定当前记录和下一条记录之间相差的天数

使用 lead() over() 分析函数

SELECT empno,ename,hiredate,LEAD (hiredate) OVER (ORDER BY hiredate) next_hd FROM employee;

在这里插入图片描述
计算日期差值

SELECT empno,ename,hiredate,next_hd,next_hd-hiredate diff FROM (SELECT empno,ename,hiredate,LEAD (hiredate) OVER (ORDER BY hiredate) next_hd FROM employee);

在这里插入图片描述
到这里，关于DM数据库的聚集函数及日期运算操作也就算说完了，后续继续进行其他场景操作。

这篇关于达梦(DM) SQL聚集函数及日期运算操作的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/949008。

相关文章

MySQL字符串转数值的方法全解析

MySQL字符串转数值的方法全解析

《MySQL字符串转数值的方法全解析》在MySQL开发中,字符串与数值的转换是高频操作,本文从隐式转换原理、显式转换方法、典型场景案例、风险防控四个维度系统梳理,助您精准掌握这一核心技能,需要的朋友可... 目录一、隐式转换：自动但需警惕的&ld编程quo;双刃剑”二、显式转换：三大核心方法详解三、典型场景
阅读更多...
MySQL中between and的基本用法、范围查询示例详解

MySQL中between and的基本用法、范围查询示例详解

《MySQL中betweenand的基本用法、范围查询示例详解》BETWEENAND操作符在MySQL中用于选择在两个值之间的数据,包括边界值,它支持数值和日期类型,示例展示了如何使用BETWEEN... 目录一、between and语法二、使用示例2.1、betwphpeen and数值查询2.2、be
阅读更多...
MySQL快速复制一张表的四种核心方法(包括表结构和数据)

MySQL快速复制一张表的四种核心方法(包括表结构和数据)

《MySQL快速复制一张表的四种核心方法(包括表结构和数据)》本文详细介绍了四种复制MySQL表（结构+数据）的方法,并对每种方法进行了对比分析,适用于不同场景和数据量的复制需求,特别是针对超大表（1... 目录一、mysql 复制表（结构+数据）的 4 种核心方法（面试结构化回答）方法 1：CREATE
阅读更多...
Go异常处理、泛型和文件操作实例代码

Go异常处理、泛型和文件操作实例代码

《Go异常处理、泛型和文件操作实例代码》Go语言的异常处理机制与传统的面向对象语言（如Java、C#）所使用的try-catch结构有所不同,它采用了自己独特的设计理念和方法,：本文主要介绍Go异... 目录一：异常处理常见的异常处理向上抛中断程序恢复程序二：泛型泛型函数泛型结构体泛型切片泛型 map三：文
阅读更多...
SQL Server中行转列方法详细讲解

SQL Server中行转列方法详细讲解

《SQLServer中行转列方法详细讲解》SQL行转列、列转行可以帮助我们更方便地处理数据,生成需要的报表和结果集,：本文主要介绍SQLServer中行转列方法的相关资料,需要的朋友可以参考下... 目录前言一、为什么需要行转列二、行转列的基本概念三、使用PIVOT运算符进行行转列1.创建示例数据表并插入数
阅读更多...
MySQL MHA集群详解(数据库高可用)

MySQL MHA集群详解(数据库高可用)

《MySQLMHA集群详解(数据库高可用)》MHA（MasterHighAvailability）是开源MySQL高可用管理工具,用于自动故障检测与转移,支持异步或半同步复制的MySQL主从架构,本... 目录mysql 高可用方案：MHA 详解与实战1. MHA 简介2. MHA 的组件组成（1）MHA
阅读更多...
SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

《SQL注入攻击(SQLInjection)原理、利用方式与防御策略深度解析》本文将从SQL注入的基本原理、攻击方式、常见利用手法,到企业级防御方案进行全面讲解,以帮助开发者和安全人员更系统地理解... 目录一、前言二、SQL 注入攻击的基本概念三、SQL 注入常见类型分析1. 基于错误回显的注入（Erro
阅读更多...
MySQL基本表查询操作汇总之单表查询+多表操作大全

MySQL基本表查询操作汇总之单表查询+多表操作大全

《MySQL基本表查询操作汇总之单表查询+多表操作大全》本文全面介绍了MySQL单表查询与多表操作的关键技术,包括基本语法、高级查询、表别名使用、多表连接及子查询等,并提供了丰富的实例,感兴趣的朋友跟... 目录一、单表查询整合（一）通用模版展示（二）举例说明（三）注意事项（四）Mapper简单举例简单查询
阅读更多...
Nginx概念、架构、配置与虚拟主机实战操作指南

Nginx概念、架构、配置与虚拟主机实战操作指南

《Nginx概念、架构、配置与虚拟主机实战操作指南》Nginx是一个高性能的HTTP服务器、反向代理服务器、负载均衡器和IMAP/POP3/SMTP代理服务器,它支持高并发连接,资源占用低,功能全面且... 目录Nginx 深度解析：概念、架构、配置与虚拟主机实战一、Nginx 的概念二、Nginx 的特点
阅读更多...
pandas使用apply函数给表格同时添加多列

pandas使用apply函数给表格同时添加多列

《pandas使用apply函数给表格同时添加多列》本文介绍了利用Pandas的apply函数在DataFrame中同时添加多列,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习... 目录一、Pandas使用apply函数给表格同时添加多列二、应用示例一、Pandas使用apply函
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2