基于发货日期与PO排序SQL数据：首SKU标记'initial'其余标记'other'

Got it, let's tackle this SQL task for your supply chain data organization. Based on your requirement, we need to group by PO (Purchase Order), mark the earliest-shipped SKU in each group as 'initial', the rest as 'other', and ensure the 'initial' SKU sits at the top of its PO group.

Assumptions #

First, let's assume your data is stored in a table named shipments with these columns:

• po: The purchase order number (your grouping key)
• sku: The stock keeping unit (product code)
• ship_date: The date the SKU was shipped

Basic SQL Implementation #

This query uses window functions to rank SKUs by shipment date within each PO, then labels and sorts them:

SELECT
    po,
    sku,
    ship_date,
    CASE 
        WHEN shipment_rank = 1 THEN 'initial'
        ELSE 'other'
    END AS sku_label
FROM (
    -- Inner query: Assign a rank to each SKU in the same PO, ordered by shipment date
    SELECT
        po,
        sku,
        ship_date,
        ROW_NUMBER() OVER (PARTITION BY po ORDER BY ship_date ASC) AS shipment_rank
    FROM shipments
) ranked_shipments
-- Sort to keep 'initial' first in each PO group
ORDER BY po, shipment_rank;

How This Works #

• Inner Query: The ROW_NUMBER() window function partitions the data by po (so we handle each PO separately) and orders rows by ship_date ASC. This assigns a unique rank starting at 1 to the earliest-shipped SKU in each PO.
• Outer Query: The CASE statement checks the rank—if it's 1, we label the SKU as 'initial'; everything else gets 'other'.
• Final Sort: We order by po first to keep all rows from the same PO together, then by shipment_rank to ensure the 'initial' SKU is at the top of its group.

Handling Tied Earliest Shipment Dates #

If your business allows multiple SKUs in the same PO to share the earliest shipment date and you want to mark all of them as 'initial', replace ROW_NUMBER() with RANK() instead. Here's the adjusted query:

SELECT
    po,
    sku,
    ship_date,
    CASE 
        WHEN shipment_rank = 1 THEN 'initial'
        ELSE 'other'
    END AS sku_label
FROM (
    SELECT
        po,
        sku,
        ship_date,
        RANK() OVER (PARTITION BY po ORDER BY ship_date ASC) AS shipment_rank
    FROM shipments
) ranked_shipments
ORDER BY po, shipment_rank;

Unlike ROW_NUMBER(), RANK() will assign the same rank (1) to all SKUs in a PO that have the exact same earliest ship_date, so all of them will get the 'initial' label.

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