HIVE 枢轴和总和 [英] HIVE Pivot and Sum

问题描述

我有一张表格，我想弄清楚如何根据第二列中的值进行透视和求和.

I have a table that I am trying to figure out how to pivot and sum based on the values in a second column.

示例输入:

|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 |

示例输出:

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

推荐答案

使用case和sum():

select own, sum(case when pet='dog' then qty end) as dog,
            sum(case when pet='cat' then qty end) as cat,
            sum(case when pet='cow' then qty end) as cow
  from your_table
 group by own;

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