PostgreSQL中能否按递增序列分区？如何实现指定分组结果？

1. 能否按递增序列分区？ #

可以。PostgreSQL支持基于递增序列的分区，常用RANGE分区方式，可基于自增主键、序列生成列、时间戳等天然递增的字段实现。例如，对带自增id列的表创建分区：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (id);

-- 创建分区，每个分区包含10000条递增id的记录
CREATE TABLE orders_1 PARTITION OF orders FOR VALUES FROM (1) TO (10001);
CREATE TABLE orders_2 PARTITION OF orders FOR VALUES FROM (10001) TO (20001);

2. 生成指定的partition列 #

针对你提供的示例数据，可通过窗口函数结合条件判断实现。核心逻辑是识别分组起始点，再累计起始点数量得到分区编号。

假设源表名为person_data，字段为last_name、year、increment，数据按last_name和year有序，执行以下SQL：

SELECT
    last_name,
    year,
    increment,
    SUM(CASE
        -- 标记分组起始点：第一行，或increment为1，或当前为负且前一行为正
        WHEN ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY year) = 1 THEN 1
        WHEN increment = 1 THEN 1
        WHEN increment < 0 AND LAG(increment) OVER (PARTITION BY last_name ORDER BY year) > 0 THEN 1
        ELSE 0
    END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM person_data
ORDER BY last_name, year;

逻辑说明： #

• LAG(increment) OVER (...)：获取当前行的上一行increment值，用于判断序列是否发生突变；
• CASE语句标记每个分组的起始点，满足条件时返回1，否则返回0；
• SUM(...) OVER (...)：按last_name分组、year排序，累计起始点的数量，最终得到匹配需求的partition编号。

执行后输出结果如下：

内容的提问来源于stack exchange，提问作者kevin.c