DM8达梦数据库范围处理

本章节主要介绍在 DM 数据库中如何利用分析函数 lead() over() 进行范围问题的处理。

适用场景

软件	版本
操作系统	Redhat 7 及以上版本
DM 数据库	DM 8.0 及以上版本
CPU 架构	x86、ARM、龙芯、飞腾等国内外主流 CPU

分析函数

使用语法：lead(列名,n,m) over(partition by ... order by ...)，不带参数 n，m，则查找当前记录后面第一行的记录列名的值。

lead() 用于取前面/后面第 n 行记录说明。
over() 在什么条件之上，使用语法 over(partition by…order by…)。
partition by 按某个字段划分组。
order by 按某个字段排序。

操作方法

定位连续值的范围

可以使用分析函数 lead() over() 定位某一段连续值的范围。为了便于引用，定义一个项目计划的明细视图 v：

CREATE OR REPLACE VIEW v(pro_id,pro_start,pro_end) as
SELECT 1,date '2020-10-01',date '2020-10-02' FROM dual UNION ALL
SELECT 2,date '2020-10-02',date '2020-10-03' FROM dual UNION ALL
SELECT 3,date '2020-10-03',date '2020-10-06' FROM dual UNION ALL
SELECT 4,date '2020-10-06',date '2020-10-07' FROM dual UNION ALL
SELECT 5,date '2020-10-09',date '2020-10-11' FROM dual UNION ALL
SELECT 6,date '2020-10-13',date '2020-10-15' FROM dual;
SELECT * FROM v;

项目计划的明细视图

现在需要查询连续值记录，即下一行记录的起始时间与上一行记录的结束时间一致。

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

SELECT 工程号, 开始日期, 结束日期
  FROM (SELECT pro_id AS 工程号,
               pro_start AS 开始日期,
               pro_end AS 结束日期,
               LEAD (pro_start) OVER (ORDER BY pro_id) 下一工程开始日期
          FROM v)
 WHERE 下一工程开始日期 = 结束日期

分析函数

使用分析函数只需要扫描一次视图即可，大部分情况下可以通过分析函数优化查询性能。

查找同一分区中行之间的差

可以使用分析函数 lead() over() 查找同一分区中行之间的差。为了便于引用，先定义一个记录用户登录日志信息的视图 v：

CREATE OR REPLACE VIEW v(log_name,log_time) as
SELECT 'HR',datetime '2020-10-01 09:28:00' FROM dual UNION ALL
SELECT 'HR',datetime '2020-10-01 09:38:10' FROM dual UNION ALL
SELECT 'HR',datetime '2020-10-01 10:50:00' FROM dual UNION ALL
SELECT 'HR',datetime '2020-10-01 11:08:50' FROM dual UNION ALL
SELECT 'SYSTEM',datetime '2020-10-01 09:18:00' FROM dual UNION ALL
SELECT 'SYSTEM',datetime '2020-10-01 12:09:40' FROM dual;
SELECT * FROM v;

登录日志

现在需要各用户两次登录的时间间隔。

使用分析函数 lead() over() 取出下一行信息：

SELECT log_name AS 登录名,
       log_time AS 登录时间,
       LEAD (log_time) OVER (PARTITION BY log_name ORDER BY log_time) 下一登录时间
  FROM v;

取出下一行信息

计算用户两次登录的时间间隔

SELECT log_name AS 登录名,
       log_time AS 登录时间,
       (下一登录时间 - 登录时间) * 24 * 60 AS 登录间隔
  FROM (SELECT log_name AS 登录名,
               log_time AS 登录时间,
               LEAD (log_time) OVER (PARTITION BY log_name ORDER BY log_time)
               下一登录时间
          FROM v);

定位连续范围的起始点

为了便于引用，先定义一个项目计划的明细视图 v：

CREATE OR REPLACE VIEW v(pro_id,pro_start,pro_end) as
SELECT 1,date '2020-10-01',date '2020-10-02' FROM dual UNION ALL
SELECT 2,date '2020-10-02',date '2020-10-03' FROM dual UNION ALL
SELECT 3,date '2020-10-03',date '2020-10-06' FROM dual UNION ALL
SELECT 4,date '2020-10-06',date '2020-10-07' FROM dual UNION ALL
SELECT 5,date '2020-10-09',date '2020-10-11' FROM dual UNION ALL
SELECT 6,date '2020-10-13',date '2020-10-15' FROM dual;
SELECT * FROM v;

项目计划的明细视图

现在要求把连续的项目合并，返回合并后的起始时间。比如上例中合并后的时间是 ‘2020-10-01’ 到 ‘2020-10-07’。可按照如下操作步骤实现：

提取上一个工程结束时间

CREATE OR REPLACE VIEW x0
AS
   SELECT pro_id AS 编号,
          pro_start AS 开始日期,
          pro_end AS 结束日期,
          LAG (pro_end) OVER (ORDER BY pro_id) AS 上一工程结束日期
     FROM v;
SELECT * FROM x0;

提取上一个工程结束时间

标定项目的连续状态

CREATE OR REPLACE VIEW x1
AS
   SELECT 编号,
          开始日期,
          结束日期,
          上一工程结束日期,
          CASE WHEN 开始日期 = 上一工程结束日期 THEN 0 ELSE 1 END AS 连续状态 FROM x0;
SELECT * FROM x1;

标定项目的连续状态

从结果图看出，每个连续分组的开始，都生成了一个“1”的标识。

对位置状态进行累加，得到分组依据

CREATE OR REPLACE VIEW x2
AS
   SELECT 编号,
          开始日期,
          结束日期,
          上一工程结束日期,
          连续状态,
          SUM(连续状态) over(ORDER BY 编号) AS 分组依据
     FROM x1;
SELECT * FROM x2;

累加得到分组依据

可以看到，通过提取数据、生成标识、累加标识这些操作后，得到了需要的 3 个连续分组。

得到分组信息

  SELECT 分组依据,
         MIN (开始日期) AS 开始日期,
         MAX (结束日期) AS 结束日期
    FROM x2
GROUP BY 分组依据
ORDER BY 1;

累加得到分组依据

参考文献

更多 SQL 语言使用说明，请参考《DM_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

DM8达梦数据库范围处理

适用场景

分析函数

操作方法

定位连续值的范围

查找同一分区中行之间的差

定位连续范围的起始点

参考文献

评论