如何查找每個用戶ID前24小時的事件數

我正在研究雪花來解決問題。我想找到每個用戶 ID 前 24 小時的事件數。

這是我正在處理的資料庫表的片段。為了簡化，我修改了表格并使用了沒有時間的日期格式。

我使用以下方法來查找每個 user_id 的最小事件時間。

SELECT user_id, client_event_time,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY client_event_time) row_number,
       MIN(client_event_time) OVER (PARTITION BY user_id) MinEventTime
FROM Data
ORDER BY user_id, client_event_time;

然后我試圖找出最小事件時間和client_event_time的差值，如果差值小于等于24，我就統計了client_event_time。

with NewTable as (
        (SELECT user_id,client_event_time, event_type,
        row_number() over (partition by user_id order by CLIENT_EVENT_TIME) row_number,
        MIN(client_event_time) OVER (PARTITION BY user_id) MinEventTime
        FROM Data
        ORDER BY user_id, client_event_time))
    
SELECT user_id,  
        COUNT(case when timestampdiff(hh, client_event_time, MinEventTime) <= 24  then 1 else 0 end) AS duration
FROM    NEWTABLE
GROUP BY user_id

我得到以下結果：

我想找到以下結果：

你能幫我解決這個問題嗎？謝謝！

uj5u.com熱心網友回復：

這看起來像是視窗函式的問題！我很喜歡他們。

這是您的示例資料

DECLARE @table TABLE (user_id INT, client_event_time DATETIME)
INSERT INTO @table (user_id, client_event_time) VALUES
(1, '2022-07-28 13:30:00'),
(1, '2022-07-29 08:30:00'),
(1, '2022-08-21 12:34:56'),
(2, '2022-07-29 08:30:00'),
(2, '2022-07-30 13:30:00'),
(2, '2022-08-03 12:34:56')

我增加了幾個小時，所以我們可以更輕松地查看 24 小時視窗。對于 user_id 1，我們可以看到他們在初始事件后的 24 小時內發生了 2 個事件。對于 user_id 2，只有第一個。我們可以使用 MIN OVER 以及實際的日期時間來捕獲它。

SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time
  FROM @table 

user_id FirstEventDateTime      client_event_time
-------------------------------------------------------
1       2022-07-28 13:30:00.000 2022-07-28 13:30:00.000
1       2022-07-28 13:30:00.000 2022-07-29 08:30:00.000
1       2022-07-28 13:30:00.000 2022-08-21 12:34:56.000
2       2022-07-29 08:30:00.000 2022-07-29 08:30:00.000
2       2022-07-29 08:30:00.000 2022-07-30 13:30:00.000
2       2022-07-29 08:30:00.000 2022-08-03 12:34:56.000

現在我們將結果集中的第一個日期時間和每一行的日期時間放在一起，我們可以進行比較：

SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time, CASE WHEN DATEDIFF(HOUR,MIN(client_event_time) OVER (PARTITION BY user_id), client_event_time)  < 24 THEN 1 ELSE 0 END AS EventsInFirst24Hours
  FROM @table 

user_id FirstEventDateTime      client_event_time       EventsInFirst24Hours
----------------------------------------------------------------------------
1       2022-07-28 13:30:00.000 2022-07-28 13:30:00.000 1
1       2022-07-28 13:30:00.000 2022-07-29 08:30:00.000 1
1       2022-07-28 13:30:00.000 2022-08-21 12:34:56.000 0
2       2022-07-29 08:30:00.000 2022-07-29 08:30:00.000 1
2       2022-07-29 08:30:00.000 2022-07-30 13:30:00.000 0
2       2022-07-29 08:30:00.000 2022-08-03 12:34:56.000 0

現在我們有了一個指標，告訴我們前 24 小時內發生了哪些事件，我們真正需要的只是對其求和，但 SQL Server 意味著在另一個聚合中使用視窗函式，因此我們需要作弊并將其放入子查詢中.

SELECT user_id, SUM(EventsInFirst24Hours) AS CountOfEventsInFirst24Hours
  FROM (
        SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time, CASE WHEN DATEDIFF(HOUR,MIN(client_event_time) OVER (PARTITION BY user_id), client_event_time)  < 24 THEN 1 ELSE 0 END AS EventsInFirst24Hours
          FROM @table 
       ) a
 GROUP BY user_id

這讓我們得到了結果：

user_id CountOfEventsInFirst24Hours
-----------------------------------
1       2
2       1

關于視窗函式發生了什么： MIN- 我們希望它進行的聚合。常見的聚合函式具有視窗對應項。 (client_event_time)- 我們想要達到的價值。 OVER (PARTITION BY user_id)- 我們要設定的視窗。在這種情況下，我們想知道每個 user_id 的最小日期時間。我們可以根據需要按任意多的列進行磁區。您還可以使用包含任意數量列的 ORDER BY，但這在此處不是必需的。例如： OVER (PARTITION BY column1, column2 ORDER BY column4, column5 DESC) 對 column1 和 column2 進行磁區（或分組），并按 column4 和 column5 降序排列。

uj5u.com熱心網友回復：

更容易完成qualify

with cte as
(select *
 from mytable
 qualify event_time<=min(event_time) over (partition by user_id)   interval '24 hours')

select user_id, count(*) as counts
from cte
group by user_id

uj5u.com熱心網友回復：

如果您想要在最小事件時間的 24 小時左右的事件計數，您可以使用 CTE 組，它為您提供所有用戶的所有最小事件集

剩下的就是獲取時間限制內的所有行

WITH min_data as
(SELECT user_id,MIN(client_event_time) mindate FROM data GROUP BY user_id)
  SELECT d.user_id, COUNT(*) 
  FROM data d JOIN min_data md ON d.user_id = md.user_id WHERE client_event_time <= mindate   INTERVAL '24 hour'
GROUP BY d.user_id
ORDER BY d.user_id

標籤：sql数据库雪花云数据平台行号with-语句

上一篇：從sql中的相對日期獲取最近n個月的資料

下一篇：使用具有多個增量（如毫秒時間戳）的鍵的復合鍵上的范圍進行過濾時的過濾器 排序性能