hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Meagher <john.meag...@gmail.com>
Subject Re: Help in hive query
Date Tue, 30 Oct 2012 13:57:41 GMT
The WHERE part in the approvals can be moved up to be an IF in the SELECT...


SELECT client_id,receive_dd,receive_hh,
              receive_hh+1,
              COUNT(1) AS transaction_count,
              SUM( IF ( response=00, 1, 0) ) AS approval_count,
              SUM( IF ( response=00, 1, 0) ) / COUNT(1) * 100 AS percent
FROM sale_test
group by fdc_client_id,receive_dd,receive_hh,receive_hh+1;



On Tue, Oct 30, 2012 at 1:51 AM, dyuti a <hadoop.hive04@gmail.com> wrote:
> Hi All,
>
> I want to perform (No.of .approvals in an hour/No.of transactions in that
> hour)*100.
>
> //COUNT(1) AS cnt  gives total transactions in an hour
> SELECT client_id,receive_dd,receive_hh,receive_hh+1,COUNT(1) AS cnt FROM
> sale_test group by fdc_client_id,receive_dd,receive_hh,receive_hh+1;
>
> GETREGREOS      23      16      17      5969
> GETREGREOS      23      21      22      2602
> GETREGREOS      24      3       4       114
>
> //Approved transactions where response=00
> SELECT client_id,receive_dd,receive_hh,receive_hh+1,COUNT(1) AS cnt FROM
> sale_test where response=00 group by
> client_id,receive_dd,receive_hh,receive_hh+1;
> GETREGREOS      23      16      17      5775
> GETREGREOS      23      21      22      2515
> GETREGREOS      24      3       4       103
>
>
> I want to perform 100 * (5775/5969) , 100 * (2515/2602) , 100 * (103/114)
> like the same for all other clients for each hour i.e., (No.of .approvals in
> an hour/No.of transactions in that hour)*100.
>
> Please help me out as how to achieve this in hive.
>
>
> Thanks & Regards,
> dti

Mime
View raw message