代码之家  ›  专栏  ›  技术社区  ›  Tokeeen.com Paul Dragoonis

用MySQL计算连续日条纹

  •  0
  • Tokeeen.com Paul Dragoonis  · 技术社区  · 6 年前

    我有下表:

    Date       | isDone
    --------------------
    2018-10-01 | 1
    2018-10-02 | 1
    2018-10-03 | 1
    2018-10-04 | 1 
    2018-10-10 | 0
    2018-10-15 | 1
    2018-10-16 | 0
    2018-10-18 | 1
    2018-10-19 | 1
    2018-10-20 | 1
    

    每天只能有一行,可能有两个值0或1。一天内不必排一行。如果一天没有行,则该值被视为0。

    我想从当前日期开始计算已完成任务(isdone列)的“当前连续记录”。假设我们是2018-10-20,结果是 . 现在,我不想计算这个月最大的连续记录,在这种情况下。 (2018-10-01至2018-10-04)。我想知道,直接使用SQL(MySQL5.7)或每天获取原始数据并使用PHP计算连胜,哪种方法是最好的?

    1 回复  |  直到 6 年前
        1
  •  2
  •   Nick SamSmith1986    6 年前

    您可以使用mysql变量来计算条纹。基本上,当 isDone 是1,当 伊索多 为0或日期不连续。此查询将生成每天的连续记录值:

    SELECT s.Date,
           @streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
           @last_date := Date AS last_date
    FROM status s
    JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
    ORDER BY s.Date
    

    输出:

    Date        streak  last_date
    2018-10-01  1       2018-10-01
    2018-10-02  2       2018-10-02
    2018-10-03  3       2018-10-03
    2018-10-04  4       2018-10-04
    2018-10-10  1       2018-10-10
    2018-10-15  1       2018-10-15
    2018-10-16  1       2018-10-16
    2018-10-18  1       2018-10-18
    2018-10-19  2       2018-10-19
    2018-10-20  3       2018-10-20
    

    然后,您可以使用它作为子查询来确定某一天的连续记录,例如

    SELECT Date, streak
    FROM (SELECT s.Date,
                 @streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
                 @last_date := Date AS last_date
          FROM status s
          JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
          ORDER BY s.Date) s
    WHERE s.Date = '2018-10-20'
    

    输出:

    Date        streak
    2018-10-20  3
    

    Demo on dbfiddle