1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
| with
t0 as (
select distinct_id, xwhat, toDateTime(toUInt64(round(xwhen/1000))) as time, first_category as jg
from test.event
where toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-09'
and xwhat = 'view_detail'
and is_login = '1'),
t1 as (
select distinct_id, xwhat, time, jg from
t0
union all
(select t2.distinct_id, t2.what as xwhat, t2.time, t0.jg
from
t0
INNER JOIN
(select distinct_id, 'add_item_cart_or_click_buy_now' what, toDateTime(toUInt64(round(xwhen/1000))) as time
from test.event
where toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-10' and (xwhat = 'add_item_cart' or xwhat = 'click_buy_now')) t2
on t0.distinct_id = t2.distinct_id)
union all
(select t2.distinct_id, t2.xwhat, t2.time, t0.jg
from
t0
INNER JOIN
(select distinct_id, xwhat, toDateTime(toUInt64(round(xwhen/1000))) as time
from test.event
where toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-10' and xwhat = 'submit_item_order') t2
on t0.distinct_id = t2.distinct_id)
union all
(select t2.distinct_id, t2.xwhat, t2.time, t0.jg
from
t0
INNER JOIN
(select distinct_id, xwhat, toDateTime(toUInt64(round(xwhen/1000))) as time
from test.event
where toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-10' and xwhat = 'pay_item_order') t2
on t0.distinct_id = t2.distinct_id)),
t2 as (
SELECT
jg,
distinct_id,
min(time) AS TIME,
windowFunnel(86400)(
time,
xwhat = 'view_detail',
xwhat = 'add_item_cart_or_click_buy_now',
xwhat = 'submit_item_order',
xwhat = 'pay_item_order'
) AS level
FROM (
SELECT
distinct_id
, xwhat
, time
, jg
FROM t1
)
GROUP BY distinct_id,jg)
,t3 as (
SELECT
jg,
distinct_id,
TIME,
level,
row_number()over(partition by distinct_id order by level desc, TIME asc) rn
from t2
SETTINGS allow_experimental_window_functions = 1)
,t4 as (
select
distinct_id,
jg,
arrayWithConstant(level, 1) levels,
arrayEnumerate( levels ) b,
arrayJoin(arrayEnumerate( levels )) level_index,
rn
from t3
where rn = 1)
select jg, level_index, cnt
from(select jg, level_index, count(1) cnt
from t4
group by jg,level_index
union all
select '总体' jg, level_index, count(1) cnt
from t4
group by level_index)
order by level_index asc, cnt desc;
|