Oracle SQL 查询：根据时间检索每组的最新值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2000908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL query: Retrieve latest values per group based on time
提问by Tom
I have the following table in an Oracle DB
我在 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
now I'd like to retrieve the latest value (and its time) per id. Example output:
现在我想检索每个 id 的最新值（及其时间）。示例输出：
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:
此外，以下选项会很好：
Option 1: the query should only return values that are from the last XX minutes.
选项 1：查询应该只返回最近 XX 分钟的值。
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).
选项 2：id 应该与来自另一个具有 id 和 idname 的表的文本连接。id 的输出应该是这样的：id-idname（例如 1-testid1）。
many thanks for any help!
非常感谢您的帮助！
回答by APC
Given this data ...
鉴于这些数据...
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>
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.
关于您的其他要求，您可以将其他过滤器应用于外部 WHERE 子句。同样，您可以像连接任何其他表一样将其他表连接到内联视图。
回答by dcp
Here's a complete, tested example.
这是一个完整的、经过测试的示例。
CREATE TABLE tbl1 (ID NUMBER, dt DATE, quantity NUMBER);
DELETE FROM tbl1;
insert into tbl1 values (1,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 152);
insert into tbl1 values (2,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 210);
insert into tbl1 values (1,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 132);
insert into tbl1 values (2,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 318);
insert into tbl1 values (4,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 122);
insert into tbl1 values (1,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 1);
insert into tbl1 values (3,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 214);
insert into tbl1 values (2,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 5515);
insert into tbl1 values (4,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 210);
SELECT t.ID
, t.DT
, t.QUANTITY
FROM tbl1 t
,( SELECT ID
, MAX(dt) dt
FROM tbl1
GROUP BY ID ) t2
WHERE t.id = t2.id
AND t.dt = t2.dt
Results:
结果：
1 1/4/2010 11:00:00 AM 152
2 1/4/2010 11:00:00 AM 210
3 1/4/2010 10:30:00 AM 214
4 1/4/2010 10:45:00 AM 122
If you want to get the records for the last XX minutes, you can do this (I'm using 500 minutes in this example, replace the 500 with whatever you desire):
如果你想获得最后 XX 分钟的记录，你可以这样做（我在这个例子中使用 500 分钟，用你想要的任何替换 500）：
SELECT t.ID
, t.DT
, t.QUANTITY
FROM tbl1 t
,( SELECT ID
, MAX(dt) dt
FROM tbl1
WHERE dt >= SYSDATE - (500 / 1400)
GROUP BY ID ) t2
WHERE t.id = t2.id
AND t.dt = t2.dt;
