查找唯一标识符重复的每个字段的最大序列号

Question

我有一个用户、ID和活动时间戳的列表。数据按用户、ID、活动时间和基于所有这些维度分配的序列号排序。我需要根据序列号提取最早的时间戳，然后根据每个用户和ID的序列号提取最近的时间戳。问题是，当ID在当天晚些时候甚至第二天重复时，我的代码不会为每个ID提取唯一的事件序列。

因此，如果用户A在ID A123上执行5个事件，然后在ID A567上移动以执行6个事件，再移回A123以执行4个附加任务，我需要结果来反映ID A123、A567和A123上的第一个和最后一个活动。

下面是我用来生成表格的当前代码：

SELECT 
user_id,
activity_date,
foc_id,
ROW_NUMBER() OVER ( PARTITION BY user_id, mno ORDER BY activity_date ) AS seq_num

FROM   cf.activity_history
MATCH_RECOGNIZE (
PARTITION BY user_id
ORDER     BY activity_date
MEASURES
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN (same_foc_id * last_row  )
DEFINE
same_foc_id AS FIRST(foc_id) = NEXT(foc_id )
)
where 
user_id in ('UVAC3','UVAH161','UVEK29','UVRB152')
and (activity_date < (sysdate - 11)
and activity_date > (sysdate - 13))
order by
user_id,
activity_date,
foc_id

在此处输入图像描述

Answer 1

这读起来像是一个缺口和岛屿问题：一个岛屿代表一系列的"相邻的"；行，其中用户执行相同的活动，并且您想要每个岛的开始和结束。

一个选项使用lead()和lag()。假设foc_id识别活动：

select user_id, activity_date, foc_id
from (
select a.*,
lead(foc_id) over(partition by user_id order by activity_date) lead_foc_id,
lag(foc_id) over(partition by user_id order by activity_date) lag_foc_id
from cf.activity_history a
where
user_id in ('UVAC3', 'UVAH161', 'UVEK29', 'UVRB152')
and activity_date < sysdate - 11
and activity_date > sysdate - 13
) a
where 
lead_foc_id is null 
or lag_foc_id is null
or foc_id <> lead_foc_id
or foc_id <> lag_foc_id