AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2023-11-13 1217
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
Redis 开源版，标准版 2GB
推荐场景：
搭建游戏排行榜
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 Tair（兼容Redis），内存型 2GB
推荐场景：
通过缓存加速数据库访问
简介： 继续讲述DM数据库Sql操作

这里继续讲解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 GROUPBY deptno;

查询结果如图

image.png

当表中没有数据时，不加 group by 会返回一行数据，加了 group by 无数据返回，先建空表

CREATETABLE employee2 ASSELECT*FROM employee WHERE1=2;

不加 group by

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

执行结果如图

image.png

增加 group by

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

执行结果如图

image.png


清空表数据

truncate table TEST

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

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

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

执行结果如图

image.png

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

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

执行结果如图

image.png

更改累计和的值

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

CREATEOR REPLACE VIEW v (id,amt,trx)ASSELECT1,100,'PR'FROM dual UNION ALL
SELECT2,100,'PR'FROM dual UNION ALL
SELECT3,50,'PY'FROM dual UNION ALL
SELECT4,100,'PR'FROM dual UNION ALL
SELECT5,200,'PY'FROM dual UNION ALL
SELECT6,50,'PY'FROM dual;SELECT*FROM v;

创建完成后结果如图

image.png

  • 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 v
ORDERBY id;

image.png

累加处理后的结果

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

执行结果如图

image.png

计算出现次数最多的值

使用 partition by 子句查看部门中哪个工资等级的员工最多

1.计算不同工资出现的次数

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

执行结果如图

image.png

2.按次数排序生成序号

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

执行结果如图

image.png

3.根据序号过滤得到需要的结果

SELECT salary FROM(SELECT salary, DENSE_RANK () OVER (ORDERBY 出现次数 DESC)AS 次数排序
FROM(SELECT salary,COUNT(*)AS 出现次数 FROM employee GROUPBY salary) x) y
WHERE 次数排序 =1;

执行结果如图

image.png

4.利用 partition by 子句查询各部门哪个工资等级的员工最多

SELECT deptno, salary FROM(SELECT deptno,salary,DENSE_RANK () OVER (PARTITION BY deptno ORDERBY 出现次数 DESC)AS 次数排序
FROM(SELECT salary, deptno,COUNT(*)AS 出现次数 FROM employee GROUPBY deptno, salary) x) y
WHERE 次数排序 =1;

执行结果如图

image.png

部门 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 employee
WHERE ROWNUM <=1;

执行结果如图

image.png

加减时、分、秒

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

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

执行结果如图

image.png

日期间隔之时、分、秒

两个 date 相减，得到的是天数，乘以 24 即为小时，以此类推可计算出秒。employee 表数据如下

image.png

计算日期间隔

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

执行结果如图

image.png

日期间隔之日、月、年

使用 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_hd
FROM employee);

执行结果如图

image.png

求两个日期间的工作天数

查询原始数据

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

执行结果如图

image.png

将原始数据转为一行

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

image.png

枚举两个日期之间的天数

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

image.png

创建中间表t500

CREATETABLE t500 ASSELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <=500;

image.png

与 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,t500
WHERE t500.id<=((max_hd - min_hd)+1);

image.png

根据日期得到对应的工作日信息

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,t500
WHERE t500.id<=((max_hd - min_hd)+1));

image.png

过滤工作日数据汇总

SELECTCOUNT(*)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,t500
WHERE t500.id<=((max_hd - min_hd)+1)))WHERE dy NOTIN('SAT','SUN');

image.png

得到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 GROUPBY 星期;

image.png

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

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

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

image.png

计算日期差值

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

image.png

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

文章标签：
SQL
数据库
Serverless
Python
关键词：
SQL函数
SQL日期
SQL聚集
达梦SQL
SQL操作
六月的雨在钉钉
目录
相关文章
大道不孤，众行致远
|
26天前
|
SQL
开发指南057-执行SQL返回结果集
业务实践中除了返回实体类外，经常需要通过SQL语句返回结果集
大道不孤，众行致远
27 0
云深知何处
|
23天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中，发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句，首先分析了 where 条件中各列的选择性，并创建了不同类型的索引，包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划，最终确定了使用复合索引（包含函数表达式）能够显著降低查询成本，提高执行效率。
云深知何处
17 3
1100237741946300
|
29天前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中，SQL函数是处理和分析数据的强大工具
1100237741946300
37 5
1100237741946300
|
30天前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中，COUNT函数是一个非常常用的聚合函数，用于计算表中满足特定条件的记录数
1100237741946300
344 5
diygwcom
|
1月前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
diygwcom
34 2
大道不孤，众行致远
|
1月前
|
SQL 安全
开发指南038-防SQL注入
这个是检查某个字符串是否含注入的SQL，为基本函数
大道不孤，众行致远
32 3
dlwlrma-晴子
|
30天前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
dlwlrma-晴子
39 0
t-25772328213-6-45193
|
2月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数（三）
可以用C（或者与C兼容，比如C++）语言编写用户自定义函数（User-defined functions）。这些函数被编译到动态可加载目标文件（也称为共享库）中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性，二者的实际编码约定本质上是相同的（因此，标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码）
t-25772328213-6-45193
113 1
wljslmz
|
3月前
|
SQL 数据处理 数据库
SQL 函数全解析：掌握数据库操作的关键工具
【8月更文挑战第31天】
wljslmz
63 0
土木林森
|
3月前
|
Java 应用服务中间件 Maven
从零到英雄：一步步构建你的首个 JSF 应用程序，揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准，用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets，便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用，包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
土木林森
88 0