sql - 删除“case when”中的重复项并计算步骤之间的平均值

Question

我目前正在生成一个表，我试图分别通过 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 != '' 

A列对应：sesssionID，而B列是pageload（第一步），C列是SelectedMerchantType（第二步）D列是EnteredNamePass（第三步），E列是EnteredAuthInfo（第四步），F列是输入公司信息（最后一步）。

我想计算两个连续步骤之间的微小差异，避免重复sessionID。在一个步骤完成之前不可能进行下一步，并且不同的步骤不能具有完全相同的时间戳。

决赛桌应该是这样的：

标签： sqlhivehiveql

Answer 1

您是否尝试按 sessionID 分组？