Support Questions
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

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

y

y

y

n

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

y 80%

n 20%

1 ACCEPTED SOLUTION

Accepted Solutions

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
Guru

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

OK

y

y

y

n

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:

OK

n 25.0

y 75.0

Thanks

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
Guru

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

OK

y

y

y

n

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:

OK

n 25.0

y 75.0

Thanks

Don't have an account?
Coming from Hortonworks? Activate your account here