获取具有列的最大值的行

表:

UserId, Value, Date.

我想得到UserId,每个UserId的max(Date)值.也就是说,具有最新日期的每个UserId的值.有没有办法在SQL中执行此操作？(最好是Oracle)

更新:对任何含糊不清的道歉:我需要获取所有UserIds.但对于每个UserId,只有该用户具有最新日期的那一行.

1> Bill Karwin..：

---

我看到许多人使用子查询或特定于供应商的功能来执行此操作,但我经常以下列方式执行此类查询而不使用子查询.它使用普通的标准SQL,因此它应该适用于任何品牌的RDBMS.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

换句话说:从t1没有其他行的位置获取具有相同UserId和更大日期的行.

(我将标识符"Date"放在分隔符中,因为它是一个SQL保留字.)

万一,如果t1."Date" = t2."Date"出现加倍.通常表有auto_inc(seq)关键,例如id.为避免倍增可以使用如下:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

---

来自@Farhan的评论:

这是一个更详细的解释:

外部连接试图加入t1带t2.默认情况下,t1返回所有结果,如果匹配t2,则返回.如果t2给定行没有匹配t1,则查询仍返回行的行t1,并NULL用作所有t2列的占位符.这就是外连接的工作原理.

此查询中的技巧是设计连接的匹配条件,使其t2必须匹配相同的条件 userid,并且更大 date.这个想法是,如果一行中存在t2一个更大date的行,那么t1它与之比较的行不能是最大date的userid.但是如果没有匹配 - 即如果没有行存在t2大于date行t1- 我们知道行中t1的行date是给定的最大行userid.

在那些情况下(当没有匹配时),列t2将是NULL- 甚至是连接条件中指定的列.所以这就是为什么我们使用WHERE t2.UserId IS NULL,因为我们正在寻找在没有行被发现具有较大的情况下,date对于给定的userid.

---

当应用于具有880万行的表时,该查询几乎是所接受答案中的两倍.

---

@Derek:优化取决于RDBMS的品牌和版本,以及适当的索引,数据类型等的存在.

---

哇比尔.这是我见过的这个问题最有创意的解决方案.在我相当大的数据集上它也非常高效.这肯定胜过了我见过的许多其他解决方案,或者我自己试图解决这个窘境.

---

在MySQL上,这种查询似乎实际上导致它循环遍历表之间的笛卡尔连接的结果,导致O(n ^ 2)时间.使用子查询方法将查询时间从2.0秒减少到0.003秒.因人而异.

2> David Aldrid..：

---

这将检索my_date列值等于该userid的my_date的最大值的所有行.这可以为userid检索多行,其中最大日期在多行上.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

"分析功能摇滚"

编辑:关于第一条评论......

"使用分析查询和自连接会破坏分析查询的目的"

此代码中没有自联接.而是在内联视图的结果上放置了一个谓词,其中包含分析函数 - 一个非常不同的问题,以及完全标准的实践.

"Oracle中的默认窗口是从分区的第一行到当前的一行"

windowing子句仅适用于order by子句的存在.如果没有order by子句,则默认情况下不应用windowing子句,并且不能显式指定任何窗口子句.

代码有效.

---

当应用于具有880万行的表时,该查询在一些其他高度投票的答案中花费了一半的查询时间.

---

任何人都想发布一个与MySQL相当的链接,如果有的话？

---

而不是'MAX(...)OVER(...)`你也可以使用`ROW_NUMBER()OVER(...)`(对于top-n-per-group)或`RANK()OVER(. ..)`(对于最大的每组n).

---

这不能重复吗？例如.如果两行具有相同的user_id和相同的日期(恰好是最大值).

---

@jastr我认为这个问题得到了承认

3> Dave Costa..：

---

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

---

请出示你的考试

---

麻烦的是它没有返回完整的记录

---

在我使用具有大量行的表的测试中,此解决方案花费的时间大约是接受答案中的两倍.

4> Steve K..：

---

我不知道你确切的列名,但它会是这样的:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)

---

您可能低估了Oracle查询优化器.

---

+1因为当您的数据表长度不是数百万行时,这是最容易理解的解决方案.当你有多个修改代码的所有技能水平的开发人员时,可理解性更重要,然后在性能上只有一秒钟是不可察觉的.

---

仅供参考,"不高效,但有效"与"工作,但效率不高"相同.我们什么时候放弃了有效的设计目标？

---

史蒂夫,可能不是很高效.

---

一点也不.这几乎肯定会实现为带有嵌套循环连接的完整扫描以获取日期.你所说的逻辑io大约是表中行数的4倍,对于非平凡的数据量来说是可怕的.

---

您对我的分析方法的评论不正确.看我的编辑.

---

+1这给了我最快的解决方案.比尔是第二名,但也许他认为这可能会因其他因素而改变.

5> Mike Woodhou..：

---

没有工作,我没有掌握Oracle,但我似乎记得Oracle允许在IN子句中匹配多个列,这至少应该避免使用相关子查询的选项,这很少是一个好的理念.

也许这样的事情(不记得列列表是否应该加括号):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

编辑:刚尝试真实:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

所以它有效,尽管其他地方提到的一些新东西可能更有效.

---

这也适用于PostgreSQL.我喜欢它的简单性和通用性 - 子查询说"这是我的标准",外部查询说"这里是我想看到的细节".+1.

6> mancaus..：

---

我知道你要求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

7> user11318..：

---

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

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

我怀疑你可以摆脱外部查询,并在内部区分,但我不确定.与此同时,我知道这个有效.

如果您想了解分析查询,我建议您阅读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还没有它们.Grrr ......)

8> 小智..：

---

QUALIFY条款不是最简单也最好的吗？

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

对于上下文,在Teradata上这里有一个不错的尺寸测试,在17秒内运行此QUALIFY版本,在23秒内运行'内联视图'/ Aldridge解决方案#1.

9> Cito..：

---

使用PostgreSQL 8.4或更高版本,您可以使用:

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1

10> Gurwinder Si..：

---

在Oracle 12c+,你可以使用前n个查询和分析函数rank来实现这个非常简洁,没有子查询:

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的所有行.

如果你想只有一排,最大日期,然后更换rank有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;