How Can I make a kind of column by distribution in a column in Hive SQL

user_14168420 0
Viewed 193

I want to make a result in hive like this :

| COL1 | HISTOGRAM             |
+------+-----------------------+
|  a   | {"A":2, "B":2}        |
|  b   | {"C":2, "A":1, "B":1} |

from this table :

| COL1 | COL2 |
+------+------+
| a    | A    |
| a    | B    |
| a    | A    |
| a    | B    |
| b    | A    |
| b    | B    |
| b    | C    |
| b    | C    |

Presto SQL has like what I want, I think :

select COL1, histogram(COL2)
from sample_table 
group by COL1
hiveql histogram hive group-by sql
1 Answers

You can calculate counts group by col1, col2, then aggregate col2:cnt strings using collect_set or collect_list into array, concatenate array with comma as a delimiter and convert resulting string to map using str_to_map.

Demo:

select stack (8, --number of tuples
 'a','A'
,'a','B'
,'a','A'
,'a','B'
,'b','A'
,'b','B'
,'b','C'
,'b','C'
) as (COL1,COL2)
)

select col1, str_to_map(concat_ws(',',collect_set(concat(col2,':',cnt)))) histogram
from
(
select col1, col2, count(*) cnt from data_example group by col1, col2
)s
group by col1
;

Result:

col1    histogram
a   {"A":"2","B":"2"}
b   {"A":"1","B":"1","C":"2"}
user_27003440
Related