挑战100天不停更之hive sql第8天 - 不使用 distinct 或 group by 去重

🌿挑战100天不停更，刷爆 hive sql🧲

NUM: 第8天 - 不使用 distinct 或 group by 去重

🧨不废话，刷题~~🧨

详情请点击🔗我的专栏🖲,共同学习,一起进步~

表结构

建表

create table t9
(
    a string,
    b string,
    c string,
    d string
);
insert into t9
values ('2014', '2016', '2014', 'A'),
       ('2014', '2015', '2015', 'B');

不使用 distinct 或 group by 去重

输出结果

思路

– 1,先将多个显示年份的列转为1列，用_union all_
– 2,使用分组排序的开窗函数(row_number())对两个字段分别进行分组，这样排序就会在a分组内，b再分组，取b的排序
– 3,取排序为1的值即可

SQL

select year,
num
from (select year,
      num,
      row_number() over (partition by tmp1.year,num) as rank_1
      from (select a as year, d as num
            from t9
            union all
            select b as year, d as num
            from t9
            union all
            select c as year, d as num
            from t9) tmp1) tmp2
where tmp2.rank_1 = 1;