参加连胜MySQL查询 [英] Attending Streak MySQL Query

问题描述

我正在处理一个查询，该查询试图提供您当前的出勤记录条纹.因此，换句话说，人1参加了最近的5个事件，人2参加了最近的6个事件，等等.为此，我得到的数据文件非常简单:

I'm working on a query that's trying to come up with what your current attendance record streak is. So in other words, person 1 has attended the last 5 events, person 2 the last 6 events, etc. The data file I've got for this is pretty simple:

Event Number - Unique Name
34 - Tim
34 - John
34 - Mike
34 - Larry
35 - John
35 - Mike
35 - Larry
35 - Paul
36 - John
36 - Steve
36 - Paul

最终状态查询结果有望产生:

And the endstate query result would hopefully produce:

John - 3
Paul - 2
Steve - 1

这里最有趣的部分是发生了几百个事件，有数百个人没有参加.我在mysql查询中找到了几篇有关当前连胜的文章，但是如果没有针对每个可能的人的明确的是/否，则找不到该文章.

The additional fun part here is that there are several hundred events that have happened, and several hundred people who have/haven't attended. I've found several articles on mysql queries for current streak, but not if there isn't a definitive yes/no for each possible person.

关于如何进行构建的任何想法?

Any thoughts on how to go about building this?

朋友-感谢您的快速回复！不过，让我澄清一下……我并不是想让每个人参加的活动总数.相反，对于参加最后一个活动的每个人的总和，他们的连胜成绩至少为1.但是，如果您参加了最后一个活动，并且还参加了最近的5个活动，则连胜成绩将为5.即使您在过去3年中参加了100次活动，如果您错过了某个日期，然后出现在最近5个可能的日期中，则您的连胜纪录是5.我想为参加最后一次活动的每个人都这样做.

Folks - thanks for the quick responses! Let me clarify it a bit, though...I'm not trying to get the total attended events for each person. Instead, for the population of everyone who attended the last event, they have at least a streak of 1. But if you attended the last event, and you also attended the last 5 events, your streak would be 5. That would be the case even if you attended 100 events over the last 3 years, if you missed a date, and then showed up for the last 5 possible dates, your streak is 5. And I'd like to do this for everyone who attended the last event.

推荐答案

让我们假设您的表为Event，列为EventID和Name.我们可以通过以下查询来确定每个人参加活动的顺序(即1、2、3等):

Let's assume your table is Event and the columns are EventID and Name. We can determine the sequence (i.e., 1, 2, 3, etc.) in which each person attended events by the following query:

SELECT
  e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
FROM
  Event e1
    INNER JOIN
  Event e2
    ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
GROUP BY
  e1.Name, e1.EventID

我们可以利用PersonalEventSequence将每个人的事件分组为条纹:

We can leverage PersonalEventSequence to group each person's events into streaks:

SELECT
  Name, EventID - PersonalEventSequence AS StreakGroup
FROM
  (
    SELECT
      e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
    FROM
      Event e1
        INNER JOIN
      Event e2
        ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
    GROUP BY
      e1.Name, e1.EventID
  ) AS SubQuery1

现在，每个人的事件都被分组为条纹(具有怪异的StreakGroup号！)，我们可以确定每个人的条纹的长度:

Now that each person's events are grouped into streaks (having admittedly weird StreakGroup numbers!), we can determine the lengths of each person's streaks:

SELECT
  Name, StreakGroup, COUNT(*) AS StreakLength
FROM
  (
    SELECT
      Name, EventID - PersonalEventSequence AS StreakGroup
    FROM
      (
        SELECT
          e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
        FROM
          Event e1
            INNER JOIN
          Event e2
            ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
        GROUP BY
          e1.Name, e1.EventID
      ) AS SubQuery1
  ) SubQuery2
GROUP BY
  Name, StreakGroup

现在我们知道了每个人的条纹的长度，我们可以确定每个人的最长条纹的长度:

Now that we know the lengths of each person's streaks, we can determine the length of each person's longest streak:

SELECT
  Name, MAX(StreakLength) AS PersonalRecordStreakLength
FROM
  (
    SELECT
      Name, StreakGroup, COUNT(*) AS StreakLength
    FROM
      (
        SELECT
          Name, EventID - PersonalEventSequence AS StreakGroup
        FROM
          (
            SELECT
              e1.Name, e1.EventID, COUNT(*) AS PersonalEventSequence
            FROM
              Event e1
                INNER JOIN
              Event e2
                ON e1.Name = e2.Name AND e1.EventID >= e2.EventID
            GROUP BY
              e1.Name, e1.EventID
          ) AS SubQuery1
      ) SubQuery2
    GROUP BY
      Name, StreakGroup
  ) SubQuery3
GROUP BY
  Name

注意:

• OP仅需要当前的条纹(即包括最新事件的条纹)，但是我将特定的解决方案留给OP来解决，因为此处显示的通用解决方案将适用于更多的程序员. li>
• 可以使用Views而不是子查询来清理代码.
• 我没有尝试运行此代码.可能有错误.

这篇关于参加连胜MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！