mysql - sql查询中是否有任何方法可以对有序记录集中的某些值进行排名，以便在遇到不同值后排名重置

Question

我想对状态标志的“失效”值进行排名，使其在遇到任何其他值后自行重置，当再次遇到“失效”时，它应该再次从 1 开始排名

我很困惑我们怎么能在sql中做这样的事情。我尝试了分析函数，但没有成功

数据看起来像这样。

id      yr_mon  status
-------------------------
1002    201703  New
1002    201704  Retained
1002    201705  Unretained
1002    201706  Lapsed
1002    201707  Lapsed
1002    201708  Reactivated
1002    201709  lapsed
1002    201710  Unretained
1002    201711  Lapsed
1002    201712  Lapsed
1003    201703  New
1003    201704  Retained
1003    201705  Lapsed
1003    201706  Lapsed
1003    201707  Reactivated
1003    201708  Lapsed
1003    201709  Lapsed
1003    201710  Lapsed

输出应该是这样的。

id      yr_mon  status    lapsed_months
----------------------------------------
1002    201703  New            0
1002    201704  Retained       0
1002    201705  Unretained     0
1002    201706  Lapsed         1
1002    201707  Lapsed         2
1002    201708  Reactivated    0
1002    201709  lapsed         1
1002    201710  Unretained     0
1002    201711  Lapsed         1
1002    201712  Lapsed         2
1003    201703  New            0
1003    201704  Retained       0
1003    201705  Lapsed         1
1003    201706  Lapsed         2
1003    201707  Reactivated    0
1003    201708  Lapsed         1
1003    201709  Lapsed         2
1003    201710  Lapsed         3

Answer 1

从 8.0 开始 MySql 支持窗口函数。分组并在组内编号行

with grp as (
 select id, yr_mon, status
  , row_number() over(partition by id order by yr_mon) - row_number() over(partition by id, status order by yr_mon)  grp
 from tlog 
)
select id, yr_mon, status, case status when 'Lapsed' then row_number() over(partition by id, grp order by yr_mon) else 0 end lapsed_months
from grp
order by id, yr_mon; 

fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2d4fcfa5458096c0f3bf296099660d1c请注意，我故意以任意顺序放置源数据。