125 lines
3.7 KiB
Transact-SQL
125 lines
3.7 KiB
Transact-SQL
USE [JSDataMiddlePlatform];
|
||
GO
|
||
|
||
/*
|
||
Template: SKU结构分析
|
||
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
|
||
NULLIF(a.undefined2, '') AS order_id,
|
||
NULLIF(a.undefined10, '') AS sku_id,
|
||
COALESCE(NULLIF(a.undefined8, ''), 'unknown') AS shop_name,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined20, '')) AS qty,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined26, '')) AS payment_amount,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined29, '')) AS actual_amount,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined31, '')) AS actual_cost,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined52, '')) AS refund_amount,
|
||
ISNULL(NULLIF(a.undefined62, ''), '') AS order_labels
|
||
INTO #base
|
||
FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
|
||
WHERE a.createdAt >= @date_from
|
||
AND a.createdAt < @date_to
|
||
AND ISNULL(NULLIF(a.undefined2, ''), '') <> ''
|
||
AND ISNULL(NULLIF(a.undefined10, ''), '') <> ''
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%配件%'
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%特殊单%';
|
||
|
||
IF OBJECT_ID('tempdb..#sku_agg') IS NOT NULL
|
||
DROP TABLE #sku_agg;
|
||
|
||
SELECT
|
||
b.sku_id,
|
||
ISNULL(NULLIF(s.name, ''), 'unknown') AS sku_name,
|
||
ISNULL(NULLIF(s.other_1, ''), 'unknown') AS spu,
|
||
ISNULL(NULLIF(s.category, ''), 'unknown') AS category,
|
||
s.sale_price,
|
||
COUNT(*) AS afs_record_count,
|
||
COUNT(DISTINCT b.order_id) AS order_count,
|
||
SUM(ISNULL(b.qty, 0)) AS qty_sum,
|
||
SUM(ISNULL(b.payment_amount, 0)) AS payment_amount,
|
||
SUM(ISNULL(b.actual_amount, 0)) AS actual_amount,
|
||
SUM(ISNULL(b.actual_cost, 0)) AS actual_cost,
|
||
SUM(ISNULL(b.refund_amount, 0)) AS refund_amount
|
||
INTO #sku_agg
|
||
FROM #base AS b
|
||
LEFT JOIN dbo.jst_sku AS s WITH (NOLOCK)
|
||
ON s.sku_id = b.sku_id
|
||
GROUP BY
|
||
b.sku_id,
|
||
s.name,
|
||
s.other_1,
|
||
s.category,
|
||
s.sale_price;
|
||
|
||
-- 1) 类目结构（按销售额）
|
||
SELECT TOP (20)
|
||
category,
|
||
COUNT(*) AS sku_count,
|
||
SUM(payment_amount) AS payment_amount,
|
||
CASE
|
||
WHEN SUM(SUM(payment_amount)) OVER () = 0 THEN NULL
|
||
ELSE SUM(payment_amount) / SUM(SUM(payment_amount)) OVER ()
|
||
END AS payment_share
|
||
FROM #sku_agg
|
||
GROUP BY category
|
||
ORDER BY payment_amount DESC;
|
||
|
||
-- 2) SKU TOP50（按销售额）
|
||
SELECT TOP (50)
|
||
sku_id,
|
||
sku_name,
|
||
spu,
|
||
category,
|
||
sale_price,
|
||
order_count,
|
||
qty_sum,
|
||
payment_amount,
|
||
refund_amount,
|
||
CASE
|
||
WHEN payment_amount = 0 THEN NULL
|
||
ELSE refund_amount / payment_amount
|
||
END AS refund_rate,
|
||
actual_amount - actual_cost AS gross_profit,
|
||
CASE
|
||
WHEN actual_amount = 0 THEN NULL
|
||
ELSE (actual_amount - actual_cost) / actual_amount
|
||
END AS gross_margin_rate
|
||
FROM #sku_agg
|
||
ORDER BY payment_amount DESC;
|
||
|
||
-- 3) 价格带结构
|
||
WITH price_band AS (
|
||
SELECT
|
||
CASE
|
||
WHEN sale_price IS NULL THEN '未知价格'
|
||
WHEN sale_price < 100 THEN '0-99'
|
||
WHEN sale_price < 200 THEN '100-199'
|
||
WHEN sale_price < 300 THEN '200-299'
|
||
WHEN sale_price < 500 THEN '300-499'
|
||
ELSE '500+'
|
||
END AS price_band,
|
||
payment_amount,
|
||
order_count,
|
||
refund_amount
|
||
FROM #sku_agg
|
||
)
|
||
SELECT
|
||
price_band,
|
||
SUM(order_count) AS order_count,
|
||
SUM(payment_amount) AS payment_amount,
|
||
SUM(refund_amount) AS refund_amount,
|
||
CASE
|
||
WHEN SUM(payment_amount) = 0 THEN NULL
|
||
ELSE SUM(refund_amount) / SUM(payment_amount)
|
||
END AS refund_rate
|
||
FROM price_band
|
||
GROUP BY price_band
|
||
ORDER BY payment_amount DESC;
|