# hive-user mailing list archives

##### Site index · List index
Message view
Top
From Bejoy KS <bejoy...@yahoo.com>
Subject Re: How to get percentage of each group?
Date Fri, 07 Sep 2012 06:59:14 GMT
```Hi

CROSS JOIN is same as giving JOIN keyword. CROSS JOIN just a new notation in later releases
of hive. JOIN without ON is same as CROSS JOIN

Regards,
Bejoy KS

________________________________
From: MiaoMiao <liy099@gmail.com>
To: user@hive.apache.org
Sent: Friday, September 7, 2012 11:46 AM
Subject: 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 <dechouxb@gmail.com> 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 <liy099@gmail.com> 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
```
Mime
View raw message