如何获取以前的记录信息 [英] How to get previous record information

问题描述

我需要检索列Random_1，Random_2，Random_3 我的另一个表正在按日期键9/25/2016联接数据.我还需要看到Random_1，Random_2，Random_3看着9/22/2016.我想知道如何从我的加入日期键说出什么日期，而可能显示给我以前的记录信息的日期. 日期是日期，我正在使用Oracle (日期会改变)

I need to retrieve the columns Random_1, Random_2, Random_3 My other table is joining data off the date key 9/25/2016. I also need to see Random_1, Random_2,Random_3 looking at the 9/22/2016. I want to know how to say from my join on date key whatever the date may be show me the previous record information. Dates are dates and I am using Oracle (The dates will change)

该怎么办? 参见示例链接

推荐答案

要拉取Random_1作为前一个日期，可以使用LAG()函数，如下所示:

To pull Random_1 for the previous date, you would use the LAG() function, like so:

select ... ,  lag(t1.random_1) over (order by t1.date_key), .....
from  table1 t1 join table2 t2 on t1.date_key = t2.date_key
...

(其他列也是如此).请注意，对于最早的行，LAG()的结果当然是NULL-因为没有"previous"值.如果第一行还需要其他内容，请将所有内容包装在COALESCE()中.

(and same for the other columns). Note that the result of LAG() will, of course, be NULL for the earliest row - since there is no "previous" value. If you want something else for the first row, wrap everything within a COALESCE().

此外，如果您有某种id，并且也通过id加入，那么您就不想将不同ID的日期混合在一起.除了按日期排序之外，LAG()函数(以及几乎所有其他分析函数)还允许您partition by id.您可以在 Oracle文档.

Also, if you have id's of some kind and you join by id as well, then you don't want to mix together dates for different id's. The LAG() functions (and almost all other analytic functions) allows you to partition by id in addition to ordering by date. You can read the definition and examples in the Oracle documentation.

这篇关于如何获取以前的记录信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！