You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Timo Walther <tw...@apache.org> on 2021/02/01 14:06:20 UTC
Re: LEAD/LAG functions
Hi Patrick,
I could imagine that LEAD/LAG are translated into RANK/ROW_NUMBER
operations that are not supported in this context.
But I will loop in @Jark who might know more about the limitaitons here.
Regards,
Timo
On 29.01.21 17:37, Patrick Angeles wrote:
> Another (hopefully newbie) question. Trying to use LEAD/LAG over window
> functions. I get the following error. The exact same query works
> properly using FIRST_VALUE instead of LEAD.
>
> Thanks in advance...
>
> - Patrick
>
> Flink SQL> describe l1_min ;
>
> +-----------+------------------------+------+-----+--------+-----------+
>
> |name | type | null | key | extras | watermark |
>
> +-----------+------------------------+------+-----+--------+-----------+
>
> |symbol | STRING | true | || |
>
> | t_start | TIMESTAMP(3) *ROWTIME* | true | || |
>
> | ask_price | DOUBLE | true | || |
>
> | bid_price | DOUBLE | true | || |
>
> | mid_price | DOUBLE | true | || |
>
> +-----------+------------------------+------+-----+--------+-----------+
>
> 5 rows in set
>
>
> Flink SQL> SELECT
>
>> symbol,
>
>> t_start,
>
>> ask_price,
>
>> bid_price,
>
>> mid_price,
>
>> LEAD (mid_price) OVER x AS prev_price
>
>> FROM l1_min
>
>> WINDOW x AS (
>
>> PARTITION BY symbol
>
>> ORDER BY t_start
>
>> ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
>
>> )
>
>> ;
>
> *[ERROR] Could not execute SQL statement. Reason:*
>
> *org.apache.calcite.sql.validate.SqlValidatorException: ROW/RANGE not
> allowed with RANK, DENSE_RANK or ROW_NUMBER functions*
>
Re: LEAD/LAG functions
Posted by Patrick Angeles <pa...@gmail.com>.
Thanks, Jark.
On Mon, Feb 1, 2021 at 11:50 PM Jark Wu <im...@gmail.com> wrote:
> Yes. RANK/ROW_NUMBER is not allowed with ROW/RANGE over window,
> i.e. the "ROWS BETWEEN 1 PRECEDING AND CURRENT ROW" clause.
>
> Best,
> Jark
>
> On Mon, 1 Feb 2021 at 22:06, Timo Walther <tw...@apache.org> wrote:
>
>> Hi Patrick,
>>
>> I could imagine that LEAD/LAG are translated into RANK/ROW_NUMBER
>> operations that are not supported in this context.
>>
>> But I will loop in @Jark who might know more about the limitaitons here.
>>
>> Regards,
>> Timo
>>
>>
>> On 29.01.21 17:37, Patrick Angeles wrote:
>> > Another (hopefully newbie) question. Trying to use LEAD/LAG over window
>> > functions. I get the following error. The exact same query works
>> > properly using FIRST_VALUE instead of LEAD.
>> >
>> > Thanks in advance...
>> >
>> > - Patrick
>> >
>> > Flink SQL> describe l1_min ;
>> >
>> > +-----------+------------------------+------+-----+--------+-----------+
>> >
>> > |name | type | null | key | extras | watermark |
>> >
>> > +-----------+------------------------+------+-----+--------+-----------+
>> >
>> > |symbol | STRING | true | || |
>> >
>> > | t_start | TIMESTAMP(3) *ROWTIME* | true | || |
>> >
>> > | ask_price | DOUBLE | true | || |
>> >
>> > | bid_price | DOUBLE | true | || |
>> >
>> > | mid_price | DOUBLE | true | || |
>> >
>> > +-----------+------------------------+------+-----+--------+-----------+
>> >
>> > 5 rows in set
>> >
>> >
>> > Flink SQL> SELECT
>> >
>> >> symbol,
>> >
>> >> t_start,
>> >
>> >> ask_price,
>> >
>> >> bid_price,
>> >
>> >> mid_price,
>> >
>> >> LEAD (mid_price) OVER x AS prev_price
>> >
>> >> FROM l1_min
>> >
>> >> WINDOW x AS (
>> >
>> >> PARTITION BY symbol
>> >
>> >> ORDER BY t_start
>> >
>> >> ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
>> >
>> >> )
>> >
>> >> ;
>> >
>> > *[ERROR] Could not execute SQL statement. Reason:*
>> >
>> > *org.apache.calcite.sql.validate.SqlValidatorException: ROW/RANGE not
>> > allowed with RANK, DENSE_RANK or ROW_NUMBER functions*
>> >
>>
>>
Re: LEAD/LAG functions
Posted by Jark Wu <im...@gmail.com>.
Yes. RANK/ROW_NUMBER is not allowed with ROW/RANGE over window,
i.e. the "ROWS BETWEEN 1 PRECEDING AND CURRENT ROW" clause.
Best,
Jark
On Mon, 1 Feb 2021 at 22:06, Timo Walther <tw...@apache.org> wrote:
> Hi Patrick,
>
> I could imagine that LEAD/LAG are translated into RANK/ROW_NUMBER
> operations that are not supported in this context.
>
> But I will loop in @Jark who might know more about the limitaitons here.
>
> Regards,
> Timo
>
>
> On 29.01.21 17:37, Patrick Angeles wrote:
> > Another (hopefully newbie) question. Trying to use LEAD/LAG over window
> > functions. I get the following error. The exact same query works
> > properly using FIRST_VALUE instead of LEAD.
> >
> > Thanks in advance...
> >
> > - Patrick
> >
> > Flink SQL> describe l1_min ;
> >
> > +-----------+------------------------+------+-----+--------+-----------+
> >
> > |name | type | null | key | extras | watermark |
> >
> > +-----------+------------------------+------+-----+--------+-----------+
> >
> > |symbol | STRING | true | || |
> >
> > | t_start | TIMESTAMP(3) *ROWTIME* | true | || |
> >
> > | ask_price | DOUBLE | true | || |
> >
> > | bid_price | DOUBLE | true | || |
> >
> > | mid_price | DOUBLE | true | || |
> >
> > +-----------+------------------------+------+-----+--------+-----------+
> >
> > 5 rows in set
> >
> >
> > Flink SQL> SELECT
> >
> >> symbol,
> >
> >> t_start,
> >
> >> ask_price,
> >
> >> bid_price,
> >
> >> mid_price,
> >
> >> LEAD (mid_price) OVER x AS prev_price
> >
> >> FROM l1_min
> >
> >> WINDOW x AS (
> >
> >> PARTITION BY symbol
> >
> >> ORDER BY t_start
> >
> >> ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
> >
> >> )
> >
> >> ;
> >
> > *[ERROR] Could not execute SQL statement. Reason:*
> >
> > *org.apache.calcite.sql.validate.SqlValidatorException: ROW/RANGE not
> > allowed with RANK, DENSE_RANK or ROW_NUMBER functions*
> >
>
>