AWS Athena(Trino SQL)基于条件行生成相对递增/递减索引列

需要为表my_table添加relative_idx列，规则如下：

• 满足my_date = DATE('2024-02-11') AND color = 'yellow'的行设为0
• 其余行按结果集的行顺序，参考行之前的行依次为-1、-2…，之后的行依次为1、2…

解决方案 #

核心思路是先用ROW_NUMBER()为所有行按结果集顺序分配序号，提取参考行的序号后，用当前行序号减去参考行序号，即可得到相对索引值。

完整SQL代码：

WITH my_table AS (
    SELECT *
    FROM (VALUES
        (DATE '2023-02-01', 'red'),
        (DATE '2023-03-22', 'red'),
        (DATE '2023-03-30', 'red'),
        (DATE '2023-06-10', 'red'),
        (DATE '2023-06-11', 'red'),
        (DATE '2023-07-03', 'green'),
        (DATE '2023-07-09', 'green'),
        (DATE '2024-01-11', 'green'),
        (DATE '2024-02-11', 'yellow'), -- 参考行
        (DATE '2024-02-12', 'yellow'),
        (DATE '2024-02-13', 'yellow'),
        (DATE '2024-02-14', 'yellow'),
        (DATE '2022-10-20', 'blue'),
        (DATE '2022-10-21', 'blue'),
        (DATE '2022-10-22', 'blue')
    ) AS t(my_date, color)
),
ranked_rows AS (
    SELECT 
        my_date,
        color,
        ROW_NUMBER() OVER () AS row_num, -- 按结果集顺序生成行号
        CASE WHEN my_date = DATE('2024-02-11') AND color = 'yellow' THEN ROW_NUMBER() OVER () END AS ref_row_num
    FROM my_table
),
ref_num AS (
    SELECT MAX(ref_row_num) AS target_row_num -- 提取参考行的行号
    FROM ranked_rows
)
SELECT 
    rr.my_date,
    rr.color,
    rr.row_num - rn.target_row_num AS relative_idx
FROM ranked_rows rr
CROSS JOIN ref_num rn
ORDER BY rr.row_num; -- 保持原结果集顺序

执行结果 #

运行上述代码后，输出将完全匹配期望格式：

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