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*
