本文介绍了相当于Oracle超前和滞后功能的Teradata的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我一直在努力,看到与Oracle超前和滞后功能等效的功能.
I have been working ot see the equivalent function for Oracle lead and lag function.
甲骨文线索看起来像
LEAD(col1.date,1,ADD_MONTHS(col1.DATE,12))
OVER(Partition By tab.a,tab.b,tab.c Order By tab.a)-1 END_DATE
LAG(col1.DATE + 7,1,col1.DATE-1)
OVER(partition by tab.a,tab.b Order By tab.b) LAG_DATE
任何更好的主意
推荐答案
我相信您可以将以下SQL作为基础并对其进行修改以满足您的需求:
I believe you can take the following SQL as a basis and modify it to meet your needs:
SELECT CALENDAR_DATE
, MAX(CALENDAR_DATE)
OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS Lag_ --Yesterday
, MIN(CALENDAR_DATE)
OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS Lead_ --Tomorrow
FROM SysCalendar.CALENDAR
WHERE year_of_calendar = 2011
AND month_of_year = 11
如果之前或之后没有记录,则返回NULL,并且可以根据需要使用COALESCE对其进行寻址.
NULL is returned when there is no record before or after and can be addressed with a COALESCE as necessary.
编辑.在Teradata 16.00中,引入了LAG/LEAD功能.
EDIT In Teradata 16.00 LAG/LEAD functions were introduced.
这篇关于相当于Oracle超前和滞后功能的Teradata的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!