根据不同列值筛选日期上的行

Question

我一直在尝试根据task_dts的值过滤code具有一定值的行。实际上我只想要每个id的行数其中task_dts时间戳落在当前code的code_dts时间戳和后面的code_dts时间戳之间。code.

例如;对于code等于'z'的行，我只想要task_dts在code_dts的值'z'和值'y'范围内的行。对于code等于'y'的行，我只想要task_dts在code_dts范围内的行，用于值'y'和值'x'等

我的表如下所示:

<表类>rowidid代码code_dts任务task_dtstbody><<tr>1z2022-02-01 10:17:08.40300012022-02-01 10:21:27.0000002z2022-02-01 10:17:08.40300022022-02-01 10:21:31.0000003z2022-02-01 10:17:08.40300032022-02-01 12:41:43.0000004y2022-02-01 11:12:13.27000012022-02-01 10:21:27.0000005y2022-02-01 11:12:13.27000032022-02-01 12:41:43.0000006y2022-02-01 11:12:13.27000082022-02-21 14:57:53.0000007x2022-02-21 12:28:50.64700062022-02-21 14:57:53.0000008x2022-02-21 12:28:50.64700072022-02-21 14:57:54.0000009道明>h2022-04-05 13:44:16.03000012022-04-05 14:03:56.57000010道明>h2022-04-05 13:44:16.03000022022-04-05 14:03:56.57000011道明>我2022-04-06 13:44:16.03000012022-04-05 14:03:56.57000012道明>j2022-04-07 13:44:16.03000032022-04-05 14:03:56.570000

可以使用表表达式预先计算时间戳范围。这样，过滤就容易了。

例如:

select t.*
from t
join (
select code, dt, lead(dt) over(order by dt) as next_dt
from (select code, min(code_dts) as dt from t group by code) x
) y on t.code = y.code
where t.task_dts between y.dt and y.next_dt or y.next_dt is null

看了The Impaler的回答后，我终于明白了你的要求:-)

这是基于窗口函数的相同逻辑:

with cte as
( 
select t.*
-- next code_dts, i.e. at least one row will return 
-- the code_dts of the following code 
,lead(code_dts,1,task_dts) over (order by code_dts) as next_dts
from tab as t
)
select *
from cte
qualify task_dts between code_dts
-- assign the next code's dts to all rows within the same code
and max(next_dts) over (partition by code)
; 

很难说哪一个会做得更好…