mysql - 从用户评分表中获取所有连续日期的条纹

Question

表score_streak用于存储用户每天的得分，定义如下。

CREATE TABLE IF NOT EXISTS score_streak(
    create_date DATE NOT NULL,
    score INT(11),
    PRIMARY KEY (create_date)
);

insert into score_streak (create_date, score) values 
    (DATE('2017-04-01'), 11) ,
    (DATE('2017-04-02'), 8) ,
    (DATE('2017-04-03'), 9) ,
    (DATE('2017-04-06'), 14) ,
    (DATE('2017-04-07'), 15) ,
    (DATE('2017-04-08'), 13) ,
    (DATE('2017-04-12'), 20) ,
    (DATE('2017-04-13'), 21) ,
    (DATE('2017-04-14'), 22) ,
    (DATE('2017-04-15'), 18) ;

select * from score_streak;
    create_date | score
    2017-04-01  |   11
    2017-04-02  |   8
    2017-04-03  |   9
    2017-04-06  |   14
    2017-04-07  |   15
    2017-04-08  |   13
    2017-04-12  |   20
    2017-04-13  |   21
    2017-04-14  |   22
    2017-04-15  |   18

我想查询表，得到所有用户得分大于或等于10且日期必须连续的连胜。每个连胜都有一个开始日期和一个结束日期。

例如，上面示例数据的预期结果如下(注意有 3 条条纹):

start_date | end_date   |streak_count
2017-04-01 | 2017-04-01 |     1
2017-04-06 | 2017-04-08 |     3
2017-04-12 | 2017-04-15 |     4

谢谢。

Answer 1

你可以做到

SELECT MIN(create_date) start_date, 
       MAX(create_date) end_date, 
       COUNT(*) streak_count
  FROM (
    SELECT q.*,
           @g := @g + COALESCE(DATEDIFF(create_date, @p) <> 1, 0) gn,
           @p := create_date
      FROM (
        SELECT *
          FROM score_streak
         WHERE score > 9
         ORDER BY create_date
    ) q CROSS JOIN (
        SELECT @g := 0, @p := NULL
    ) i
) r
 GROUP BY gn

输出:

+------------+------------+--------------+| start_date | end_date   | streak_count |+------------+------------+--------------+| 2017-04-01 | 2017-04-01 |            1 || 2017-04-06 | 2017-04-08 |            3 || 2017-04-12 | 2017-04-15 |            4 |+------------+------------+--------------+

SQLFiddle