表:

UserId, Value, Date.

我想获得UserId,为每个UserId的最大值(日期)的值。也就是说,具有最新日期的每个UserId的值。有没有一种方法可以在SQL中简单地做到这一点?(最好是Oracle)

更新:为任何歧义道歉:我需要得到所有的用户id。但是对于每个UserId,只有该用户拥有最新日期的行。


当前回答

select userid, value, date
  from thetable t1 ,
       ( select t2.userid, max(t2.date) date2 
           from thetable t2 
          group by t2.userid ) t3
 where t3.userid t1.userid and
       t3.date2 = t1.date

恕我直言,这是可行的。HTH

其他回答

MySQL没有分区KEEP, DENSE_RANK概念的解决方案。

select userid,
       my_date,
       ...
from
(
select @sno:= case when @pid<>userid then 0
                    else @sno+1
    end as serialnumber, 
    @pid:=userid,
       my_Date,
       ...
from   users order by userid, my_date
) a
where a.serialnumber=0

参考:http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html

我没有Oracle来测试它,但最有效的解决方案是使用分析查询。它应该看起来像这样:

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

我怀疑您可以摆脱外部查询,并在内部放置distinct,但我不确定。与此同时,我知道这招管用。

如果您想了解分析查询,我建议您阅读http://www.orafaq.com/node/55和http://www.akadia.com/services/ora_analytic_functions.html。这是一个简短的总结。

在底层,分析查询对整个数据集进行排序,然后按顺序进行处理。当你处理它的时候,你根据特定的标准对数据集进行分区,然后对每一行查看一些窗口(默认为当前行分区中的第一个值-默认也是最有效的),并可以使用许多分析函数(其列表非常类似于聚合函数)计算值。

在本例中,下面是内部查询的功能。整个数据集先按UserId排序,再按Date DESC排序,然后一次处理。对于每一行,您返回UserId和该UserId看到的第一个日期(因为日期是按DESC排序的,所以这是最大日期)。这就得到了重复行的答案。然后外部的DISTINCT压缩重复项。

这并不是一个特别引人注目的分析查询示例。如果想获得更大的胜利,可以考虑制作一张财务收据表格,计算每个用户和收据的总花费。分析查询可以有效地解决这个问题。其他解决方案效率较低。这就是为什么它们是2003 SQL标准的一部分。(不幸的是Postgres还没有。叽阿…)

我知道你要求使用Oracle,但是在SQL 2005中我们现在使用这个:


-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

只是需要在工作中写一个“活”的例子:)

它支持在同一日期为UserId设置多个值。

列: 用户id,值,日期

SELECT
   DISTINCT UserId,
   MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
   MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
   SELECT UserId, Date, SUM(Value) As Values
   FROM <<table_name>>
   GROUP BY UserId, Date
)

您可以使用FIRST_VALUE而不是MAX,并在解释计划中查找它。我没有时间玩它。

当然,如果搜索巨大的表,在查询中使用FULL提示可能会更好。

在Oracle 12c+中,你可以使用Top n查询和分析函数排名来实现这一点,而且不需要子查询:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

上面的代码返回每个用户my_date最大的所有行。

如果你只想要一个最大日期的行,那么用row_number替换秩:

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;