sql中几个有趣的函数

分组拼接函数

group_concat(拼接字段 order by 排序字段)这个函数可以按照指定分组，拼接每组中指定字段按照规则排序后的值为一个字符串。
也可以开窗使用group_concat(拼接字段) over(partition by 分组字段 order by 排序字段)

select 
	*
	,split(pay_gmv,',')[1]
	,split(pay_gmv,',')[2]
from
(SELECT 
	data_date
	,shop_num
	,shop_name
	,power
	,GROUP_CONCAT(concat_ws('-',data_item,pay_gmv) order by data_date,shop_num,data_item) pay_gmv
FROM kscs_test.test
where data_date='2023-02-20'
group by data_date,shop_num)

select 
	a.* 
	,b.time_group 
from dws.dws_niu_kwai_data a 
left join
(select
	data_date
	,brand
	,people
	,max(time_group) time_group -- 取最全的一个分组拼接值
from
( 
	select 
    	data_date
    	,brand
    	,people
    	,time_d
    	,group_concat(time_d) over(partition by data_date,brand,people order by time_d) time_group -- 按照data_date,brand,people分组，每组中所有的time_d升序后拼接展示为一个字符串
	from dws.dws_niu_kwai_data
	group by data_date,brand,people,time_d
) b 
group by data_date,brand,people) b 
on a.data_date=b.data_date and a.brand=b.brand and a.people=b.people

cross join函数

cross join可以返回两表的笛卡尔积，返回的表的记录数为两表记录数的乘积；常与UNNEST使用，UNNEST可以将一个数组存为一张临时表，且与数组所属的原表默认有关联关系。
AnalyticDB MySQL中的cross join实例
原结构：
A:[‘a’,‘b’]
B:[‘c’,‘d’,‘e’]
处理后结构：
A a
A b
B c
B d
B e

insert into dim.dim__list
SELECT 
	data_date
	,agent_id 
	,advertider_id
	,now()
FROM 
(
	select 
		data_date
		,agent_id
		,split(replace(replace(json_extract(data,'$.list'),'[',''),']',''),',') as id_array -- 将列表中的元素按逗号拆分
	from kscs_ods.ods_list)
CROSS JOIN UNNEST(id_array) as temp_table(advertider_id) -- 用UNNEST将拆分后的元素组成的数组存为临时表，并与原表进行笛卡尔积

可代替union all，进行列转行
例：将如下jsonarray格式进行解析

{
   
	"all_traffic_distribution": [{
   
		"main_source": {
   
			"source_name": "f1",
			"watch_pv": 4
		},
		"detail_source"