How do I use IFNULL with window function in MySQL

Question

How do I use ifnull with a window function?

Let's say I have this query result below, lag1 is just the budget column shifted down using the window function, because of that, the value is null. But I want to replace that null with a zero so I can calculate the difference between budget and lag1.

select id, budget,
    lag(budget) over (order by id) as lag1
    from projects;

+----+---------+---------+
| id | budget  | lag1    |
+----+---------+---------+    
|  1 | 1000000 |    NULL |  
|  2 |  100000 | 1000000 |  
|  3 |     100 |  100000 |
+----+---------+---------+

I tried the following two examples but it doesn't work:

select id, budget,
ifnull(lag(budget),0) over (order by id) as lag1
from projects;

select id, budget,
ifnull((lag(budget) over (order by id) as lag1),0)
from projects;

Answer 1

lag() accepts up to three parameters. The first one is the expression for the returned value. Here it's simply the column name. The second one determines how many rows it should look behind. The default is one. And the third, the interesting one for you, is a default value if no previous row was found.

So you can define your default directly in the lag() call:

SELECT id,
       budget,
       lag(budget, 1, 0) OVER (ORDER BY id) lag1
       FROM projects;

Answer 2

I guess your mysql version lower than 8.0 because your second solution had been worked.

select id, budget, ifnull(lag(budget) over (order by id),0) as lag1
from projects;

sqlfiddle

if you mysql version lower than 8.0, you need to write a subquery in select to get before row data by ID

TESTDDL

CREATE TABLE projects(
  ID INT,
   budget INT
);
INSERT INTO projects VALUES (1,1000000);
INSERT INTO projects VALUES (2,100000);
INSERT INTO projects VALUES (3,100);

Query

SELECT *,IFNULL((select budget FROM projects t1 WHERE t.id > t1.id order by t1.id desc limit 1 ) ,0) lag1
FROM projects t

[Results]:

| ID |  budget |    lag1 |
|----|---------|---------|
|  1 | 1000000 |       0 |
|  2 |  100000 | 1000000 |
|  3 |     100 |  100000 |

sqlfiddle