MySQL - 连续出现的计数

Question

关于 this fiddle ,

create table tbl(`date` date, customer varchar(200), 
                  serv_info varchar(200), category varchar(20));

insert into tbl values
('2015-01-01', 'customerA', 'Type1Id1', 'AG'),
('2015-01-02', 'customerA', 'Type1Id1', 'AG'),
('2015-01-03', 'customerA', 'Type1Id1', 'AG'),
('2015-01-11', 'customerA', 'Type1Id2', 'AG'),
('2015-01-13', 'customerA', 'Type1Id2', 'AG'),
('2015-01-16', 'customerA', 'Type1Id3', 'AG'),
('2015-01-20', 'customerA', 'Type2Id1', 'AG'),
('2015-01-21', 'customerA', 'Type2Id1', 'AG'),
('2015-01-22', 'customerA', 'Type2Id1', 'AG'),
('2015-01-23', 'customerA', 'Type2Id1', 'AG'),
('2015-01-11', 'customerA', 'Type1Id1', 'AG'),
('2015-01-12', 'customerA', 'Type1Id1', 'AG'),
('2015-01-13', 'customerA', 'Type1Id1', 'AG');

我想看到下面的输出-

10/01/15 - Type1Id1 -10
11/01/15 - Type1Id2 - 5
16/01/15 - Type1Id1 -3

我已经设法用 SQL 进行了分类，但是，我无法完全满足要求，特别是在不同时间点对给定类型的相同服务器 ID 的连续计数。 (我不是一个成熟的 MYSQL 人，因此很难做到这一点)

我可以请求帮助以使其完全正常工作吗？我已经尽力解释了这种情况。如果不清楚，请让我知道不清楚的地方，我会尽量重述。

Answer 1

这是一个基于聚合函数、分组依据和用户变量的解决方案。这里使用用户变量来保证结果。

输出也稍微格式化以符合要求。

SQL:

-- Data preparation
create table tbl(`date` date, customer varchar(200), serv_info varchar(200), category varchar(20));
insert into tbl values
('2015-01-01', 'customerA', 'Type1Id1', 'AG'),
('2015-01-02', 'customerA', 'Type1Id1', 'AG'),
('2015-01-03', 'customerA', 'Type1Id1', 'AG'),
('2015-01-11', 'customerA', 'Type1Id2', 'AG'),
('2015-01-13', 'customerA', 'Type1Id2', 'AG'),
('2015-01-16', 'customerA', 'Type1Id3', 'AG'),
('2015-01-20', 'customerA', 'Type2Id1', 'AG'),
('2015-01-21', 'customerA', 'Type2Id1', 'AG'),
('2015-01-22', 'customerA', 'Type2Id1', 'AG'),
('2015-01-23', 'customerA', 'Type2Id1', 'AG'),
('2015-01-11', 'customerA', 'Type1Id1', 'AG'), 
('2015-01-12', 'customerA', 'Type1Id1', 'AG'), 
('2015-01-13', 'customerA', 'Type1Id1', 'AG');
SELECT * FROM tbl;

-- Needed
SET @rownum = 0;
SET @typeid = '';
SELECT 
    CONCAT( DATE_FORMAT(assign_date, '%d/%m/%y'), ' - ', tbl2.serv_info, ' - ', tbl2.consecutive_days ) Output
FROM 
    (SELECT 
        MIN(`date`) assign_date, 
        serv_info, 
        COUNT(1) consecutive_days,
        @rownum:=@rownum+(serv_info != @typeid) conse_group,
        @typeid:=serv_info
    FROM tbl
    WHERE customer = 'customerA'
    GROUP BY conse_group) tbl2;

输出:

mysql> SELECT * FROM tbl;
+------------+-----------+-----------+----------+
| date       | customer  | serv_info | category |
+------------+-----------+-----------+----------+
| 2015-01-01 | customerA | Type1Id1  | AG       |
| 2015-01-02 | customerA | Type1Id1  | AG       |
| 2015-01-03 | customerA | Type1Id1  | AG       |
| 2015-01-11 | customerA | Type1Id2  | AG       |
| 2015-01-13 | customerA | Type1Id2  | AG       |
| 2015-01-16 | customerA | Type1Id3  | AG       |
| 2015-01-20 | customerA | Type2Id1  | AG       |
| 2015-01-21 | customerA | Type2Id1  | AG       |
| 2015-01-22 | customerA | Type2Id1  | AG       |
| 2015-01-23 | customerA | Type2Id1  | AG       |
| 2015-01-11 | customerA | Type1Id1  | AG       |
| 2015-01-12 | customerA | Type1Id1  | AG       |
| 2015-01-13 | customerA | Type1Id1  | AG       |
+------------+-----------+-----------+----------+
13 rows in set (0.00 sec)

mysql>
mysql> -- Needed
mysql> SET @rownum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @typeid = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> CONCAT( DATE_FORMAT(assign_date, '%d/%m/%y'), ' - ', tbl2.serv_info, ' - ', tbl2.consecutive_days ) Output
    -> FROM
    ->     (SELECT
    ->     MIN(`date`) assign_date,
    ->     serv_info,
    ->     COUNT(1) consecutive_days,
    ->     @rownum:=@rownum+(serv_info != @typeid) conse_group,
    ->     @typeid:=serv_info
bl2;
    ->     FROM tbl
    ->     WHERE customer = 'customerA'
    ->     GROUP BY conse_group) tbl2;
+-------------------------+
| Output                  |
+-------------------------+
| 01/01/15 - Type1Id1 - 3 |
| 11/01/15 - Type1Id2 - 2 |
| 16/01/15 - Type1Id3 - 1 |
| 20/01/15 - Type2Id1 - 4 |
| 11/01/15 - Type1Id1 - 3 |
+-------------------------+
5 rows in set (0.00 sec)