Zählen Sie die Anzahl der „registrierten“ Einträge mit einem „Kauf“-Ereignis innerhalb von X Tagen nach dem Registrierungsdatum (gruppiert nach Datum).

Question

Ich habe einen Tisch wie diesen:

<表类=“s-表”> <标题> id Zeitstempel E-Mail ip Veranstaltungen <正文> 1 2021-07-15 00:01:00 demo@demo.com 11.11.11.11 Registrieren 2 2021-07-15 00:04:00 demo@demo.com 11.11.11.11 Kaufen 3 2021-07-15 00:07:00 test@test.com 22.22.22.22 Registrieren 4 2021-07-15 00:08:00 someone@else.com 33.33.33.33 Registrieren 5 2021-07-16 00:01:00 test@test.com 22.22.22.22 Kaufen 6 2021-07-16 00:02:00 someone@else.com 33.33.33.33 Kaufen

Verfolgen Sie die E-Mails, IPs, Daten/Zeiten und Ereignisse aller Benutzer (Registrierungen und Einkäufe).

Im Moment versuche ich, tägliche Statistiken zu a) Anmeldungen und b) Conversions (Käufe, die innerhalb von 7 Tagen nach der Anmeldung erfolgen, das ursprüngliche Anmeldedatum, das dieser E-Mail/IP zugewiesen ist, nicht das Kaufdatum) zu erstellen.

Ich könnte a) die Registrierungen leicht herausfinden ... aber der Versuch, herauszufinden, wie man die Konvertierungen innerhalb von 7 Tagen abfragt und dann die Konvertierung jeder Registrierung dem Registrierungsdatum zuordnet (anstelle des Konvertierungsdatums, was einfach ist), hat sich herausgestellt eine ziemliche Herausforderung sein.

Das ist meine bisherige Anfrage:

选择日期（时间戳）作为日期，
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'
按日期分组
按日期排序

Dadurch erhalte ich folgendes Ergebnis:

<表类=“s-表”> <标题> Datum Registrieren Konvertierung <正文> 2021-07-15 3 1 2021-07-16 0 2

Was ich idealerweise brauche, ist so etwas (3 Kaufereignisse verbunden mit 3 Registrierungsereignissen am 15., daher werden 3 Conversions dem 15. und keine dem 16. zugeordnet):

<表类=“s-表”> <标题> Datum Registrieren Konvertierung <正文> 2021-07-15 3 3 2021-07-16 0 0

Macht es Sinn?

Denken Sie daran, dass die Größe dieser click_tracking-Tabelle eine Million oder zwei Datensätze beträgt und ich mehrmals JOINS versucht habe, um sie zum Absturz zu bringen, sodass nicht jede Abfrage funktioniert ...

Irgendeine Idee, wie ich dieses Problem effizient lösen und meine Abfrage ändern kann, um diese Aufgabe zu erfüllen?

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 万行，查询也应该足够快。