SQL:间隙和孤岛问题-日期不连续导致排名不正确 [英] SQL: Gaps and Island Problem - Date not consecutive causing rank inaccurate

问题描述

这是对如果日期是连续的，则下面的查询有效.如何调整查询以获取所需内容?我尝试添加一个滞后1行的新列，并尝试检查新列中的四分之一值是否本质上是前一个四分之一的真实值(如果是，请使用计数器，否则请使用"1"重新启动).但是它不会重置以下记录.那我不确定该怎么办.

Below query works if the dates are consecutive. How would I adjust the query to get what I'm looking for? I tried adding a new column that is 1 row lag, and tried to check if the quarter value in the new column is essentially the true previous 1 quarter (if yes, use the counter, if not, use "1" to restart). But it doesn't reset the following records. Then I am not sure what to do.

select quarter, customer, segment,
       row_number() over (partition by customer, segment, seqnum - seqnum_cs order by right(quarter, 4), left(quarter, 2)) as counter
from (select t.*,
             row_number() over (partition by customer order by right(quarter, 4), left(quarter, 2)) as seqnum,
             row_number() over (partition by customer, segment order by right(quarter, 4), left(quarter, 2)) as seqnum_cs
      from t
     ) t
order by customer, seqnum;

推荐答案

您需要修复数据模型！无论如何，这都可以通过枚举四分之一来解决.

You need to fix your data model! In any case, this is solved by enumerating the quarters.

select quarter, customer, segment,
       row_number() over (partition by customer, segment, q_seqnum - seqnum_cs order by q_seqnum) as counter
from (select t.*,
             row_number() over (partition by customer, segment order by q_seqnum) as seqnum_cs
      from (select t.*,
                   cast(right(quarter, 4) as int) * 4 + cast(substring(quarter, 2, 1) as int) as q_seqnum
            from t
           ) t
     ) t
order by customer, q_seqnum;

这篇关于SQL:间隙和孤岛问题-日期不连续导致排名不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！