You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Nitin Pawar <ni...@gmail.com> on 2016/11/23 13:34:07 UTC
Window function
Hi,
according to DRILL-3596 <https://issues.apache.org/jira/browse/DRILL-3596>,
lead or lag function are limited to use offset as 1.
according to documentation on postgres
lag(value any [, offset integer [, default any ]]) same type as value
returns value evaluated at the row that is offset rows before the current
row within the partition; if there is no such row, instead return default.
Both offset and default are evaluated with respect to the current row. If
omitted, offset defaults to 1 and default to null
is there any plan to allow offset according to needs but not restrict equal
to 1
usecase :
I have daily data for a month.
every day I want to do a delta with last week same day like compare monday
with monday and tuesday with tuesday so basically do a lag(col, 7)
--
Nitin Pawar
Re: Window function
Posted by Nitin Pawar <ni...@gmail.com>.
Thanks Deneche for the explanation.
Thanks Khurram for the ticket.
let me see if I can pick it up and close it soon.
On Sun, Dec 4, 2016 at 12:11 AM, Khurram Faraaz <kf...@maprtech.com>
wrote:
> 1. DRILL-5099 <https://issues.apache.org/jira/browse/DRILL-5099> is
> created to track this.
>
>
> On Sat, Dec 3, 2016 at 5:46 PM, Khurram Faraaz <kf...@maprtech.com>
> wrote:
>
> > Hakim, thanks for sharing those details and the explanation.
> > I will file a JIRA and anyone interested can pick it up and provide the
> > fix to support OFFSET values greater than one for LAG window function.
> >
> > Regards,
> > Khurram
> >
> > On Fri, Dec 2, 2016 at 10:18 PM, deneche abdelhakim <ad...@gmail.com>
> > wrote:
> >
> >> Hello Nitin,
> >>
> >> It's definitely possible to support offsets other than 1 for Lead and
> Lag,
> >> the main reason I didn't do it is just lack of time :P
> >>
> >> Things that need to be done to make Lag (or Lead) support offsets other
> >> than 1:
> >> - WindowFunction.Lead should extract the offset value from its
> >> FunctionCall
> >> argument, you can look at WindowFunctionNtile.numTilesFromExpression()
> >> for
> >> and example on how to do that.
> >> - make sure calls to copyNext() and copyPrev() in NoFrameSupportTemplate
> >> use the offset and not the hard coded value (you already figured that
> out)
> >> - finally make sure you update UnsupportedOperatorsVisitor to no longer
> >> throw an exception when we pass an offset value other than 1 to Lead or
> >> Lag. Just search for DRILL-3596 in that class and you will find the if
> >> block that need to be removed
> >>
> >> I think this should be enough to get it to work in the general case, do
> >> you
> >> want to volunteer and get this done ? that would be an awesome
> >> contribution
> >> to the project.
> >>
> >> Thanks
> >>
> >> On Thu, Dec 1, 2016 at 10:10 PM Nitin Pawar <ni...@gmail.com>
> >> wrote:
> >>
> >> > any help on this ?
> >> >
> >> > from class NoFrameSupportTemplate, I see that
> >> >
> >> > inIndex is hard coded to point to previous row in case of lag and
> >> > next row in case of lead.
> >> >
> >> > Is there a way I can modify this and pass it as parameter to pic
> >> > appropriate row?
> >> >
> >> >
> >> > On Fri, Nov 25, 2016 at 2:57 PM, Nitin Pawar <nitinpawar432@gmail.com
> >
> >> > wrote:
> >> >
> >> > > adding dev list for comments
> >> > >
> >> > > On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <
> nitinpawar432@gmail.com
> >> >
> >> > > wrote:
> >> > >
> >> > >> Hi,
> >> > >>
> >> > >> according to DRILL-3596
> >> > >> <https://issues.apache.org/jira/browse/DRILL-3596>, lead or lag
> >> > function
> >> > >> are limited to use offset as 1.
> >> > >>
> >> > >> according to documentation on postgres
> >> > >> lag(value any [, offset integer [, default any ]]) same type as
> value
> >> > >> returns value evaluated at the row that is offset rows before the
> >> > >> current row within the partition; if there is no such row, instead
> >> > return
> >> > >> default. Both offset and default are evaluated with respect to the
> >> > >> current row. If omitted, offset defaults to 1 and default to null
> >> > >>
> >> > >>
> >> > >> is there any plan to allow offset according to needs but not
> restrict
> >> > >> equal to 1
> >> > >>
> >> > >> usecase :
> >> > >>
> >> > >> I have daily data for a month.
> >> > >> every day I want to do a delta with last week same day like compare
> >> > >> monday with monday and tuesday with tuesday so basically do a
> >> lag(col,
> >> > 7)
> >> > >>
> >> > >> --
> >> > >> Nitin Pawar
> >> > >>
> >> > >
> >> > >
> >> > >
> >> > > --
> >> > > Nitin Pawar
> >> > >
> >> >
> >> >
> >> >
> >> > --
> >> > Nitin Pawar
> >> >
> >>
> >
> >
>
--
Nitin Pawar
Re: Window function
Posted by Khurram Faraaz <kf...@maprtech.com>.
1. DRILL-5099 <https://issues.apache.org/jira/browse/DRILL-5099> is
created to track this.
On Sat, Dec 3, 2016 at 5:46 PM, Khurram Faraaz <kf...@maprtech.com> wrote:
> Hakim, thanks for sharing those details and the explanation.
> I will file a JIRA and anyone interested can pick it up and provide the
> fix to support OFFSET values greater than one for LAG window function.
>
> Regards,
> Khurram
>
> On Fri, Dec 2, 2016 at 10:18 PM, deneche abdelhakim <ad...@gmail.com>
> wrote:
>
>> Hello Nitin,
>>
>> It's definitely possible to support offsets other than 1 for Lead and Lag,
>> the main reason I didn't do it is just lack of time :P
>>
>> Things that need to be done to make Lag (or Lead) support offsets other
>> than 1:
>> - WindowFunction.Lead should extract the offset value from its
>> FunctionCall
>> argument, you can look at WindowFunctionNtile.numTilesFromExpression()
>> for
>> and example on how to do that.
>> - make sure calls to copyNext() and copyPrev() in NoFrameSupportTemplate
>> use the offset and not the hard coded value (you already figured that out)
>> - finally make sure you update UnsupportedOperatorsVisitor to no longer
>> throw an exception when we pass an offset value other than 1 to Lead or
>> Lag. Just search for DRILL-3596 in that class and you will find the if
>> block that need to be removed
>>
>> I think this should be enough to get it to work in the general case, do
>> you
>> want to volunteer and get this done ? that would be an awesome
>> contribution
>> to the project.
>>
>> Thanks
>>
>> On Thu, Dec 1, 2016 at 10:10 PM Nitin Pawar <ni...@gmail.com>
>> wrote:
>>
>> > any help on this ?
>> >
>> > from class NoFrameSupportTemplate, I see that
>> >
>> > inIndex is hard coded to point to previous row in case of lag and
>> > next row in case of lead.
>> >
>> > Is there a way I can modify this and pass it as parameter to pic
>> > appropriate row?
>> >
>> >
>> > On Fri, Nov 25, 2016 at 2:57 PM, Nitin Pawar <ni...@gmail.com>
>> > wrote:
>> >
>> > > adding dev list for comments
>> > >
>> > > On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <nitinpawar432@gmail.com
>> >
>> > > wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> according to DRILL-3596
>> > >> <https://issues.apache.org/jira/browse/DRILL-3596>, lead or lag
>> > function
>> > >> are limited to use offset as 1.
>> > >>
>> > >> according to documentation on postgres
>> > >> lag(value any [, offset integer [, default any ]]) same type as value
>> > >> returns value evaluated at the row that is offset rows before the
>> > >> current row within the partition; if there is no such row, instead
>> > return
>> > >> default. Both offset and default are evaluated with respect to the
>> > >> current row. If omitted, offset defaults to 1 and default to null
>> > >>
>> > >>
>> > >> is there any plan to allow offset according to needs but not restrict
>> > >> equal to 1
>> > >>
>> > >> usecase :
>> > >>
>> > >> I have daily data for a month.
>> > >> every day I want to do a delta with last week same day like compare
>> > >> monday with monday and tuesday with tuesday so basically do a
>> lag(col,
>> > 7)
>> > >>
>> > >> --
>> > >> Nitin Pawar
>> > >>
>> > >
>> > >
>> > >
>> > > --
>> > > Nitin Pawar
>> > >
>> >
>> >
>> >
>> > --
>> > Nitin Pawar
>> >
>>
>
>
Re: Window function
Posted by Khurram Faraaz <kf...@maprtech.com>.
Hakim, thanks for sharing those details and the explanation.
I will file a JIRA and anyone interested can pick it up and provide the fix
to support OFFSET values greater than one for LAG window function.
Regards,
Khurram
On Fri, Dec 2, 2016 at 10:18 PM, deneche abdelhakim <ad...@gmail.com>
wrote:
> Hello Nitin,
>
> It's definitely possible to support offsets other than 1 for Lead and Lag,
> the main reason I didn't do it is just lack of time :P
>
> Things that need to be done to make Lag (or Lead) support offsets other
> than 1:
> - WindowFunction.Lead should extract the offset value from its FunctionCall
> argument, you can look at WindowFunctionNtile.numTilesFromExpression() for
> and example on how to do that.
> - make sure calls to copyNext() and copyPrev() in NoFrameSupportTemplate
> use the offset and not the hard coded value (you already figured that out)
> - finally make sure you update UnsupportedOperatorsVisitor to no longer
> throw an exception when we pass an offset value other than 1 to Lead or
> Lag. Just search for DRILL-3596 in that class and you will find the if
> block that need to be removed
>
> I think this should be enough to get it to work in the general case, do you
> want to volunteer and get this done ? that would be an awesome contribution
> to the project.
>
> Thanks
>
> On Thu, Dec 1, 2016 at 10:10 PM Nitin Pawar <ni...@gmail.com>
> wrote:
>
> > any help on this ?
> >
> > from class NoFrameSupportTemplate, I see that
> >
> > inIndex is hard coded to point to previous row in case of lag and
> > next row in case of lead.
> >
> > Is there a way I can modify this and pass it as parameter to pic
> > appropriate row?
> >
> >
> > On Fri, Nov 25, 2016 at 2:57 PM, Nitin Pawar <ni...@gmail.com>
> > wrote:
> >
> > > adding dev list for comments
> > >
> > > On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <ni...@gmail.com>
> > > wrote:
> > >
> > >> Hi,
> > >>
> > >> according to DRILL-3596
> > >> <https://issues.apache.org/jira/browse/DRILL-3596>, lead or lag
> > function
> > >> are limited to use offset as 1.
> > >>
> > >> according to documentation on postgres
> > >> lag(value any [, offset integer [, default any ]]) same type as value
> > >> returns value evaluated at the row that is offset rows before the
> > >> current row within the partition; if there is no such row, instead
> > return
> > >> default. Both offset and default are evaluated with respect to the
> > >> current row. If omitted, offset defaults to 1 and default to null
> > >>
> > >>
> > >> is there any plan to allow offset according to needs but not restrict
> > >> equal to 1
> > >>
> > >> usecase :
> > >>
> > >> I have daily data for a month.
> > >> every day I want to do a delta with last week same day like compare
> > >> monday with monday and tuesday with tuesday so basically do a lag(col,
> > 7)
> > >>
> > >> --
> > >> Nitin Pawar
> > >>
> > >
> > >
> > >
> > > --
> > > Nitin Pawar
> > >
> >
> >
> >
> > --
> > Nitin Pawar
> >
>
Re: Window function
Posted by deneche abdelhakim <ad...@gmail.com>.
Hello Nitin,
It's definitely possible to support offsets other than 1 for Lead and Lag,
the main reason I didn't do it is just lack of time :P
Things that need to be done to make Lag (or Lead) support offsets other
than 1:
- WindowFunction.Lead should extract the offset value from its FunctionCall
argument, you can look at WindowFunctionNtile.numTilesFromExpression() for
and example on how to do that.
- make sure calls to copyNext() and copyPrev() in NoFrameSupportTemplate
use the offset and not the hard coded value (you already figured that out)
- finally make sure you update UnsupportedOperatorsVisitor to no longer
throw an exception when we pass an offset value other than 1 to Lead or
Lag. Just search for DRILL-3596 in that class and you will find the if
block that need to be removed
I think this should be enough to get it to work in the general case, do you
want to volunteer and get this done ? that would be an awesome contribution
to the project.
Thanks
On Thu, Dec 1, 2016 at 10:10 PM Nitin Pawar <ni...@gmail.com> wrote:
> any help on this ?
>
> from class NoFrameSupportTemplate, I see that
>
> inIndex is hard coded to point to previous row in case of lag and
> next row in case of lead.
>
> Is there a way I can modify this and pass it as parameter to pic
> appropriate row?
>
>
> On Fri, Nov 25, 2016 at 2:57 PM, Nitin Pawar <ni...@gmail.com>
> wrote:
>
> > adding dev list for comments
> >
> > On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <ni...@gmail.com>
> > wrote:
> >
> >> Hi,
> >>
> >> according to DRILL-3596
> >> <https://issues.apache.org/jira/browse/DRILL-3596>, lead or lag
> function
> >> are limited to use offset as 1.
> >>
> >> according to documentation on postgres
> >> lag(value any [, offset integer [, default any ]]) same type as value
> >> returns value evaluated at the row that is offset rows before the
> >> current row within the partition; if there is no such row, instead
> return
> >> default. Both offset and default are evaluated with respect to the
> >> current row. If omitted, offset defaults to 1 and default to null
> >>
> >>
> >> is there any plan to allow offset according to needs but not restrict
> >> equal to 1
> >>
> >> usecase :
> >>
> >> I have daily data for a month.
> >> every day I want to do a delta with last week same day like compare
> >> monday with monday and tuesday with tuesday so basically do a lag(col,
> 7)
> >>
> >> --
> >> Nitin Pawar
> >>
> >
> >
> >
> > --
> > Nitin Pawar
> >
>
>
>
> --
> Nitin Pawar
>
Re: Window function
Posted by Nitin Pawar <ni...@gmail.com>.
any help on this ?
from class NoFrameSupportTemplate, I see that
inIndex is hard coded to point to previous row in case of lag and
next row in case of lead.
Is there a way I can modify this and pass it as parameter to pic
appropriate row?
On Fri, Nov 25, 2016 at 2:57 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> adding dev list for comments
>
> On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <ni...@gmail.com>
> wrote:
>
>> Hi,
>>
>> according to DRILL-3596
>> <https://issues.apache.org/jira/browse/DRILL-3596>, lead or lag function
>> are limited to use offset as 1.
>>
>> according to documentation on postgres
>> lag(value any [, offset integer [, default any ]]) same type as value
>> returns value evaluated at the row that is offset rows before the
>> current row within the partition; if there is no such row, instead return
>> default. Both offset and default are evaluated with respect to the
>> current row. If omitted, offset defaults to 1 and default to null
>>
>>
>> is there any plan to allow offset according to needs but not restrict
>> equal to 1
>>
>> usecase :
>>
>> I have daily data for a month.
>> every day I want to do a delta with last week same day like compare
>> monday with monday and tuesday with tuesday so basically do a lag(col, 7)
>>
>> --
>> Nitin Pawar
>>
>
>
>
> --
> Nitin Pawar
>
--
Nitin Pawar
Re: Window function
Posted by Nitin Pawar <ni...@gmail.com>.
adding dev list for comments
On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> Hi,
>
> according to DRILL-3596 <https://issues.apache.org/jira/browse/DRILL-3596>,
> lead or lag function are limited to use offset as 1.
>
> according to documentation on postgres
> lag(value any [, offset integer [, default any ]]) same type as value
> returns value evaluated at the row that is offset rows before the current
> row within the partition; if there is no such row, instead return default.
> Both offset and default are evaluated with respect to the current row. If
> omitted, offset defaults to 1 and default to null
>
>
> is there any plan to allow offset according to needs but not restrict
> equal to 1
>
> usecase :
>
> I have daily data for a month.
> every day I want to do a delta with last week same day like compare monday
> with monday and tuesday with tuesday so basically do a lag(col, 7)
>
> --
> Nitin Pawar
>
--
Nitin Pawar
Re: Window function
Posted by Nitin Pawar <ni...@gmail.com>.
adding dev list for comments
On Wed, Nov 23, 2016 at 7:04 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> Hi,
>
> according to DRILL-3596 <https://issues.apache.org/jira/browse/DRILL-3596>,
> lead or lag function are limited to use offset as 1.
>
> according to documentation on postgres
> lag(value any [, offset integer [, default any ]]) same type as value
> returns value evaluated at the row that is offset rows before the current
> row within the partition; if there is no such row, instead return default.
> Both offset and default are evaluated with respect to the current row. If
> omitted, offset defaults to 1 and default to null
>
>
> is there any plan to allow offset according to needs but not restrict
> equal to 1
>
> usecase :
>
> I have daily data for a month.
> every day I want to do a delta with last week same day like compare monday
> with monday and tuesday with tuesday so basically do a lag(col, 7)
>
> --
> Nitin Pawar
>
--
Nitin Pawar