SQLsummary/报表/月奖金拨比分析（VIP奖，荣衔奖）.sql

SET @MONTHS = '202103';

SELECT
*
FROM
(SELECT
	S.key_value * B1.`报单PV` AS 原奖金,
	S.key_value * B1.`报单PV`/B1.`报单PV` AS 报单PV,
	0 AS 复消PV,
	S.key_value * B1.`报单PV`/D1.`总PV` AS 总PV,
	S.key_value * B1.`报单PV`/D1.`总金额` AS 总金额
FROM
(SELECT
	SUM(O.`PV合计`) AS 报单PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS AND O.`订单类型` = '报单'
)B1,
(SELECT
	SUM(O.`PV合计`) AS 复消PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS AND O.`订单类型` <> '报单'
)C1,
(SELECT
	SUM(O.`商品金额`) AS 总金额,
	SUM(O.`PV合计`) AS 总PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS)D1
	LEFT JOIN 参数表 S ON S.key_name = 'VIP奖参考比例'
	
	UNION ALL
	
	SELECT
	S2.key_value * D2.`总PV` AS 原奖金,
	S2.key_value * D2.`总PV`/D2.`总PV` AS 报单PV,
	S2.key_value * D2.`总PV`/D2.`总PV` AS 复消PV,
	S2.key_value * D2.`总PV`/D2.`总PV` AS 总PV,
	S2.key_value * D2.`总PV`/D2.`总金额` AS 总金额
FROM
(SELECT
	SUM(O.`PV合计`) AS 报单PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS AND O.`订单类型` = '报单'
)B2,
(SELECT
	SUM(O.`PV合计`) AS 复消PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS AND O.`订单类型` <> '报单'
)C2,
(SELECT
	SUM(O.`商品金额`) AS 总金额,
	SUM(O.`PV合计`) AS 总PV
FROM
	zr_order_vw O
	WHERE O.`月份` = @MONTHS)D2
	LEFT JOIN 参数表 S2 ON S2.key_name = '荣衔奖参考比例')Z