php - 选择最低连续值？

Question

背景故事
我有一个 MYSQL 表，用于存储年度比赛的应用程序数据。除其他外，该表包含赛车手的 ID 和该比赛申请的年份。

今年，我们希望对申请的随机选择过程进行加权，以便连续申请 X 年的人有更大的机会被选中。

在我的 PHP 脚本中，我让 MYSQL 对象为他们申请的每一年返回 1 条记录，随机选择一个并将其从数据库结果数组中取消分配，将其分配给 winners 数组，并忽略所有其他获胜者今年的 ID 相同。

实际问题
这种方法的问题是我当前的 MYSQL 查询返回所有申请今年和过去比赛的人的所有记录。有什么方法(最好使用 MYSQL)可以让它只返回 2014 年及之前的连续应用程序？

来自 SQL FIddle 的示例结果集将是“1, 1, 1, 2, 2, 2, 2, 3”。

我当前的查询是:

SELECT a.racer_id FROM applications a

WHERE a.racer_id IN
    (
        SELECT ab.racer_id FROM applications ab

        WHERE ab.racer_id = a.racer_id AND ab.race_year=2014
    )

ORDER BY RAND();

这是行不通的，因为它抓取了过去所有年份，而不仅仅是 2014 年及之前的连续记录。

Answer 1

表达此问题的另一种方法是查找赛车手最近没有参加比赛的年份。然后，当前年减去那年就是连续年数。

您可以通过在年份和赛车手之间进行交叉连接，并将左侧连接到应用程序来获得最近的一年。没有比赛的地方是该赛车手没有参加比赛的一年。

您确实需要做一些额外的簿记来处理所有年份参加比赛的赛车手。最终的查询是:

select racers.racer_id,
       maxyear - max(case when a.race_year is null then years.race_year
                          else const.minyear - 1
                     end)
from (select distinct race_year
      from applications
     ) years cross join
     (select distinct racer_id
      from applications
     ) racers cross join
     (select max(race_year) as maxyear, min(race_year) as minyear
      from applications
     ) const left outer join 
     applications a
     on a.race_year = years.race_year and
        a.racer_id = racers.racer_id
group by racers.racer_id;

我不确定这与 SQL Fiddle 中的数据有何关系。那里有四个赛车手，但问题中有 8 个建议值。

编辑:

现在我明白了。这是很合理的。下面的查询基本上扩展了上面的想法，通过返回 application 来获取没有应用程序的最大年份之后的任何年份。子查询是上面的简化:

select a.racer_id
from (select racers.racer_id,
             max(case when a.race_year is null then years.race_year
                 end) as maxyear
      from (select distinct race_year
            from applications
           ) years cross join
           (select distinct racer_id
            from applications
           ) racers left outer join 
           applications a
           on a.race_year = years.race_year and
              a.racer_id = racers.racer_id
      group by racers.racer_id
     ) ry join
     applications a
     on a.racer_id = ry.racer_id and
        (a.race_year > ry.maxyear or ry.maxyear is null);