计算Hive列中类别的百分比 [英] Calculate the percentage of categories in a column in Hive

问题描述

我在Hive中有一个表，colors看起来像这样:

I have a table, colors in Hive that looks like this:

 id cname
 1 Blue
 2 Green
 3 Green
 4 Blue
 5 Blue

我在编写Hive查询时需要帮助，该查询在cname列中给出每种颜色的百分比.看起来像这样:

I need help with writing a Hive query that gives the percentages of each color in the cname column. Something that looks like this:

Blue  60%
Green 40%

提前谢谢！

推荐答案

使用分析功能:

select cname, concat(pct, ' %') pct
from
(
select (
        count(*) over (partition by cname)/
        count(*) over ()
       )*100 as pct,
       cname
  from (--Replace this subquery with your table
        select stack (5,
                      1, 'Blue',
                      2, 'Green',
                      3, 'Green',
                      4, 'Blue',
                      5, 'Blue' )  as (id, cname)

        ) colors
)s
group by cname, pct;

结果:

OK
Blue    60.0 %
Green   40.0 %

只需将colors子查询替换为您的表

Just replace colors subquery with your table

这篇关于计算Hive列中类别的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！