库存耗尽预测：基于销售数据计算库存耗尽日期的查询需求

To solve this problem, we need to calculate when each store's inventory will be depleted by sequentially subtracting daily sales (repeating the weekly pattern if necessary) until the inventory reaches zero or below. Here's a step-by-step SQL solution:

Approach #

1. Map Days to Order: First, we assign a numerical order to each day of the week to ensure we calculate cumulative sales in the correct sequence (Monday to Sunday).
2. Calculate Sales Metrics: For each store, compute the daily cumulative sales and total weekly sales.
3. Inventory Breakdown: Determine how many full weeks of sales fit into the initial inventory, and the remaining inventory after those full weeks.
4. Determine Exhaustion Date:
   • If weekly sales are zero, inventory never runs out.
   • If remaining inventory is zero, inventory is exhausted after the last day of the final full week (Sunday).
   • Otherwise, find the first day in the next week where cumulative sales exceed the remaining inventory.

SQL Query #

WITH day_order AS (
    SELECT 'Monday' AS day, 1 AS order_num UNION ALL
    SELECT 'Tuesday', 2 UNION ALL
    SELECT 'Wednesday', 3 UNION ALL
    SELECT 'Thursday', 4 UNION ALL
    SELECT 'Friday', 5 UNION ALL
    SELECT 'Saturday', 6 UNION ALL
    SELECT 'Sunday', 7
),
store_sales_summary AS (
    SELECT 
        b.Store,
        b.Day,
        b.ItemsSold,
        do.order_num,
        SUM(b.ItemsSold) OVER (PARTITION BY b.Store ORDER BY do.order_num) AS cumulative_daily,
        SUM(b.ItemsSold) OVER (PARTITION BY b.Store) AS weekly_total
    FROM TableB b
    JOIN day_order do ON b.Day = do.day
),
store_inventory_details AS (
    SELECT 
        a.Store,
        a.NumItems AS initial_inventory,
        sss.weekly_total,
        CASE WHEN sss.weekly_total = 0 THEN NULL ELSE FLOOR(a.NumItems / sss.weekly_total) END AS full_weeks_needed,
        CASE WHEN sss.weekly_total = 0 THEN a.NumItems ELSE a.NumItems % sss.weekly_total END AS remaining_inventory
    FROM TableA a
    JOIN store_sales_summary sss ON a.Store = sss.Store
    GROUP BY a.Store, a.NumItems, sss.weekly_total
)
SELECT 
    sid.Store,
    sid.initial_inventory,
    sid.weekly_total,
    CASE 
        WHEN sid.weekly_total = 0 THEN 'Inventory will never be exhausted (no sales)'
        WHEN sid.remaining_inventory = 0 THEN 
            CONCAT('Exhausted after ', sid.full_weeks_needed, ' full weeks on Sunday')
        ELSE 
            CONCAT(
                'Exhausted on ', 
                (SELECT sss.Day FROM store_sales_summary sss WHERE sss.Store = sid.Store AND sss.cumulative_daily > sid.remaining_inventory ORDER BY sss.order_num LIMIT 1),
                ' of week ', sid.full_weeks_needed + 1
            )
    END AS exhaustion_date
FROM store_inventory_details sid
GROUP BY sid.Store, sid.initial_inventory, sid.weekly_total, sid.full_weeks_needed, sid.remaining_inventory;

Explanation #

• day_order: This CTE ensures we sort days correctly, which is critical for accurate cumulative sales calculations.
• store_sales_summary: Computes the running total of sales per day (to track when cumulative sales exceed inventory) and the total weekly sales for each store.
• store_inventory_details: Joins inventory data with sales metrics to calculate how many full weeks of sales the inventory can cover, plus any remaining inventory.
• Final SELECT: Uses conditional logic to determine the exact exhaustion date, handling edge cases like no sales or inventory exactly matching full weeks of sales.

Sample Results #

For your provided data, the query will return:

This matches our manual calculations:

• Store 1 runs out on Saturday when cumulative sales reach 561, exceeding the 500 inventory.
• Store 2 runs out on Wednesday when cumulative sales hit 1051, which is over 1000.
• Store 3 runs out on Tuesday when cumulative sales jump to 1150, surpassing 575.

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