针对“来自具有最大日期的行中的属性"的解析函数被设置为"0". [英] Analytic functions for "the attribute from the row with the max date"

问题描述

我正在重构一位同事的代码，在几种情况下，他正在使用游标获取与某些谓词匹配的最新行":

I'm refactoring a colleague's code, and I have several cases where he's using a cursor to get "the latest row that matches some predicate":

他的技术是将联接写为游标，按日期字段降序对其进行排序，打开游标，获取第一行，然后关闭游标.

His technique is to write the join as a cursor, order it by the date field descending, open the cursor, get the first row, and close the cursor.

这需要为驱动该结果的结果集的每一行调用一个游标，这对于许多行来说是昂贵的.我希望能够加入，但是比相关子查询便宜的东西:

This requires calling a cursor for each row of the result set that drives this, which is costly for many rows. I'd prefer to be able to join, but what something cheaper than a correlated subquery:

select a.id_shared_by_several_rows, a.foo from audit_trail a
where a.entry_date = (select max(a.entry_date) 
                     from audit_trail b 
                     where b.id_shared_by_several_rows = a.id_shared_by_several_rows 
                     );

我猜想由于这是普遍需要，所以有一个Oracle分析函数可以做到这一点?

I'm guessing that since this is a common need, there's an Oracle analytic function that does this?

推荐答案

这只会对数据进行一次传递，并且可用于根据需要从表中获取尽可能多的列，而无需进行自我联接.

This will only do one pass over the data, and may be used to get as many columns from the table as needed without doing a self-join.

select DISTINCT
       a.id_shared_by_several_rows,
       FIRST_VALUE(a.foo)
       OVER (PARTITION BY a.id_shared_by_several_rows
             ORDER BY a.entry_date DESC)
       AS foo
from audit_trail a;

这篇关于针对“来自具有最大日期的行中的属性"的解析函数被设置为"0".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！