发现有连续三年服务的用户

Question

我有一个事件表，其中将存储用户参与的列表。

样本数据：

╔════╦══════╦══════╗
║ id ║ name ║ year ║
╠════╬══════╬══════╣
║  1 ║ Arun ║ 2001 ║
║  2 ║ Arun ║ 2002 ║
║  3 ║ Arun ║ 2003 ║
║  4 ║ Arun ║ 2004 ║
║  5 ║ Arun ║ 2009 ║
║  6 ║ Arun ║ 2010 ║
║  7 ║ Arun ║ 2011 ║
║  8 ║ Bala ║ 2014 ║
║  9 ║ Bala ║ 2015 ║
║ 10 ║ Bala ║ 2017 ║
║ 11 ║ chan ║ 2014 ║
║ 12 ║ chan ║ 2015 ║
║ 13 ║ chan ║ 2018 ║
╚════╩══════╩══════╝

我只需要找到的用户，谁已经连续出席了三年与这些年在新的专栏。即使用户连续参加了6年，也必须在一栏中分成前三名，在另一栏中再分三名。

输出：

╔════╦══════╦═══════════╗
║ id ║ name ║   years   ║
╠════╬══════╬═══════════╣
║  1 ║ Arun ║ 2001-2003 ║
║  2 ║ Arun ║ 2009-2011 ║
╚════╩══════╩═══════════╝

Answer 1

一种利用year和row_number()生成的递增序列之间的差异来获取这些信息的方法

select name, min(year), max(year)
from (select e.*,
             (year - row_number() over (partition by name order by year)
             ) as grp
      from events e
     ) e
group by name, grp
having count(*) >= 3;

这将找到任意长度的序列。如果你真的只关心3年，那么你也可以使用联接：

select e.name, e.year as FirstYearOfAtLeastThree
from events e
where exists (select 1 from events e2 where e2.name = e.name and e2.year = e.year + 1) and
      exists (select 1 from events e2 where e2.name = e.name and e2.year = e.year + 2) ;

这两种方法都可以利用events(name, year)上的索引。第二种方法可能比第一种方法要快一些。