我正在尋找一個 SQL 腳本來查找過去 1 小時內包含超過 2 個條目的資料。我有一個包含 user_id 和 event_time 的表。我想要一種方法來確定 user_id 在過去 1 小時內是否有超過 1 個條目。
到目前為止，我已經嘗試過以下方法：
- 創建臨時表以放置所有重復條目：
SELECT a.*
INTO #temp
FROM Table a
JOIN (
SELECT USERID, COUNT(*) AS Duplicates
FROM Table
GROUP BY userid
HAVING count(*) > 1
) AS b ON a.userid = b.USERID
- 運行 self Joins 以獲取時差為 1 小時或更短的記錄：
SELECT a.*
FROM #temp a
INNER JOIN #temp b ON a.userid = b.USERID
WHERE DATEDIFF(hour, a.EVENTTIME, b.EVENTTIME) = 1
運行第一個腳本后，它會為重復資料提供大約 800 多行。但是在運行第二個腳本后，我得到的資料以數千為單位。有人可以幫忙嗎？
uj5u.com熱心網友回復：
cross apply 可用于根據您的標準獲取每個事件的所有相關事件，如下所示：
With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
)
Select a.ID, a.USERID, a.EVENTTIME, T.ID, T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select ID, USERID, EVENTTIME
From CTE
Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
And USERID=a.USERID And ID<>a.ID) As T
Order by a.ID, a.USERID, a.EVENTTIME, T.ID, T.USERID, T.EVENTTIME
或者您可以在不系結到特定事件的情況下獲取事件串列：
With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
)
Select T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select USERID, EVENTTIME
From CTE
Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
And USERID=a.USERID And ID<>a.ID) As T
Group by T.USERID, T.EVENTTIME
資料庫<>小提琴
要僅獲取最后一小時的事件，您可以Where在CTE.
With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
Where EVENTTIME Between dateadd(minute, -60, GetDate()) And GetDate()
)
Select T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select USERID, EVENTTIME
From CTE
Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
And USERID=a.USERID
And ID<>a.ID) As T
Group by T.USERID, T.EVENTTIME
uj5u.com熱心網友回復：
user_id按照日期差異的小時數順序為每組指定一個行號。請記住過濾event_date過去 1 小時內具有的行。
詢問
;with cte as(
select [rn] = row_number() over(
partition by [user_id]
order by [user_id], datediff(hour, [event_time], getdate())
), *
from [your_table_name]
where datediff(hour, [event_time], getdate()) < 2
)
select * from [your_table_name] as [t1]
where exists(
select 1 from cte as [t2]
where [t1].[user_id]= [t2].[user_id]
and [t2].[rn] > 1
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/398062.html
標籤：sql sql-server 数据库 查询语句
上一篇：如何找到一組值的精確匹配？