MariaDB – Count Number of Consecutive Occurrence of values

測試環境為 CentOS 8 X86_64 (虛擬機)

如何計算出連續資料的數目,參考文章 – https://stackoverflow.com/questions/36927685/count-number-of-consecutive-occurrence-of-values-in-table ,主要是透過 ROW_NUMBER() 函數來使用 – https://benjr.tw/104156

+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | A    |
|  3 | B    |
|  4 | B    |
|  5 | B    |
|  6 | B    |
|  7 | C    |
|  8 | B    |
|  9 | B    |
+----+------+

如以上資料來計算出 Name 連續重複的次數.

+------+----------+
| Name | COUNT(*) |
+------+----------+
| A    |        2 |
| B    |        4 |
| C    |        1 |
| B    |        2 |
+------+----------+

先建立一個資料庫 (testdb) , 與 t tables 格式為 ID int(11) 以及 Name char(20)

[root@localhost ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> CREATE TABLE t (ID INT(11) NOT NULL AUTO_INCREMENT ,Name VARCHAR(20) , PRIMARY KEY (ID));
Query OK, 0 rows affected (0.003 sec)

透過 INSERT 新增加了四筆資料.

MariaDB [testdb]> INSERT INTO t ( ID , Name) VALUES (1, 'A'),(2, 'A'),(3, 'B'),(4, 'B'),(5, 'B'),(6, 'B'),(7, 'C'),(8, 'B'),(9, 'B');
Query OK, 9 rows affected (0.002 sec)
Records: 9  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM t;
+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | A    |
|  3 | B    |
|  4 | B    |
|  5 | B    |
|  6 | B    |
|  7 | C    |
|  8 | B    |
|  9 | B    |
+----+------+
9 rows in set (0.000 sec)

資料必須包含 Index ,如資料在建立時沒有 Index 時可以使用 ROW_NUMBER 函數來建立並把這些資料當作來源 sub-query.

MariaDB [testdb]> SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ID , Name FROM t;
+-----+------+
| ID  | Name |
+-----+------+
|   1 | A    |
|   2 | A    |
|   3 | B    |
|   4 | B    |
|   5 | B    |
|   6 | B    |
|   7 | C    |
|   8 | B    |
|   9 | B    |
+-----+------+
9 rows in set (0.000 sec)

程式

SELECT Name, COUNT(*) 
FROM (SELECT t.*,
             (ROW_NUMBER() OVER (ORDER BY ID) -
              ROW_NUMBER() over (PARTITION BY NAME ORDER BY ID)
             ) AS GRP
      FROM t
     ) t
GROUP BY GRP , NAME ORDER BY ID;

執行結果.

+------+----------+
| Name | COUNT(*) |
+------+----------+
| A    |        2 |
| B    |        4 |
| C    |        1 |
| B    |        2 |
+------+----------+
4 rows in set (0.001 sec)

程式說明

SELECT t.*, 
ROW_NUMBER() OVER (ORDER BY ID) AS Index1 , 
ROW_NUMBER() over (PARTITION BY NAME ORDER BY ID) AS Index2 ,
ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() over (PARTITION BY NAME ORDER BY ID) AS Index3
FROM t;

執行結果.

+----+------+--------+--------+--------+
| ID | Name | Index1 | Index2 | Index3 |
+----+------+--------+--------+--------+
|  1 | A    |      1 |      1 |      0 |
|  2 | A    |      2 |      2 |      0 |
|  3 | B    |      3 |      1 |      2 |
|  4 | B    |      4 |      2 |      2 |
|  5 | B    |      5 |      3 |      2 |
|  6 | B    |      6 |      4 |      2 |
|  8 | B    |      8 |      5 |      3 |
|  9 | B    |      9 |      6 |      3 |
|  7 | C    |      7 |      1 |      6 |
+----+------+--------+--------+--------+
9 rows in set (0.000 sec)

• ROW_NUMBER() OVER (ORDER BY ID)
  就是全部資料的排序
• ROW_NUMBER() over (PARTITION BY NAME ORDER BY ID)
  就是依據 Name 的資料來排序.
• ROW_NUMBER() OVER (ORDER BY ID) – ROW_NUMBER() over (PARTITION BY NAME ORDER BY ID)
  當連續資料皆相同時其相減的數字會相同,後面透過 GROUP 用 COUNT 去計算就可以算出連續資料的數目.