分类客户是否使用SQL转换了初始邮件或提醒邮件

Question

我一直坚持在Oracle SQL中解决这个问题。我有一个表，看起来像下面。

tbody> <<tr>112222244555555555666677777777778888

cust_id	event	event_date	camp_id	email_ind(计算列)
1	打开	06/09/2021	80
	发送	06/09/2021	80	初始
	发送	14/09/2021	80	提示
	点击	15/04/2021	80
	打开	15/04/2021	80
	发送	15/04/2021	80	初始
	为	15/04/2021	80
	WEBSITE_VISIT	15/04/2021	80
3	打开	01/06/2021	80
3	发送	01/06/2021	80	初始
3	打开	09/06/2021	80
3	发送	09/06/2021	80	提示
3	点击	10/06/2021	80
3	打开	10/06/2021	80
3	发送	10/06/2021	80
3	为	10/06/2021	80
3	WEBSITE_VISIT	10/06/2021	80
	发送	06/09/2021	80	初始
	发送	14/09/2021	80	提示
	打开	20/09/2021	80
	发送	20/09/2021	80	初始
	发送	28/09/2021	80	提示
	点击	03/10/2021	80
	打开	03/10/2021	80
	发送	03/10/2021	80	不是reinder邮件
	为	03/10/2021	80
	WEBSITE_VISIT	03/10/2021	80
	打开	05/11/2021	80
	打开	01-Jun-21	80
	发送	01-Jun-21	80	初始
	打开	09-Jun-21	80
	发送	09-Jun-21	80	提示
	打开	26-Jul-21	80
	发送	26-Jul-21	80	初始
	点击	03-Aug-21	80
	打开	03-Aug-21	80
	发送	03-Aug-21	80	不是一个提醒邮件
	为	03-Aug-21	80
	WEBSITE_VISIT	03-Aug-21	80
	点击	04-Aug-21	80
	打开	04-Aug-21	80
	发送	04-Aug-21	80
	打开	12-Jul-21	80
	发送	12-Jul-21	80	初始
	打开	20-Jul-21	80
	发送	20-Jul-21	80	提示
9	发送	29-Apr-21	80	初始
9	发送	07-May-21	80	提示

在Oracle 12中，您可以使用MATCH_RECOGNIZE进行逐行处理:

SELECT cust_id,
event,
event_date,
camp_id,
CASE email_ind
WHEN 'CONVERTED' THEN 'CONVERTED'
WHEN 'INITIAL'   THEN 'INITIAL'
WHEN 'REMINDER'  THEN 'REMINDER'
END AS email_ind,
CASE
WHEN email_ind = 'CONVERTED'
AND  has_reminder = 1
THEN 'REMINDER'
WHEN email_ind = 'CONVERTED'
THEN 'INITIAL'
END AS conversion_type
FROM   (SELECT t.*, ROWNUM AS rn FROM table_name t)
MATCH_RECOGNIZE (
PARTITION BY cust_id
ORDER     BY rn
MEASURES
CLASSIFIER() AS email_ind,
COUNT(reminder.cust_id)  AS has_reminder
ALL ROWS PER MATCH
PATTERN ( ("INITIAL" not_sent*)? (reminder not_sent*)? not_reminder converted | other )
DEFINE
"INITIAL"    AS event = 'SENT',
reminder     AS event = 'SENT',
not_reminder AS event = 'SENT',
not_sent     AS event <> 'SENT',
converted    AS event = 'CONVERTED'
)

db<此处小提琴>