Hive: group by using average function and most frequency function

user_4692937 0
Viewed 151

I have a table structure like this

|---------------------|----------|-----------|
|    col_1            |  col_2   |   col_3   |
|---------------------|----------|-----------|
|  2018-01-15 17:56   | A        |   3       |
|---------------------|----------|-----------|
|  2018-01-15 17:56   | A        |   2       |
|---------------------|----------|-----------|
|  2018-10-23 23:43   | B        |   True    |
|---------------------|----------|-----------|
|  2018-10-23 23:43   | B        |   False   |
|---------------------|----------|-----------|
|  2018-10-23 23:43   | A        |    3      |
|---------------------|----------|-----------|
|  2018-10-23 23:43   | B        |    True   |
|---------------------|----------|-----------|

I would like to group by col_1, and take average of col_3 if col_2 is A and take frequent value of col_3 if col_2 is B. The desired result would be 

|---------------------|----------|-----------|
|    col_1            |  A       |   B       |
|---------------------|----------|-----------|
|  2018-01-15 17:56   | 2.5      |   Null    |
|---------------------|----------|-----------|
|  2018-10-23 23:43   | 3        |   True    |
|---------------------|----------|-----------|

Without the frequency function when col_2 is B, I know I can do something like this

select col_1,
       avg(case when col_2='A' then col_3 end) as A
from my_table
group by col_1

How shall I add frequency function when col_2 is B?

hiveql hive group-by sql
2 Answers

You can use two levels of aggregation:

select col1,
       (sum(case when col2 = 'A' then col3 * cnt end)/
        sum(case when col2 = 'A' then cnt end)
       ) as A,
       max(case when col2 = 'B' and seqnum = 1 then col3 end) as B
from (select col1, col2, col3, count(*) as cnt,
             row_number() over (partition by col1 order by count(*) desc) as seqnum
      from t
      group by col1, col2, col3
     ) t
group by col1;
user_11440350

Use analytic functions, see comments in the code:

with my_table as (
select stack(6,
'2018-01-15 17:56','A', '3'    ,
'2018-01-15 17:56','A', '2'    ,
'2018-10-23 23:43','B', 'True' ,
'2018-10-23 23:43','B', 'False',
'2018-10-23 23:43','A', '3'    ,
'2018-10-23 23:43','B', 'True' ) as (col_1 , col_2,  col_3)
)
select col_1, --final aggregation by col_1
       max(avg)           as A,
       max(most_frequent) as B
from(       
select col_1, col_2, col_3, cnt, --calculate avg and most_frequent
       case when col_2='A' then avg(col_3) over(partition by col_1, col_2) else null end as avg,
       case when col_2='B' then first_value(col_3) over(partition by col_1, col_2 order by cnt desc) else null end as most_frequent
  from
      (
      select  col_1, col_2, col_3, --calculate count
              case when col_2='B' then count(*) over(partition by col_1, col_2, col_3) else null end as cnt
        from my_table
      )s  
)s      
group by col_1      
;

Result:

col_1                   a       b
2018-01-15 17:56        2.5     NULL
2018-10-23 23:43        3.0     True
user_27003440
Related