我将对以下查询提供一些指导。我们有一个实验及其当前进度状态的列表（为简单起见，我将状态简化为4种，但是数据中有10种不同的状态）。我最终需要返回所有未完成实验的当前状态的列表。
给定一个表exp_status，
Experiment | ID | Status
----------------------------
A | 1 | Starting
A | 2 | Working On It
B | 3 | Starting
B | 4 | Working On It
B | 5 | Finished Type I
C | 6 | Starting
D | 7 | Starting
D | 8 | Working On It
D | 9 | Finished Type II
E | 10 | Starting
E | 11 | Working On It
F | 12 | Starting
G | 13 | Starting
H | 14 | Starting
H | 15 | Working On It
H | 16 | Finished Type II
所需结果集：
Experiment | ID | Status
----------------------------
A | 2 | Working On It
C | 6 | Starting
E | 11 | Working On It
F | 12 | Starting
G | 13 | Starting
最新的ID号将对应于最新的状态。
现在，我当前的代码将在150秒内执行。
SELECT *
FROM
(SELECT Experiment, ID, Status,
row_number () over (partition by Experiment
order by ID desc) as rn
FROM exp_status)
WHERE rn = 1
AND status NOT LIKE ('Finished%')
问题是，这段代码浪费了时间。结果集是从390万张表中提取的4.5万行。这是因为大多数实验都处于完成状态。代码经过并排序所有代码，然后仅在最后过滤掉完成的代码。表中约有95％的实验处于完成阶段。我无法弄清楚如何使查询首先挑选出该实验没有“完成”状态的所有实验和状态。我尝试了以下操作，但性能非常慢。
SELECT *
FROM exp_status
WHERE experiment NOT IN
(
SELECT experiment
FROM exp_status
WHERE status LIKE ('Finished%')
)
任何帮助，将不胜感激！
最佳答案
根据您的要求，我认为您当前使用row_number()进行的查询是最高效的查询之一。该查询花费时间不是因为它必须对数据进行排序，而是因为首先要读取的数据太多（与获取时间相比，额外的cpu时间可以忽略不计）。此外，第一个查询进行完全扫描，这实际上是读取大量数据的最佳方法。
如果要提高性能，则需要找到一种方法以减少很多行。第二个查询的方向不正确：
内部查询可能是完整扫描，因为“完成的”行将散布在整个表中，并且可能占所有行的很大一部分。
外部查询也将像是完整扫描和一个不错的ANTI-HASH JOIN，它应该比45k *（每个实验的状态更改次数）非唯一索引扫描快。
因此，第二个查询似乎具有至少两倍的读取次数（加上一个连接）。
如果您想真正提高性能，我认为您将需要更改设计。
例如，您可以构建一个活动实验表并加入该表。您可以将该表保留为物化视图，也可以对插入实验状态的代码进行修改。您可以走得更远，并将最后的状态存储在此表中。维持这种“最后状态”可能会带来额外的负担，但这可以通过提高性能来证明。