SQLsummary/报表/期奖金拨比分析1.sql

SET @PERIOD = 158;

SELECT
	A1.`总金额`,
	A1.`总PV`,
	B1.`报单金额`,
	B1.`报单PV`,
	C1.`复消金额`,
	C1.`复消PV`,
	SUM(D1.`服务奖`+D1.`推广奖`+D1.`业绩奖`+D1.`消费奖`+D1.`共享奖`+D1.`管理奖`+D1.`团队奖`+B1.`报单PV`*D1.`VIP奖参考比例`* 0.95 + A1.`总PV`*D1.`荣衔奖参考比例` * 0.95) AS 实际奖金,
	SUM(D1.`服务奖`+D1.`推广奖`+D1.`业绩奖`+D1.`消费奖`+D1.`共享奖`+D1.`管理奖`+D1.`团队奖`+B1.`报单PV`*D1.`VIP奖参考比例`* 0.95 + A1.`总PV`*D1.`荣衔奖参考比例` * 0.95)/A1.`总PV` AS 实际奖金PV占比,
	SUM(D1.`服务奖`+D1.`推广奖`+D1.`业绩奖`+D1.`消费奖`+D1.`共享奖`+D1.`管理奖`+D1.`团队奖`+B1.`报单PV`*D1.`VIP奖参考比例`* 0.95 + A1.`总PV`*D1.`荣衔奖参考比例` * 0.95)/A1.`总金额` AS 实际奖金金额占比,
	B1.`报单金额`/A1.`总金额` AS 报单金额占比,
	C1.`复消金额`/A1.`总金额` AS 复消金额占比
FROM
(SELECT
	SUM(O.`商品金额`) AS 总金额,
	SUM(O.`PV合计`) AS 总PV
FROM
	zr_order_vw O
	WHERE O.`期数` = @PERIOD)A1,

(SELECT
	SUM(O2.`商品金额`) AS 报单金额,
	SUM(O2.`PV合计`) AS 报单PV
FROM
	zr_order_vw O2
	WHERE O2.`订单类型` = '报单'
	AND O2.`期数` = @PERIOD)B1,
	
(SELECT
	SUM(O3.`商品金额`) AS 复消金额,
	SUM(O3.`PV合计`) AS 复消PV
FROM
	zr_order_vw O3
	WHERE O3.`订单类型` <> '报单'
	AND O3.`期数` = @PERIOD)C1,
(SELECT
	B.PERIOD_NUM AS 期数,
	SUM(B.BONUS_BD) AS 服务奖,
	SUM(B.BONUS_TG) AS 推广奖,
	SUM(B.BONUS_YJ) AS 业绩奖,
	SUM(B.BONUS_XF) AS 消费奖,
	SUM(B.BONUS_GX) AS 共享奖,
	SUM(B.BONUS_GL) AS 管理奖,
	SUM(B.BONUS_QY) AS 团队奖,
	C.key_value AS VIP奖参考比例,
	C2.key_value AS 荣衔奖参考比例
FROM
	ar_calc_bonus B
	LEFT JOIN 参数表 C ON C.key_name = 'VIP奖参考比例'
	LEFT JOIN 参数表 C2 ON C2.key_name = '荣衔奖参考比例'
	WHERE B.PERIOD_NUM = @PERIOD)D1