Databricks SQL查询求助：提取首启动日期与最新状态

针对需求——按order_number分组，输出每组的首个start_date和最新的status，可以用以下几种SQL实现方式：

方法1：窗口函数标记法 #

通过两个窗口函数分别标记出首个日期的行和最新状态的行，再通过聚合筛选结果：

WITH order_details AS (
    SELECT 
        order_number,
        start_date,
        status,
        -- 标记首个start_date的行
        ROW_NUMBER() OVER (PARTITION BY order_number ORDER BY start_date ASC) AS rn_first,
        -- 标记最新start_date对应的行（若同日期有多条，取第一条）
        ROW_NUMBER() OVER (PARTITION BY order_number ORDER BY start_date DESC) AS rn_last
    FROM your_table_name
)
SELECT 
    order_number,
    MAX(CASE WHEN rn_first = 1 THEN start_date END) AS first_start_date,
    MAX(CASE WHEN rn_last = 1 THEN status END) AS latest_status
FROM order_details
GROUP BY order_number;

方法2：子查询分别取值 #

直接通过子查询获取每组的最小start_date，以及按start_date倒序取第一条的status：

SELECT DISTINCT
    order_number,
    (SELECT MIN(start_date) FROM your_table_name WHERE order_number = t.order_number) AS first_start_date,
    (SELECT status FROM your_table_name WHERE order_number = t.order_number ORDER BY start_date DESC LIMIT 1) AS latest_status
FROM your_table_name t;

方法3：FIRST_VALUE/LAST_VALUE函数（适用于PostgreSQL、SQL Server等） #

利用窗口函数直接提取首个日期和最新状态：

SELECT DISTINCT
    order_number,
    FIRST_VALUE(start_date) OVER (
        PARTITION BY order_number 
        ORDER BY start_date ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_start_date,
    LAST_VALUE(status) OVER (
        PARTITION BY order_number 
        ORDER BY start_date DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_status
FROM your_table_name;

关于你之前用Dense Rank遇到的问题 #

如果只单一按start_date排序打Dense Rank，当存在多个相同start_date的记录时，会出现多条同rank的行，导致无法精准拿到唯一的最新状态；另外如果没有分别处理「首个日期」和「最新状态」两个独立的排序维度，就会出现日期和状态不匹配的情况，以上方法都是分开处理这两个维度，确保结果符合需求。

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