首页信息统计

– 1.累计授权数 xiaofeizhe 和 weixinyonghu 两张表 的 union_id相等且各自表中的union_id不为空(null)即可 ，另外测试时候修改c.register_time即可。

SELECT
*
FROM
xiaofeizhe c
LEFT JOIN weixinyonghu u ON c.union_id = u.unionid 		
WHERE
c.union_id != ''
AND c.union_id IS NOT NULL
AND u.unionid != ''
AND u.unionid IS NOT NULL

2.年龄分布 xiaofeizhe 、weixinyonghu 除了unionid相等，各自表中的union_id不为空（null），weixinyonghu 的appid 还必须为’abc’

select 	
	age_temp title,                                -     -- 年龄描述
	count(id) as total, 	                               -- 数量
	round(100*(
		count(id)/(SELECT count(c.id) 
		from xiaofeizhe c left join weixinyonghu u on c.union_id = u.unionid 
		where
		u.appid = 'abc'  and  c.union_id != '' and c.union_id is not null and u.unionid != '' and u.unionid  is not null
		)),2) as percentage                               -- 百分比
from
(			-- 子查询     xiaofeizhe 和   weixinyonghu
			select 
				t.id, 
				t.age, 
				case
					when t.age <= 0 then '未知'
					when t.age between 1 and 17 then '17岁以下'
					when t.age between 18 and 24 then '18-24岁'
					when t.age between 25 and 29 then '25-29岁'
					when t.age between 30 and 39 then '30-39岁'
					when t.age between 40 and 49 then '40-49岁'
					when t.age between 50 and 59 then '50-59岁'
					when t.age >= 60 then '60岁以上'
				end as age_temp
			from
			(SELECT c.union_id id, IFNULL(TIMESTAMPDIFF(YEAR, c.birthday, CURDATE()), 0) age
			FROM xiaofeizhe c
			left join weixinyonghu u on c.union_id = u.unionid
			where
			 u.appid = 'abc' and c.union_id is not null and u.unionid != '' and u.unionid  is not null
			) t
)
t_user
group by age_temp

– 3.性别分布 xiaofeizhe、weixinyonghu除了unionid相等，各自表中的union_id不为空（null），weixinyonghu的appid 还必须为’abc’

SELECT                                         -- xiaofeizhe 和   weixinyonghu
	u.sex sex ,                                -- 性别
	COUNT(sc.id) total,                        -- 数量
	round(100 * CAST(count(sc.id) AS DECIMAL) / CAST((SELECT COUNT(c.id) FROM xiaofeizhe c left join weixinyonghu wu on c.union_id  = wu.unionid
	WHERE wu.appid = 'abc' and c.union_id != '' and c.union_id is not null and wu.unionid != '' and wu.unionid is not null
	) AS DECIMAL), 2) AS percentage            -- 占比
FROM  xiaofeizhe sc 
    left join weixinyonghu u on sc.union_id = u.unionid
WHERE u.appid = 'abc' and  sc.union_id != '' and sc.union_id is not null and u.unionid != '' and u.unionid  is not null

GROUP BY u.sex
ORDER BY total desc

– 4.省级分布
– area的国家为 中国或者 China，并且xiaofeizhe 和 weixinyonghu 的union_id必须相等，通过area左连接右面两张综合成的一张表

select * from area;

SELECT 
  t.title,
	IFNULL(temp.count,0) as count,
	IFNULL(temp.percentage,0) percentage
from (      -- 表t  area
			SELECT
			a.id,                          					-- id 
			CASE a.`name`
				WHEN '内蒙古自治区' THEN '内蒙古'
				WHEN '黑龙江省' THEN '黑龙江'
				ELSE left(a.`name`, 2) 
			END AS title 								    -- 省份名
			from
			area a  where a.`level` = '1'
			union   -- 联合查询
			SELECT UUID(),
			'其他' title from DUAL
) t
left join
(           -- 表temp     xiaofeizhe c  和  weixinyonghu st 
			SELECT 
			case 
				when st.province = '' or  st.province is null then '其他'
				WHEN st.province = '内蒙古自治区' THEN '内蒙古'
				WHEN st.province = '黑龙江省' THEN '黑龙江'
				ELSE left(st.province, 2)
			END as title,                                 	-- 省份名
			count(c.id) as count,                 		    -- 数量
			round(100 *                            
				count(c.id)/(SELECT COUNT(sc.id) FROM xiaofeizhe sc left join weixinyonghu wu on sc.union_id = wu.unionid
				where
				wu.country in ('中国','China')
				and wu.appid = 'abc'
				),2
			) as percentage									-- 占比
			from
			xiaofeizhe c
			left join
				weixinyonghu st on c.union_id = st.unionid
			where
			st.country in ('中国','China')
			and st.appid = 'abc'
			group by st.province
) temp on temp.title = t.title ORDER BY count desc