如何获取每个用户的首次及末次发货日期对应的到货日期？

需求明确 #

针对包含NationalID、shipmentdate、arrivaldate等字段的货运表，需提取每个用户的：

• 首次发货日期及对应行的到货日期
• 末次发货日期及对应行的到货日期
  核心是避免单独取到货日期的最值，必须匹配首次/末次发货行的原始到货日期。

方法一：窗口函数（推荐，高效简洁） #

利用ROW_NUMBER()窗口函数对每个用户的发货记录按日期排序，标记首次和末次发货行，再聚合提取目标字段：

WITH ranked_shipments AS (
    SELECT 
        NationalID,
        shipmentdate,
        arrivaldate,
        -- 按发货日期升序排名，1为首次发货
        ROW_NUMBER() OVER (PARTITION BY NationalID ORDER BY shipmentdate ASC) AS rn_first,
        -- 按发货日期降序排名，1为末次发货
        ROW_NUMBER() OVER (PARTITION BY NationalID ORDER BY shipmentdate DESC) AS rn_last
    FROM your_freight_table -- 替换为你的表名
)
SELECT 
    NationalID,
    -- 提取首次发货的日期和对应到货日期
    MAX(CASE WHEN rn_first = 1 THEN shipmentdate END) AS first_shipment_date,
    MAX(CASE WHEN rn_first = 1 THEN arrivaldate END) AS first_arrival_date,
    -- 提取末次发货的日期和对应到货日期
    MAX(CASE WHEN rn_last = 1 THEN shipmentdate END) AS last_shipment_date,
    MAX(CASE WHEN rn_last = 1 THEN arrivaldate END) AS last_arrival_date
FROM ranked_shipments
GROUP BY NationalID;

注：若同一用户同一天有多条发货记录，ROW_NUMBER()会随机选取一条；若需保留所有同日记录，可替换为RANK()，并使用STRING_AGG()等函数合并结果（根据数据库语法调整）。

方法二：关联子查询（兼容性强） #

通过子查询找到每个用户的首次/末次发货日期，再关联原表匹配对应行的到货日期：

SELECT 
    t.NationalID,
    first_ship.shipmentdate AS first_shipment_date,
    first_ship.arrivaldate AS first_arrival_date,
    last_ship.shipmentdate AS last_shipment_date,
    last_ship.arrivaldate AS last_arrival_date
FROM (
    -- 先获取所有唯一用户ID
    SELECT DISTINCT NationalID
    FROM your_freight_table -- 替换为你的表名
) t
-- 关联首次发货行
LEFT JOIN your_freight_table first_ship 
    ON t.NationalID = first_ship.NationalID
    AND first_ship.shipmentdate = (
        SELECT MIN(shipmentdate) 
        FROM your_freight_table 
        WHERE NationalID = t.NationalID
    )
-- 关联末次发货行
LEFT JOIN your_freight_table last_ship 
    ON t.NationalID = last_ship.NationalID
    AND last_ship.shipmentdate = (
        SELECT MAX(shipmentdate) 
        FROM your_freight_table 
        WHERE NationalID = t.NationalID
    );

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