Hi Nitin, Thanks for your question. Could you/did you share your code? If not, could you please post a draft PR so that we can take a look and offer suggestions? Thanks, -- C
> On Nov 5, 2019, at 7:27 AM, Nitin Pawar <nitinpawar...@gmail.com> wrote: > > Hi Devs, > > I had sent request for this almost 2.5 years ago. Trying it again now. > > Currently Apache drill window functions LEAD and LAG support offset as 1. > In another words in a given window these functions can return either > previous or next row only. > > > I am trying modify the behavior these function and allow offset >=1 in > query such as > select employee_id, department_id,salary, lag(salary,*4*) over(partition by > department_id order by salary asc) from cp.`employee.json`; > > I have managed to remove the limitation which fails the query can not have > offset > 1 and able to pass the offset to actual function implementation. > > Currently I am stuck where the record processor is crossing the window > boundary of department_id and gets row from next/previous window in > lead/lag function > > For eg: If you notice in row 2 for department_id=2, it is getting previous > windows of department_id=1 > > Here is sample output for below query > apache drill> select employee_id, department_id,salary, lag(salary,4) > over(partition by department_id order by salary asc) from > cp.`employee.json` where department_id <=3; > +-------------+---------------+---------+----------+ > | employee_id | department_id | salary | EXPR$3 | > +-------------+---------------+---------+----------+ > | 20 | 1 | 30000.0 | null | > | 5 | 1 | 35000.0 | null | > | 22 | 1 | 35000.0 | null | > | 21 | 1 | 35000.0 | null | > | 2 | 1 | 40000.0 | 30000.0 | > | 4 | 1 | 40000.0 | 35000.0 | > | 1 | 1 | 80000.0 | 35000.0 | > | 37 | 2 | 6700.0 | null | > | 38 | 2 | 8000.0 | 40000.0 | > | 39 | 2 | 10000.0 | 40000.0 | > | 40 | 2 | 10000.0 | 80000.0 | > | 6 | 2 | 25000.0 | 6700.0 | > | 42 | 3 | 5000.0 | null | > | 41 | 3 | 8500.0 | 10000.0 | > | 7 | 3 | 15000.0 | 10000.0 | > | 36 | 3 | 45000.0 | 25000.0 | > +-------------+---------------+---------+----------+ > > > Thanks, > Nitin Pawar
