HiveSQL面试题47：京东面试题

0 需求

1 数据准备

数据
1aa2aa3aa4d5c6aa7aa8e9f10g
**建表 **

create table a(
    id string,
    name string
)
ROW format delimited FIELDS TERMINATED BY ",";

加载数据

load data local inpath "/home/dandan/a.txt" into table a;

2 求解

A->B

A->B
select flg 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 flg
    from a
) t
group by flg

A->C

select max(id) as id
      ,concat_ws('|',collect_list(name)) as name
from(
    select id
          ,name
          ,sum(if(name!=lag_name,1,0)) over(order by cast(id as int)) as flg 
    from(
        select id
              ,name
              ,lag(name,1,name) over(order by cast(id as int)) as lag_name
        from a
    ) m
) n
group by flg

3 小结

本题还是利用开窗函数打标签进行辅助计算，并利用了分类重分组的思想（**sum() over(order by )**）.