本章节主要介绍在 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 按某个字段排序。
三、操作方法
3.1 定位连续值的范围
可以使用分析函数 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 下一工程开始日期 = 结束日期
使用分析函数只需要扫描一次视图即可，大部分情况下可以通过分析函数优化查询性能。
3.2 查找同一分区中行之间的差
可以使用分析函数 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 登录名,
登录时间,
(下一登录时间 - 登录时间) * 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);
3.3 定位连续范围的起始点
为了便于引用，先定义一个项目计划的明细视图 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 文件夹下。如有其他问题，请在社区内咨询。
