Thanks, Jark. On Mon, Feb 1, 2021 at 11:50 PM Jark Wu <imj...@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 <twal...@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* >> > >> >>
