如何在MySQL中将IFNULL与窗口函数一起使用

Question

如何将ifnull与窗口函数一起使用？

假设我有下面的查询结果，lag1只是使用窗口函数下移的预算列，因此，值为null。但我想用零代替这个null，这样我就可以计算预算和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 |
+----+---------+---------+

我尝试了以下两个例子，但都不起作用：

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()最多接受三个参数。第一个是返回值的表达式。这里只是列名。第二个决定了它应该向后看多少行。默认值为1。第三个，对您来说很有趣的一个，是一个默认值，如果没有找到前一行的话。

因此，您可以直接在lag()调用中定义默认值：

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

Answer 2

我猜您的mysql版本低于8.0，因为您的第二个解决方案已经运行。

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

sqlfiddle

如果您的mysql版本低于8.0，则需要在select中编写一个子查询，以通过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);

查询

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

[结果]:

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

sqlfiddle