SQLsummary/报表/活跃会员占比趋势.sql

SET @PERIOD_MIN = 155;
SET @PERIOD_MAX = 158;

SELECT 
	E1.期数,
	B1.`激活会员`,
	C1.`有奖金会员`,
	D1.`报单会员`,
	D1.`报单会员`/C1.`有奖金会员` AS 报单会员占比,
	E1.`复消会员`,
	E1.`复消会员`/C1.`有奖金会员` AS 复消会员占比,
	C1.`有奖金会员` - D1.`报单会员` - E1.`复消会员` AS 无消费会员,
	(C1.`有奖金会员` - D1.`报单会员` - E1.`复消会员`)/C1.`有奖金会员` AS 无消费会员占比
FROM
(SELECT
	O.PERIOD_NUM,
	COUNT(DISTINCT O.USER_NAME) AS 报单会员
FROM
ar_order O 
	WHERE O.PERIOD_NUM >= @PERIOD_MIN AND O.PERIOD_NUM <= @PERIOD_MAX AND O.ORDER_TYPE = 'ZC'
	GROUP BY O.PERIOD_NUM)D1 LEFT JOIN
	
(SELECT
	O2.`期数`,
	COUNT(DISTINCT O2.`会员编号`) AS 复消会员
FROM
zr_order_vw O2
	WHERE O2.`期数` >= @PERIOD_MIN AND O2.`期数` <= @PERIOD_MAX AND O2.`订单类型` <> '报单'
	GROUP BY O2.`期数`)E1 ON E1.`期数` = D1.PERIOD_NUM LEFT JOIN

	(SELECT
	B1.期数,
	COUNT(B1.`会员编号`) AS 有奖金会员
FROM
(SELECT
	B.PERIOD_NUM AS 期数,
	U.USER_NAME AS 会员编号,
	B.BONUS_TOTAL AS 总奖金
FROM
	ar_calc_bonus B
	LEFT JOIN ar_user U ON U.ID = B.USER_ID
	WHERE B.PERIOD_NUM >= @PERIOD_MIN AND B.PERIOD_NUM <= @PERIOD_MAX AND B.BONUS_TOTAL >= 1
	UNION ALL
SELECT
	J.`期数`,
	J.`会员编号`,
	J.`总奖金`
FROM
	`奖金数据表` J
	WHERE J.`期数` >= @PERIOD_MIN AND J.`期数` <= @PERIOD_MAX AND J.`总奖金` >= 1)B1
	GROUP BY B1.`期数`)C1 ON C1.`期数` = E1.`期数`,
(SELECT
	COUNT(U.ID) AS 激活会员
FROM
ar_user U
WHERE U.`STATUS` = 1)B1