SQLsummary/报表/会员数量明细.sql

SELECT
	A.新增银钻,
	(A.新增银钻/(A.新增银钻+A.新增金钻+A.新增VIP)) AS 银钻比例,
	A.新增金钻,
	(A.新增金钻/(A.新增银钻+A.新增金钻+A.新增VIP)) AS 金钻比例,
	A.新增VIP,
	(A.新增VIP/(A.新增银钻+A.新增金钻+A.新增VIP)) AS VIP比例,
	(A.新增银钻+A.新增金钻+A.新增VIP) AS 新增合计,
	A.复消会员,
	(A.复消会员/B.`累计激活会员`) AS 复消比例,
	B.`累计激活会员`
FROM
	(SELECT P.PERIOD_NUM AS 期数,
			(
			SELECT COUNT(`实时会员级别`) AS 新增银钻 FROM zr_user_vw V1 WHERE 实时会员级别 = "银钻会员" AND V1.`加入期数` = P.PERIOD_NUM
			) 新增银钻,
			(
			SELECT COUNT(`实时会员级别`) AS 新增金钻 FROM zr_user_vw V2 WHERE 实时会员级别 = "金钻会员" AND V2.`加入期数` = P.PERIOD_NUM
			) 新增金钻,
			(
			SELECT COUNT(`实时会员级别`) AS 新增VIP FROM zr_user_vw V3 WHERE 实时会员级别 = "VIP会员" AND V3.`加入期数` = P.PERIOD_NUM
			) 新增VIP,
			(
			SELECT COUNT(DISTINCT 会员编号) AS 复消会员 FROM zr_product_pkg_vw WHERE 期数 = P.PERIOD_NUM AND (订单类型 = "复消" OR 订单类型 = "积分")
			) 复消会员
		FROM ar_period P
		WHERE P.PERIOD_NUM = 167) A,
		
		(SELECT COUNT(U.USER_NAME) AS 累计激活会员 FROM ar_user U WHERE U.STATUS = 1) B