sql - 使用 group by 从 hive 表中删除重复项

Question

我有一个这样的表。a,b,c,d,e 是表的列

我想在 group by(d,e) 的组合上找到不同的记录并对表进行一些操作最终表应该删除重复的键。

决赛桌应如下所示

我做了一个类似的查询

SELECT *
FROM (SELECT a+"cis" as a_1,
             b+"cis1" as b_1,
             c as c_1,
             d+"cis2" as d_1,
             e as e_1
            ROW_NUMBER() OVER (PARTITION BY d, e order by d,e) as cnt
      FROM table1
      
     ) x
WHERE cnt = 1;

我得到的结果像

我怎样才能得到实际的结果

提前致谢

标签： sqlhivehiveql

Answer 1

我想我找到了解决方案

SELECT concat(x.a,"cis") as a_1,concat(x.b,'cis1') as b_1,x.c as c_1,
             concat(x.d,'cis2') as d_1,x.e as e_1 FROM (SELECT a,b,c,d,e,
    ROW_NUMBER() OVER (PARTITION BY d, e order by d,e) as cnt FROM table  ) x
WHERE cnt = 1