Redshift中按no分组获取指定首行及末行数据的查询方法

问题场景 #

现有Redshift数据表如下：

no   date_status           date_ant          status   row_ant
1    11 Jan 2023, 07.00  11 Jan 2023, 07.00   ANT      1
1    11 Jan 2023, 09.00  11 Jan 2023, 10.00   AU       2
1    12 Jan 2023, 12.00  12 Jan 2023, 12.00   DLV      3
2    14 Jan 2023, 09.00  14 Jan 2023, 09.00   ANT      1
2    14 Jan 2023, 10.00  14 Jan 2023, 10.00   AU       2
2    15 Jan 2023, 10.00  15 Jan 2023, 14.00   ANT      3    

需要按no分组获取两类数据：

• 首行：提取row_ant=1且status=ANT对应的date_ant和first_status
• 末行：提取每组中date_status最大的记录对应的date_last_status和last_status（不限制status值）

期望得到的结果：

no         date_ant        first_status   date_last_status    last_status
1    11 Jan 2023, 07.00        ANT        12 Jan 2023, 12.00     DLV
2    14 Jan 2023, 09.00        ANT        15 Jan 2023, 10.00     ANT

实现方案 #

可以通过窗口函数（ROW_NUMBER()）结合CTE（公共表表达式）实现，以下提供两种可行写法：

写法一：单CTE标记后聚合 #

WITH ranked_data AS (
    SELECT 
        no,
        date_ant,
        status AS first_status,
        date_status AS date_last_status,
        status AS last_status,
        -- 标记符合首行条件的记录
        CASE WHEN row_ant = 1 AND status = 'ANT' THEN 1 ELSE 0 END AS is_first,
        -- 按no分组，date_status降序排名，排名1为末行
        ROW_NUMBER() OVER (PARTITION BY no ORDER BY date_status DESC) AS last_rank
    FROM your_table_name -- 替换为你的实际表名
)
SELECT 
    no,
    MAX(CASE WHEN is_first = 1 THEN date_ant END) AS date_ant,
    MAX(CASE WHEN is_first = 1 THEN first_status END) AS first_status,
    MAX(CASE WHEN last_rank = 1 THEN date_last_status END) AS date_last_status,
    MAX(CASE WHEN last_rank = 1 THEN last_status END) AS last_status
FROM ranked_data
GROUP BY no;

逻辑说明：

1. 用CTEranked_data给每条记录打标记：is_first标记满足首行条件的记录，last_rank按no分组后对date_status降序排名；
2. 按no分组后，通过MAX()聚合函数提取对应标记的字段值，每个no仅存在一条符合首行/末行条件的记录，因此MAX()能精准取出目标值。

写法二：分CTE筛选后关联 #

-- 筛选首行数据
WITH first_rows AS (
    SELECT 
        no, 
        date_ant, 
        status AS first_status
    FROM your_table_name -- 替换为你的实际表名
    WHERE row_ant = 1 AND status = 'ANT'
),
-- 筛选末行数据
last_rows AS (
    SELECT 
        no, 
        date_status AS date_last_status, 
        status AS last_status
    FROM (
        SELECT 
            no,
            date_status,
            status,
            ROW_NUMBER() OVER (PARTITION BY no ORDER BY date_status DESC) AS rn
        FROM your_table_name -- 替换为你的实际表名
    ) t
    WHERE rn = 1
)
-- 关联首末行结果
SELECT 
    f.no,
    f.date_ant,
    f.first_status,
    l.date_last_status,
    l.last_status
FROM first_rows f
INNER JOIN last_rows l ON f.no = l.no;

逻辑说明：

1. 用两个CTE分别筛选首行和末行数据：first_rows直接通过条件过滤出目标首行，last_rows用窗口函数筛选每组date_status最大的记录；
2. 通过no字段关联两个结果集，得到最终合并数据，这种写法逻辑更直观，便于调试。

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