78 lines
2.6 KiB
Transact-SQL
78 lines
2.6 KiB
Transact-SQL
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;
|