MySQL - 是否可以使用正则表达式来计算行数？

Question

我需要有关 MySQL 问题的帮助。我有两张表，一张名为订阅，另一张名为付款。每个订阅都有多次付款。有些付款未启动 (0)，有些付款失败 (1)，有些付款成功 (2)。

每个订阅都可以生成多次付款尝试，直到成功为止。例如:

• 第一次尝试出现网络连接问题，并且status = 0
• 第二次尝试到达付款 API，但信用卡数据错误或资金不足，因​​此获得 status = 1
• 第三次尝试成功，并获得 status = 2

前两个可以是两个以上，事实上它可以是这样的:

0, 1, 0, 0, 1, 1, 1, 1, 2, 0, 0, 1, 2, 1, 0, 1, 2, 2, 2, 0, 1, 2, 1, 0, 2, 0, 2, 1, 2, 2

因此，每个订阅都有多个付款序列(每月或经常，不一定与日期相关)，可以使用此正则表达式 [0|1]*2 通过其状态进行识别，得到如下内容:

0, 1, 0, 0, 1, 1, 1, 1, 2,   // 9
0, 0, 1, 2,                  // 4
1, 0, 1, 2,                  // 4 
2,                           // 1
2,                           // 1
0, 1, 2,                     // 3 
1, 0, 2,                     // 3
0, 2,                        // 2
1, 2,                        // 2 
2                            // 1

但是，当然，由于有多个订阅，付款行在数据库中是混合的，唯一连接它们的是 subscription_id。

我需要获取的是从第一次尝试、第二次尝试、第三次、...、第10次尝试、10次以上尝试成功付款的订阅数量。

在上面的例子中它应该是:

attempts  count
9         1
4         2
3         2
2         2
1         3

这可能吗？

测试数据

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `payment` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subscription_id` int(11) DEFAULT NULL,
`status` smallint(6) NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_6D28840D9A1887DC` (`subscription_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED AUTO_INCREMENT=36 ;

INSERT INTO `payment` (`id`, `subscription_id`, `status`) VALUES
(1, 1, 1),(2, 1, 2),(3, 1, 2),(4, 2, 2),(5, 3, 2),(6, 4, 2),(7, 5, 2),
(8, 6, 1),(9, 6, 2),(10, 7, 2),(11, 7, 2),(12, 8, 0),(13, 8, 1),(14, 8, 2),
(15, 8, 2),(16, 9, 1),(17, 9, 2),(18, 9, 2),(19, 9, 1),(20, 9, 2),(21, 10, 0),
(22, 10, 1),(23, 10, 1),(24, 10, 1),(25, 10, 1),(26, 11, 0),(27, 11, 0),(28, 11, 1),
(29, 11, 1),(30, 11, 1),(31, 8, 0),(32, 8, 1),(33, 8, 2),(34, 10, 1),(35, 10, 2);

CREATE TABLE IF NOT EXISTS `subscription` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` smallint(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED AUTO_INCREMENT=12 ;

INSERT INTO `subscription` (`id`, `status`) VALUES
(1, 1),(2, 1),(3, 1),(4, 1),(5, 1),(6, 1),(7, 1),
(8, 1),(9, 1),(10, 0),(11, 0);

ALTER TABLE `payment`
ADD CONSTRAINT `sub_id` FOREIGN KEY (`subscription_id`) REFERENCES `subscription` (`id`);

注意:测试数据与上面的示例并不相似。对于测试数据，结果应如下所示:

subscription_id    cntAttempts     [attempts counted]
1                  2               1, 2,
1                  1               2, 
2                  1               2, 
3                  1               2, 
4                  1               2, 
5                  1               2, 
6                  2               1, 2, 
7                  1               2, 
7                  1               2, 
8                  3               0, 1, 2, 
8                  1               2, 
9                  2               1, 2, 
9                  1               2, 
9                  2               1, 2, 
10                 7               0, 1, 1, 1, 1, 1, 2
11                 5               0, 0, 1, 1, 1, 
8                  3               0, 1, 2, 

对于 subscription_id = 10，最后两笔付款插入到最后的 11 和 8 付款之后。

预期最终结果:

paymentAttemptsCount    count
1                       9
2                       5
3                       1
4                       0
5                       1
6                       0
7                       1
8                       0
9                       0
10                      0

注意:id = 11的订阅没有成功付款。

Answer 1

如上所述，您实际上应该为 paymentid/invoicenumber 添加一列(这将是发票表的外键)，以将多次付款尝试分组在一起 - 因为它们属于在一起，并且您的数据模型应该表示该逻辑。您的问题看起来更复杂，因为您缺少该数据。

您可以在运行时计算该 ID(至少如果您按顺序添加它们，并且没有为同一 subscription_id 混合付款，例如，因为发票可能在下个月仍处于打开状态，并且您现在尝试为两者开具发票)。对于您的第一个查询，您可以例如使用

select subscription_id, 
       count(*) as cntAttempts, 
       group_concat(status order by id) as attempts_counted 
from 
  (SELECT id, subscription_id, status, 
         @pid := CASE WHEN @last_status = 2 or subscription_id <> @last_id 
                      THEN @pid + 1 else @pid END as pid,
         @last_id := subscription_id,
         @last_status := status
   from (select @last_id := 0, @pid := 0, @last_status := 0) init, payment
   order by subscription_id, id
  ) as payment_grpd
group by pid, subscription_id;

您的第二个查询需要再次对此结果进行分组，例如，看起来像

select cntAttempts, 
       count(*) as count
from 
  (select pid, 
          count(*) as cntAttempts 
   from 
     (SELECT id, subscription_id, status, 
            @pid := CASE WHEN @last_status = 2 or subscription_id <> @last_id 
                         THEN @pid + 1 else @pid END as pid,
            @last_id := subscription_id,
            @last_status := status
      from (select @last_id := 0, @pid := 0, @last_status := 0) init, payment
      order by subscription_id, id
     ) as payment_grpd
     group by pid, subscription_id
  ) as subcounts 
group by cntAttempts;

子查询 payment_grpd将重新计算您的ID。如果您添加缺少的关系，您的第一个查询将类似于

select subscription_id, 
       count(*) as cntAttempts, 
       group_concat(status order by id) as attempts_counted 
from payment
group by pid, subscription_id;

第二个也是类似的。为了规范化，subscription_id 可能必须移至引用表。