查找最大值和相关字段 [英] Find the Max and related fields

问题描述

这是我的(简化)问题，我猜很常见:
create table sample (client, recordDate, amount)

Here is my (simplified) problem, very common I guess:
create table sample (client, recordDate, amount)

我想为每个客户查找带有recordDate和数量的最新记录.
我制作了下面的代码，它可以工作，但是我想知道是否有更好的模式或Oracle调整来提高这种SELECT的效率. (我不允许修改数据库的结构，因此索引等对我来说是遥不可及的，而且超出了该问题的范围).

I want to find out the latest recording, for each client, with recordDate and amount.
I made the below code, which works, but I wonder if there is any better pattern or Oracle tweaks to improve the efficiency of such SELECT. (I am not allowed to modify to the structure of the database, so indexes etc are out of reach for me, and out of scope for the question).

select client, recordDate, Amount 
from sample s
inner join (select client, max(recordDate) lastDate
            from sample 
            group by client) t on s.id = t.id and s.recordDate = t.lastDate

该表有50万条记录，选择需要2-4秒的时间，这是可以接受的，但我很好奇是否可以改进.

The table has half a million records and the select takes 2-4 secs, which is acceptable but I am curious to see if that can be improved.

谢谢

推荐答案

在大多数情况下，窗口聚合函数的性能可能更好(至少更容易编写):

In most cases Windowed Aggregate Functions might perform better (at least it's easier to write):

select client, recordDate, Amount 
from 
  (
   select client, recordDate, Amount,
      rank() over (partition by client order by recordDate desc) as rn 
   from sample s
  ) dt
where rn = 1

这篇关于查找最大值和相关字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！