需求背景：验证同步人工刊登服务是否有数据遗漏。

需求内容：

按此统计逻辑统计线上数据源的同步覆盖率

WITH
-- 1. 人工刊登：按 ASIN、国家去重，取最早刊登月份
manual_base AS (
    SELECT
        DATE_FORMAT(MIN(publish_time), '%Y-%m') AS month_str,
        asin,
        country
    FROM zhcxkj_dw_bi.t_amazon_listing AS t
    INNER JOIN zhcxkj_dw_bi.rb_admin c ON t.business_user_id = c.ID
    INNER JOIN zhcxkj_dw_bi.rb_department d ON c.DepartmentID = d.id
    WHERE t.low_price_type = 0               -- 非低价商城
      AND t.delivery_type = 1                -- 配送方式=FBA
      AND t.is_deleted = 0                   -- 非已删除
      AND t.msku NOT LIKE 'amzn.gr%'         -- 非二手
      AND t.del_status = 0                   -- 非详情页已删除（此处重复，保留一个即可）
      AND t.publish_time > '2026-01-01 00:00:00'  -- 刊登时间大于今年
      AND (d.DeptFullPath LIKE '{0}_{73}%' OR d.DeptFullPath LIKE '{0}_{519}%')
    GROUP BY asin, country
),

-- 2. 同步到的 listing（去重）
synced_base AS (
    SELECT DISTINCT
        asin,
        OrderSourceCountry AS country
    FROM zhcxkj_dw_bi.rb_amazon_autofba_check
    WHERE isdelete = 0
),

-- 3. 左连接，标记是否同步
joined AS (
    SELECT
        m.month_str,
        m.asin,
        m.country,
        CASE WHEN s.asin IS NOT NULL THEN 1 ELSE 0 END AS is_synced
    FROM manual_base m
    LEFT JOIN synced_base s
        ON m.asin = s.asin
        AND m.country = s.country
)

-- 4. 按月聚合
SELECT
    month_str AS 刊登月份,
    COUNT(*) AS FBA刊登listing总量,
    SUM(is_synced) AS 自动FBA审核匹配listing总量,
    ROUND(SUM(is_synced) * 100.0 / NULLIF(COUNT(*), 0), 2) AS 覆盖率
FROM joined
GROUP BY month_str
ORDER BY month_str;