------帆软报表数据集-with…as…写法。
WITH PUR_REGION
AS (
SELECT – top 10
“Year”, – 年份
“Month”, – 月份
“Region”, – 销售大区
– “DivisionCode”, – 产品线编码
– “DivisionName”, – 产品线名称
– “DealerName”, – 经销商名称
– “ValueType”, – 值类型（Actual/Target）
– “Target_Type”, – 指标类型(大区或经销商(数量/金额)指标)
“DIM_DivisionFranchiseCode”, – 事业部编码
“DIM_DivisionFranchiseName”, – 事业部名称

		 CASE WHEN "Target_Type" = 'PUR_Taget_Region_Amount'
			  THEN  SUM("PUR_Target") ELSE 0 END AS "Amount_Target", -- 商采目标-金额
		 CASE WHEN "Target_Type" = 'PUR_Taget_Region_Qty'
			  THEN  SUM("PUR_Target") ELSE 0 END AS "Qty_Target",    -- 商采目标-数量
		
		-- 逻辑1-rq商采数量,只统计主产品(吻合器-枪&CSUS-刀头),可参考rq产品映射表
		CASE WHEN "DIM_DivisionFranchiseCode" = 'RQ'
		      AND TRIM(UPPER("KEY_Product")) IN ('刀头', '枪', '枪OR刀头','刀头OR枪')
			 THEN SUM("Qty") ELSE SUM("Qty") END AS "Qty_Actual",    -- 实际商采-数量
		 -- SUM("Qty") AS "Qty_Actual",                   				
		 SUM("AmountByActualPurchasePriceTax") AS "Amount_Actual"    -- 实际商采-金额
						   
FROM "_SYS_BIC"."EDW.GENESIS_DMS/GENESIS_RPT_DealerPurchase" 
WHERE "DIM_DivisionFranchiseCode" = 'RQ'  
 and ("Year"=${Years} OR "Year"=${Years}-1)
 -- 暂时限制RQ的(DMS2-Franchise) 
  AND "Region" IS NOT NULL						 -- 限制大区非空
  AND ("ValueType" = 'Actual' 					 -- 实际商采(金额/数量)
   OR  "Target_Type" = 'PUR_Taget_Region_Amount' -- 商采指标类型-金额
   OR  "Target_Type" = 'PUR_Taget_Region_Qty')   -- 商采指标类型-数量 
GROUP BY "Year",
		 "Month",
		 "Region",
		 "DIM_DivisionFranchiseCode",
		 "DIM_DivisionFranchiseName",
		 "Target_Type",
		 "KEY_Product"

),

– Below CTE for This Year
PUR_REGION_RUNNING
AS(
SELECT
“Year”, – 年份
“Month”, – 月份
“Region”, – 销售大区
“DIM_DivisionFranchiseCode”, – 事业部编码
“DIM_DivisionFranchiseName”, – 事业部名称

	 SUM("Amount_Target") AS "Amount_Target", 	-- 商采目标-金额
	 SUM("Qty_Target") AS "Qty_Target",    		-- 商采目标-数量
     SUM("Qty_Actual") AS "Qty_Actual",    		-- 实际商采-数量
     SUM("Amount_Actual") AS "Amount_Actual",   -- 实际商采-金额
	 
	 SUM(SUM("Amount_Target")) 
	 OVER (PARTITION BY "Year","Region" ORDER BY "Month") AS "Amount_Target_Running", -- 商采目标-金额-逐月累计
	 SUM(SUM("Qty_Target")) 
	 OVER (PARTITION BY "Year","Region" ORDER BY "Month") AS "Qty_Target_Running",  	 -- 商采目标-数量-逐月累计
	 SUM(SUM("Qty_Actual")) 
	 OVER (PARTITION BY "Year","Region" ORDER BY "Month") AS "Qty_Actual_Running",  	 -- 实际商采-数量-逐月累计
	 SUM(SUM("Amount_Actual")) 
	 OVER (PARTITION BY "Year","Region" ORDER BY "Month") AS "Amount_Actual_Running"  -- 实际商采-金额-逐月累计
					   
FROM PUR_REGION
GROUP BY "Year",
		 "Month",
		 "Region",
		 "DIM_DivisionFranchiseCode",
		 "DIM_DivisionFranchiseName"

),

– Below CTE for LY - Last_Year
PUR_REGION_RUNNING_LY
AS(
SELECT
“Year” + 1 AS “Year”, – 年份
“Month”, – 月份
“Region”, – 销售大区
“DIM_DivisionFranchiseCode”, – 事业部编码
“DIM_DivisionFranchiseName”, – 事业部名称

	 "Amount_Target" AS "Amount_Target_LY", 			-- 商采目标-金额-LY 
	 "Qty_Target" AS "Qty_Target_LY",    				-- 商采目标-数量-LY
     "Qty_Actual" AS "Qty_Actual_LY",    				-- 实际商采-数量-LY
     "Amount_Actual" AS "Amount_Actual_LY",   			-- 实际商采-金额-LY
	 
	 "Amount_Target_Running" AS "Amount_Target_Running_LY", -- 商采目标-金额-逐月累计-LY
	 "Qty_Target_Running" AS "Qty_Target_Running_LY",  	 	-- 商采目标-数量-逐月累计-LY
	 "Qty_Actual_Running" AS "Qty_Actual_Running_LY",  	 	-- 实际商采-数量-逐月累计-LY
	 "Amount_Actual_Running" AS "Amount_Actual_Running_LY" 	-- 实际商采-金额-逐月累计-LY
					   
FROM PUR_REGION_RUNNING

),

– Blow CTE for Region Sorter per Fracnhise
– Region_Sorter_RQ
– Region_Sorter_NV
– Region_Sorter_IC
– Region_Sorter_PI
– Region_Sorter_Suture
– Region_Sorter_SH
Region_Sorter_RQ
AS(
SELECT
‘RQ’ AS “FrachiseCode”,
“SERIES”.“ELEMENT_NUMBER” AS “Region_Sorter”,
MAP(“SERIES”.“ELEMENT_NUMBER”,
1, ‘西大区’, 2, ‘华中大区’, 3, ‘南大区’, 4, ‘北一区’,
5, ‘北二区’, 6, ‘渝贵特区’, 7, ‘江浙特区’, 8, ‘上海特区’
) AS “Region”
FROM DUMMY AS Region_2024,
SERIES_GENERATE_INTEGER(1, 0, 8) AS “SERIES”
ORDER BY “SERIES”.“ELEMENT_NUMBER”
),

– 如果后续需要添加其他BU的大区排序， 可以UNION ALL 在下面的CTE中
Region_Sorter_UNION
AS (
SELECT
“FrachiseCode”,
“Region_Sorter”,
“Region”
FROM Region_Sorter_RQ
)

– Final Output
SELECT
“Year”, – 年份
“Month”, – 月份
PUR.“Region”, – 销售大区
Region_Sort.“Region_Sorter”, – 销售大区
“DIM_DivisionFranchiseCode”, – 事业部编码
“DIM_DivisionFranchiseName”, – 事业部名称

		 "Amount_Target", 				-- 商采目标-金额
		 "Qty_Target",    				-- 商采目标-S数量
		 "Qty_Actual",    				-- 实际商采-数量
		 "Amount_Actual",   			-- 实际商采-金额
		 "Amount_Target_Running", 		-- 商采目标-金额-逐月累计
		 "Qty_Target_Running",  	 	-- 商采目标-数量-逐月累计
		 "Qty_Actual_Running",  	 	-- 实际商采-数量-逐月累计
		 "Amount_Actual_Running",  		-- 实际商采-金额-逐月累计
		 
		 0 AS "Amount_Target_LY", 			-- 商采目标-金额-LY 
		 0 AS "Qty_Target_LY",    			-- 商采目标-数量-LY
		 0 AS "Qty_Actual_LY",    			-- 实际商采-数量-LY
		 0 AS "Amount_Actual_LY",   			-- 实际商采-金额-LY
		 0 AS "Amount_Target_Running_LY", 	-- 商采目标-金额-逐月累计-LY
		 0 AS "Qty_Target_Running_LY",  	 	-- 商采目标-数量-逐月累计-LY
		 0 AS "Qty_Actual_Running_LY",  	 	-- 实际商采-数量-逐月累计-LY
		 0 AS "Amount_Actual_Running_LY" 	-- 实际商采-金额-逐月累计-LY
						   
	FROM PUR_REGION_RUNNING	AS PUR
	LEFT JOIN Region_Sorter_UNION AS Region_Sort
	       ON Region_Sort."FrachiseCode" = PUR."DIM_DivisionFranchiseCode"
		  AND Region_Sort."Region" = PUR."Region"

UNION ALL

		SELECT
		 "Year",          				-- 年份
		 "Month",                   	-- 月份
		 PUR_LY."Region",               -- 销售大区
		 Region_Sort."Region_Sorter",   -- 销售大区
		 "DIM_DivisionFranchiseCode", 	-- 事业部编码
		 "DIM_DivisionFranchiseName", 	-- 事业部名称
			
		 0 AS "Amount_Target", 				-- 商采目标-金额
		 0 AS "Qty_Target",    				-- 商采目标-S数量
		 0 AS "Qty_Actual",    				-- 实际商采-数量
		 0 AS "Amount_Actual",   			-- 实际商采-金额
		 0 AS "Amount_Target_Running", 		-- 商采目标-金额-逐月累计
		 0 AS "Qty_Target_Running",  	 	-- 商采目标-数量-逐月累计
		 0 AS "Qty_Actual_Running",  	 	-- 实际商采-数量-逐月累计
		 0 AS "Amount_Actual_Running",  		-- 实际商采-金额-逐月累计
		 
		 "Amount_Target_LY", 			-- 商采目标-金额-LY 
		 "Qty_Target_LY",    			-- 商采目标-数量-LY
		 "Qty_Actual_LY",    			-- 实际商采-数量-LY
		 "Amount_Actual_LY",   			-- 实际商采-金额-LY
		 "Amount_Target_Running_LY", 	-- 商采目标-金额-逐月累计-LY
		 "Qty_Target_Running_LY",  	 	-- 商采目标-数量-逐月累计-LY
		 "Qty_Actual_Running_LY",  	 	-- 实际商采-数量-逐月累计-LY
		 "Amount_Actual_Running_LY" 	-- 实际商采-金额-逐月累计-LY
						   
	FROM PUR_REGION_RUNNING_LY AS PUR_LY
	LEFT JOIN Region_Sorter_UNION AS Region_Sort
	       ON Region_Sort."FrachiseCode" = PUR_LY."DIM_DivisionFranchiseCode"
		  AND Region_Sort."Region" = PUR_LY."Region"
# 数据库 # 服务器 # microsoft
Logo
永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

cover

物联网大数据时代：时序数据库选型深度指南，为何IoTDB脱颖而出？

avatar 永洪数据分析社区
cover

时序数据库选型指南：工业大数据场景下基于Apache IoTDB技术价值与实践路径

avatar 永洪数据分析社区
cover

统计与大数据分析与数学金融课程解析

avatar 永洪数据分析社区