在MySQL中获取最大连续重复记录数

Question

假设有25组程序员，每组程序员有5-100名程序员。每组程序员的任务是编写本问题所涉及的查询。为了完成这个任务，每组程序员中的许多程序员开始大量饮酒。每个组都有一个备有以下饮品的酒吧：

• 威士忌
• 伏特加
• 啤酒
• 水

每次程序员喝完一杯饮料时，表格会添加一行，包括：

• 饮料完成时间
• 组ID
• 程序员ID
• 消耗的饮料类型

项目经理希望每6小时通过电子邮件收到一份程序员名单，其中包括在过去6小时内连续饮用5杯或更多啤酒且没有喝伏特加/威士忌或一杯水的程序员。报告中需要包括每个程序员在不切换其他饮料的情况下消耗的啤酒总数。

如果在喝5杯啤酒之前喝了除啤酒以外的饮料，则该程序员不会列入列表。

在6小时内，程序员可以消耗任意数量的饮料，没有上限或下限。

任何程序员可以消耗任何类型或顺序的饮料，没有要求。

MySQL数据库有一个名为“drinks”的表格，其中包括：

• drinks_id INT(11) PK NN AI
• group_id INT(11) NN
• programmer_id INT(11) NN
• type_of_drink VARCHAR(25) NN
• time_finished DATETIME NN

（饮料类型可能应该在另一个表格中，并使用drink_type_id，但我选择了简单性）

我想要的核心是在指定时间段内，每个组/程序员组合的具有type_of_drink = 'beer'的连续行数的最大计数值。我已经用尽了我的SQL技能，试图计算存在于两个type_of_drink <> 'beer'记录之间的连续记录的数量，并返回每个组/程序员组合的最大值。我似乎无法做到这一点，而且这可能不是首先解决这个问题的方法。

提前感谢您的帮助。如果需要，我很乐意提供任何其他信息或要求。

- user2832688

---

你尝试过窗口函数吗？你可以按程序员和饮料分组，返回count(*)并调整设置以在每次遇到新饮料时重新开始计数。请查看http://technet.microsoft.com/en-us/library/ms189461.aspx，文档非常详细，但希望能指引你正确的方向。如果需要更多帮助，请将示例数据放入SQL Fiddle中，并给出我们要处理的预期输出。 - Vulcronos

@Vulcronos MySql没有内置这些函数...但是可以通过使用用户变量来模拟它们。 - Michael Fredrickson

@MichaelFredrickson 感谢提供的信息。我发现窗口函数非常有用，经常使用它们，所以我不想使用没有这些函数的平台。我知道你也可以使用 group by 来获取聚合值，然后再将其与原始表连接以获取所需数据。 - Vulcronos

我觉得MySQL在这里相当直观。无论如何，我打算回答这个问题... :) - fancyPants

谢谢你们的回复。我还在阅读相关的文章，已经从中学到了很多知识。非常感激！ - user2832688

Answer 1

SELECT DISTINCT programmer_id 
FROM (
    SELECT
    programmer_id,
    @beercounter := IF(@prev_programmer != programmer_id OR type_of_drink != 'beer', 1, @beercounter + 1) AS how_many_beer_in_a_row,
    @prev_programmer := programmer_id
    FROM
    your_table y
    , (SELECT @beercounter:=1, @prev_programmer:=NULL) vars
    WHERE time_finished >= NOW() - INTERVAL 6 HOUR
    ORDER BY programmer_id, time_finished
) sq
WHERE how_many_beer_in_a_row >= 5

- fancyPants

---

这个做到了。不过需要稍微修改一下才能和实际数据一起工作，但这总是必要的。非常感谢！ - user2832688