Hive summary function inside case statement

Question

I am trying to write a simple Hive query:

select sum(case when pot_sls_q > 2* avg(pit_sls_q) then 1 else 0)/count(*) from prd_inv_fnd.item_pot_sls where dept_i=43 and class_i=3 where p_wk_end_d = 2014-06-28;

Here pit_sls_q and pot_sls_q both are columns in the Hive table and I want proportion of records which have pot_sls_q more than 2 times average of pit_sls_q. However I get error:

FAILED: SemanticException [Error 10128]: Line 1:95 Not yet supported place for UDAF 'avg'

To fool around I even tried using some window function:

select sum(case when pot_sls_q > 2* avg(pit_sls_q) over (partition by dept_i,class_i)  then 1 else 0 end)/count(*) from prd_inv_fnd.item_pot_sls where dept_i=43 and class_i=3 and p_wk_end_d = '2014-06-28';

which is fine considering the fact filtering or partitioning the data on same condition is "same" data essentially but even with this I get error:

FAILED: SemanticException [Error 10002]: Line 1:36 Invalid column reference 'avg': (possible column names are: p_wk_end_d, dept_i, class_i, item_i, pit_sls_q, pot_sls_q)

please suggest right way of doing this.

Gurwinder Singh Gurwinder Singh · Accepted Answer · 2017-01-17T11:06:46

You are using AVG inside SUM which won't work (along with other syntax errors).

Try analytic AVG OVER () this:

select sum(case when pot_sls_q > 2 * avg_pit_sls_q then 1 else 0 end) / count(*)
from (
    select t.*,
        avg(pit_sls_q) over () avg_pit_sls_q
    from prd_inv_fnd.item_pot_sls t
    where dept_i = 43
        and class_i = 3
        and p_wk_end_d = '2014-06-28'
    ) t;

Hive summary function inside case statement

1 Answers