Clickhouse中高阶函数使用案例

文章摘要：Clickhouse中高阶函数的一写常见使用案例，包含滑动窗口计算、分组计算TopK值，时序数据求diff、漏斗函数、留存函数

案例一：滑动窗口计算

需求描述

1、创建表

CREATE TABLE test_windows_function
(
    `dt` Date,
    `vales` Int64
)
ENGINE = Memory

2、插入数据

insert into test_windows_function(dt,vales) values('2020-01-01',5),('2020-01-02',10),('2020-01-03',15),('2020-01-04',20),('2020-01-05',25),('2020-01-06',30),('2020-01-07',35),('2020-01-08',40),('2020-01-09',45),('2020-01-10',50)

3、查看数据

┌─────────dt─┬─vales─┐
│ 2020-01-01 │     5 │
│ 2020-01-02 │    10 │
│ 2020-01-03 │    15 │
│ 2020-01-04 │    20 │   
│ 2020-01-05 │    25 │
│ 2020-01-06 │    30 │
│ 2020-01-07 │    35 │
│ 2020-01-08 │    40 │
│ 2020-01-09 │    45 │
│ 2020-01-10 │    50 │
└────────────┴───────┘

4、预期效果

求top3(N)的最大值、最小值、平均值、求和

┌─────────dt─┬─vales─┬─max─┬─min─┬─ave─┬─sum─┐
│ 2020-01-01 │     5 │  15 │   5 │  10 │  30 │
│ 2020-01-02 │    10 │  20 │  10 │  15 │  45 │
│ 2020-01-03 │    15 │  25 │  15 │  20 │  60 │
│ 2020-01-04 │    20 │  30 │  20 │  25 │  75 │
│ 2020-01-05 │    25 │  35 │  25 │  30 │  90 │
│ 2020-01-06 │    30 │  40 │  30 │  35 │ 105 │
│ 2020-01-07 │    35 │  45 │  35 │  40 │ 120 │
│ 2020-01-08 │    40 │  50 │  40 │  45 │ 135 │
│ 2020-01-09 │    45 │  50 │  45 │47.5 │  95 │
│ 2020-01-10 │    50 │  50 │  50 │  50 │  50 │
└────────────┴───────┴─────┴─────┴─────┴─────┘

解决方案

方案一：

SQL逻辑

• 使用with语句按照dt字段排序，得到原始数据 [5,10,15,20,25,30,35,40,45,50]
• arraySlice(vales_arr, shift + 1, 3)，每次都去前三个数。eg:第一次 [5,10,15]
• arrayReduce(‘avg’, arraySlice(vales_arr, shift + 1, 3),计算平均数
• arrayMap函数，类似做个for循环

WITH
    (
        SELECT groupArray(vales)
        FROM
        (
            SELECT vales
            FROM test_windows_function
            ORDER BY dt ASC
        )
    ) AS vales_arr
SELECT
    vales_arr,
    arrayMap(shift -> arrayReduce('avg', arraySlice(vales_arr, shift + 1, 3