PostgreSQL有序行分组方法及指定输出实现技术咨询

Hey there! Let's work through your PostgreSQL questions step by step:

分组有序行的需求通常分为两种常见场景，我分别给你举例说明：

场景1：按连续的相同值分组（比如连续相同状态、连续相同类别） #

假设你有一张按时间排序的事件表，想把连续处于相同状态的行归为一组，可以用窗口函数LAG()来识别分组边界，再通过累加标记生成分组ID：

首先创建示例表和数据：

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_time TIMESTAMP,
    status VARCHAR(20)
);

INSERT INTO events (event_time, status) VALUES
('2024-01-01 08:00', 'active'),
('2024-01-01 08:10', 'active'),
('2024-01-01 08:20', 'inactive'),
('2024-01-01 08:30', 'inactive'),
('2024-01-01 08:40', 'active');

然后执行分组查询：

WITH ranked_events AS (
    SELECT
        *,
        -- 获取前一行的状态
        LAG(status) OVER (ORDER BY event_time) AS prev_status,
        -- 当前行和前一行状态不同时，标记为分组变化点
        CASE WHEN LAG(status) OVER (ORDER BY event_time) != status THEN 1 ELSE 0 END AS group_change
    FROM events
),
grouped_events AS (
    SELECT
        *,
        -- 累加变化点，得到唯一的分组ID
        SUM(group_change) OVER (ORDER BY event_time) AS group_id
    FROM ranked_events
)
SELECT
    group_id,
    MIN(event_time) AS group_start_time,
    MAX(event_time) AS group_end_time,
    status
FROM grouped_events
GROUP BY group_id, status
ORDER BY group_id;

这个查询会输出每个连续状态组的时间范围和状态，结果如下：

场景2：按固定行数分组（比如每N行一组） #

如果是想按固定行数拆分分组，比如每3行一组，可以用ROW_NUMBER()生成行号，再通过行号计算分组ID：

WITH numbered_rows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY event_time) AS row_num
    FROM events
)
SELECT
    -- 用(row_num - 1)除以每组行数，得到分组ID
    (row_num - 1) / 3 AS group_id,
    -- 把每组的event_id聚合为数组，也可以根据需求用其他聚合函数
    ARRAY_AGG(event_id) AS event_ids,
    ARRAY_AGG(status) AS status_list
FROM numbered_rows
GROUP BY group_id
ORDER BY group_id;

你提到有一张数据表想要转换成指定格式，但还没提供具体的表结构（比如CREATE TABLE语句）、示例数据以及你期望的最终输出样式。麻烦补充这些细节哦！比如可以贴出表的列信息、几行真实的样本数据，还有你想要的输出结果示例，这样我就能精准地帮你写出对应的SQL语句啦。

内容的提问来源于stack exchange，提问作者akashdeep