question Re: Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table in Archives of Support Questions (Read Only)

Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table

PentaReddy — Sat, 22 Jul 2017 02:31:52 GMT

Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table(if the percentage of n is less 20%) or else not to load

colA

------------------ Output: -- This is what i am expecting

y 80%

n 20%

Re: Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table

rbiswas1 — Sat, 22 Jul 2017 03:59:21 GMT

@PPR Reddy

Here goes the solution (You can do it in other ways if you choose to):

Column name in table a is c

hive> select * from a;

hive>

Query:

hive> select c,per from(  > select x.c c,(x.cc/y.ct)*100 per,(z.cn/y.ct)*100 pern  from  > (select c, count(*) cc from a group by c) x, (select count(*)ct from a) y,  > (select c, count(*) cn from a where c='n' group by c) z) f  > where pern > 20;

Output:

n 25.0

y 75.0

Thanks