Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> How to get percentage of each group?


Copy link to this message
-
Re: How to get percentage of each group?
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB