Apache hive Windowing functions lag and lead error

Question

I am trying to select lead and lag value from a column using LAG and LEAD functions. Below are table and data.

hive> select * from foo;                                                      
OK
a
1
2
3
4
5
6
7
8
9
Time taken: 0.923 seconds

When I try to select lead and lag value, the following error is throwing.

hive> select LAG(a, 1) over (order by a) as previous, a, LEAD(a, 1) over (order by a) as next from foo;
FAILED: ParseException line 1:22 missing FROM at '(' near '(' in subquery source
line 1:23 cannot recognize input near 'order' 'by' 'a' in subquery source

How to solve tis issue?

Answer 1

I can see "partition by" clause is missing in the statement.

Select lead(a,1) over (partition by a order by a) as next from foo;

It won't be useful to run on just one column in table, if you have more columns like date/time, category, it will work.