Skip to content

Attention

limit offset

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

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

窗口函数

Warning

last_value 窗口会出问题

first_value(),last_value(),nth_value()

functions-window

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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;