如何编写SQL按user_unique_access_id分组获取每组首条及最新状态记录
你写的SQL出现重复记录的核心原因是GROUP BY子句中加入了ul.status、ul.created_timestamp两个字段,这会把同一个用户的每一条位置记录都分成独立的组,最终每个用户会输出和他的位置记录条数相同的结果行,自然就出现了重复。
最小改动解法
对你原有写法调整,把聚合函数改为窗口函数、去掉多余的GROUP BY、加distinct去重即可:
select distinct u.user_unique_access_id, first_value(ul.status) over (partition by u.user_unique_access_id order by ul.created_timestamp asc) as first_status, min(ul.created_timestamp) over (partition by u.user_unique_access_id) as first_created_at, first_value(ul.status) over (partition by u.user_unique_access_id order by ul.created_timestamp desc) as current_status, max(ul.created_timestamp) over (partition by u.user_unique_access_id) as last_created_at from public.users u join public.user_location ul on u.user_key = ul.user_key;
更高性能解法
如果数据量较大,可以用CTE加行号的写法,避免全量去重:
with user_location_rank as ( select u.user_unique_access_id, ul.status, ul.created_timestamp, -- 按时间正排标记最早记录 row_number() over(partition by u.user_unique_access_id order by ul.created_timestamp asc) as rn_asc, -- 按时间倒排标记最新记录 row_number() over(partition by u.user_unique_access_id order by ul.created_timestamp desc) as rn_desc from public.users u inner join public.user_location ul on u.user_key = ul.user_key ) select user_unique_access_id, max(case when rn_asc = 1 then status end) as first_status, max(case when rn_asc = 1 then created_timestamp end) as first_created_at, max(case when rn_desc = 1 then status end) as current_status, max(case when rn_desc = 1 then created_timestamp end) as last_created_at from user_location_rank group by user_unique_access_id;
以上两种写法都可以实现每个user_unique_access_id仅输出一行结果,包含对应的最早状态、最早时间、最新状态、最新时间。
内容的提问来源于stack exchange,提问作者Giri
相关产品推荐
相关产品推荐

