Attention

limit offset

在 PostgreSQL 中，如果你在使用 LIMIT 和 OFFSET 时观察到重复的数据，那是因为你的查询结果没有明确的排序顺序。

LIMIT 和 OFFSET 依赖于查询结果的顺序
如果未指定顺序，数据库系统可能以不同的顺序返回相同的记录集

窗口函数

first_value(), last_value(), nth_value()

https://www.postgresql.org/docs/15/functions-window.html

drop table if exists demo_window;
create table demo_window
(
    id           bigserial   not null primary key,
    partition_id int         not null,
    order_by_id  int         not null,
    name         varchar(50) not null default 'unknown',
    create_time  timestamp   not null default current_timestamp
);
comment on table demo_window is 'demo_window';
comment on column demo_window.id is 'id';
comment on column demo_window.name is 'name';

insert into demo_window (partition_id, order_by_id, name)
values
    (1, 1, '1_1'),
    (1, 2, '1_2'),
    (1, 2, '1_2_copy'),
    (2, 1, '2_1'),
    (2, 1, '2_2'),
    (2, 3, '2_3');


-- order by的排序不会作用于 相同的 order_by_id
-- order_by 不作用于相同的值，无论是 asc 还是 desc

select first_value(name) over (partition by partition_id order by order_by_id), *
from demo_window;

-- first_value 在 order_by_id 相同的时候 取决于数据库存储的顺序
select first_value(name) over (partition by partition_id order by order_by_id desc ), *
from demo_window;

-- last_value 在 order_by_id 相同的时候，取决于数据库更靠后的值
-- 但是 排序本身不生效！！！
select last_value(name) over (partition by partition_id order by order_by_id), *
from demo_window;

-- last_value 在 order_by_id 相同的时候，取决于数据库更靠后的值
-- 但是 排序本身不生效！！！
select last_value(name) over (partition by partition_id order by order_by_id desc), *
from demo_window;

-- last_value 和 first_value 一样生效的方法
select last_value(name)
       over (partition by partition_id order by order_by_id groups between unbounded preceding and unbounded following),
       *
from demo_window;


-- last_value 和 first_value 一样生效的方法
-- 但是 order by的排序不会作用于 相同的 order_by_id
select last_value(name)
       over (partition by partition_id order by order_by_id desc groups between unbounded preceding and unbounded following),
       *
from demo_window;

-- last_value 和 first_value 一样生效的方法
select last_value(name)
       over (partition by partition_id order by order_by_id desc rows between unbounded preceding and unbounded following),
       *
from demo_window;

27 January 2026