京东大数据面试SQL-合并数据

一、题目

已知有数据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         |
+-----+-----------+

二、分析

首先题目虽然给出了最终期望结果，但描述实在不够清晰，所以我给题目清晰度打分3⭐️。这里我对题目进行进一步描述

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

题目第1问考察聚合函数、字符串拼接，以及同时对两列完成不同形式的分组“聚合”操作，这里使用开窗函数实现；题目第2问是在第一问的基础上考察连续问题；

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

三、SQL

1.生成B数据

1.使用聚合函数开窗，给每行数据添加最大ID，作为新的分组ID

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

执行结果

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

2.根据new_id分组，拼接name，得到结果

这里根据new_id进行分组，使用collect_list进行拼接，然后使用concat_ws()函数转换为字符，制定拼接符。

执行SQL

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 t1_jd_idname_concat) t
group by new_id;

执行结果

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

3.额外的问题

这也是题目描述不清楚的地方，或者叫潜在的坑，我们可以看到我们给出的结果B的结果在顺序上有所不同，如果要求数据必须按照目标数据完成，我们观察到是按照分组的最小ID取值。即：题目目标为：聚合name，显示最大ID，根据最小ID进行排序,可是这里的排序并不是强制排序，并不能确定有序，想要确定有序则在结果中必须增加min_id序号。忽然觉得清晰度给3⭐️给高了

执行SQL

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,
        min(id) over (partition by name) as ord_id
    from t1_jd_idname_concat
    ) t
    group by ord_id,new_id

执行结果

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

2.生成C数据

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

1.增加标识列，确认是否与上一行相同

我们增加一列，确认是否与上一行相同，如果相同则给0，不同给1。

执行SQL

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

执行结果

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

2.对flag累积求和，得到分组标志

这里使用sum()over(order by)，根据id进行排序，得到新的分组标志。

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

执行sql

select
    id,
    name,
    flag,
    sum(flag)over(order by id asc) as grp
from
    (
    select
        id,
        name,
        if(name = lag(name,1,name)over(order by id asc),0,1) as flag
    from t1_jd_idname_concat
    ) t;

执行结果

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

3.与第一问类似，求取分组内的最大值，完成拼接

执行SQL

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 asc) as grp
        from
            (
            select
                id,
                name,
                if(name = lag(name,1,name)over(order by id asc),0,1) as flag
            from t1_jd_idname_concat
            ) t
        ) tt
    )ttt
group by id;

执行结果

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

四、建表语句和数据插入

CREATE TABLE IF NOT EXISTS t1_jd_idname_concat (
    id bigint, --id
    name STRING -- name
);
 
insert into t1_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');

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；

4.连续签到领金币 1.取出累计值与1000差最小的记录