Hive compute median and average by groups

Question

I have a dataset that has counts by state and county and I would like to calculate the median and average by state and county such as:

Have:

ID  state    county  count
1   MD       aa          2
2   MD       aa          4
3    VA        bb         1
4    VA        bb         2
5    VA        bb         4
6    VA        cc          7
7    VA        cc          8

Want:

What I have so far that gives me error:

Select id,  STATE,COUNTY,count,
percentile(cast(count as BIGINT), 0.5) OVER() as overall_median, 
round(avg(count),2) OVER() as overall_avg,

percentile(cast(count as bigint),0.5) OVER(PARTITION BY id,STATE) as med_state,
percentile(cast(count as bigint),0.5) as med_county,

AVG(count) OVER (PARTITION BY id, STATE) as avg_state,
AVG(count) AS avg_county,
from have
group by id, state, county

Error received when not using group:

ERROR: Execute error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:457 Expression not in GROUP BY key 'id'

Code without group:

Select id,  STATE,county,count,
percentile(cast(count as BIGINT), 0.5) OVER() as overall_median, 
round(avg(count),2) OVER() as overall_avg,

percentile(cast(count as bigint),0.5) OVER(PARTITION BY id,STATE) as med_state,
percentile(cast(count as bigint),0.5) OVER(PARTITION BY id,STATE,county) as med_county,


AVG(count) OVER (PARTITION BY id, STATE) as avg_state,
AVG(count) OVER (PARTITION BY id, STATE, county) as avg_county,
from have

Thank you!

can you post the error message? from what i see you have closing parenthesis issue in some places — Vamsi Prabhala

User9123 User9123 · Accepted Answer · 2020-02-26T19:19:42

fix: round(avg(count) OVER(), 2)

    select 
        id, STATE, county, count,
        percentile(cast(count as BIGINT), 0.5) OVER() as overall_median, 
        round(avg(count) OVER(), 2) as overall_avg,

        percentile(cast(count as bigint), 0.5) OVER(PARTITION BY id, STATE) as med_state,
        percentile(cast(count as bigint), 0.5) OVER(PARTITION BY id, STATE, county) as med_county,

        AVG(count) OVER (PARTITION BY id, STATE) as avg_state,
        AVG(count) OVER (PARTITION BY id, STATE, county) as avg_county
    from 
        have

tip: don't use keyword (i.e. count) as column name - you will have a lot problems in future

Hive compute median and average by groups

1 Answers