最高连续出现次数之和

本文介绍了最高连续出现次数之和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I have a table with three columns (lending_id int, installment_n serial int, status text) and I wonder how to retrieve the biggest gap of WAITING_PAYMENT (status) for each 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)之间.

Since for 71737 it would consider from installment 6 to 21 (16) and for 354226 the gap between 8 and 15 (8).

推荐答案

这是一种基于模拟row_number()的方法，该方法可在不支持窗口功能的MySQL版本上使用(计划将窗口功能包含在MySQL v8.x中)

This is an approach based on mimicking row_number() that will work on MySQL versions not supporting window functions (window functions are planned for inclusion with MySQL v8.x).

此方法的结果将揭示有关最长序列的事实，而不仅仅是计数.有关详细信息，请参见下面的结果.

The result of this approach will reveal more facts about the longest sequence than just the count alone. See results below for details of this.

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

结果 :

Results:

| 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方法更有效.

Whilst you can't use row_number() until V8.x of MySQL is in production release; But for users of db's already supporting it, and for MySQL users when it is available, here is the same approach using row_number() which I would xpect to be more efficient than the @variable approach.

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)此处