HIVE数据透视表和总和 [英] HIVE Pivot and Sum

问题描述

输入示例：

我有一张表，我想知道如何根据第二列中的值进行数据透视和求和。

Example input:

|own|pet|qty|
|---|---|---|
|bob|dog| 2 |
|bob|dog| 3 |
|bob|dog| 1 |
|bob|cat| 1 |
|jon|dog| 1 |
|jon|cat| 1 |
|jon|cat| 1 |
|jon|cow| 4 |
|sam|dog| 3 |
|sam|cow| 1 |
|sam|cow| 2 |

输出示例：

Example output:

|own|dog|cat|cow|
|---|---|---|---|
|bob| 6 | 1 |   |
|jon| 1 | 2 | 4 |
|sam| 1 |   | 3 |

推荐答案

对于动态数据，您可以使用MAP

For dynamic data you can use MAP

select      own
           ,str_to_map(concat_ws(',',collect_list(concat(pet,':',cast(qty as string))))) as pet_qty

from       (select      own,pet
                       ,sum(qty) qty 

            from        mytable

            group by    own,pet
            ) t

group by    own
;

---

---

+-----+---------------------------------+
| own |             pet_qty             |
+-----+---------------------------------+
| bob | {"cat":"1","dog":"6"}           |
| jon | {"cat":"2","cow":"4","dog":"1"} |
| sam | {"cow":"3","dog":"3"}           |
+-----+---------------------------------+

这篇关于HIVE数据透视表和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！