USE [JSDataMiddlePlatform];
GO

/*
  Template: 运费效率分析
  Window: 最近30天 [date_from, date_to)
*/

DECLARE @date_from datetime2 = CAST(DATEADD(DAY, -30, CAST(GETDATE() AS date)) AS datetime2);
DECLARE @date_to datetime2 = CAST(DATEADD(DAY, 1, CAST(GETDATE() AS date)) AS datetime2);

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

SELECT
    fc.so_id AS order_id,
    ISNULL(NULLIF(fc.shop_name, ''), 'unknown') AS shop_name,
    CASE
        WHEN CHARINDEX('-', ISNULL(fc.shop_name, '')) > 1
            THEN LEFT(fc.shop_name, CHARINDEX('-', fc.shop_name) - 1)
        ELSE 'unknown'
    END AS platform_name,
    ISNULL(NULLIF(fc.receiver_state, ''), 'unknown') AS receiver_state,
    ISNULL(NULLIF(fc.expressName, ''), 'unknown') AS express_name,
    TRY_CONVERT(decimal(18, 2), fc.weight) AS weight,
    TRY_CONVERT(decimal(18, 2), fc.freightCost) AS freight_cost,
    fc.send_date,
    fc.type AS freight_type
INTO #base
FROM dbo.fys_jstFreightCost AS fc WITH (NOLOCK)
WHERE fc.send_date >= @date_from
  AND fc.send_date < @date_to
  AND ISNULL(NULLIF(fc.so_id, ''), '') <> '';

-- 1) 总览
SELECT
    @date_from AS date_from,
    @date_to AS date_to,
    COUNT(*) AS shipment_count,
    SUM(ISNULL(weight, 0)) AS total_weight,
    SUM(ISNULL(freight_cost, 0)) AS total_freight_cost,
    CASE
        WHEN COUNT(*) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / COUNT(*)
    END AS avg_cost_per_order,
    CASE
        WHEN SUM(ISNULL(weight, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / SUM(ISNULL(weight, 0))
    END AS avg_cost_per_kg
FROM #base;

-- 2) 平台运费效率
SELECT
    platform_name,
    COUNT(*) AS shipment_count,
    SUM(ISNULL(weight, 0)) AS total_weight,
    SUM(ISNULL(freight_cost, 0)) AS total_freight_cost,
    CASE
        WHEN COUNT(*) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / COUNT(*)
    END AS avg_cost_per_order,
    CASE
        WHEN SUM(ISNULL(weight, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / SUM(ISNULL(weight, 0))
    END AS avg_cost_per_kg
FROM #base
GROUP BY platform_name
ORDER BY shipment_count DESC;

-- 3) 店铺运费异常（发货单>=100）
SELECT TOP (50)
    shop_name,
    platform_name,
    COUNT(*) AS shipment_count,
    SUM(ISNULL(weight, 0)) AS total_weight,
    SUM(ISNULL(freight_cost, 0)) AS total_freight_cost,
    CASE
        WHEN COUNT(*) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / COUNT(*)
    END AS avg_cost_per_order,
    CASE
        WHEN SUM(ISNULL(weight, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / SUM(ISNULL(weight, 0))
    END AS avg_cost_per_kg
FROM #base
GROUP BY shop_name, platform_name
HAVING COUNT(*) >= 100
ORDER BY avg_cost_per_kg DESC, shipment_count DESC;

-- 4) 省份与快递公司组合成本
SELECT TOP (50)
    receiver_state,
    express_name,
    COUNT(*) AS shipment_count,
    SUM(ISNULL(weight, 0)) AS total_weight,
    SUM(ISNULL(freight_cost, 0)) AS total_freight_cost,
    CASE
        WHEN SUM(ISNULL(weight, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(freight_cost, 0)) / SUM(ISNULL(weight, 0))
    END AS avg_cost_per_kg
FROM #base
GROUP BY receiver_state, express_name
ORDER BY total_freight_cost DESC;