Compter le nombre d'entrées « enregistrées » avec un événement « achat » dans les X jours suivant la date d'inscription (regroupées par date)

Question

J'ai une table comme celle-ci :

<表类=“s-表”> <标题> id Horodatage E-mail ip Événements <正文> 1 2021-07-15 00:01:00 demo@demo.com 11.11.11.11 S'inscrire 2 2021-07-15 00:04:00 demo@demo.com 11.11.11.11 Acheter 3 2021-07-15 00:07:00 test@test.com 22.22.22.22 S'inscrire 4 2021-07-15 00:08:00 quelqu'un@else.com 33.33.33.33 S'inscrire 5 2021-07-16 00:01:00 test@test.com 22.22.22.22 Acheter 6 2021-07-16 00:02:00 quelqu'un@else.com 33.33.33.33 Acheter

Suivez les e-mails, adresses IP, dates/heures et événements de tous les utilisateurs (inscriptions et achats).

À l'heure actuelle, j'essaie de faire des statistiques quotidiennes sur a) les inscriptions et b) les conversions (les achats effectués dans les 7 jours suivant l'inscription, la date d'inscription originale attribuée à cet e-mail/IP, pas la date d'achat).

Je pourrais facilement comprendre a) les inscriptions... mais en essayant de comprendre comment interroger les conversions dans les 7 jours, puis attribuer la conversion de chaque inscription à la date d'inscription (au lieu de la date de conversion, ce qui est facile), il s'est avéré être un tout un défi.

Voici ma requête jusqu'à présent :

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

Cela me donne le résultat suivant :

<表类=“s-表”> <标题> Date S'inscrire Conversion <正文> 2021-07-15 3 1 2021-07-16 0 2

Ce dont j'ai idéalement besoin, c'est de quelque chose comme ça (3 événements d'achat associés à 3 événements d'inscription le 15, d'où pourquoi 3 conversions sont attribuées au 15 et aucune au 16) :

<表类=“s-表”> <标题> Date S'inscrire Conversion <正文> 2021-07-15 3 3 2021-07-16 0 0

Est-ce que cela a du sens ?

Gardez à l'esprit que cette table click_tracking a une taille d'un million ou deux enregistrements, et j'ai essayé JOINS sur elle-même plusieurs fois pour la faire planter, donc n'importe quelle requête ne fonctionnera pas...

Une idée sur la façon de résoudre ce problème efficacement et de modifier ma requête pour accomplir cette tâche ?

Answer 1

Vous avez besoin de fonctions de fenêtre pour effectuer de telles requêtes :

与组合 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 分组

En supposant que pour chaque e-mail, le premier enregistrement est toujours 注册，第二条记录（如果有）始终是购买, vous obtiendrez les 2 premiers enregistrements du type et de la date de cet e-mail à la fois. Vous pourrez ensuite compter facilement les inscriptions et les achats séparément, tout en appliquant des filtres supplémentaires pour qu'il n'y ait pas plus de 7 jours entre 2 événements.

Si vous avez une clé sur timestamp alors la requête devrait être assez rapide même avec 1 million de lignes.