Re: LEAD/LAG functions
Thanks, Jark. On Mon, Feb 1, 2021 at 11:50 PM Jark Wu 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 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
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 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
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*
LEAD/LAG functions
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*
