SQL 查询用户连续登录时长

1.建表

CREATE TABLE person_view
(
  id    INT  NULL,
  start DATE NULL
)

2.插入数据

INSERT INTO person_view (id, start) VALUES (1, '2018-07-01');
INSERT INTO person_view (id, start) VALUES (1, '2018-07-02');
INSERT INTO person_view (id, start) VALUES (1, '2018-07-04');
INSERT INTO person_view (id, start) VALUES (1, '2018-07-05');
INSERT INTO person_view (id, start) VALUES (1, '2018-07-06');
INSERT INTO person_view (id, start) VALUES (2, '2018-07-01');
INSERT INTO person_view (id, start) VALUES (2, '2018-07-02');
INSERT INTO person_view (id, start) VALUES (2, '2018-07-03');
INSERT INTO person_view (id, start) VALUES (2, '2018-07-04');
INSERT INTO person_view (id, start) VALUES (3, '2018-07-04');

3.SQL实现：

SELECT id,max(days) FROM (SELECT
  id,
  @cont_day :=
  (CASE
   WHEN (@id = id AND DATEDIFF(start, @start) = 1)
     THEN
       (@cont_day + 1)
   WHEN (@id = id AND DATEDIFF(start, @start) < 1)
     THEN
       (@cont_day + 0)
   ELSE
     1
   END) AS days,
  @id := id,
  @start := start login_day
FROM (SELECT
        id,
        DATE(start) start
      FROM dmp.person_view
      WHERE id != 0
      ORDER BY id, start) AS t,
  (SELECT
     @id := '',
     @start := '',
     @cont_day := 0) AS t1) t2 GROUP BY id;