我有一個帶有事件條目的日志表（MS SQL SERVER）（事件是用戶操作，如“用戶登錄”、“用戶查看物體 A”等）。
諸如“用戶查看的物體 A”之類的某些事件可能會在短時間內發生多次。例如，如果用戶在他的瀏覽器中來回前進，他可能在一分鐘內多次進入物體 A 的頁面，并且將記錄多個“用戶查看”事件。
對于我的分析儀表板，我想計算用戶查看物體 A 的次數，但我想“去抖動”結果。我想將多個彼此靠近的“用戶視圖”事件視為一個“用戶視圖”事件。具體來說，我只想在距離上一個事件超過 30 分鐘時才考慮一個??新的“用戶視圖”事件。
所以有一個像這樣的表格（最后一列是我的評論，為了清楚起見）：
| 時間戳 | evt_type | 用戶身份 | entity_id | *與上一個事件的時間差異 |
|---|---|---|---|---|
| 15:30 | ENTITY_VIEW | U1 | E1 | NULL（第一個視圖） |
| 15:38 | ENTITY_VIEW | U1 | E1 | 8分鐘 |
| 16:05 | ENTITY_VIEW | U1 | E1 | 28分鐘 |
| 16:50 | ENTITY_VIEW | U1 | E1 | 45 分鐘（這算作新視圖） |
| 17:15 | ENTITY_VIEW | U1 | E1 | 25分鐘 |
| 17:44 | ENTITY_VIEW | U1 | E1 | 29分鐘 |
| 18:30 | ENTITY_VIEW | U1 | E1 | 46分鐘（這算另一個視圖） |
我想確定用戶“查看”了物體 3 次。
確定這一點的查詢是什么？我嘗試了 LEAD、LAG、PARTITION BY 和其他組合，但我似乎沒有找到正確的方法，因為我不是 SQL 專家。
uj5u.com熱心網友回復：
應該很容易LAG()獲取上一個時間戳并檢查差異。會說您的列 [timestamp] 是一種奇怪的資料型別，那么不同的日期呢？日期是否有單獨的列？
從上一個記錄回傳 >30 分鐘的記錄
WITH cte_DeltaSinceLastView AS (
SELECT *
/*Grab previous record for each user_id/entity_id combo*/
,PrevTimestamp = LAG([timestamp]) OVER (PARTITION BY [user_id],[entity_id] ORDER BY [timestamp])
FROM YourTable
) AS A(ID,[user_id],[entity_id],[timestamp])
)
SELECT *,MinutesSinceLastView = DATEDIFF(minute,PrevTimestamp,[Timestamp])
FROM cte_DeltaSinceLastView
WHERE DATEDIFF(minute,PrevTimestamp,[timestamp]) > 30 /*Over 30 minutes between last view*/
OR PrevTimestamp IS NULL /*First view will not have previous timestamp to compare against*/
uj5u.com熱心網友回復：
您可以嘗試的是相關子查詢，它忽略前行 30 分鐘內的任何行，其余行應該是符合條件的行（即存在 30 分鐘以上的間隙）。看看這對你有用嗎？
select Sum(vc) as ViewedCount
from (
select case when exists (
select * from t t2
where t2.timestamp > t.timestamp
and t2.evt_type = t.evt_type
and t2.user_id = t.user_id
and t2.entity_id = t.entity_id
and DateDiff(minute, t.timestamp,t2.timestamp) <30
) then 0 else 1 end vc
from t
)b;
這假定Timestamp是一種時間資料型別。這不適用于跨日邊界，但同樣的概念應該適用于日期時間型別。
演示為小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/460692.html
標籤：sql服务器 tsql sql-server-2016
上一篇：使用JOIN從三個表中獲取資料