Redshift SQL:添加和重置考虑了日期和组的计数器 [英] Redshift SQL: add and reset a counter with date and group considered

问题描述

假设我有一张桌子.我希望有一个计数器来计算客户(有很多)在细分A中的次数.如果客户在两个季度之间跳到另一个细分，则当客户跳回到细分时计数器将重置.答:我确信有很多方法可以做到这一点，但是我只是想不通.请帮助.谢谢！

Suppose I have a table below. I'd like to have a counter to count the # of times when a Customer (there are many) is in Segment A. If the Customer jumps to a different Segment between 2 quarters, the counter will reset when the Customer jumps back to Segment A. I am sure there are many ways to do it, but I just can't figure this out..Please help. Thank you!

Quarter    Segment    Customer    *Counter*
Q1 2018    A          A1          1
Q2 2018    A          A1          2
Q3 2018    A          A1          3
Q4 2018    B          A1          1
Q1 2019    B          A1          2
Q2 2019    A          A1          1
Q1 2020    A          A1          *1* I want 1 not 2 here because it's not consecutive

推荐答案

这是一种缺岛"问题.您可以使用不同的行号来解决此问题.真正的问题是处理宿舍.但是字符串函数可以解决这个问题.

This is a type of gaps-and-islands problem. You can solve this with a difference of row numbers. The real problem is dealing with the quarters. But string functions can handle that.

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;

此处的关键思想是，行号的不同为状态相同的客户定义了相邻的行.很难理解为什么会这样.但是，如果您查看子查询的结果，那么您无疑会看到并理解为什么这是可行的.

The key idea here is that the difference of row numbers defines the adjacent rows for a customer with the same status. It can be a bit hard to see why this is the case. However, if you look at the results of the subquery, you will no doubt see and understand why this is works.

这篇关于Redshift SQL:添加和重置考虑了日期和组的计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！