You need to enable JavaScript to run this app. 如何编写SQL按user_unique_access_id分组获取每组首条及最新状态记录
优惠活动
大模型
产品
解决方案
定价
更多

如何编写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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.14 07:15:06