clickhouse如何实现实现分区排序 类似HIVE的 row_number() over(partition by order by)函数

想必大家对hive实现分区内排名已经掌握的很好了，这篇文章主要就是将的就是用clickhouse如何实现

测试数据

store_code	sale_amt
6819	29
9974	111
9974	60
9974	2061
4376	6
8677	24
8677	12
3483	52
3483	1638
3483	108
3483	24
3483	105
3483	53
3483	1299
9974	120
9974	27
9974	3980
9974	2198
4376	288
8677	15
8677	120
8677	53
8677	19
3483	1128
3483	1653
3483	48
3483	228
3483	11
3483	73

hive实现

 -- 建表语句，我是csv导入的 所以用,分割的
 create table store_test (
 store_code string,sale_amt int) 
 row format delimited fields terminated by ",";
 -- 数据导入 看自己的实际情况
 hdfs dfs -put store_code.csv  /user/hive/warehouse/tmp.db/store_test
 --查询语句
select store_code
,sale_amt
,row_number() over(partition by store_code order by sale_amt desc) row_num  
from store_test;

但是clickhouse 没有 row_number() 函数 该如何实现呢？其实clickhouse是有很多函数的，让我们往下看

clickhouse实现

-- 建表语句
create table tmp.store_test (store_code String, sale_amt Float32 )ENGINE=TinyLog;
--数据导入语
--我直接通过dbever 导入csv文件的
-- 查询sql 
select * from
(
	select store_code,groupArray(sale_amt) as arr_sale_amt , arrayEnumerate(arr_sale_amt) AS row_number
	from ( select *	from tmp.store_test	order by sale_amt desc ) a
	group by store_code
) a ARRAY JOIN row_number , arr_sale_amt
order by store_code ;

其中我们需要注意的就是一下几个函数了：
先将原始数据排序,因为groupArray不确定顺序

order by sale_amt desc 

创建参数值所有数据的数组。 值可以按任何（不确定）顺序添加到数组中。第二个版本（带有 max_size 参数）将结果数组的大小限制为 max_size 个元素。 例如, groupArray (1) (x) 相当于 [any (x)] 。在某些情况下，您仍然可以依赖执行顺序。这适用于SELECT(查询)来自使用了 ORDER BY 子查询的情况。

groupArray(sale_amt) 

返回数组中的元素下标或者说个数 Array [1, 2, 3, …, length (arr) ]，此功能通常与ARRAY JOIN
一起使用。它允许在应用ARRAY JOIN后为每个数组计算一次

arrayEnumerate(arr_sale_amt) 

对于包含数组列的表来说是一种常见的操作，用于生成一个新表，该表具有包含该初始列中的每个单独数组元素的列，而其他列的值将被重复显示。 这是ARRAY JOIN 语句最基本的场景。 类似于 arrayJoin 功能获取每一行并将他们展开到多行（unfold）。而聚合函数则是将多行压缩到一行中（fold或reduce）。其实就是将数组列那行数据打散到多行中。

ARRAY JOIN  

-- 再单独举个官方的列子(clickhouse官网的https://clickhouse.com/docs/en/sql-reference/statements/select/array-join)
CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

SELECT s, arr FROM arrays_test ARRAY JOIN arr;
┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘
-- 下一个示例使用 LEFT ARRAY JOIN 子句:
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

小伙伴们可能会觉的clickhouse会比较复杂，我们不妨换个角度想，是不是clickhouse很灵活呢？
假如我现在要实现的是要分区范围内topN呢？ 就top 1吧，hive是不是还有在外面包一层 where row_num =1;如果是clickhouse的话 就可以直接groupArray(1)(sale_amt) 就行了。是不是觉得很自由 哈哈。当然clickhouse还有其他的实现方式，这里只展示这一种，而且还有特别多的函数等待着大家的挖掘吧，包括我之前有用过的漏斗分析、留存分析等。