clickhouse中开窗函数的实现

clickhouse 实现 Hive 中的 max() over(partition by)

1. 数据准备
   DDL student.sql

create database if not exists testdb;;

drop table if exists testdb.student;;
create table testdb.student
        ENGINE = MergeTree
        partition by tuple()
        order by tuple()
        SETTINGS index_granularity = 8192
as
select
                 1 as id, 'stua' as name, 'a' as course, 0  as score
union all select 2 as id, 'stua' as name, 'b' as course, 10 as score
union all select 3 as id, 'stua' as name, 'c' as course, 20 as score
union all select 4 as id, 'stub' as name, 'a' as course, 30 as score
union all select 5 as id, 'stub' as name, 'b' as course, 10 as score
union all select 6 as id, 'stub' as name, 'c' as course, 20 as score
union all select 7 as id, 'stub' as name, 'd' as course, 30 as score
;;

执行 DDL

clickhouse-client --multiquery < student.sql
clickhouse-client --query "desc testdb.student"
# id		UInt8					
# name		String					
# course	String					
# score		UInt8
clickhouse-client --query "select * from  testdb.student order by id"
1	stua	a	0
2	stua	b	10
3	stua	c	20
4	stub	a	30
5	stub	b	10
6	stub	c	20
7	stub	d	30

2. 查询 sql
   query_grouparray.sql

use testdb;;

select a.*, b.max_score
from student a
join (
        select arrayJoin(arr_id_name) as id_name
                ,course, max_score
        from (
                select course
                        ,max(score) as max_score
                        ,groupArray(toString(id) || toString(name)) as arr_id_name
                        -- ,groupArray(name) as arr_name
                from student group by course
                )
        order by id_name, course
) b on toString(id) || toString(name) = b.id_name
order by a.id
;;

执行查询脚本

clickhouse-client --multiquery < query_grouparray.sql > ./query_grouparray.out
cat ./query_grouparray.out
1	stua	a	0	30
2	stua	b	10	10
3	stua	c	20	20
4	stub	a	30	30
5	stub	b	10	10
6	stub	c	20	20
7	stub	d	30	30