跟踪实例状态更改的次数

Question

我需要跟踪一个客户如何从一种状态更改为另一种状态，并跟踪其在每种状态下的操作。我有下表：

Name - Purchase_date - Customer_category
Susan - 01_01_2021 - A
Susan - 02_01_2021 - B
Susan - 03_01_2021 - B
Susan - 04_01_2021 - B
Susan - 05_01_2021 - B
Susan - 06_01_2021 - A
Susan - 07_01_2021 - A
Susan - 08_01_2021 - B
Susan - 09_01_2021 - B
Susan - 10_01_2021 - B
Susan - 11_01_2021 - C
Susan - 12_01_2021 - D

预期的结果是一个包含两行的表，category_switch_number和occurrence_number，其中category_witch_number显示该特定客户的交换机(从1开始计数(，occurrences_number显示客户属于该类别时的购买次数：

Name - Purchase_date - Customer_category - category_switch_number - occurences_number
Susan - 01_01_2021 - A - 1 - 1
Susan - 02_01_2021 - B - 2 - 4
Susan - 03_01_2021 - B - 2 - 4
Susan - 04_01_2021 - B - 2 - 4
Susan - 05_01_2021 - B - 2 - 4
Susan - 06_01_2021 - A - 3 - 2
Susan - 07_01_2021 - A - 3 - 2
Susan - 08_01_2021 - B - 4 - 3
Susan - 09_01_2021 - B - 4 - 3
Susan - 10_01_2021 - B - 4 - 3
Susan - 11_01_2021 - C - 5 - 1
Susan - 12_01_2021 - D - 6 - 1

要复制此表并尝试自己运行查询，请参阅下面的表创建语句：

CREATE TABLE Table1
(`Customer_id` varchar(5), `purchase_time` varchar(10), `Customer_category` varchar(1), `category_switch_number` int, `occurences_number` int)
GO

INSERT INTO Table1
(`Customer_id`, `purchase_time`, `Customer_category`, `category_switch_number`, `occurences_number`)
VALUES
('Susan', '01_01_2021', 'A', 1, 1),
('Susan', '02_01_2021', 'B', 2, 4),
('Susan', '03_01_2021', 'B', 2, 4),
('Susan', '04_01_2021', 'B', 2, 4),
('Susan', '05_01_2021', 'B', 2, 4),
('Susan', '06_01_2021', 'A', 3, 2),
('Susan', '07_01_2021', 'A', 3, 2),
('Susan', '08_01_2021', 'B', 4, 3),
('Susan', '09_01_2021', 'B', 4, 3),
('Susan', '10_01_2021', 'B', 4, 3),
('Susan', '11_01_2021', 'C', 5, 1),
('Susan', '12_01_2021', 'D', 6, 1)
GO

我很抱歉没有分享我尝试过的东西，就像代码示例一样(我觉得更有用(，这篇文章已经很大了，但展示我的尝试会让它变得更大。

Answer 1

步骤1创建一个具有窗口功能的cte，根据购买时间查找客户之前的购买类别。

步骤2使用步骤1中的cte，创建每个序列的分组，其中从一个购买日期到下一个购买日，客户类别是相同的。当客户类别发生变化时，递增该序列分组。这是通过使用一个运行的求和窗口函数来实现的。

步骤3使用步骤2中的cte，为客户和序列分组使用final count((窗口函数，该函数将计算客户连续进入该类别的次数。

with priors as (
select t.*
, lag(t.customer_category) over(partition by t.customer_id order by t.purchase_time) as prior_category
from Table1 t
),


category_seq_grps as (
select t2.*
, sum(case when t2.prior_category = t2.customer_category then 0 else 1 end) over(partition by  t2.customer_id order by t2.purchase_time rows unbounded preceding) as cust_cat_seq_grouping
from priors t2
)

select csg.customer_id
, csg.purchase_time
, csg.customer_category
, csg.cust_cat_seq_grouping as category_switch_number
, count(*) over(partition by csg.customer_id,    csg.cust_cat_seq_grouping) as occurences_number
from category_seq_grps csg
order by csg.customer_id
, csg.purchase_time
;

这里有一把小提琴要示范。感谢您为您的问题提供数据、结果和DDL。

https://www.db-fiddle.com/f/8er78RNugk9TWQPzx5xdXP/1