sql - 获取每个组的第一条和最后一条记录

Question

我有 2 张桌子

我的输出应该是这样的。基本上，每个 user_unique_access_id 的最新和第一个状态以及时间戳

我尝试了以下查询，但我的输出有重复的记录。

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) 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) as last_created_at
from
    public.users u
join public.user_location ul on
    u.user_key = ul.user_key
group by
    u.user_unique_access_id,
    u.user_name,
    ul.status,
    ul.created_timestamp ;

DDL 和 DML 语句

create table public.users ( user_key serial primary key, user_name varchar(20), user_unique_access_id varchar(20) );

create table public.user_location( user_key bigint not null, STATUS VARCHAR (512) not null, CREATED_TIMESTAMP timestamp not null, foreign key (user_key) references public.users (user_key) );

insert
    into
    public.users ( user_unique_access_id, user_name)
values('ABC_1', 'ABC');

insert
    into
    public.users ( user_unique_access_id, user_name)
values('ABC_2', 'ABC');

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(1, 'Entrance', current_timestamp);

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(1, 'Building A', current_timestamp);

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(1, 'Building B', current_timestamp);

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(1, 'Exit', current_timestamp);

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(2, 'Entrance', current_timestamp);

insert
    into
    public.user_location (user_key, status, created_timestamp)
values(2, 'Building A', current_timestamp);

标签： sqlpostgresql

Answer 1

您可以使用DISTINCT ON. 在两个派生表中使用它（一个用于“第一个”，一个用于“最后一个”记录）并将它们连接到users.

SELECT u.user_unique_access_id,
       f.status,
       f.created_timestamp,
       l.status,
       l.created_timestamp
       FROM users u
            LEFT JOIN (SELECT DISTINCT ON (ul.user_key)
                              ul.user_key,
                              ul.status,
                              ul.created_timestamp
                              FROM user_location ul
                              ORDER BY ul.user_key ASC,
                                       ul.created_timestamp DESC) f
                      ON f.user_key = u.user_key
            LEFT JOIN (SELECT DISTINCT ON (ul.user_key)
                              ul.user_key,
                              ul.status,
                              ul.created_timestamp
                              FROM user_location ul
                              ORDER BY ul.user_key ASC,
                                       ul.created_timestamp ASC) l
                      ON l.user_key = u.user_key;

（我会链接一个 db<>fiddle，但你的 DML 非常没用，因为时间戳都是一样的。）