194 lines
7.0 KiB
Transact-SQL
194 lines
7.0 KiB
Transact-SQL
USE [JSDataMiddlePlatform];
|
||
GO
|
||
|
||
/*
|
||
Template: 售后360分析（退款 + 原因 + 工单 + 客诉）
|
||
Window: 上一个自然周 [week_start, week_end)
|
||
*/
|
||
|
||
DECLARE @week_start datetime2 = CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0) AS datetime2);
|
||
DECLARE @week_end datetime2 = CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS datetime2);
|
||
|
||
IF OBJECT_ID('tempdb..#chat_latest') IS NOT NULL
|
||
DROP TABLE #chat_latest;
|
||
|
||
WITH chat_rank AS (
|
||
SELECT
|
||
NULLIF(c.orderNo, '') AS order_id,
|
||
ISNULL(NULLIF(c.mainReason, ''), '') AS main_reason,
|
||
ISNULL(NULLIF(c.secondReason, ''), '') AS second_reason,
|
||
ISNULL(NULLIF(c.thirdReason, ''), '') AS third_reason,
|
||
c.createdAt,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY NULLIF(c.orderNo, '')
|
||
ORDER BY c.createdAt DESC, c.id DESC
|
||
) AS rn
|
||
FROM dbo.pc_kfChatLog AS c WITH (NOLOCK)
|
||
WHERE c.createdAt >= @week_start
|
||
AND c.createdAt < @week_end
|
||
AND ISNULL(NULLIF(c.orderNo, ''), '') <> ''
|
||
)
|
||
SELECT
|
||
order_id,
|
||
main_reason,
|
||
second_reason,
|
||
third_reason
|
||
INTO #chat_latest
|
||
FROM chat_rank
|
||
WHERE rn = 1;
|
||
|
||
IF OBJECT_ID('tempdb..#afs') IS NOT NULL
|
||
DROP TABLE #afs;
|
||
|
||
SELECT
|
||
NULLIF(a.undefined2, '') AS order_id,
|
||
COALESCE(NULLIF(a.undefined8, ''), 'unknown') AS shop_name,
|
||
CASE
|
||
WHEN CHARINDEX('-', ISNULL(a.undefined8, '')) > 1
|
||
THEN LEFT(a.undefined8, CHARINDEX('-', a.undefined8) - 1)
|
||
ELSE 'unknown'
|
||
END AS platform_name,
|
||
ISNULL(NULLIF(a.undefined56, ''), '未标注') AS afs_type,
|
||
ISNULL(NULLIF(a.undefined58, ''), '未标注') AS afs_result,
|
||
ISNULL(NULLIF(a.undefined57, ''), '') AS native_reason,
|
||
ISNULL(cl.main_reason, '') AS chat_main_reason,
|
||
ISNULL(cl.second_reason, '') AS chat_second_reason,
|
||
ISNULL(cl.third_reason, '') AS chat_third_reason,
|
||
CASE
|
||
WHEN ISNULL(NULLIF(a.undefined57, ''), '') <> '' THEN ISNULL(NULLIF(a.undefined57, ''), '')
|
||
WHEN ISNULL(cl.main_reason, '') <> '' THEN cl.main_reason
|
||
ELSE '未标注'
|
||
END AS afs_reason_l1,
|
||
CASE
|
||
WHEN ISNULL(cl.second_reason, '') <> '' THEN cl.second_reason
|
||
ELSE '未标注'
|
||
END AS afs_reason_l2,
|
||
CASE
|
||
WHEN ISNULL(cl.third_reason, '') <> '' THEN cl.third_reason
|
||
ELSE '未标注'
|
||
END AS afs_reason_l3,
|
||
CASE
|
||
WHEN ISNULL(NULLIF(a.undefined57, ''), '') <> '' THEN 'afs_reason'
|
||
WHEN ISNULL(cl.main_reason, '') <> '' THEN 'chat_main_reason'
|
||
ELSE 'unknown'
|
||
END AS reason_source,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined20, '')) AS order_qty,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined30, '')) AS shipped_qty,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined26, '')) AS payment_amount,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined47, '')) AS apply_amount,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined50, '')) AS return_amount,
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined52, '')) AS refund_amount
|
||
INTO #afs
|
||
FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
|
||
LEFT JOIN #chat_latest AS cl
|
||
ON cl.order_id = NULLIF(a.undefined2, '')
|
||
WHERE a.createdAt >= @week_start
|
||
AND a.createdAt < @week_end
|
||
AND ISNULL(NULLIF(a.undefined2, ''), '') <> ''
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%配件%'
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%特殊单%';
|
||
|
||
IF OBJECT_ID('tempdb..#install') IS NOT NULL
|
||
DROP TABLE #install;
|
||
|
||
SELECT
|
||
NULLIF(i.OrderNum, '') AS order_id,
|
||
i.SubmitTime,
|
||
TRY_CONVERT(int, i.StatusId) AS status_id,
|
||
TRY_CONVERT(int, i.installComplete) AS install_complete,
|
||
TRY_CONVERT(decimal(18, 2), i.Num) AS install_num,
|
||
TRY_CONVERT(decimal(18, 2), i.PayMent) AS payment_amount,
|
||
TRY_CONVERT(decimal(18, 2), i.installcost) AS install_cost,
|
||
TRY_CONVERT(decimal(18, 2), i.settleAmount) AS settle_amount,
|
||
TRY_CONVERT(int, i.kfsh) AS kf_audit,
|
||
TRY_CONVERT(int, i.cwsh) AS cw_audit,
|
||
ISNULL(NULLIF(i.Province, ''), 'unknown') AS province,
|
||
ISNULL(NULLIF(i.City, ''), 'unknown') AS city
|
||
INTO #install
|
||
FROM dbo.fys_Ticketing_install AS i WITH (NOLOCK)
|
||
WHERE i.SubmitTime >= @week_start
|
||
AND i.SubmitTime < @week_end
|
||
AND ISNULL(NULLIF(i.OrderNum, ''), '') <> ''
|
||
AND (i.[Delete] = 0 OR i.[Delete] IS NULL);
|
||
|
||
-- 1) 售后总览（退款口径）
|
||
SELECT
|
||
@week_start AS week_start,
|
||
@week_end AS week_end,
|
||
COUNT(*) AS afs_record_count,
|
||
COUNT(DISTINCT order_id) AS afs_order_count,
|
||
SUM(ISNULL(order_qty, 0)) AS order_qty_sum,
|
||
SUM(ISNULL(shipped_qty, 0)) AS shipped_qty_sum,
|
||
SUM(ISNULL(payment_amount, 0)) AS payment_amount,
|
||
SUM(ISNULL(refund_amount, 0)) AS refund_amount,
|
||
SUM(ISNULL(return_amount, 0)) AS return_amount,
|
||
CASE
|
||
WHEN SUM(ISNULL(payment_amount, 0)) = 0 THEN NULL
|
||
ELSE SUM(ISNULL(refund_amount, 0)) / SUM(ISNULL(payment_amount, 0))
|
||
END AS refund_rate
|
||
FROM #afs;
|
||
|
||
-- 2) 售后原因分析（一级原因 + 来源）
|
||
SELECT TOP (30)
|
||
afs_reason_l1 AS afs_reason,
|
||
reason_source,
|
||
COUNT(*) AS afs_record_count,
|
||
COUNT(DISTINCT order_id) AS order_count,
|
||
SUM(ISNULL(shipped_qty, 0)) AS shipped_qty_sum,
|
||
SUM(ISNULL(refund_amount, 0)) AS refund_amount,
|
||
CASE
|
||
WHEN SUM(ISNULL(payment_amount, 0)) = 0 THEN NULL
|
||
ELSE SUM(ISNULL(refund_amount, 0)) / SUM(ISNULL(payment_amount, 0))
|
||
END AS reason_refund_rate
|
||
FROM #afs
|
||
GROUP BY afs_reason_l1, reason_source
|
||
ORDER BY afs_record_count DESC;
|
||
|
||
-- 3) 售后二级原因 TOP20
|
||
SELECT TOP (20)
|
||
afs_reason_l1,
|
||
afs_reason_l2,
|
||
COUNT(*) AS afs_record_count,
|
||
SUM(ISNULL(shipped_qty, 0)) AS shipped_qty_sum,
|
||
SUM(ISNULL(refund_amount, 0)) AS refund_amount
|
||
FROM #afs
|
||
GROUP BY afs_reason_l1, afs_reason_l2
|
||
ORDER BY afs_record_count DESC;
|
||
|
||
-- 4) 售后类型/结果矩阵
|
||
SELECT
|
||
afs_type,
|
||
afs_result,
|
||
COUNT(*) AS afs_record_count,
|
||
SUM(ISNULL(shipped_qty, 0)) AS shipped_qty_sum,
|
||
SUM(ISNULL(refund_amount, 0)) AS refund_amount
|
||
FROM #afs
|
||
GROUP BY afs_type, afs_result
|
||
ORDER BY afs_record_count DESC;
|
||
|
||
-- 5) 安装工单概览（同周期）
|
||
SELECT
|
||
COUNT(*) AS install_ticket_count,
|
||
COUNT(DISTINCT order_id) AS install_order_count,
|
||
SUM(ISNULL(install_num, 0)) AS install_piece_count,
|
||
SUM(ISNULL(payment_amount, 0)) AS install_payment_amount,
|
||
SUM(ISNULL(install_cost, 0)) AS install_cost,
|
||
SUM(ISNULL(settle_amount, 0)) AS settle_amount,
|
||
SUM(CASE WHEN ISNULL(install_complete, 0) = 1 THEN 1 ELSE 0 END) AS install_completed_count,
|
||
SUM(CASE WHEN ISNULL(kf_audit, 0) <> 1 OR ISNULL(cw_audit, 0) <> 1 THEN 1 ELSE 0 END) AS pending_audit_count
|
||
FROM #install;
|
||
|
||
-- 6) 售后退款与安装工单联动（按订单）
|
||
SELECT
|
||
COUNT(*) AS overlap_order_count,
|
||
SUM(ISNULL(a.refund_amount, 0)) AS overlap_refund_amount,
|
||
SUM(ISNULL(i.install_cost, 0)) AS overlap_install_cost,
|
||
SUM(CASE WHEN ISNULL(i.install_complete, 0) = 1 THEN 1 ELSE 0 END) AS overlap_install_completed_count
|
||
FROM (
|
||
SELECT DISTINCT order_id, refund_amount
|
||
FROM #afs
|
||
WHERE ISNULL(order_id, '') <> ''
|
||
) a
|
||
INNER JOIN #install i
|
||
ON i.order_id = a.order_id;
|