去除CASE WHEN重复值并计算注册流程各步骤平均时间间隔

需要生成按sessionid梳理的注册表单旅程事件序列表，计算连续两个步骤之间的分钟差，同时保证sessionid唯一。已知注册步骤不可跳步、不同步骤的时间戳不会完全相同。 #

create table merchant_general.signup_step_v20 as
select a.sessionid, min(b.sentat) over (partition by b.sessionid) as pageload, 
case when a.properties.itemname in ("MerchantType1", "MerchantType2", "MerchantType3", "MerchantType4") and a.properties.clicktype = 'forward' then max(a.sentat) over (partition by a.sessionid) end as SelectedMerchantType,
case when a.properties.itemname = "Continue" and a.properties.`location`= "Yeni başvuru-mağaza bilgileri" then max(a.sentat) over (partition by a.sessionid) end as EnteredNamePass,
case when a.properties.itemname = "Continue" and a.properties.`location`= "yeni başvuru-başvuru bilgileri" then max(a.sentat) over (partition by a.sessionid) end as EnteredAuthInfo,
case when a.properties.itemname = "Submit" and a.properties.`location`= "yeni başvuru-başvuruyu tamamlayın" then max(a.sentat) over (partition by a.sessionid) end as EnteredCompanyInfo
from merchant.mp_clickitem a 
inner join merchant.mp_pageload b 
on a.sessionid = b.sessionid 
where b.dy >= date_sub(current_date,30) and b.dy <= date_sub(current_date,1) and a.dy >= date_sub(current_date,30) and a.dy <= date_sub(current_date,1) 
and b.properties.pagename = "Register" and
a.properties.itemname in("MerchantType1", "MerchantType2", "MerchantType3", "MerchantType4","Continue","Submit") and a.properties.`location` != "Reset Password" and a.sessionid != '' 

现有代码运行结果包含5个步骤的时间戳，顺序依次为：

• pageload：页面加载（第一步）
• SelectedMerchantType：选择商户类型（第二步）
• EnteredNamePass：输入账号密码（第三步）
• EnteredAuthInfo：输入认证信息（第四步）
• EnteredCompanyInfo：提交企业信息（最后一步）

运行结果示例：
#

现有代码输出存在同一sessionid对应多行的问题，需要先按sessionid聚合去重，再计算相邻步骤的分钟差，最终生成目标表的代码如下：

create table merchant_general.signup_step_duration_v20 as
select 
    sessionid,
    pageload,
    SelectedMerchantType,
    EnteredNamePass,
    EnteredAuthInfo,
    EnteredCompanyInfo,
    -- 计算相邻步骤的分钟差
    timestampdiff(minute, pageload, SelectedMerchantType) as step1_2_min,
    timestampdiff(minute, SelectedMerchantType, EnteredNamePass) as step2_3_min,
    timestampdiff(minute, EnteredNamePass, EnteredAuthInfo) as step3_4_min,
    timestampdiff(minute, EnteredAuthInfo, EnteredCompanyInfo) as step4_5_min
from (
    -- 按sessionid聚合，拿到每个步骤的唯一时间戳，实现sessionid去重
    select 
        sessionid,
        max(pageload) as pageload,
        max(SelectedMerchantType) as SelectedMerchantType,
        max(EnteredNamePass) as EnteredNamePass,
        max(EnteredAuthInfo) as EnteredAuthInfo,
        max(EnteredCompanyInfo) as EnteredCompanyInfo
    from merchant_general.signup_step_v20
    group by sessionid
) t

目标表样式示例：
#

内容的提问来源于stack exchange，提问作者Ipek