Oracle SQL:LAG在一系列值上

Question

我们如何在值的范围内使用分析函数LAG。如果分区中没有具有较早in_date的记录，则应返回null，否则为上一个in_date 的年份和月份

示例：

CID  IN_DATE        
1    2020-05-29
1    2020-06-10
1    2020-06-21
1    2020-07-08
1    2020-08-11
1    2020-10-01
2    2020-05-05
2    2020-05-03
2    2020-06-01
2    2020-06-02
2    2020-06-03

预期输出，

CID  IN_DATE        LAG
1    2020-05-29     null
1    2020-06-10     2020-05
1    2020-06-21     2020-05
1    2020-07-08     2020-06
1    2020-08-11     2020-07
1    2020-10-01     2020-08
2    2020-05-05     null 
2    2020-05-03     null
2    2020-06-01     2020-05
2    2020-06-02     2020-05
2    2020-06-03     2020-05
2    2020-07-03     2020-06
2    2020-08-13     2020-07

我当前使用LAG的查询返回低于

with data as (
select 1  CID,  TO_DATE('2020-05-29','YYYY-MM-DD')  IN_DATE from dual union all
select 1,  TO_DATE('2020-06-10','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-06-21','YYYY-MM-DD')  from dual union all
select 1,  TO_DATE('2020-07-08','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-08-11','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-05','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-03','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-01','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-02','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-03','YYYY-MM-DD') from dual 
) 
select CID, 
to_char(TO_DATE(IN_DATE), 'YYYY-MM-DD') IN_DATE, 
LAG(to_char(TO_DATE(IN_DATE), 'YYYY-MM')) OVER (PARTITION BY CID 
ORDER BY to_char(TO_DATE(IN_DATE), 'YYYY-MM') ) LAG 
from data

当前结果

CID IN_DATE     LAG
1   1   2020-05-29  NULL
2   1   2020-06-10  2020-05
3   1   2020-06-21  2020-06
4   1   2020-07-08  2020-06
5   1   2020-08-11  2020-07
6   2   2020-05-05  NULL
7   2   2020-05-03  2020-05
8   2   2020-06-01  2020-05
9   2   2020-06-02  2020-06
10  2   2020-06-03  2020-06

看起来，LAG不允许我们在范围内使用分区这个有其他方法吗

Answer 1

如果您希望上个月在本月之前，则不需要LAG()。我建议：

with data as (
select 1  CID,  TO_DATE('2020-05-29','YYYY-MM-DD')  IN_DATE from dual union all
select 1,  TO_DATE('2020-06-10','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-06-21','YYYY-MM-DD')  from dual union all
select 1,  TO_DATE('2020-07-08','YYYY-MM-DD') from dual union all
select 1,  TO_DATE('2020-08-11','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-05','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-05-03','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-01','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-02','YYYY-MM-DD') from dual union all
select 2,  TO_DATE('2020-06-03','YYYY-MM-DD') from dual 
) 
select CID, 
to_char(TO_DATE(IN_DATE), 'YYYY-MM-DD') IN_DATE, 
TO_CHAR(MAX(IN_DATE) OVER (PARTITION BY CID 
ORDER BY TRUNC(IN_DATE, 'MON')
RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' MONTH PRECEDING
),
'YYYY-MM') as LAG 
from data;

这里有一个db<gt；不停摆弄

Answer 2

记录7的滞后字段不为空，因为滞后函数正在查找记录6。

我可以看到两种方法来澄清你的问题：

1. 如果分区中没有具有早期in_date 的记录，则希望返回null

2. 如果in_date所在的分区中没有来自当前记录的in_date前一个月的记录，则需要返回null