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?
You mean this one? Hive 0.8 doesn't have CROSS JOIN, so I don't know
if this query works.
SELECT
      A.userType
    , A.userType_count/B.global_count
FROM
    (
    SELECT
          userType
        , COUNT(1) as userType_count
    FROM
        some_table
    GROUP BY
        userType
    ) A
CROSS JOIN
    (
    SELECT
        COUNT(1) as global_count
    FROM
        some_table
    ) B;
On Fri, Sep 7, 2012 at 1:34 PM, Bertrand Dechoux <[EMAIL PROTECTED]> wrote:
> 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