计算注册日期 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 万行，查询也应该足够快。