如何检查一个组的列中是否有三个连续值？

Question

我有一个表 games ，其值如下：

+----------+------+
|  game    | year |
+----------+------+
| Football | 1999 |
| Football | 2000 |
| Football | 2001 |
| Football | 2002 |
| Cricket  | 1996 |
| Tennis   | 2001 |
| Tennis   | 2002 |
| Tennis   | 2003 |
| Tennis   | 2009 |
| Golf     | 1994 |
| Golf     | 1996 |
| Golf     | 1997 |
+----------+------+

我试图查看表中是否有一个游戏具有至少连续三年的条目。我的预期输出是：

+----------+
|  game    |
+----------+
| Football |
| Tennis   |
+----------+

因为：

• 足球有四个条目，其中四个是连续年份 => 1999、2000、2001、2002
• 网球有四届参赛，其中三届是连续年份=> 2001, 2002, 2003

为了找到至少包含三个连续条目的行，我首先在 game 上对表进行分区，然后检查当前行和下一行之间的差异：下面：

select game, year, case
    when (year - lag(year) over (partition by game order by year)) is null then 1
    else year - lag(year) over (partition by game order by year)
end as diff
from games

上述查询的输出：

+----------+------+------+
|  game    | year | diff |
+----------+------+------+
| Football | 1999 | 1    |
| Football | 2000 | 1    |
| Football | 2001 | 1    |
| Football | 2002 | 1    |
| Cricket  | 1996 | 1    |
| Tennis   | 2001 | 1    |
| Tennis   | 2002 | 1    |
| Tennis   | 2003 | 1    |
| Tennis   | 2009 | 6    |
| Golf     | 1994 | 1    |
| Golf     | 1996 | 2    |
| Golf     | 1997 | 1    |
+----------+------+------+

我无法从这里继续通过过滤每个游戏的数据及其差异来获取输出。

谁能告诉我我是否处于正确的实施轨道上？如果没有，我如何准备查询以获得预期的输出？

Answer 1

使用 CTE（通用表表达式）和有用的 ROW_NUMBER 窗口函数可以轻松解决这个问题。

WITH CTE (name, RN) AS (
select name, ROW_NUMBER() OVER (PARTITION BY name order by year) RN
        from game)
Select Distinct name
from CTE 
Where RN >= 3

Answer 2

您可以在此处使用自联接方法：

SELECT DISTINCT g1.Game
FROM games g1
INNER JOIN games g2
    ON g2.Game = g1.Game AND g2.Year = g1.Year + 1
INNER JOIN games g3
    ON g3.Game = g2.Game AND g3.Year = g2.Year + 1;

演示

上述查询要求任何匹配的游戏至少有一条记录，其年份可以在下一年和下一年找到。

Answer 3

您可以使用 lag() 和 lead() 并将它们与当前年份进行比较：

with u as
(select *, case
when lag(Year) over(partition by Game order by Year) = Year - 1
and lead(Year) over(partition by Game order by Year) = Year + 1
then 1 else 0
end as consec
from games)
select distinct Game
from u
where consec = 1;

小提琴

Answer 4

是的，您最初的方法是正确的。事实上，你自己已经非常接近完全弄清楚了。

我要做的就是稍微改变一下 LAG：

year - LAG(year, 2) OVER (
    PARTITION BY game
    ORDER BY year
    ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
)

对于每一行，这将比较当前行的年份和（当前 - 2）行的年份之间的差异。

如果它是连续第三行，它将产生 2 ，您可以在 where 子句中过滤它。

如果您的数据包含重复项，您需要首先按游戏、年份进行分组。