Unable to use RANK, OVER, WINDOW functions in MySQL Workbench v8.0.1

user_6315118 0
Viewed 327

I'm using MySQL Workbench v8.0.1 as per the below version check and still unable to use functions like RANK(), DENSE_RANK(), WINDOW, OVER, PRECEDING, UNBOUNDED PRECEDING, and all others which should be supported in v8.0 and above.

My query:

WITH daily_shipping_summary AS 
( 
    SELECT ship_date, SUM(shipping_cost) AS daily_total FROM market_fact_full AS m
    INNER JOIN shipping_dimen AS s
    ON s.ship_id = m.ship_id
    GROUP BY ship_date
)
SELECT *,
SUM(daily_total) OVER w1 AS running_total,
AVG(daily_total) OVER w2 AS moving_avg
FROM daily_shipping_summary
WINDOW w1 AS (ORDER BY daily_total ROWS UNBOUNDED PRECEDING),
w2 AS (ORDER BY daily_total ROWS 6 PRECEDING)

Getting this below error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'w1 AS running_total, AVG(daily_total) OVER w2 AS moving_avg FROM daily_shipp' at line 10

Could anyone please help me in how to get this resolved?

MySQL Workbench version details:

Variable_name Value
innodb_version 8.0.1
protocol_version 10
tls_version TLSv1,TLSv1.1
version 8.0.1-dmr-log
version_comment MySQL Community Server (GPL)
dense-rank rank window-functions mysql
1 Answers

Use the word WINDOW once, then do name as (spec), name2 as (spec2) after it

Example

See comment about doing it inline if you don't plan to reuse the window spec (or prefer do it inline most the time even if the spec is reused, which is what we tend to do because it avoids having to jump around the sql to work out what does what)

user_14106640
Related