HQL面试题练习 —— 合并数据

题目来源：京东

1 题目

已知有数据 A 如下，请分别根据 A 生成 B 和 C。

数据A

+-----+-------+
| id  | name  |
+-----+-------+
| 1   | aa    |
| 2   | aa    |
| 3   | aa    |
| 4   | d     |
| 5   | c     |
| 6   | aa    |
| 7   | aa    |
| 8   | e     |
| 9   | f     |
| 10  | g     |
+-----+-------+

数据B

+-----+-----------------+
| id  |      name       |
+-----+-----------------+
| 7   | aa|aa|aa|aa|aa  |
| 4   | d               |
| 5   | c               |
| 8   | e               |
| 9   | f               |
| 10  | g               |
+-----+-----------------+

数据C

+-----+-----------+
| id  |   name    |
+-----+-----------+
| 3   | aa|aa|aa  |
| 4   | d         |
| 5   | c         |
| 7   | aa|aa     |
| 8   | e         |
| 9   | f         |
| 10  | g         |
+-----+-----------+

希望对 name 相同的数据进行合并处理，name 相同的合并到一起用 '|'进行拼接，id 取组内最大值；
希望对相邻 name 相同的数据进行合并，name 相同的合并到一起用 '|' 进行拼接，id 取组内最大值；

2 建表语句

CREATE TABLE IF NOT EXISTS t_jd_idname_concat (
    id bigint, --id
    name STRING -- name
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC;

insert into t_jd_idname_concat(id, name) values
(1,'aa'),
(2,'aa'),
(3,'aa'),
(4,'d'),
(5,'c'),
(6,'aa'),
(7,'aa'),
(8,'e'),
(9,'f'),
(10,'g');

3 题解

1. 生成B

第一步：使用聚合函数开窗，给每行数据添加最大ID，作为新的分组ID。

select
    id,
    name,
    max(id) over (partition by name) as new_id
from t_jd_idname_concat;

结果如下：

在这里插入图片描述

第二步：根据 new_id 分组，拼接 name，得到结果

select
    new_id as id,
    concat_ws('|',collect_list(name)) as name
from
    (
    select
        id,
        name,
        max(id) over (partition by name) as new_id
    from t_jd_idname_concat) t
group by new_id;

结果如下：

在这里插入图片描述

2. 生成C

该小问属于是连续问题上进行数据拼接，所以我们先要对数据进行分组处理。

第一步：增加标识列，确认是否与上一行相同，如果相同则给0，不同给1。

select id,
       name,
       if(name = lag(name, 1, name) over (order by id), 0, 1) as flag
from t_jd_idname_concat;

结果如下：

在这里插入图片描述

第二步：对 flag 累积求和，得到分组标志。

注意，第一步给flag 相同为0 不同为 1，叠加本步骤累积求和是一个常见解决连续问题的方式。

select
    id,
    name,
    flag,
    sum(flag)over(order by id) as grp
from
    (
    select
        id,
        name,
        -- 这里要注意if语句中0,1的位置不能互换（核心）
        if(name = lag(name,1,name)over(order by id),0,1) as flag
    from t_jd_idname_concat
    ) t;

结果如下：

在这里插入图片描述

第三步：求分组内的最大值，完成拼接。

select
    id,
    concat_ws('|',collect_list(name)) as name
from
(select
    grp,
    name,
    max(id) over(partition by grp) as id
from
(select
    id,
    name,
    flag,
    sum(flag)over(order by id) as grp
from
    (select
        id,
        name,
        -- 这里要注意if语句中0,1的位置不能互换（核心）
        if(name = lag(name,1,name)over(order by id),0,1) as flag
    from t_jd_idname_concat
    ) t ) tt ) ttt
group by id;