如何在5-10分钟的时间差内拉取重复记录？
如何在5-10分钟的时间差内拉取重复记录？
提问于 2018-04-21 00:26:35
SQL数据库。
我将不得不确定在5- 6分钟范围内发生的重复事务的列表。
BillId Trans DateTime
A100125 Paid 2018-04-18 11:21:40.873 - Valid Transaction
A100125 Paid 2018-04-18 11:24:40.873 - Duplicate Transaction
A100125 Paid 2018-04-18 11:30:40.873 - Duplicate Transaction
A100125 Paid 2018-04-18 12:30:40.873 - Valid Transaction我可以为日期范围生成报告。这大约是5-10分钟的平均差异。
回答 4
Stack Overflow用户
发布于 2018-04-21 01:20:12
这里有一些东西
简而言之，在case语句中有一个eXiste子查询来比较每一行。这不是最有效的，因为datediff不是sargable
declare @tabel Table (BillId varchar(8000), Trans varchar(50), DateOfSomethingProbablyTheTransaction DateTime)
declare @interval int = 6
insert into @tabel
values ('A100125', 'Paid', '2018-04-18 11:21:40.873'), --valid
('A100125', 'Paid', '2018-04-18 11:24:40.873'), -- Duplicate Transaction
('A100125', 'Paid', '2018-04-18 11:30:40.873'), -- Duplicate Transaction
('A100125', 'Paid', '2018-04-18 12:30:40.873') -- Valid Transaction)
select *,
case when exists(select 1
from @tabel t2
where t1.BillId = t2.BillId
and t1.Trans = t2.Trans
and DATEDIFF(MINUTE, t2.DateOfSomethingProbablyTheTransaction, t1.DateOfSomethingProbablyTheTransaction) <= @interval
and t1.DateOfSomethingProbablyTheTransaction > t2.DateOfSomethingProbablyTheTransaction)
then 'Invalid' else'valid' end as Validity
from @tabel t1还请注意，这可以重写为连接，如果您希望它看起来更好，因为查询优化器只是在后台执行连接，如果事务状态是无关的，则可以删除它以检查重复项
Stack Overflow用户
发布于 2018-04-21 01:54:45
我偷了其他人的设置：
但这里有一个使用lag的答案：
declare @tabel Table (BillId varchar(8000), Trans varchar(50), DateOfSomethingProbablyTheTransaction DateTime)
declare @interval int = 6
insert into @tabel
values ('A100125', 'Paid', '2018-04-18 11:21:40.873'), --valid
('A100125', 'Paid', '2018-04-18 11:24:40.873'), -- Duplicate Transaction
('A100125', 'Paid', '2018-04-18 11:30:40.873'), -- Duplicate Transaction
('A100125', 'Paid', '2018-04-18 12:30:40.873') -- Valid Transaction)
(
select BillId, Trans, DateOfSomethingProbablyTheTransaction dt
,lag(DateOfSomethingProbablyTheTransaction,1) over (partition by BillID,Trans order by DateOfSomethingProbablyTheTransaction)
,case when datediff(minute
,lag(DateOfSomethingProbablyTheTransaction,1) over (partition by BillID,Trans order by DateOfSomethingProbablyTheTransaction)
,DateOfSomethingProbablyTheTransaction)<=6
then 'Invalid'
else 'Valid'
end DataCheck
from @tabel
)结果：
BillId Trans dt (No column name) DataCheck
A100125 Paid 2018-04-18 11:21:40.873 NULL Valid
A100125 Paid 2018-04-18 11:24:40.873 2018-04-18 11:21:40.873 Invalid
A100125 Paid 2018-04-18 11:30:40.873 2018-04-18 11:24:40.873 Invalid
A100125 Paid 2018-04-18 12:30:40.873 2018-04-18 11:30:40.873 Valid并再次使用CTE：(不确定LAG何时启动)
;with base as
(
select BillId, Trans, DateOfSomethingProbablyTheTransaction dt
,rn = row_number() over (partition by BillId, Trans order by DateOfSomethingProbablyTheTransaction)
from @tabel
)
select base.*
,prior.dt
,Test = case when datediff(minute,isnull(prior.dt,'1/1/1900'),base.dt) <=6 then 'Invalid' else 'Valid' end
from base
left join base as [prior] on base.rn-1 = [prior].rn
and base.BillId=[prior].BillId
and base.Trans = [prior].transStack Overflow用户
发布于 2018-04-21 00:55:41
我不明白你需要的是什么，但这可能会有帮助。这使得同一账单id上的事务具有不同的时间，其中时间差小于7分钟。
select *
from Transactions T1
INNER JOIN Transactions T2
ON T1.BillId=T2.BillId
AND T1.DateTime<>T2.DateTime
AND DATEDIFF(MI,T1.DateTime,T2.DateTime)<7如果我误解了，请告诉我。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49946007
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号