PostgreSQL条件SQL查询：为缺货商品匹配最优替代零售商

解决方案 #

要解决多零售商可选时无法筛选库存最高者的问题，核心是用窗口函数对同公司同商品的有货零售商按库存排序，精准定位最优替代方。以下是完整的查询语句：

WITH optimal_alternatives AS (
    SELECT 
        company_id,
        product_id,
        retailer_id AS alternative_retailer,
        quantity_available,
        -- 按库存降序排名，同公司同商品下库存最高的排第1；库存相同时追加retailer_id排序保证结果唯一
        ROW_NUMBER() OVER (
            PARTITION BY company_id, product_id 
            ORDER BY quantity_available DESC, retailer_id ASC
        ) AS rn
    FROM t2
    WHERE availability = 1 -- 仅筛选有货的零售商
)
SELECT 
    t1.company_id,
    t1.retailer_id AS out_of_stock_retailer,
    t1.product_id,
    -- 无替代时返回'na'
    COALESCE(oa.alternative_retailer, 'na') AS best_alternative_retailer,
    COALESCE(oa.quantity_available::TEXT, 'na') AS alternative_quantity
FROM t1
LEFT JOIN optimal_alternatives oa 
    ON t1.company_id = oa.company_id
    AND t1.product_id = oa.product_id
    AND oa.rn = 1 -- 仅取库存最高的替代零售商
    AND t1.retailer_id != oa.alternative_retailer -- 排除缺货的零售商自身
WHERE t1.availability = 0; -- 仅处理t1中的缺货商品

代码解释 #

1. CTE optimal_alternatives：

   • 从t2中过滤出所有有货状态的零售商数据。
   • 通过PARTITION BY company_id, product_id对同公司同商品的条目分组，ROW_NUMBER()按库存降序生成排名，确保每组内库存最高的零售商排名为1。如果存在库存并列的情况，追加retailer_id ASC排序可以固定唯一结果。

2. 主查询：

   • 将t1的缺货数据与筛选后的最优替代数据左连接，关联条件限定为同公司、同商品，且仅取排名第1的替代方，同时排除缺货零售商自身。
   • 用COALESCE函数处理无替代零售商的场景，返回'na'作为兜底结果。

特殊场景处理 #

• 若业务允许返回所有库存并列最高的零售商，可将ROW_NUMBER()替换为RANK()，并通过ARRAY_AGG(oa.alternative_retailer)将多个零售商合并为数组返回。
• 若t1本身已限定为缺货数据，主查询的WHERE t1.availability = 0可省略，保留则更严谨。

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