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
