[大数据]group by 去重处理数据

1.

# 需求：结果：
# + ----+------+
# | name|boy_id|
# + ----+------+
# | A   |1     |
# | B   |2     |
# | C   |3     |
# + ----+------+

#  group by 对结果的重复数据去重
select  * from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |2     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# +--+----+------+



select  name ,boy_id  from girl group by name ,boy_id ;

# +----+------+
# |name|boy_id|
# +----+------+
# |A   |1     |
# |B   |2     |
# |C   |3     |
# +----+------+


# 进阶版本：

select  * from boy  ;

# +--+----+--------+
# |id|name|del_flag|
# +--+----+--------+
# |1 |慕容皝 |0       |
# |2 |慕容垂 |0       |
# |3 |慕容博 |1       |
# |4 |慕容复 |1       |
# +--+----+--------+

select  * from girl ;

# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A   |1     |
# |2 |B   |2     |
# |3 |C   |3     |
# |4 |C   |3     |
# |5 |C   |3     |
# |6 |D   |NULL  |
# +--+----+------+

# 需求：  统计每个女生是否真正有男友的情况（假设女生姓名唯一；boy_id 为 3 的男生 已经 被 删掉了，认为 c 没有男友 ），并且去重，结果数据如下：

# +  ----+------+
# |  name|boy_id| flag
# +  ----+------+
# |  A   |1     |  0
# |  B   |2     |  0
# |  C   |3     |  0
# |  D   |NULL  |  0
# +  ----+------+


select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = girl.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from girl;
# 结果如下:
# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |C   |3     |0   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+

#  用户c 的boy_id 都是 3 ，所以分组思路有两个 ① 对元数据进行分组  ② 对 结果数据进行分组

# ① 对元数据进行分组

select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = Tem.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from (select  name,boy_id from girl group by name,boy_id ) Tem group by   name ,boy_id ,flag ;

# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+




# ② 对 结果数据进行分组

select name,
       boy_id ,
       case
           when (select count(1) from boy where boy.id  = girl.boy_id and boy.del_flag = 0 ) > 0
               then  1
           else
               0
        end as flag
from girl group by   name ,boy_id ,flag ;

# +----+------+----+
# |name|boy_id|flag|
# +----+------+----+
# |A   |1     |1   |
# |B   |2     |1   |
# |C   |3     |0   |
# |D   |NULL  |0   |
# +----+------+----+