등록일로부터 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일 이내에 전환을 쿼리한 다음 각 등록의 전환을 등록 날짜(전환 날짜 대신 쉽게)에 할당하는 방법을 알아내려고 노력한 결과 꽤 도전이 될 것입니다.

지금까지의 쿼리는 다음과 같습니다.

으아아아

이 결과는 다음과 같습니다.

<表类=“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

이러한 쿼리를 수행하려면 창 기능이 필요합니다.

으아악

각 이메일의 첫 번째 기록은 항상 注册，第二条记录（如果有）始终是购买이라고 가정하면 해당 이메일 유형과 날짜의 처음 2개 기록을 한 번에 받게 됩니다. 그런 다음 가입과 구매를 별도로 쉽게 계산할 수 있으며, 두 이벤트 사이에 7일을 넘지 않도록 추가 필터를 적용할 수 있습니다.

키가 timestamp에 있으면 쿼리는 100만 행에서도 충분히 빨라야 합니다.