范围处理

本章节主要介绍在 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 行记录说明,不能取前面的。如果要取前面第 n 行记录说明,使用 lag()函数。
  • 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 文件夹下。如有其他问题,请在社区内咨询。

微信扫码
分享文档
扫一扫
联系客服