Agent-03 库存预测与补货
版本: v1.0
概述: 基于历史销量预测库存需求，生成智能补货建议
1. Agent职责
| 维度 |
说明 |
| 名称 |
inventory-forecast-agent |
| 执行时间 |
每日 06:00 |
| 推送渠道 |
企业微信 / 邮件 |
| 接收人 |
采购主管 / 仓库主管 / 店长 |
核心能力
| 能力 |
说明 |
| 安全库存 |
正态分布法，Z×σ×√LT |
| 缺货预测 |
未来7/14/30天缺货风险 |
| 智能补货 |
含MOQ约束的最优补货量 |
| 滞销识别 |
零销售/库龄/清理优先级 |
| 品类关联 |
Apriori算法搭配销售推荐 |
| 季节性预测 |
Holt-Winters指数平滑 |
2. 安全库存计算
正态分布法公式
安全库存（SS）= Z × σ × √(LT)
其中：
- Z = 服务水平系数（95%→1.65, 97.5%→1.96, 99%→2.33）
- σ = 需求标准差（日均销量的波动）
- LT = 补货提前期（天）
- 再订货点(ROP) = 日均销量 × 补货提前期 + 安全库存
安全库存SQL
WITH daily_sales AS (
SELECT
product_id,
toDate(sale_date) AS sale_date,
SUM(quantity) AS daily_qty
FROM sales_order_detail
WHERE sale_date >= today() - 90
GROUP BY product_id, toDate(sale_date)
),
sales_stats AS (
SELECT
product_id,
AVG(daily_qty) AS avg_daily_sales,
stddevPop(daily_qty) AS demand_stddev
FROM daily_sales
GROUP BY product_id
),
lead_times AS (
SELECT product_id, AVG(lead_time_days) AS avg_lead_time
FROM supplier_lead_times
GROUP BY product_id
),
safe_stock_calc AS (
SELECT
s.product_id,
p.product_name,
s.avg_daily_sales,
s.demand_stddev,
l.avg_lead_time,
-- 安全库存 97.5%服务水平
ROUND(1.96 * s.demand_stddev * sqrt(l.avg_lead_time)) AS safe_stock_975,
-- 再订货点
ROUND(s.avg_daily_sales * l.avg_lead_time + 1.96 * s.demand_stddev * sqrt(l.avg_lead_time)) AS reorder_point
FROM sales_stats s
LEFT JOIN lead_times l ON s.product_id = l.product_id
JOIN product_master p ON s.product_id = p.product_id
)
SELECT *, GREATEST(0, reorder_point + safe_stock_975 - current_stock) AS suggested_order_qty
FROM safe_stock_calc
ORDER BY suggested_order_qty DESC;
3. 缺货预测SQL
WITH future_demand AS (
SELECT
product_id,
SUM(CASE WHEN sale_date >= today() AND sale_date < today() + 7 THEN quantity ELSE 0 END) AS demand_7d,
AVG(CASE WHEN sale_date >= today() - 7 THEN daily_qty ELSE NULL END) AS avg_daily_7d
FROM (
SELECT product_id, toDate(sale_date) AS sale_date, SUM(quantity) AS quantity, SUM(quantity)/7 AS daily_qty
FROM sales_order_detail
WHERE sale_date >= today() - 30
GROUP BY product_id, toDate(sale_date)
)
GROUP BY product_id
)
SELECT
p.product_code,
p.product_name,
i.current_stock,
f.demand_7d,
f.avg_daily_7d,
ROUND(i.current_stock / NULLIF(f.avg_daily_7d, 0)) AS stock_days_remaining,
CASE
WHEN i.current_stock <= f.demand_7d THEN '🔴 7天内缺货'
WHEN i.current_stock <= f.demand_7d * 2 THEN '🟠 14天内缺货'
ELSE '🟢 库存充足'
END AS stockout_risk
FROM product_master p
LEFT JOIN inventory_current i ON p.product_id = i.product_id
LEFT JOIN future_demand f ON p.product_id = f.product_id
WHERE i.current_stock <= f.demand_7d * 2 OR i.current_stock = 0
ORDER BY i.current_stock ASC;
4. 智能补货计算
核心算法
def calculate_reorder_quantity(
current_stock, avg_daily_sales, demand_stddev,
lead_time, safety_factor=1.96, moq=10, eoq=100, max_stock=None
):
safety_stock = safety_factor * demand_stddev * (lead_time ** 0.5)
reorder_point = avg_daily_sales * lead_time + safety_stock
available_stock = current_stock # 简化模型
if available_stock <= reorder_point:
target_stock = avg_daily_sales * lead_time * 2 + safety_stock
if max_stock:
target_stock = min(target_stock, max_stock)
theoretical_qty = target_stock - available_stock
order_qty = max(moq, math.ceil(theoretical_qty / eoq) * eoq)
else:
order_qty = 0
return {
'need_reorder': available_stock <= reorder_point,
'safety_stock': round(safety_stock, 2),
'reorder_point': round(reorder_point, 2),
'suggested_qty': math.ceil(order_qty),
'urgency': 'high' if available_stock < safety_stock else 'medium'
}
5. 滞销识别SQL
SELECT
p.product_code,
p.product_name,
i.current_stock,
i.current_stock_value,
today() - toDate(i.last_out_date) AS days_since_last_sale,
today() - toDate(i.last_in_date) AS days_in_stock,
CASE
WHEN days_since_last_sale > 180 THEN '🆕 死库存'
WHEN days_in_stock > 365 THEN '⚠️ 超龄库存'
WHEN days_since_last_sale > 90 THEN '🔴 长期滞销'
WHEN days_in_stock > 90 THEN '🟡 积压'
ELSE '🟢 正常'
END AS slow_moving_type,
ROUND(days_in_stock * 0.3 + days_since_last_sale * 0.4, 0) AS clearance_priority_score
FROM inventory_current i
JOIN product_master p ON i.product_id = p.product_id
WHERE i.current_stock > 0
AND (days_since_last_sale > 90 OR days_in_stock > 180)
ORDER BY clearance_priority_score DESC
LIMIT 50;
6. 品类关联分析（Apriori）
-- 经常一起购买的商品组合
WITH order_items AS (
SELECT
order_no,
groupArray(product_id) AS product_ids
FROM (
SELECT DISTINCT order_no, product_id
FROM sales_order_detail
WHERE order_date >= today() - 90
)
GROUP BY order_no
)
SELECT
p1.product_name AS item1,
p2.product_name AS item2,
COUNT(*) AS co_occurrence_count,
ROUND(COUNT(*) / (SELECT count() FROM order_items) * 100, 2) AS support_pct,
ROUND(COUNT(*) / (SELECT count() FROM order_items WHERE has(product_ids, p1.product_id)) * 100, 2) AS confidence_pct,
CASE WHEN confidence_pct >= 50 THEN '⭐⭐⭐ 强关联' WHEN confidence_pct >= 30 THEN '⭐⭐ 中关联' ELSE '⭐ 弱关联' END AS strength
FROM order_items oi
ARRAY JOIN product_ids AS p1_id, product_ids AS p2_id
JOIN product_master p1 ON p1_id = p1.product_id
JOIN product_master p2 ON p2_id = p2.product_id
WHERE p1_id < p2_id
GROUP BY p1.product_name, p2.product_name
HAVING co_occurrence_count >= 10
ORDER BY confidence_pct DESC
LIMIT 20;
7. 季节性预测（Holt-Winters）
-- 月度季节性指数
WITH monthly_sales AS (
SELECT
toStartOfMonth(sale_date) AS month,
SUM(total_amount) AS monthly_sales
FROM sales_order
WHERE sale_date >= addMonths(today(), -24)
AND order_status NOT IN ('cancelled')
GROUP BY toStartOfMonth(sale_date)
),
seasonal_index AS (
SELECT
toMonth(month) AS month_num,
AVG(monthly_sales) AS avg_sales,
ROUND(AVG(monthly_sales) / (SELECT AVG(monthly_sales) FROM monthly_sales), 3) AS seasonal_index
FROM monthly_sales
GROUP BY toMonth(month)
)
SELECT
month_num,
CASE month_num
WHEN 1 THEN '1月' WHEN 2 THEN '2月' WHEN 3 THEN '3月'
WHEN 4 THEN '4月' WHEN 5 THEN '5月' WHEN 6 THEN '6月'
WHEN 7 THEN '7月' WHEN 8 THEN '8月' WHEN 9 THEN '9月'
WHEN 10 THEN '10月' WHEN 11 THEN '11月' WHEN 12 THEN '12月'
END AS month_label,
seasonal_index,
CASE WHEN seasonal_index > 1.2 THEN '🔴 旺季' WHEN seasonal_index < 0.8 THEN '🔵 淡季' ELSE '🟡 平季' END AS season_type
FROM seasonal_index
ORDER BY month_num;
