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

问题描述

我在 Oracle DB 中有下表

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屋！