Hive：根据特定列中的唯一值填充其他列

作者: 溪林
发布时间: 2024-06-19 10:40:30 (1月前)
转自：

3 条回复

0#
回复此人
文艺青年3 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 计算分析 <code> count(*) over(partition by value) </code> 。使用您的数据示例进行测试 </p> <pre> <code> with table1 as ( select stack (4, 1,'abc','stack', 3,'abc','overflow', 4,'abc','foo', 6,'abc','bar' ) as (id, name, value) ), table2 as ( select stack (4, 5, 'xyz','overflow', 9, 'xyz','stackoverflow', 3, 'xyz','foo', 23, 'xyz','bar' ) as (id, name, value) ) select id, name, value from( select id, name, value, count(*) over(partition by value) value_cnt from (SELECT id,name,value FROM table1 UNION ALL SELECT id,name,value FROM table2) s )s where value_cnt=1; </code> </pre> <P> 结果： </p> <pre> <code> OK id name value 1 abc stack 9 xyz stackoverflow Time taken: 55.423 seconds, Fetched: 2 row(s) </code> </pre> </DIV>

编辑
1#
回复此人
1号 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 你可以尝试下面 - </p> <pre> <code> seELECT id,name,value FROM table1 a left join table2 b on a.value=b.value where b.value is null UNION ALL SELECT seELECT id,name,value FROM table2 a left join table1 b on a.value=b.value where b.value is null </code> </pre> </DIV>

编辑

登录后才能参与评论