USE [JSDataMiddlePlatform];
GO

/*
  Template: 链接目标覆盖分析（运营视角）
  Window: 在售链接（linkStatus = 当前在线）
*/

IF OBJECT_ID('tempdb..#base') IS NOT NULL
    DROP TABLE #base;

SELECT
    t.Id,
    ISNULL(NULLIF(t.shopname, ''), 'unknown') AS shop_name,
    ISNULL(NULLIF(t.category, ''), 'unknown') AS category,
    ISNULL(NULLIF(t.shopGoodsCategory, ''), 'unknown') AS shop_goods_category,
    ISNULL(NULLIF(t.spu, ''), 'unknown') AS spu,
    ISNULL(NULLIF(t.linkType, ''), 'unknown') AS link_type,
    ISNULL(NULLIF(t.linkStatus, ''), 'unknown') AS link_status,
    TRY_CONVERT(int, t.onSaleDays) AS on_sale_days,
    TRY_CONVERT(decimal(18, 2), t.salesMoneyTarget) AS sales_money_target,
    TRY_CONVERT(int, t.salesAmountTarget) AS sales_amount_target,
    TRY_CONVERT(decimal(18, 2), t.salesMoneyTargetAvg) AS sales_money_target_avg,
    TRY_CONVERT(int, t.salesAmountTargetAvg) AS sales_amount_target_avg,
    ISNULL(NULLIF(t.oper, ''), 'unknown') AS oper,
    ISNULL(NULLIF(t.cater, ''), 'unknown') AS cater,
    ISNULL(NULLIF(t.ider, ''), 'unknown') AS designer,
    ISNULL(NULLIF(t.dept, ''), 'unknown') AS dept,
    ISNULL(NULLIF(t.topsgn, ''), 'unknown') AS topsgn,
    ISNULL(NULLIF(t.bind, ''), 'unknown') AS price_band_label,
    TRY_CONVERT(decimal(18, 2), t.priceBand) AS sale_floor_price
INTO #base
FROM dbo.op_TargetManagement AS t WITH (NOLOCK)
WHERE ISNULL(NULLIF(t.linkStatus, ''), 'unknown') = '当前在线';

-- 1) 在线链接总览
SELECT
    COUNT(*) AS online_link_count,
    COUNT(DISTINCT shop_name) AS shop_count,
    COUNT(DISTINCT spu) AS spu_count,
    SUM(ISNULL(sales_money_target, 0)) AS sales_money_target,
    SUM(ISNULL(sales_amount_target, 0)) AS sales_amount_target
FROM #base;

-- 2) 店铺覆盖度
SELECT TOP (50)
    shop_name,
    COUNT(*) AS online_link_count,
    COUNT(DISTINCT spu) AS spu_count,
    SUM(ISNULL(sales_money_target, 0)) AS sales_money_target,
    AVG(ISNULL(on_sale_days, 0)) AS avg_on_sale_days
FROM #base
GROUP BY shop_name
ORDER BY online_link_count DESC;

-- 3) 运营人负载
SELECT TOP (50)
    dept,
    oper,
    COUNT(*) AS online_link_count,
    COUNT(DISTINCT shop_name) AS shop_count,
    SUM(ISNULL(sales_money_target, 0)) AS sales_money_target
FROM #base
GROUP BY dept, oper
ORDER BY online_link_count DESC;

-- 4) 类目与价格带分布
SELECT
    category,
    shop_goods_category,
    price_band_label,
    COUNT(*) AS online_link_count,
    AVG(ISNULL(sale_floor_price, 0)) AS avg_sale_floor_price,
    SUM(ISNULL(sales_money_target, 0)) AS sales_money_target
FROM #base
GROUP BY category, shop_goods_category, price_band_label
ORDER BY online_link_count DESC;