我有桌子
id, date, value
a, 5/22/2019, 22
b, 5/22/2019, 22
c, 5/22/2019, 22
a, 5/21/2019, 21
b, 5/21/2019, 21
c, 5/21/2019, 21
a, 5/20/2019, 20
b, 5/20/2019, 20
c, 5/20/2019, 20
想要获取当前（最新）和上一个日期的ID和值：
id, date, date-1
a, 22, 21
b, 22, 21
c, 22, 21
到目前为止，我有一些部分，但每行仅需要1个ID（此波纹管为每个ID提供多于1行-对于同一ID，它将在下一行显示5/20日期，这是错误的）：
select
id, value,
lag(value, 1, 0) over (partition by id order by date ) as "date - 1"
from
table1
如何实现呢？
你可以尝试如下
with cte as
(
select
id, value,
lag(value, 1, 0) over (partition by id order by date ) as "date - 1",
lag(value, 2, 0) over (partition by id order by date ) as "date - 2",
row_number() over(partition by id order by date desc) rn
from
table1
) select * from cte where rn=1
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句