How to use expression as LAG() second parameter on MySQL 8 (like MSSQL)?

I am trying to migrate this SQL SERVER query:

SELECT year, sales,   
    LAG(year-2, 2*(SELECT MIN(sales) FROM product_sales), sales/2.0 ) OVER (ORDER BY year) AS no_sense  
FROM product_sales;

dbfiddle link

To MySQL 8 (same query):

dbfiddle link

Unfortunately I got this error:

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 ‘*(SELECT MIN(sales) FROM product_sales), sales/2.0 ) OVER (ORDER BY year) AS no_’ at line 2

Is it possible to “port” this query to mysql?

Thanks in advance !

Answer

Note: this answer is based on the Vignesh Kumar A ‘s answer. So why MySQL 8 does not support that SQL Server syntax is already fully explained in his answer, i choose to not explain it double.

In MySQL 8 you would need to make a dynamic SQL query out of it, as offset param of LAG() does not support SQL expressions..

SET @sql = CONCAT("
SELECT
    year
  , sales
  , LAG(year-2,  ",(SELECT FLOOR(MIN(sales)) FROM product_sales),", sales/2.0 ) OVER (ORDER BY year) AS no_sense  
FROM product_sales;
");

PREPARE q FROM @sql;
EXECUTE q;

Note: FLOOR() is there to fix 19874.00 not giving a error on the lag function. Offcource you could rewite that SET @sql := CONCAT("..") part different, just use the writing style you understand the best.

Result

| year | sales | no_sense |
| ---- | ----- | -------- |
| 2017 | 55000 | 27500    |
| 2017 | 78000 | 39000    |
| 2017 | 49000 | 24500    |
| 2017 | 32000 | 16000    |
| 2018 | 41000 | 20500    |
| 2018 | 89651 | 44825.5  |
| 2018 | 19874 | 9937     |
| 2018 | 32562 | 16281    |
| 2019 | 87456 | 43728    |
| 2019 | 75000 | 37500    |
| 2019 | 96500 | 48250    |
| 2019 | 85236 | 42618    |

see demo

This works because PREPARE q FROM @sql; generates this SQL. (Vignesh Kumar A answer)

SELECT 
   year  
 , sales  
 , LAG(year-2, 19874, sales/2.0 ) OVER (ORDER BY year) AS no_sense
FROM product_sales;