亚马逊50万平方英尺仓库库存最大化SQL查询需求及问题修正

需求与约束 #

• 仓库总面积：500,000平方英尺
• 存储优先级：优先最大化存储prime_eligible物品，剩余空间再最大化存储not_prime物品
• 输出要求：按prime_eligible、not_prime顺序展示可存储的最大物品数量
• 核心约束：
  • 同一类型物品必须等量存储（即该类型所有物品的存储数量一致，相当于每"套"包含该类型所有item_id各一个）
  • not_prime物品存储数量不可为0
  • 物品数量必须为整数

数据表信息 #

使用INVENTORY表，字段如下：

• item_id(integer)：唯一物品ID
• item_type(string)：物品类型，取值为prime_eligible或not_prime
• item_category(string)：物品分类
• square_footage(decimal)：单个物品占用的平方英尺数

现有错误SQL及结果 #

已尝试的SQL未遵循"优先存储prime，剩余空间分配给not_prime"的逻辑，错误平分了仓库空间：

WITH cte AS (
    SELECT
        item_type,
        CASE
            WHEN item_type = 'prime_eligible' THEN 'prime'
            ELSE 'not_prime'
        END AS category,
        SUM(square_footage) AS total_square_footage
    FROM
        INVENTORY
    GROUP BY
        item_type
)
SELECT
    category AS item_type,
    FLOOR(500000 / 2 / MIN(total_square_footage)) AS item_count
FROM
    cte
GROUP BY
    category;

输出结果：not_prime:5102，prime:1547，与预期的prime_eligible:9285、not_prime:6不符

修正后的SQL查询 #

WITH type_square_footage AS (
    -- 计算每种类型物品存一套（所有item_id各一个）的总占用面积
    SELECT
        item_type,
        SUM(square_footage) AS per_set_sqft
    FROM INVENTORY
    GROUP BY item_type
),
prime_max_sets AS (
    -- 计算prime_eligible的最大存储套数及剩余空间
    SELECT
        FLOOR(500000 / per_set_sqft) AS prime_sets,
        500000 % per_set_sqft AS remaining_space
    FROM type_square_footage
    WHERE item_type = 'prime_eligible'
)
-- 输出prime_eligible的结果
SELECT
    'prime_eligible' AS item_type,
    prime_sets AS item_count
FROM prime_max_sets
UNION ALL
-- 输出not_prime的结果，确保数量至少为1
SELECT
    'not_prime' AS item_type,
    GREATEST(FLOOR(remaining_space / per_set_sqft), 1) AS item_count
FROM prime_max_sets
CROSS JOIN type_square_footage
WHERE type_square_footage.item_type = 'not_prime'
-- 按要求顺序排序
ORDER BY item_type;

逻辑说明 #

1. type_square_footage：统计每种类型的物品，各存一个（一套）需要的总平方英尺数，作为计算存储套数的基础。
2. prime_max_sets：优先计算prime_eligible能存储的最大套数（用仓库总面积除以每套面积取整），同时算出存储完prime后剩余的空间。
3. 合并结果：
   • 直接输出prime的最大套数
   • 用剩余空间计算not_prime的最大套数，通过GREATEST函数保证结果至少为1，满足约束条件
4. 最后按prime_eligible在前、not_prime在后的顺序输出

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