登録日から X 日以内に「購入」イベントが発生した「登録」エントリの数をカウントします (日付ごとにグループ化)

Question

次のようなテーブルがあります:

<表类=“s-表”> <标题> id タイムスタンプ ＃＃＃Eメール＃＃＃ 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 日以内のコンバージョンをクエリして、各登録コンバージョンを登録日 (コンバージョン日ではなく、簡単です) に割り当てる方法を理解しようとすると、次のようになります。これはかなりの挑戦です。 これはこれまでの私のクエリです: リーリー これにより、次の結果が得られます: ＃＃＃日付＃＃＃ ＃＃＃登録する＃＃＃ ＃＃＃変換＃＃＃

2021-07-15

3

1

2021-07-16<表类=“s-表”> <标题>0 2 私が理想的に必要とするのは次のようなものです (15 日の 3 つの登録イベントに 3 つの購入イベントが関連付けられているため、15 日に 3 つのコンバージョンが割り当てられ、16 日に割り当てられないのはなぜですか): <正文> ＃＃＃日付＃＃＃ ＃＃＃登録する＃＃＃ ＃＃＃変換＃＃＃ 2021-07-15 3 3 2021-07-16 0 0

<表类=“s-表”> <标题> それは意味がありますか? この click_tracking テーブルのサイズは 100 万レコードまたは 2 レコードであり、クラッシュさせるために何度も JOINS を試みました。そのため、どんなクエリでも機能するわけではありません... この問題を効率的に解決し、このタスクを達成するためにクエリを変更する方法はありますか?

Answer 1

この種のクエリを実行するにはウィンドウ関数が必要です:

リーリー

各電子メールの最初のレコードが常に Registration であり、2 番目のレコード (存在する場合) が常に Purchase であると仮定すると、電子メールのタイプと日付レコードが取得されます。最初の 2 レコードを一度に。その後、2 つのイベントの間に 7 日以上の間隔が空かないように追加のフィルターを適用しながら、サインアップと購入を別々に簡単にカウントできます。

timestamp にキーがある場合、クエリは 100 万行でも十分に高速になるはずです。