选择运动员在过去 3 场比赛中没有先完成的终点 [英] Select finishes where athlete didn't finish first for the past 3 events

问题描述

假设我有一个运动会结果数据库，其架构如下

Suppose I have a database of athletic meeting results with a schema as follows

DATE,NAME,FINISH_POS

我希望进行查询以选择运动员至少参加了三项赛事但未获胜的所有行.例如使用以下示例数据

I wish to do a query to select all rows where an athlete has competed in at least three events without winning. For example with the following sample data

2013-06-22,Johnson,2
2013-06-21,Johnson,1
2013-06-20,Johnson,4
2013-06-19,Johnson,2
2013-06-18,Johnson,3
2013-06-17,Johnson,4
2013-06-16,Johnson,3
2013-06-15,Johnson,1

以下行:

2013-06-20,Johnson,4
2013-06-19,Johnson,2

会匹配.我只设法从以下存根开始:

Would be matched. I have only managed to get started at the following stub:

select date,name FROM table WHERE ...;

我一直在想办法解决 where 子句，但我什至无法开始

I've been trying to wrap my head around the where clause but I can't even get a start

推荐答案

我认为这可以更简单/更快:

I think this can be even simpler / faster:

SELECT day, place, athlete
FROM  (
   SELECT *, min(place) OVER (PARTITION BY athlete
                              ORDER BY day
                              ROWS 3 PRECEDING) AS best
   FROM   t
   ) sub
WHERE  best > 1

->SQLfiddle

使用聚合函数min()作为窗口函数，得到最后三行加上当前行的最小位置.
由于窗口函数在 WHERE< 之后应用，因此必须在下一个查询级别上完成对无赢"(best > 1)的微不足道的检查/code> 子句.因此，您至少需要一个 CTE 的子选择窗口函数结果的条件.

Uses the aggregate function min() as window function to get the minimum place of the last three rows plus the current one.
The then trivial check for "no win" (best > 1) has to be done on the next query level since window functions are applied after the WHERE clause. So you need at least one CTE of sub-select for a condition on the result of a window function.

关于窗口函数调用的详细信息手册在这里.特别是:

如果省略 frame_end，则默认为 CURRENT ROW.

If frame_end is omitted it defaults to CURRENT ROW.

如果 place (finishing_pos) 可以为 NULL，请改用:

If place (finishing_pos) can be NULL, use this instead:

WHERE  best IS DISTINCT FROM 1

min() 忽略 NULL 值，但如果帧中的所有行都是 NULL，则结果为 NULL代码>.

min() ignores NULL values, but if all rows in the frame are NULL, the result is NULL.

不要使用类型名称和保留字作为标识符，我用 day 替换了您的 date.

Don't use type names and reserved words as identifiers, I substituted day for your date.

这假设每天最多有 1 场比赛，否则您必须定义如何在时间线上处理同行或使用 timestamp 而不是 date.

This assumes at most 1 competition per day, else you have to define how to deal with peers in the time line or use timestamp instead of date.

@Craig 已经提到了索引以加快速度.

@Craig already mentioned the index to make this fast.

这篇关于选择运动员在过去 3 场比赛中没有先完成的终点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！