Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - How to get percentage of each group?


+
MiaoMiao 2012-09-07, 05:23
Copy link to this message
-
Re: How to get percentage of each group?
Bertrand Dechoux 2012-09-07, 05:34
Hi,

You could use a cross join.
You basically have one table

select
      userType
    , count(1)
from
    some_table
group by
    userType

and a second one

select count(1) from some_table

With a cross join you can add the global count to every results in the
first table and compute a ratio.

Regards

Bertrand
On Fri, Sep 7, 2012 at 7:23 AM, MiaoMiao <[EMAIL PROTECTED]> wrote:

> I have a table, containing userId and userType.
> userId    userType
> 1    A
> 2    B
> 3    C
> 4    A
> 5    B
> 6    B
>
> I want to get percentage of each userType.
> My current solution:
> 1. Get count of each group via THRIFT
> select
>       userType
>     , count(1)
> from
>     some_table
> group by
>     userType
>
> 2. Calculate each userType using other programming language like PHP.
>
> This solution is fine, but I'm just curious, is there a way to do it
> in one query?
> I know this query works in mysql, but not hive.
> select
>       userType
>     , count(1)/(select count(1) from some_table)
> from
>     some_table
> group by
>     userType
>

--
Bertrand Dechoux
+
MiaoMiao 2012-09-07, 06:16
+
Bejoy KS 2012-09-07, 06:59
+
MiaoMiao 2012-09-07, 07:10