Mysql刷题笔记 02.24 统计每组连续时间内的连续完成数，有某一时间的完成数为0就重新计算

0224 
需要统计每组（NAME）连续时间（TIME）内的连续完成数（COMPLETE），其中有某一时间的完成数为0就重新计算。

CREATE TABLE T0224 
(
 TIME INT,
 NAME VARCHAR(5),
 COMPLETE VARCHAR(5)
);
  
INSERT INTO T0224 VALUES(1,'1','0');
INSERT INTO T0224 VALUES(2,'1','1');
INSERT INTO T0224 VALUES(3,'1','1');
INSERT INTO T0224 VALUES(4,'1','1');
INSERT INTO T0224 VALUES(5,'1','0');
INSERT INTO T0224 VALUES(6,'1','0');
INSERT INTO T0224 VALUES(7,'1','1');
INSERT INTO T0224 VALUES(8,'1','1');
INSERT INTO T0224 VALUES(2,'3','1');
INSERT INTO T1208 VALUES(3,'3','1');
INSERT INTO T0224 VALUES(4,'3','1');
INSERT INTO T0224 VALUES(5,'3','0');
INSERT INTO T0224 VALUES(6,'3','1');
INSERT INTO T0224 VALUES(7,'3','1');
INSERT INTO T0224 VALUES(8,'3','0' );

T0224
+------+------+----------+
| TIME | NAME | COMPLETE |
+------+------+----------+
|    1 | 1    | 0        |
|    2 | 1    | 1        |
|    3 | 1    | 1        |
|    4 | 1    | 1        |
|    5 | 1    | 0        |
|    6 | 1    | 0        |
|    7 | 1    | 1        |
|    8 | 1    | 1        |
|    2 | 3    | 1        |
|    4 | 3    | 1        |
|    5 | 3    | 0        |
|    6 | 3    | 1        |
|    7 | 3    | 1        |
|    8 | 3    | 0        |
+------+------+----------+

结果：
+------+------+----------+--------+
| TIME | NAME | COMPLETE | Result |
+------+------+----------+--------+
|    1 | 1    | 0        |      0 |
|    2 | 1    | 1        |      1 |
|    3 | 1    | 1        |      2 |
|    4 | 1    | 1        |      3 |
|    5 | 1    | 0        |      0 |
|    6 | 1    | 0        |      0 |
|    7 | 1    | 1        |      1 |
|    8 | 1    | 1        |      2 |
|    2 | 3    | 1        |      1 |
|    3 | 3    | 1        |      2 |
|    4 | 3    | 1        |      3 |
|    5 | 3    | 0        |      0 |
|    6 | 3    | 1        |      1 |
|    7 | 3    | 1        |      2 |
|    8 | 3    | 0        |      0 |
+------+------+----------+--------+

结题思路：
1.从之前做过的题可以得出结论 要算累计的需要用到开窗函数
 如[题1.12](https://blog.csdn.net/qq_30031221/article/details/112531636)
2. 按照NAME、COMPLETE分组 TIME排序 写出sql语句查看
SELECT *,(TIME-ROW_NUMBER() OVER(PARTITION BY NAME,COMPLETE ORDER BY TIME)) AS W 
FROM T0224 ORDER BY NAME,TIME;
'''
+------+------+----------+------+
| TIME | NAME | COMPLETE | W    |
+------+------+----------+------+
|    1 | 1    | 0        |    0 |
|    2 | 1    | 1        |    1 |
|    3 | 1    | 1        |    1 |
|    4 | 1    | 1        |    1 |
|    5 | 1    | 0        |    3 |
|    6 | 1    | 0        |    3 |
|    7 | 1    | 1        |    3 |
|    8 | 1    | 1        |    3 |
|    2 | 3    | 1        |    1 |
|    3 | 3    | 1        |    1 |
|    4 | 3    | 1        |    1 |
|    5 | 3    | 0        |    4 |
|    6 | 3    | 1        |    2 |
|    7 | 3    | 1        |    2 |
|    8 | 3    | 0        |    6 |
+------+------+----------+------+
'''
3. 根据上面的可以看到 连续的都被分为一组 再按照NAME、COMPLETE、W分组 TIME排序
SELECT *,ROW_NUMBER() OVER(PARTITION BY NAME,COMPLETE,W ORDER BY TIME) AS T 
FROM(
  SELECT *,(TIME-ROW_NUMBER() OVER(PARTITION BY NAME,COMPLETE ORDER BY TIME)) AS W 
  FROM T0224
)b ORDER BY NAME,TIME;
4. 当COMPLETE是0时 结果也是0

Mysql代码：
SELECT a.TIME,a.NAME,a.COMPLETE,(CASE a.COMPLETE WHEN 0 THEN 0 ELSE a.T END) as Result
FROM
(
  SELECT *,ROW_NUMBER() OVER(PARTITION BY NAME,COMPLETE,W ORDER BY TIME) AS T 
  FROM(
    SELECT *,(TIME-ROW_NUMBER() OVER(PARTITION BY NAME,COMPLETE ORDER BY TIME)) AS W 
    FROM T0224
  )b
)a
ORDER BY a.NAME,a.TIME;