計算註冊日期 X 天內發生「購買」事件的「已註冊」條目數（按日期分組）

Question

我有一個這樣的表：

<表类=“s-表”> <标题> id 時間戳 電子郵件 ip 事件 <正文> 1 2021-07-15 00:01:00 demo@demo.com 11.11.11.11 註冊 2 2021-07-15 00:04:00 demo@demo.com 11.11.11.11 購買 3 2021-07-15 00:07:00 test@test.com 22.22.22.22 註冊 4 2021-07-15 00:08:00 someone@else.com 33.33.33.33 註冊 5 2021-07-16 00:01:00 test@test.com 22.22.22.22 購買 6 2021-07-16 00:02:00 someone@else.com 33.33.33.33 購買

追蹤所有使用者的電子郵件、IP、日期/時間和事件（註冊和購買）。

現在，我正在嘗試對 a) 註冊和 b) 轉換進行每日統計（註冊後 7 天內發生的購買，分配給該電子郵件/IP 的初始註冊日期，而不是購買日期）。

我可以輕鬆計算出a) 註冊...但試圖弄清楚如何查詢7 天內的轉化，然後將每個註冊的轉化分配給註冊日期（而不是轉化日期，這很容易） ，事實證明這是一個相當大的挑戰。

這是我迄今為止的查詢：

选择日期（时间戳）作为日期，
SUM(CASE WHEN event = '注册' THEN 1 ELSE 0 END) AS 注册，
SUM(CASE WHEN event = '购买' THEN 1 ELSE 0 END) AS 转化
来自点击跟踪
哪里日期（时间戳）<='2021-07-31'
和日期（时间戳）>='2021-07-01'
按日期分组
按日期排序

這給了我以下結果：

<表类=“s-表”> <标题> 日期 註冊 轉化 <正文> 2021-07-15 3 1 2021-07-16 0 2

我理想中需要的是這樣的（3 個購買事件與 15 日的 3 個註冊事件相關聯，因此為什麼 3 個轉換被分配給 15 日，而沒有分配給 16 日）：

<表类=“s-表”> <标题> 日期 註冊 轉化 <正文> 2021-07-15 3 3 2021-07-16 0 0

有道理嗎？

請記住，這個 click_tracking 表的大小有一百萬或兩條記錄，而且我已經多次嘗試在其自身上使用 JOINS 使其崩潰，因此並非任何查詢都可以執行...

知道如何有效地解決這個問題並更改我的查詢來完成這個任務嗎？

Answer 1

您需要視窗函數來執行此類查詢：

与组合 AS (
  选择日期（时间戳）作为日期0，
  电子邮件，
  FIRST_VALUE(事件) OVER(按电子邮件分区 ORDER BY 当前行和 0 个后续行之间的时间戳行) AS event1,
  NTH_VALUE(事件,2) OVER(按电子邮件分区 ORDER BY 当前行和后续 1 行之间的时间戳行) AS event2,
  FIRST_VALUE(日期(时间戳)) OVER(按电子邮件分区 ORDER BY 1 PRECEDING AND 1 FOLLOWING 之间的时间戳行) AS date1,
  NTH_VALUE(DATE(时间戳),2) OVER(按电子邮件分区 ORDER BY 1 PRECEDING AND 1 FOLLOWING 之间的时间戳行) AS date2
来自点击跟踪
WHERE 时间戳位于“2021-07-01 00:00:00”和“2021-07-30 23:59:59”之间）
选择日期 0 作为日期，
  SUM(CASE WHEN event1='注册' THEN 1 ELSE 0 END) AS 注册，
  SUM(CASE WHEN event1='注册' AND event2='购买' AND DATEDIFF(date2,date1) < 8 THEN 1 ELSE 0 END) AS 转化
从组合
按 1 分组

假設對於每封電子郵件，第一筆記錄始終是註冊，第二筆記錄（如果有）始終是購買，您將獲得該電子郵件的類型和日期一次記錄前2 筆記錄。然後，您可以輕鬆地分別統計註冊和購買量，同時套用附加篩選條件，使 2 個事件之間的間隔不超過 7 天。

如果您在 timestamp 上有一個鍵，那麼即使有 100 萬行，查詢也應該足夠快。