如何透视/转换表格？将含run_ts、user、rnk的表转为指定结构

原始数据表格：

目标是按user分组，提取每组rnk=1的run_ts作为first_run_ts，rnk=2的run_ts作为last_run_ts，以下是几种通用实现方式：

方法1：条件聚合（最通用，支持所有SQL方言） #

通过CASE语句筛选指定rnk的记录，结合聚合函数按user分组提取对应值：

SELECT
    MAX(CASE WHEN rnk = 1 THEN run_ts END) AS first_run_ts,
    MAX(CASE WHEN rnk = 2 THEN run_ts END) AS last_run_ts,
    user
FROM your_table
GROUP BY user;

注：这里用MAX/MIN均可，因为每个user对应每个rnk仅一条记录，聚合函数会直接返回目标值。

方法2：窗口函数（适合扩展更多排序场景） #

如果rnk是按run_ts排序生成的，可使用窗口函数直接提取首尾值：

方式A：用FIRST_VALUE/LAST_VALUE #

WITH ranked_data AS (
    SELECT
        run_ts,
        user,
        rnk,
        FIRST_VALUE(run_ts) OVER (PARTITION BY user ORDER BY rnk) AS first_run_ts,
        LAST_VALUE(run_ts) OVER (PARTITION BY user ORDER BY rnk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_run_ts
    FROM your_table
)
SELECT DISTINCT
    first_run_ts,
    last_run_ts,
    user
FROM ranked_data;

方式B：用LEAD（仅当每个user固定2条记录时适用） #

SELECT
    run_ts AS first_run_ts,
    LEAD(run_ts) OVER (PARTITION BY user ORDER BY rnk) AS last_run_ts,
    user
FROM your_table
WHERE rnk = 1;

方法3：自连接 #

通过同表关联，匹配每个user的两条不同rnk记录：

SELECT
    t1.run_ts AS first_run_ts,
    t2.run_ts AS last_run_ts,
    t1.user
FROM your_table t1
JOIN your_table t2
    ON t1.user = t2.user
    AND t1.rnk = 1
    AND t2.rnk = 2;

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