最高连续出现次数之和

Question

我有一个包含三列（lending_id int, installment_n serial int, status text）的表，我想知道如何WAITING_PAYMENT (status) 为每个lending_id检索最大的差距 。

对于以下示例：

lending_id | installment_n | status
71737   1    PAID
71737   2    PAID
71737   3    PAID
71737   4    PAID
71737   5    PAID
71737   6    WAITING_PAYMENT
71737   7    WAITING_PAYMENT
71737   8    WAITING_PAYMENT
71737   9    WAITING_PAYMENT
71737   10   WAITING_PAYMENT
71737   11   WAITING_PAYMENT
71737   12   WAITING_PAYMENT
71737   13   WAITING_PAYMENT
71737   14   WAITING_PAYMENT
71737   15   WAITING_PAYMENT
71737   16   WAITING_PAYMENT
71737   17   WAITING_PAYMENT
71737   18   WAITING_PAYMENT
71737   19   WAITING_PAYMENT
71737   20   WAITING_PAYMENT
71737   21   WAITING_PAYMENT
354226  1    PAID
354226  2    PAID
354226  3    WAITING_PAYMENT
354226  4    WAITING_PAYMENT
354226  5    WAITING_PAYMENT
354226  6    WAITING_PAYMENT
354226  7    PAID
354226  8    WAITING_PAYMENT
354226  9    WAITING_PAYMENT
354226  10   WAITING_PAYMENT
354226  11   WAITING_PAYMENT
354226  12   WAITING_PAYMENT
354226  13   WAITING_PAYMENT
354226  14   WAITING_PAYMENT
354226  15   WAITING_PAYMENT

我想知道如何检索：

lending_id | count
71737      | 16
354226     | 8

因为对于71737，它将考虑从装置6到21（16），对于354226，将考虑8和15（8）之间的间隙。

Answer 1

这是一种基于模仿的方法row_number()，适用于不支持窗口功能的MySQL版本（计划将窗口功能包含在MySQL v8.x中）。

这种方法的结果将揭示有关最长序列的更多事实，而不仅仅是计数本身。有关详细信息，请参见下面的结果。

SQL小提琴

MySQL 5.6模式设置 ：

CREATE TABLE Table1
    (`lending_id` int, `installment_n` int, `status` varchar(15))
;

INSERT INTO Table1
    (`lending_id`, `installment_n`, `status`)
VALUES
    (71737, 1, 'PAID'),
    (71737, 2, 'PAID'),
    (71737, 3, 'PAID'),
    (71737, 4, 'PAID'),
    (71737, 5, 'PAID'),
    (71737, 6, 'WAITING_PAYMENT'),
    (71737, 7, 'WAITING_PAYMENT'),
    (71737, 8, 'WAITING_PAYMENT'),
    (71737, 9, 'WAITING_PAYMENT'),
    (71737, 10, 'WAITING_PAYMENT'),
    (71737, 11, 'WAITING_PAYMENT'),
    (71737, 12, 'WAITING_PAYMENT'),
    (71737, 13, 'WAITING_PAYMENT'),
    (71737, 14, 'WAITING_PAYMENT'),
    (71737, 15, 'WAITING_PAYMENT'),
    (71737, 16, 'WAITING_PAYMENT'),
    (71737, 17, 'WAITING_PAYMENT'),
    (71737, 18, 'WAITING_PAYMENT'),
    (71737, 19, 'WAITING_PAYMENT'),
    (71737, 20, 'WAITING_PAYMENT'),
    (71737, 21, 'WAITING_PAYMENT'),
    (354226, 1, 'PAID'),
    (354226, 2, 'PAID'),
    (354226, 3, 'WAITING_PAYMENT'),
    (354226, 4, 'WAITING_PAYMENT'),
    (354226, 5, 'WAITING_PAYMENT'),
    (354226, 6, 'WAITING_PAYMENT'),
    (354226, 7, 'PAID'),
    (354226, 8, 'WAITING_PAYMENT'),
    (354226, 9, 'WAITING_PAYMENT'),
    (354226, 10, 'WAITING_PAYMENT'),
    (354226, 11, 'WAITING_PAYMENT'),
    (354226, 12, 'WAITING_PAYMENT'),
    (354226, 13, 'WAITING_PAYMENT'),
    (354226, 14, 'WAITING_PAYMENT'),
    (354226, 15, 'WAITING_PAYMENT')
;

查询1 ：

select lending_id, status, start_at_inst, end_at_inst, inst_count
from (
      select IF(@prev_value=lending_id, @rn:=@rn+1 , @rn:=1) AS rn
            , lending_id, status, start_at_inst, end_at_inst, inst_count
            , @prev_value := lending_id z
      from (
           select lending_id
                   , status
                   , grpby
                   , min(installment_n) start_at_inst
                   , max(installment_n) end_at_inst
                   , (max(installment_n) + 1) - min(installment_n) inst_count
            from (
                 select
                        IF(@prev_value=concat_ws(',',lending_id,status), @rn:=@rn+1 , @rn:=1) AS rn
                      , t.*
                      , installment_n - @rn grpby
                      , @prev_value := concat_ws(',',lending_id,status) z
                 from Table1 t
                 cross join (
                     select @rn := 0 , @prev_value := ''
                     ) vars
                 order by lending_id, status,installment_n ASC
                 ) d1
            group by lending_id, status, grpby
          ) d2
      cross join (
          select @rn := 0 , @prev_value := ''
          ) vars
      order by lending_id, inst_count DESC
     ) d3
where rn = 1

结果 ：

| lending_id |          status | start_at_inst | end_at_inst | inst_count |
|------------|-----------------|---------------|-------------|------------|
|     354226 | WAITING_PAYMENT |             8 |          15 |          8 |
|      71737 | WAITING_PAYMENT |             6 |          21 |         16 |

在MySQL的V8.x正式发布之前，您不能使用row_number（）。但是对于已经支持db的用户，以及对于可用的MySQL用户，这是使用row_number（）的相同方法，我希望它比@variable方法更有效。

select
       lending_id, status, start_at_inst, end_at_inst, inst_count
from (
select 
       lending_id
       , status
       , grpby
       , min(installment_n) start_at_inst
       , max(installment_n) end_at_inst
       , (max(installment_n) + 1) - min(installment_n) inst_count
       , row_number() over(partition by lending_id order by (max(installment_n) + 1) - min(installment_n) DESC) rn
from (
     select
            t.*
          , installment_n - row_number() over(partition by lending_id, status order by installment_n) grpby
     from Table1 t
     ) d1
group by
       lending_id, status, grpby
    ) d2
where rn = 1
;

结果：

 lending_id | status          | start_at_inst | end_at_inst | inst_count
 ---------: | :-------------- | ------------: | ----------: | ---------:
      71737 | WAITING_PAYMENT |             6 |          21 |         16
     354226 | WAITING_PAYMENT |             8 |          15 |          8

dbfiddle（mariadb_10.2）
在这里