文章摘要: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