我的LAG函数没有像预期的MySQL 8.x那样工作

Question

我的表如下所示：

metro_region，value，date

在11月份具有多个值(每个日期一个(。大约有100个都会区。

我希望我的报告有以下数据：

Metro_region  Today  Yesterday  2daysAgo  3dayAgo
MetroRegionA   40.1    54.3       64.8     48.1
MetroRegionB   31.1    53.1       97.8     43.2

我尝试过的：

select 
metro_region, 
date,
LAG(value,3) over (Partition by metro order by metro) as "3daysAgo", 
LAG(value,2) over (Partition by metro order by metro) as "2daysAgo", 
LAG(value,1) over (Partition by metro order by metro) as "Yesterday", 
value as Today
from mytable 
where date = curdate();

我怀疑我没有正确分区。。。或者只是严重遗漏了如何使用CCD_ 1。。。任何见解都将不胜感激！

Answer 1

您需要今天和过去3天的结果，所以您不能只筛选今天的结果
首先获取4天的结果，然后筛选今天的结果
此外，在每个分区中，必须按date而不是metro:进行排序

select metro_region, date, `3daysAgo`, `2daysAgo`, `Yesterday`, Today
from (
select metro_region, 
date, 
LAG(value,3) over (partition by metro_region order by date) as `3daysAgo`, 
LAG(value,2) over (partition by metro_region order by date) as `2daysAgo`, 
LAG(value,1) over (partition by metro_region order by date) as `Yesterday`, 
value as Today 
from mytable 
where date between curdate() - interval 3 day and curdate()
) t
where date = curdate()

查看简化的演示。