clickhouse实现排序排名以及同比环比新老版本对比
一、准备测试数据
首先准备测试表：
CREATE TABLE test_data engine = Memory AS WITH( SELECT ['A','A','A','A','B','B','B','B','B','A','59','90','80','80','65','75','78','88','99','70'])AS dict SELECT dict[number%10+1] AS id, dict[number+11] AS val FROM system.numbers LIMIT 10;
查看数据：
select * from test_data ;
SELECT *
FROM test_data
Query id: 88a6a086-6400-4aeb-9997-2baa7fd2a320
┌─id─┬─val─┐
│ A │ 59 │
│ A │ 90 │
│ A │ 80 │
│ A │ 80 │
│ B │ 65 │
│ B │ 75 │
│ B │ 78 │
│ B │ 88 │
│ B │ 99 │
│ A │ 70 │
└────┴─────┘
二、老版本实现（21版本以下 不包含21版本）
此处使用版本是：20.9.3.45
1）实现排序排名
CH中并没有直接提供对应的开窗函数，需要利用一些特殊函数变相实现，主要会用到下面几个数组函数，它们分别是:
arrayEnumerate arrayEnumerateDense arrayEnumerateUniq
这些函数均接受一个数组作为输入参数，并返回数组中元素出现的位置，例如:
SELECT arrayEnumerate([10, 20, 30, 10, 40]) AS row_number, arrayEnumerateDense([10, 20, 30, 10, 40]) AS dense_rank, arrayEnumerateUniq([10, 20, 30, 10, 40]) AS uniq_rank ┌─row_number──┬─dense_rank──┬─uniq_rank───┐ │ [1,2,3,4,5] │ [1,2,3,1,4] │ [1,1,1,2,1] │ └─────────────┴─────────────┴─────────────┘
我们的目标是实现开窗查询：
ROW_NUMBER() OVER( PARTITION BY id ORDER BY val )==>arrayEnumerate
DENSE_RANK() OVER( PARTITION BY id ORDER BY val )==>arrayEnumerateDense
UNIQ_RANK() OVER( PARTITION BY id ORDER BY val )==>arrayEnumerateUniq
如下：
SELECT id, val, row_number, dense_rank, uniq_rank FROM ( SELECT id, groupArray(val) AS arr_val, arrayEnumerate(arr_val) AS row_number, arrayEnumerateDense(arr_val) AS dense_rank, arrayEnumerateUniq(arr_val) AS uniq_rank FROM ( SELECT * FROM test_data ORDER BY val ASC ) GROUP BY id ) ARRAY JOIN arr_val AS val, row_number, dense_rank, uniq_rank ORDER BY id ASC, row_number ASC, dense_rank ASC Query id: ae812342-2d60-4d6e-9e4e-4a5f97e0670f ┌─id─┬─val─┬─row_number─┬─dense_rank─┬─uniq_rank─┐ │ A │ 59 │ 1 │ 1 │ 1 │ │ A │ 70 │ 2 │ 2 │ 1 │ │ A │ 80 │ 3 │ 3 │ 1 │ │ A │ 80 │ 4 │ 3 │ 2 │ │ A │ 90 │ 5 │ 4 │ 1 │ │ B │ 65 │ 1 │ 1 │ 1 │ │ B │ 75 │ 2 │ 2 │ 1 │ │ B │ 78 │ 3 │ 3 │ 1 │ │ B │ 88 │ 4 │ 4 │ 1 │ │ B │ 99 │ 5 │ 5 │ 1 │ └────┴─────┴────────────┴────────────┴───────────┘ 10 rows in set. Elapsed: 0.005 sec.
2）实现同比/环比，lag/lead实现：
neighbor(column, offset[, default_value])
函数的结果取决于受影响的数据块和数据块中数据的顺序。
如果使用ORDER BY进行子查询，并从子查询外部调用该函数，则可以得到预期的结果。
参数
列-列名或标量表达式。
偏移量—从列的当前行向前或向后的行数。Int64。
默认值-可选。如果偏移量超出块的范围，则返回的值。受影响的数据块的类型。
返回值
如果偏移值不在块边界之外，则当前行偏移距离中的列的值。
如果偏移值超出块边界，则列的默认值。如果给定了默认的_值，则将使用它。
类型：受影响数据块的类型或默认值类型。
借助 neighbor 函数实现:
WITH toDate('2019-01-01') AS start_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money, neighbor(money, -12) AS prev_year, neighbor(money, -1) AS prev_month FROM numbers(16) Query id: 228b6b8b-a4a8-42e5-a976-3cd67f8587cd ┌──date_time─┬─money─┬─prev_year─┬─prev_month─┐ │ 2019-01-01 │ 100 │ 0 │ 0 │ │ 2019-02-01 │ 200 │ 0 │ 100 │ │ 2019-03-01 │ 300 │ 0 │ 200 │ │ 2019-04-01 │ 400 │ 0 │ 300 │ │ 2019-05-01 │ 500 │ 0 │ 400 │ │ 2019-06-01 │ 600 │ 0 │ 500 │ │ 2019-07-01 │ 700 │ 0 │ 600 │ │ 2019-08-01 │ 800 │ 0 │ 700 │ │ 2019-09-01 │ 900 │ 0 │ 800 │ │ 2019-10-01 │ 1000 │ 0 │ 900 │ │ 2019-11-01 │ 1100 │ 0 │ 1000 │ │ 2019-12-01 │ 1200 │ 0 │ 1100 │ │ 2020-01-01 │ 1300 │ 100 │ 1200 │ │ 2020-02-01 │ 1400 │ 200 │ 1300 │ │ 2020-03-01 │ 1500 │ 300 │ 1400 │ │ 2020-04-01 │ 1600 │ 400 │ 1500 │ └────────────┴───────┴───────────┴────────────┘ 16 rows in set. Elapsed: 0.003 sec.
扩展用例：求过去10分钟每分钟都出现并连续出现3次的活跃ip数。
SELECT count( distincts_ip ) FROM ( SELECT s_ip FROM ( SELECT s_ip, dt2 FROM ( SELECT s_ip.groupArray ( dt ) dt_arr, arrayEnumerate ( dt_arr ) dt_arr_enum, arrayMap (( X.y )->( X - y * 60 ), dt_arr, dt_arr_enum ) map_ arr FROM ( SELECT s_ip, found_time dt FROM s_ip_group_view2 WHERE found_time > = 1620583215 AND found_time <= 1620583915 ORDER BY s_ip, dt ) GROUP BY s_ip ) ARRAY JOIN map_arr AS dt2 ) GROUP BY s_ip, dt2 HAVING count()> = 3 );
三、新版本实现（21版本以上 包含21版本）
1）实现排序排名等功能：
rank() over () 并列有间隔，rank值为：1 2 2 4 5
dense_rank() over() 并列不间断，rank值为：1 2 2 3 4
row_number() over() 相同连续排名，rank值为：1 2 3 4 5
SELECT id, val, rank() OVER w AS rank, dense_rank() OVER w AS dense_rank, row_number() OVER w AS row_number, count(*) OVER w AS count, sum(toInt32(val)) OVER w AS sum_v, avg(toInt32(val)) OVER w AS avg_v, max(toInt32(val)) OVER w AS max_v FROM test_data WINDOW w AS (PARTITION BY id ORDER BY val ASC range unbounded preceding) ORDER BY id ASC SETTINGS allow_experimental_window_functions = 1;
┌─id─┬─val─┬─rank─┬─dense_rank─┬─row_number─┬─count─┬─sum_v─┬─────────────avg_v─┬─max_v─┐ │ A │ 59 │ 1 │ 1 │ 1 │ 1 │ 59 │ 59 │ 59 │ │ A │ 70 │ 2 │ 2 │ 2 │ 2 │ 129 │ 64.5 │ 70 │ │ A │ 80 │ 3 │ 3 │ 3 │ 4 │ 289 │ 72.25 │ 80 │ │ A │ 80 │ 3 │ 3 │ 4 │ 4 │ 289 │ 72.25 │ 80 │ │ A │ 90 │ 5 │ 4 │ 5 │ 5 │ 379 │ 75.8 │ 90 │ │ B │ 65 │ 1 │ 1 │ 1 │ 1 │ 65 │ 65 │ 65 │ │ B │ 75 │ 2 │ 2 │ 2 │ 2 │ 140 │ 70 │ 75 │ │ B │ 78 │ 3 │ 3 │ 3 │ 3 │ 218 │ 72.66666666666667 │ 78 │ │ B │ 88 │ 4 │ 4 │ 4 │ 4 │ 306 │ 76.5 │ 88 │ │ B │ 99 │ 5 │ 5 │ 5 │ 5 │ 405 │ 81 │ 99 │ └────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘
可以看到，ClickHouse 现在支持了原生的:
分析函数 rank()、dense_rank()、row_number()
开窗函数 over()，且开窗函数也支持分组子句 partition by、排序子句 order by 和窗口子句 range/row
由于默认窗口子句是 range ，下面语句等价:
PARTITION BY id ORDER BY val ASC range unbounded preceding --和 PARTITION BY id ORDER BY val ASC
即
SELECT id, val, rank() OVER w AS rank, dense_rank() OVER w AS dense_rank, row_number() OVER w AS row_number, count(*) OVER w AS count, sum(toInt32(val)) OVER w AS sum_v, avg(toInt32(val)) OVER w AS avg_v, max(toInt32(val)) OVER w AS max_v FROM test_data WINDOW w AS (PARTITION BY id ORDER BY val ASC) ORDER BY id ASC SETTINGS allow_experimental_window_functions = 1; ┌─id─┬─val─┬─rank─┬─dense_rank─┬─row_number─┬─count─┬─sum_v─┬─────────────avg_v─┬─max_v─┐ │ A │ 59 │ 1 │ 1 │ 1 │ 1 │ 59 │ 59 │ 59 │ │ A │ 70 │ 2 │ 2 │ 2 │ 2 │ 129 │ 64.5 │ 70 │ │ A │ 80 │ 3 │ 3 │ 3 │ 4 │ 289 │ 72.25 │ 80 │ │ A │ 80 │ 3 │ 3 │ 4 │ 4 │ 289 │ 72.25 │ 80 │ │ A │ 90 │ 5 │ 4 │ 5 │ 5 │ 379 │ 75.8 │ 90 │ │ B │ 65 │ 1 │ 1 │ 1 │ 1 │ 65 │ 65 │ 65 │ │ B │ 75 │ 2 │ 2 │ 2 │ 2 │ 140 │ 70 │ 75 │ │ B │ 78 │ 3 │ 3 │ 3 │ 3 │ 218 │ 72.66666666666667 │ 78 │ │ B │ 88 │ 4 │ 4 │ 4 │ 4 │ 306 │ 76.5 │ 88 │ │ B │ 99 │ 5 │ 5 │ 5 │ 5 │ 405 │ 81 │ 99 │ └────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘
扩展用例：求过去10分钟每分钟都出现并连续出现3次的活跃ip数。
SELECT count( DISTINCT s_ip ) FROM ( SELECT s_ip, dt - rank * 60 flat_date FROM ( SELECT s_ip, found_time dt, rank() over ( PARTITION BY s_ip ORDER BY dt ) rank FROM s_ip_group_view2 WHERE found_time >= 1620583215 AND found_time <= 1620583915 ORDER BY s_ip, dt ) GROUP BY s_ip, flat_date HAVING count()>= 3 );
2）同比/环比功能，如下实现：
在新的版本中，虽然目前也还未实现 lead/lag 函数，但通过开窗函数的窗口子句就能变相实现该功能:
SELECT date_time, money, any(money) OVER (ORDER BY money ASC Rows BETWEEN 12 PRECEDING AND 12 PRECEDING) AS prev_year, any(money) OVER (ORDER BY money ASC Rows BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_month FROM ( WITH toDate('2019-01-01') AS start_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money FROM numbers(16) ) SETTINGS allow_experimental_window_functions = 1 Query id: 12ca2353-cb6e-4218-be1f-85ef666577ec ┌──date_time─┬─money─┬─prev_year─┬─prev_month─┐ │ 2019-01-01 │ 100 │ 0 │ 0 │ │ 2019-02-01 │ 200 │ 0 │ 100 │ │ 2019-03-01 │ 300 │ 0 │ 200 │ │ 2019-04-01 │ 400 │ 0 │ 300 │ │ 2019-05-01 │ 500 │ 0 │ 400 │ │ 2019-06-01 │ 600 │ 0 │ 500 │ │ 2019-07-01 │ 700 │ 0 │ 600 │ │ 2019-08-01 │ 800 │ 0 │ 700 │ │ 2019-09-01 │ 900 │ 0 │ 800 │ │ 2019-10-01 │ 1000 │ 0 │ 900 │ │ 2019-11-01 │ 1100 │ 0 │ 1000 │ │ 2019-12-01 │ 1200 │ 0 │ 1100 │ │ 2020-01-01 │ 1300 │ 100 │ 1200 │ │ 2020-02-01 │ 1400 │ 200 │ 1300 │ │ 2020-03-01 │ 1500 │ 300 │ 1400 │ │ 2020-04-01 │ 1600 │ 400 │ 1500 │ └────────────┴───────┴───────────┴────────────┘
--利用窗口子句，将 range 换成 row ，通过如下的句式实现： any(value) over (.... rows between <offset> preceding and <offset> preceding), or following
