需求背景
需要统计分析不同刊登方式、不同文案优化类型，执行量以及出单率。
需求内容
刊登
按月分表
维度按【月+添加人员+来源渠道】
统计字段：月、添加人员（AddUserName）、来源渠道（ID+店铺名称）、刊登提交量，成功量，失败量，成功率，7天出单率，30天出单率，30天出单listing数
统计频率：每个月1号，统计上上个月的数据
文案优化
按月分表
维度按【月+策略ID+类型（type）】
统计字段：月、策略ID、类型、成功量，7天出单率，30天出单率，30天出单listing数
统计频率：每个月1号，统计上上个月的数据
查询SQL如下：WITH tmp AS (
SELECT
DATE_FORMAT(a.ResultTime, '%Y-%m') AS bury_month,
a.StrategyId,
a.Type,
a.OrderSourceID,
a.OrderSourceSKU,
CONCAT(a.OrderSourceID, a.OrderSourceSKU) AS OrderSourceSKUID,
a.ResultTime,
SUM(
CASE
WHEN DATEDIFF(DATE(b.PayTime), DATE(a.ResultTime)) <= 7
THEN b.销量
END
) AS `7天销量`,
SUM(
CASE
WHEN DATEDIFF(DATE(b.PayTime), DATE(a.ResultTime)) <= 30
THEN b.销量
END
) AS `30天销量`
FROM zhcxkj_AutoPublish_bi.amazon_common_queue a
LEFT JOIN zhcxkj_bi.mv_market_ai_order_history b
ON a.OrderSourceID = b.OrderSourceID
AND a.OrderSourceSKU = b.SellerSKU
AND DATE(b.PayTime) >= DATE(a.ResultTime)
WHERE a.Type IN (1, 2, 10, 29, 30)
AND a.Status IN (80, 2)
AND a.ResultTime >= '2026-08-01'
AND a.ResultTime < '2026-08-02'
GROUP BY
DATE_FORMAT(a.ResultTime, '%Y-%m'),
a.StrategyId,
a.Type,
a.OrderSourceID,
a.OrderSourceSKU,
CONCAT(a.OrderSourceID, a.OrderSourceSKU),
a.ResultTime
)
SELECT
bury_month AS `月份`,
StrategyId,
Type,
COUNT(DISTINCT OrderSourceSKUID) AS `Listing数`,
COUNT(
DISTINCT CASE
WHEN `7天销量` > 0
THEN OrderSourceSKUID
END
) AS `7天出单Listing数`,
ROUND(
COUNT(
DISTINCT CASE
WHEN `7天销量` > 0
THEN OrderSourceSKUID
END
) * 1.0
/ NULLIF(COUNT(DISTINCT OrderSourceSKUID), 0),
6
) AS `7天出单率`,
COUNT(
DISTINCT CASE
WHEN `30天销量` > 0
THEN OrderSourceSKUID
END
) AS `30天出单Listing数`,
ROUND(
COUNT(
DISTINCT CASE
WHEN `30天销量` > 0
THEN OrderSourceSKUID
END
) * 1.0
/ NULLIF(COUNT(DISTINCT OrderSourceSKUID), 0),
6
) AS `30天出单率`
FROM tmp
GROUP BY
bury_month,
StrategyId,
Type
ORDER BY
bury_month,
StrategyId,
Type;