Kira bilangan penyertaan "berdaftar" dengan acara "pembelian" dalam masa X hari dari tarikh pendaftaran (dihimpunkan mengikut tarikh)

Question

Saya ada meja seperti ini:

<表类=“s-表”> <标题> id Cap masa E-mel ip Acara <正文> 1 2021-07-15 00:01:00 demo@demo.com 11.11.11.11 Daftar 2 2021-07-15 00:04:00 demo@demo.com 11.11.11.11 Beli 3 2021-07-15 00:07:00 test@test.com 22.22.22 Daftar 4 2021-07-15 00:08:00 someone@else.com 33.33.33.33 Daftar 5 2021-07-16 00:01:00 test@test.com 22.22.22 Beli 6 2021-07-16 00:02:00 someone@else.com 33.33.33.33 Beli

Jejak semua e-mel pengguna, IP, tarikh/masa dan acara (pendaftaran dan pembelian).

Sekarang ini, saya sedang cuba melakukan statistik harian tentang a) pendaftaran dan b) penukaran (pembelian yang berlaku dalam masa 7 hari selepas pendaftaran, tarikh pendaftaran asal yang ditetapkan kepada e-mel/IP tersebut, bukan tarikh pembelian).

Saya boleh mengetahui dengan mudah a) pendaftaran... tetapi cuba memikirkan cara untuk menanyakan penukaran dalam masa 7 hari dan kemudian menetapkan setiap penukaran pendaftaran kepada tarikh pendaftaran (bukan tarikh penukaran, yang mudah), ternyata menjadi satu cabaran yang cukup.

Ini adalah pertanyaan saya setakat ini:

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

Ini memberikan saya hasil berikut:

<表类=“s-表”> <标题> Tarikh Daftar Penukaran <正文> 2021-07-15 3 1 2021-07-16 0 2

Sebaik-baiknya saya perlukan sesuatu seperti ini (3 acara pembelian dikaitkan dengan 3 acara pendaftaran pada 15hb, oleh itu mengapa 3 penukaran diberikan kepada yang ke-15 dan tiada satu pun kepada yang ke-16):

<表类=“s-表”> <标题> Tarikh Daftar Penukaran <正文> 2021-07-15 3 3 2021-07-16 0 0

Adakah ia masuk akal?

Perlu diingat bahawa jadual click_tracking ini bersaiz satu juta atau dua rekod, dan saya telah mencuba JOINS sendiri beberapa kali untuk membuatnya ranap, jadi bukan sebarang pertanyaan akan berfungsi...

Ada idea bagaimana untuk menyelesaikan masalah ini dengan cekap dan menukar pertanyaan saya untuk menyelesaikan tugas ini?

Answer 1

Anda memerlukan fungsi tetingkap untuk melaksanakan pertanyaan sedemikian:

与组合 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) 
Dengan mengandaikan bahawa untuk setiap e-mel, rekod pertama sentiasa 注册，第二条记录（如果有）始终是购买, anda akan mendapat 2 rekod pertama jenis dan tarikh e-mel itu sekali gus. Anda kemudiannya boleh mengira pendaftaran dan pembelian secara berasingan dengan mudah sambil menggunakan penapis tambahan supaya tidak lebih daripada 7 hari antara 2 acara. 
Jika anda mempunyai kunci timestamp maka pertanyaan harus cukup pantas walaupun dengan 1 juta baris.