增加查询参数，直到达到极限

Question

我正在试图找到前5名跑步者谁运行在1km，但如果有少于5的跑步者谁满足这一条件，然后增量km 1，直到5的LIMIT被满足。

唯一的警告是，如果在增量km时发现了2个或更多的结果，则应该返回具有较高score的结果，即使另一个结果的km_run较低。

最终结果集应按分数排序。

Runners

id   runner   km_run   score
 1     mary      3.5    0.55
 2     anna      1.5    0.95
 3     john      6.5    0.90
 4     bill      1.5    0.15
 5     jess      6.2    0.35
 6     jack      2.5    0.75

我尝试使用子查询首先返回由score排序的表，然后在外部查询上放置一个LIMIT。

SELECT runner, km_run, score 
FROM runners
WHERE runner IN 
(SELECT runner FROM runners
ORDER BY score DESC)
ORDER BY km_run LIMIT 5

但这只会返回km最低的5个跑步者。

相反，我想要的是：：

 id   runner   km_run   score
  2     anna      1.5    0.95
  3     john      6.5    0.90
  6     jack      2.5    0.75
  1     mary      3.5    0.55
  5     jess      6.2    0.35

请注意以下几点：

• 结果按降分排序。
• 返回ID 3和5，但不返回ID 4。

这是因为只有4/5的结果被发现，直到找到两个结果的6-7 km间隔。这两项结果的得分均优于ID4，因此将其包括在内。

 Between     Results
  0-1 km        0
  1-2 km        2
  2-3 km        1   
  3-4 km        1    --> here we have 4/5 results required
  4-5 km        0
  5-6 km        0
  6-7 km        2    --> now we have 6, but we only want the 5 w/ best score

安装查询：

CREATE TABLE runners(
 id SERIAL,
 runner VARCHAR, 
 km_run DECIMAL,
 score DECIMAL,
 PRIMARY KEY (id)
);

INSERT INTO runners (runner, km_run, score)
VALUES ('mary', 3.5, 0.55);
INSERT INTO runners (runner, km_run, score)
VALUES ('anna', 1.5, 0.95);
INSERT INTO runners (runner, km_run, score)
VALUES ('john', 6.5, 0.90);
INSERT INTO runners (runner, km_run, score)
VALUES ('bill', 1.5, 0.15);
INSERT INTO runners (runner, km_run, score)
VALUES ('jess', 6.2, 0.35);
INSERT INTO runners (runner, km_run, score)
VALUES ('jack', 2.5, 0.75);

Answer 1

首先，让所有的跑步者谁的排名5或更高，其中排名是基于截断的km_run。然后得到5个得分最高的。

在SQL中：

SELECT runner, km_run, score
FROM (SELECT runner, km_run, score,
             rank() OVER (ORDER BY floor(km_run))
      FROM runners) AS q
WHERE rank <= 5
ORDER BY score DESC
LIMIT 5;

 runner | km_run | score 
--------+--------+-------
 anna   |    1.5 |  0.95
 john   |    6.5 |  0.90
 jack   |    2.5 |  0.75
 mary   |    3.5 |  0.55
 jess   |    6.2 |  0.35
(5 rows)

查询效率不高，因为它必须扫描整个runners表。但是，根据您的要求，这是不可避免的--我们需要扫描多少表行还不清楚。