任务背景：
(1)需求:13590 JDWalmart 支持按动态库存变更触发平衡库存&listing同步优化,已发布,目前需要初始化一下历史有变更的库存数据,后续全部用增量数据同步!
(2)JDW的平衡库存需求上线后，需要写SQL建自动补货＆平衡库存的及时性，用于确认是否有问题
任务内容：
(1)将动态库存表(dynamic_inventory)有变化的数据,即更新时间(update_date)在最近1个月的数据,执行一次初始化判断,验证是否要平衡库存
(2)JDW自动补货/平衡库存考核处理及时率 统计:动态库存变化1h内，满足条件的数据，自动补货\平衡库存需要98%执行完成
需要参考其他平台的SQL逻辑,写好监控数据,做到BI看板中,用来跟踪执行情况
参考智能刊登 --shopee 平台的逻辑进行梳理编写
-- shopee本地仓动态库存最新补货时间超过本地仓动态库存最近同步时间1小时的数据(范围48小时)
SELECT
CASE
WHEN a.补货执行时间差>1 THEN '补货执行异常'
WHEN a.Status='normal' THEN '补货执行异常'
else '补货执行正常'
end as '补货情况',
count(a.sku) '执行数量'
FROM (
SELECT
r.StrategyId,
r.OrderSourceId,
r.ProductId,
r.SellerSku,
r.Sku,
r.`Status`,
r.Inventory AS 修改后可售数,
r.OriginalInventory AS 原可售数,
w.DynamicInventory AS SKU最新动态库存合计,
r.Remark AS 补货队列日志,
r.FailureReason,
date_add( w.LastModificationTime, INTERVAL 8 HOUR ) AS 动态库存最新更新时间,
date_add( r.lastReplenishmentTime , INTERVAL 8 HOUR ) AS 补货队列最新执行时间,
TIMESTAMPDIFF(hour, w.LastModificationTime, r.lastReplenishmentTime) AS 补货执行时间差
FROM
(SELECT CustomerId,Sku,Sum(AvailableNum) as DynamicInventory,LastModificationTime FROM p_product_warehouse_dynamicinventory GROUP BY CustomerId,Sku) w
JOIN (
SELECT
r.CustomerId,
r.StrategyId,
r.OrderSourceId,
r.ProductId,
r.SellerSku,
r.OriginalInventory,
r.Inventory ,
r.Remark,
r.FailureReason,
r.Sku,
r.`Status`,
r.LastModificationTime as lastReplenishmentTime
FROM pp_replenishment as r
JOIN (
SELECT
Max(Id) as Id
FROM
pp_replenishment
WHERE
CustomerId = 1
AND MarketId = 97
AND StrategyId IN (328,320,351)-- 设置补货策略
-- AND StrategyId IN (SELECT ID FROM pp_replenishment_strategy WHERE CustomerId=1 AND MarketId=97 AND IsEnabled=1 AND IsDeleted=0)
AND Time>= DATE_SUB(NOW(),interval 72 HOUR)-- 可以设置成比当前查询时间差3天
AND BusinessType =1 -- 只统计动态库存变更添加到队列的数据
GROUP BY
CustomerId,
StrategyId,
OrderSourceId,
ProductId,
SellerSku,
Sku) p
ON r.Id = p.Id
) r ON w.CustomerId = r.CustomerId
AND w.Sku = r.Sku
WHERE
w.LastModificationTime >= DATE_ADD(NOW(),INTERVAL -58 HOUR) -- 设置48小时的数据
and w.LastModificationTime <= DATE_ADD(NOW(),INTERVAL -10 HOUR)-- 设置比当前时间晚2个小时
-- AND DATE_ADD( w.LastModificationTime, INTERVAL 2 HOUR ) < r.lastReplenishmentTime -- 最新补货时间超过最近动态库存同步时间2小时
AND w.LastModificationTime <= r.lastReplenishmentTime
) as a
GROUP BY
CASE
WHEN a.补货执行时间差>1 THEN '补货执行异常'
WHEN a.Status='normal' THEN '补货执行异常'
end