ClickHouse UBT 不包含细分维度的 windowFunnel 计算

发表于 分类于

Funnel 计算（不包含细分维度）

查询条件:

1
2
3
4
5
1. 漏斗流程
   login_in -> $pageview -> $end -> $startup -> $end -> $startup
2. 对第一层 login_in 进行了 screen_width > 500 的筛选
3. 时间范围 2021-07-01 ~ 2021-07-09
4. 转化周期 1 天
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
SELECT level_index,count(1) FROM
(
    SELECT  distinct_id,
        arrayWithConstant(level, 1) levels,
        arrayEnumerate( levels ) b, -- for_test_col
        arrayJoin(arrayEnumerate( levels )) level_index
      FROM (
        SELECT
          distinct_id,
          windowFunnel(86400)(
            time,
            xwhat = 'login_in',
            xwhat = '$pageview',
            xwhat = '$end',
            xwhat = '$startup',
            xwhat = '$end',
            xwhat = '$startup'
          ) AS level
        FROM (
          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-09' and xwhat = 'login_in' and screen_width > 500 )
          or (toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-10' and xwhat in ('$pageview','$end','$startup'))
        )
        GROUP BY distinct_id
    )
)
group by level_index
ORDER BY level_index;