clickhouse应用- 实现spark/hive的collect_list/collect_set

1、先通过concat函数人肉拼接json字符串

2、再通过group分组，用groupArray计算出Array(String)（如：['{"enrollment_year_grade_name": "2021级二年级", "avg_value": "0.00"}','{"enrollment_year_grade_name": "2022级二年级", "avg_value": "0.00"}']），

再用arrayStringConcat转换成String（如：{"enrollment_year_grade_name": "2021级二年级", "avg_value": "0.00"},{"enrollment_year_grade_name": "2022级二年级", "avg_value": "0.00"}）

最后用cancat拼接成json字符串

select school_id,school_name,
concat('[',arrayStringConcat(arrayDistinct(groupArray(avg_value_json)),','),']') avg_value_json_array
from(select 
	tenant_id school_id,tenant_name school_name,enrollment_year,
	concat('{"enrollment_year_grade_name": "', concat(toString(enrollment_year),'级',grade_name), '", "avg_value": "',  toString(avg_value), '"}') avg_value_json
	from tab
	order by enrollment_year
)
group by school_id,school_name

其中，添加arrayDistinct函数实现spark/hive的collect_set