sql - 如何从原始事件的 SQL 表中计算漏斗分析？

Question

如何根据原始事件的 SQL 表计算漏斗分析？

例如，如果漏斗是 event1 -> event2 -> event3，这就是数据:

user1, event1, time1
user1, event2, time2
user1, eventOther, time3
user2, event2, time4
user2, event1, time5
user3, event1, time6
user3, event2, time7
user4, event1, time8
user5, event1, time9
user6, event3, time10
user1, event3, time11
user1, event1, time12
user1, event3, time13

那么漏斗是:

event1: 5 (users 1, 2, 3, 4, 5)
event2: 2 (users 1 and 3)
event3: 1 (user 1 only)

类似于这个问题:SQL or OLAP schema design for funnel analysis

有点像:

Query 1: select distinct user ids where event1
Query 2: select distinct user ids where event1 AND THEN event2 (is there an "AND THEN"?)
Query 3: select distinct user ids where event1 AND THEN event2 AND THEN event3

Answer 1

通用方法是使用 left join 和聚合:

select count(distinct d1.user) as NumEvent1,
       count(distinct d2.user) as NumEvent2,
       count(distinct d3.user) as NumEvent3
from data d1 left join
     data d2
     on d2.user = d1.user and d2.time > d1.time and d2.event = 'event2' left join
     data d3
     on d3.user = d2.user and d3.time > d2.time and d3.event = 'event3'
where d1.event = 'event1' ;