最高连续出现次数之和 [英] sum highest consecutive occurrence
问题描述
我有一个包含三列(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.
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)此处
这篇关于最高连续出现次数之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
