Oracle SQL查询:根据时间检索每个组的最新值 [英] Oracle SQL query: Retrieve latest values per group based on time

问题描述

我在Oracle数据库中有下表

I have the following table in an Oracle DB

id     date              quantity
1      2010-01-04 11:00  152
2      2010-01-04 11:00  210
1      2010-01-04 10:45  132
2      2010-01-04 10:45  318
4      2010-01-04 10:45  122
1      2010-01-04 10:30  1
3      2010-01-04 10:30  214
2      2010-01-04 10:30  5515
4      2010-01-04 10:30  210

现在我想按ID检索最新值(及其时间).输出示例:

now I'd like to retrieve the latest value (and its time) per id. Example output:

id     date              quantity
1      2010-01-04 11:00  152
2      2010-01-04 11:00  210
3      2010-01-04 10:30  214
4      2010-01-04 10:45  122

我只是不知道如何将其放入查询...

I just can't figure out how to put that into a query...

另外，以下选项将是不错的选择:

Additionally the following options would be nice:

选项1:查询应仅返回最近XX分钟内的值.

Option 1: the query should only return values that are from the last XX minutes.

选项2:应将id与另一个具有id和idname的表中的文本连接起来. id的输出应类似于:id-idname(例如1-testid1).

Option 2: the id should be concatenated with text from another table that has id and idname. output for id should then be like: id-idname (eg 1-testid1).

非常感谢您的帮助！

推荐答案

给出此数据...

SQL> select * from qtys
  2  /

        ID TS                      QTY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         1 2010-01-04 10:45        132
         2 2010-01-04 10:45        318
         4 2010-01-04 10:45        122
         1 2010-01-04 10:30          1
         3 2010-01-04 10:30        214
         2 2010-01-04 10:30       5515
         4 2010-01-04 10:30        210

9 rows selected.

SQL>

...以下查询给出了您想要的...

... the following query gives what you want ...

SQL> select x.id
  2         , x.ts as "DATE"
  3         , x.qty as "QUANTITY"
  4  from (
  5      select id
  6             , ts
  7             , rank () over (partition by id order by ts desc) as rnk
  8             , qty
  9      from qtys ) x
 10  where x.rnk = 1
 11  /

        ID DATE               QUANTITY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         3 2010-01-04 10:30        214
         4 2010-01-04 10:45        122

SQL>

关于其他要求，可以将其他过滤器应用于外部WHERE子句.同样，您可以像将其他表一样将其他表连接到内联视图.

With regards to your additional requirements, you can apply additional filters to the outer WHERE clause. Similarly you can join additional tables to the inline view like it was any other table.

这篇关于Oracle SQL查询:根据时间检索每个组的最新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！