如何查询sp_restock_inventory_v2_view中各ASIN的最新库存数据？

我的表sp_restock_inventory_v2_view每天会被拉取多次数据，导致每个ASIN每天对应多条记录——这些记录有时重复，有时因库存变化而不同。我需要编写SQL语句，仅查询每个ASIN的最新库存数据。

我尝试过以下脚本，但未达到预期效果：

SELECT DISTINCT *
FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view AS t1
JOIN (
    SELECT product_name, MAX(ob_date) AS max_ob_date
    FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view
    GROUP BY product_name
) AS t2 ON t1.product_name = t2.product_name AND t1.ob_date = t2.max_ob_date;

数据结构示例：

你当前脚本的核心问题有两个：

1. 分组依据错误：你按product_name分组，但需求是按asin获取最新数据——一个product_name可能对应多个ASIN，反之亦然，这会导致结果不符合预期。
2. 未处理同一天内的多条记录：如果ob_date仅包含日期（无时分秒），MAX(ob_date)只能筛选出当天的所有记录，无法定位到当天最新的那一条；即使ob_date是带时间的datetime类型，原脚本也没处理同一ASIN在同一时间点的重复记录。

使用窗口函数ROW_NUMBER()是解决这类「每组取最新记录」问题的最优方案，它可以为每个ASIN的记录按时间排序，精准标记出最新的那一条。

方案1：仅保留每个ASIN的最新记录（按时间降序） #

假设ob_date是包含时分秒的datetime类型（如果只有日期，建议添加更精确的拉取时间字段，比如pull_time）：

WITH ranked_inventory AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY asin ORDER BY ob_date DESC) AS rn
    FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view
)
SELECT asin, available, product_name, ob_date, merchant_sku
FROM ranked_inventory
WHERE rn = 1;

方案2：去重后取每个ASIN的最新记录 #

如果同一ASIN在同一ob_date下有重复记录（比如库存值完全相同），可以先去重再排序：

WITH unique_inventory AS (
    SELECT DISTINCT asin, available, product_name, ob_date, merchant_sku
    FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view
),
ranked_inventory AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY asin ORDER BY ob_date DESC) AS rn
    FROM unique_inventory
)
SELECT asin, available, product_name, ob_date, merchant_sku
FROM ranked_inventory
WHERE rn = 1;

方案3：无精确时间时，按库存值判断最新 #

如果ob_date仅存储日期，没有更细的时间维度，可以假设库存值越小（库存消耗后减少）越新，结合日期排序：

WITH ranked_inventory AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY asin ORDER BY ob_date DESC, available ASC) AS rn
    FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view
)
SELECT asin, available, product_name, ob_date, merchant_sku
FROM ranked_inventory
WHERE rn = 1;

如果你的SQL引擎不支持CTE（WITH子句），可以将窗口函数逻辑放到子查询中：

SELECT asin, available, product_name, ob_date, merchant_sku
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY asin ORDER BY ob_date DESC) AS rn
    FROM sp-api-connector.CollectorMount.sp_restock_inventory_v2_view
) t
WHERE t.rn = 1;

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