hiveSql 重分组聚合问题

问题

将下图中A表转变为B和C，即A->B A->C

分析

• 1.首先看A->B，可见是将name列分组，取最大组内最大id。介绍两种求解方式：
  1）很容易想到，开窗函数first_value() over() 按照name分组取组内最大id，再按照最大id分组，聚合name即可；
  2）也容易想到直接按照name分组，求name个数，利用函数repeat按照每个name个数重复name即可，这里有连接符|有个小技巧，具体见sql实现。
• 2.再看A->C，可见是将连续的name分为一组，取连续name分组内的最大id，很容易想到重分组解法，将不连续的部分计为1，sum() over()累加造是否是连续的分组字段，从而分组取组内最大id 和 串联拼接name

实现

• 1.A->B：
  1）第一种分析思路解法：

select 
	flag as id
    ,concat_ws('|',collect_list(name)) as name
from(
    select id
          ,name
          ,first_value(id) over(partition by name order by cast(id as int) desc) as flag
    from tmp
	) t
group by flag

2）第二种分析思路解法：

select 
    max(id) as id
    ,regexp_replace(trim(repeat(concat(name,' '),count(1))),' ','|')as name
from tmp group by name

此处利用repeat()方法，重复name分组个数，按照空格连接，但是最后会多出一个空格，利用trim()将前后空格去除，再将空格替换为竖线连接name即可
结果：

• 2.A->C：

select 
    max(t1.id) as id,
    wm_concat('|',t1.name) as name
from
    (select 
        id,name,sum(if(name = name1,0,1)) over(order by id) as flag
    from  
        (select 
            id,name,
            lag(name) over( order by id ) as name1
        from tmp
        ) t
    ) t1
group by t1.flag

结果：

最后

喜欢的点赞、关注、收藏吧~ 你的支持是最大的创作动力~~