sql - 查找唯一标识符重复的每个字段的最大序列号
问题描述
我有一个用户、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
解决方案
这读起来就像一个间隙和孤岛问题:一个孤岛代表一系列“相邻”行,用户在其中执行相同的活动,并且您需要每个孤岛的开始和结束。
一种选择使用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
推荐阅读
- json - 是否可以从 Facebook Graph API 中检索有关页面城市的更多详细信息?
- c++ - 关于调用 srand 的说明
- performance - 是否有与 DATETIME2FROMPARTS 等效的“尝试”?
- java - 如何在另一个类(let B)中使用一个类(let A)的非静态变量。在这里,不能在 B 中创建 A 的实例
- node.js - 节点服务器在使用集群模块的 express api 设置上没有处理 1500 个用户
- python - 调整散点图的注释位置
- facebook - Facebook bot 消息未显示空行
- c++ - 有没有办法在旧版本的 c++(c++11 之前)中实现初始化列表(种类)?
- eclipse - 如何用 JSP 解决这个问题?
- java - 将字符串数组转换为数组列表