Record count for day and next day from table

Question

I am trying to find the count of records for day + 1 using below hive query. Getting error because We have hive 0.10 version.

select 
lead(count(*)) over (order by day),
day 
from device_fact_kpis 
where day=20160301 group 
by day;

FAILED: ParseException line 1:27 missing FROM at '(' near '(' in subquery source line 1:28 cannot recognize input near 'order' 'by' 'day' in subquery source

Is there a way to find count for next day without using lead function as hive 0.10 does not have lead function.

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-03-06T00:39:37

Your query wouldn't work anyway, because you need to do the lead() before you filter by day.

In any case, you can use a CTE and a join:

with t as (
      select day, count(*) as cnt
      from table t
      group by day
     )
select day, cnt, tnext.cnt as next_cnt
from t left join
     t tnext
     on tnext.day = date_add(t.day, 1);

EDIT:

The version without the CTE is very similar:

select day, cnt, tnext.cnt as next_cnt
from (select day, count(*) as cnt
      from table t
      group by day
     ) t left join
     (select day, count(*) as cnt
      from table t
      group by day
     ) tnext
     on tnext.day = date_add(t.day, 1);

Record count for day and next day from table

1 Answers