sql - SQL查询比较不相邻行的日期间隔?
问题描述
ID
我想为我的数据中的每个单元标记至少 31 天的每个窗口中的第一个日期。
ROW ID INDEX_DATE
1 ABC 1/1/2019
2 ABC 1/7/2019
3 ABC 1/21/2019
4 ABC 2/2/2019
5 ABC 2/9/2019
6 ABC 3/6/2019
7 DEF 1/5/2019
8 DEF 2/1/2019
9 DEF 2/8/2019
所需的行是 1、4、6、7 和 9;这些要么是INDEX_DATE
给定的第一个ID
,要么发生在先前标记的至少 31 天之后INDEX_DATE
。我发现的每个建议都使用LAG()
或LEAD
与窗口函数一起使用,但我只能得到这些来比较相邻的行。例如,第 4 行需要与第 1 行进行比较,以便在 31 天窗口完成后被识别为第一行。
我尝试了以下方法:
数据
DROP TABLE tTest IF EXISTS;
CREATE TEMP TABLE tTest
(
ROWN INT,
ID VARCHAR(3),
INDEX_DATE DATE
) ;
GO
INSERT INTO tTEST VALUES (1, 'ABC', '1/1/2019');
INSERT INTO tTEST VALUES (2, 'ABC', '1/7/2019');
INSERT INTO tTEST VALUES (3, 'ABC', '1/21/2019');
INSERT INTO tTEST VALUES (4, 'ABC', '2/2/2019');
INSERT INTO tTEST VALUES (5, 'ABC', '2/9/2019');
INSERT INTO tTEST VALUES (6, 'ABC', '3/6/2019');
INSERT INTO tTEST VALUES (7, 'DEF', '1/5/2019');
INSERT INTO tTEST VALUES (8, 'DEF', '2/1/2019');
INSERT INTO tTEST VALUES (9, 'DEF', '2/8/2019');
GO
询问:
DROP TABLE TTEST2 IF EXISTS;
CREATE TEMP TABLE TTEST2 AS (
WITH
RN_CTE(ROWN, ID, INDEX_DATE, RN) AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY INDEX_DATE)
FROM tTEST),
MIN_CTE(ROWN, ID, INDEX_DATE, RN) AS (SELECT * FROM RN_CTE WHERE RN=1),
DIFF_CTE(ROWN,ID, INDEX_DATE, RN, DAY_DIFF) AS (
SELECT RN.*, DATE(RN.INDEX_DATE + INTERVAL '30 DAYS')
FROM RN_CTE AS RN
JOIN MIN_CTE AS MC ON RN.ID=MC.ID
WHERE RN.RN=1
OR RN.INDEX_DATE > MC.INDEX_DATE + INTERVAL '30 DAYS' ),
MIN_DIFF_CTE AS (
SELECT ID, DAY_DIFF, MIN(ROWN) AS MIN_ROW
FROM DIFF_CTE
GROUP BY ID, DAY_DIFF)
SELECT T.*
FROM MIN_DIFF_CTE AS MDC
JOIN tTEST AS T ON MDC.MIN_ROW = T.ROWN
ORDER BY ID, INDEX_DATE
);
结果:
SELECT * FROM TTEST2 ORDER BY ID, INDEX_DATE;
ROWN ID INDEX_DATE
1 ABC 2019-01-01
4 ABC 2019-02-02
5 ABC 2019-02-09
6 ABC 2019-03-06
7 DEF 2019-01-05
9 DEF 2019-02-08
INDEX_DATE = 2019-02-09 的第 5 行不应出现在输出中,因为它在第 4 行的 INDEX_DATE 之后不到 31 天。
解决方案
像这样的东西。CTE 为每个 ID 转换和 31 天滚动定位最小 ROW 值的唯一窗口。
数据
drop table if exists #tTEST;
go
select * INTO #tTEST from (values
(1, 'abc', '1/1/2019'),
(2, 'abc', '1/7/2019'),
(3, 'abc', '1/21/2019'),
(4, 'abc', '2/2/2019'),
(5, 'abc', '2/9/2019'),
(6, 'abc', '3/6/2019'),
(7, 'def', '1/5/2019'),
(8, 'def', '2/1/2019'),
(9, 'def', '2/8/2019')) V([ROW], ID, INDEX_DATE);
询问
;with
rn_cte([ROW], ID, INDEX_DATE, rn) as (
select *, row_number() over (partition by ID order by INDEX_DATE)
from #tTEST),
min_cte([ROW], ID, INDEX_DATE, rn) as (select * from rn_cte where rn=1),
diff_cte([ROW], ID, INDEX_DATE, rn, day_diff) as (
select rn.*, datediff(d, mc.INDEX_DATE, rn.INDEX_DATE)/31
from rn_cte rn
join min_cte mc on rn.ID=mc.ID
where rn.rn=1
or datediff(d, mc.INDEX_DATE, rn.INDEX_DATE)/31>0),
min_diff_cte as (
select ID, day_diff, min([ROW]) min_row
from diff_cte
group by ID, day_diff)
select t.*
from min_diff_cte mdc
join #tTEST t on mdc.min_row=t.ROW
order by 1;
输出
ROW ID INDEX_DATE
1 abc 1/1/2019
4 abc 2/2/2019
6 abc 3/6/2019
7 def 1/5/2019
9 def 2/8/2019
推荐阅读
- python - 关闭通过 pty.openpty() 创建的 PseudoTTY
- ruby-on-rails - 在 Rails 6 中为 webpacker 使用 erb 集成的原因是什么?
- javascript - 错误:使用 createReadStream 时无法创建长度超过 0x3fffffe7 个字符的字符串
- javascript - 倒数计时器在给定的日期和时间显示错误
- ios - SwiftUI 点击手势阻止列表中的项目删除操作
- python - 在基类中操作函数文档字符串和签名
- flutter - 如何在底部导航栏颤动中重新加载特定选项卡?
- c - 当我想销毁 GtkWindows 时,gtk_main_quit 和 gtk_widget_destroy 有什么区别?
- python - 在启动时修复 sessionNotFound
- php - MySQL 锁定 - 更新 - 解锁