outer连接或联合所有行以将行转换为列

nxagd54h 于 2021-06-24 发布在 Hive

关注(0)|答案(1)|浏览(283)

我有的是：table

_______________________________________            
 | MachUUID | State|  Pass   | Fail    |   
 |--------------------------------------
 |  1234    | A    |   0.2   |    0.98 |  
 |  1234    | B    |   0.5   |    0.5  |  
 |  1234    | C    |   0.8   |    0.2  |
 ---------------------------------------

我想要的是：一张table

| MachUUID | A_Pass | A_Fail | B_Pass | B_Fail | C_Pass | C_Fail
  --------------------------------------------------------------
 |  1234    |  0.2   | 0.98   |  0.5   |  0.5   | 0.8    |  0.2

状态数（a、b等是固定的）。目前他们已经有20个了。所以要改变这个
我在做什么：

Transformed_Table AS (
SELECT MachUUID, Pass AS A_Pass, Fail AS A_Fail
FROM table
WHERE State = 'A'
UNION ALL

SELECT MachUUID, Pass AS B_Pass, Fail AS B_Fail
FROM table
WHERE State = 'B'
UNION ALL

SELECT MachUUID, Pass AS C_Pass, Fail AS C_Fail
FROM table
WHERE State = 'C')

然而，这返回了一个奇怪的结合，看起来像：
我得到了错误的输出

| MachUUID | A_Pass| A_Fail |
|   1234   | 0.2   | 0.98   |
|   1234   | 0.5   | 0.5    |
|   1234   | 0.8   | 0.2    |

问题
我认为在这一点上我对工会的理解是错误的。我了解外饰，不知道这样做是否更好。
我愿意接受关于解决这个问题的其他方法的建议

sql Hive Join union-all

来源：https://stackoverflow.com/questions/56856361/outer-join-or-union-all-to-transform-the-rows-into-columns

1条答案

按热度按时间

tuwxkamq1#

您可以使用条件聚合来执行此操作：

select MachUUID,
       max(case when state = 'A' then pass end) as a_pass,
       max(case when state = 'A' then fail end) as a_fail,
       max(case when state = 'B' then pass end) as b_pass,
       max(case when state = 'B' then fail end) as b_fail,
       max(case when state = 'C' then pass end) as c_pass,
       max(case when state = 'C' then fail end) as c_fail
from t
group by MachUUID

赞(0）回复(0）举报 2021-06-24

我来回答

outer连接或联合所有行以将行转换为列

1条答案

相关问题

热门标签

最新问答