mysql-二维矩阵表格sql实现

背景

1、分两个维度，统计数据并形成一个二维数据展现形式

2、横轴标为统计维度1，纵轴为统计维度2

横轴x区分为：x<=5，5<x<10，x>10
纵轴y区分为：y<=5，5<y<10，y>10

形成如下图表格数据

维度2-->维度1	x<=5	5<x<10	x>10
y<=5	20	3	1
5<x<=10	1	2	1
y>10	0	0	1

步骤一：先统计出单笔两个维度的值

select user,x,y from test01

如图

user	x	y
A	1	2
B	6	2
C	5	10
D	7	11
E	11	2

步骤二：维度划分打标记

将每条记录的x,y划入到对应分级中，例如：
记录：A,x=1,y=2 划入等级为 A (x<=5) (y<=5)
记录：B,x=6,y=2 划入等级为 A (5<x<=10) (y<=5)

select user 
user,
(case 
	when x<=5 then 'x<=5' 
	when x>5 and x<=10 then '5<x<=10' 
	when x>10 then 'x>10'
	else 'NULL' end) as tagX,
(case 
	when y<=5 then 'y<=5' 
	when y>5 and y<=10 then '5<y<=10' 
	when y>10 then 'y>10'
	else 'NULL' end) as tagY
from test01

结果如图：

user	tagX	tagY
A	x<=5	y<=5
B	5<x<=10	y<=5
C	x<=5	5<y<=10
D	5<x<=10	y>10
E	x>10	y<=5

步骤三：取维度1，维度2组合-group by的结果

SQL如下

select tagX,tagY,count(1) as cnt from (
  步骤二sql
)t
group by tagX,tagY

即：

select tagX,tagY,count(1) as cnt from (
    select user 
    user,
    (case 
    	when x<=5 then 'x<=5' 
    	when x>5 and x<=10 then '5<x<=10' 
    	when x>10 then 'x>10'
    	else 'NULL' end) as tagX,
    (case 
    	when y<=5 then 'y<=5' 
    	when y>5 and y<=10 then '5<y<=10' 
    	when y>10 then 'y>10'
    	else 'NULL' end) as tagY
    from test01 
)t
group by tagX,tagY

结果如图：

tagX	tagY	cnt
5<x<=10	y<=5	1
5<x<=10	y>10	1
x<=5	5<y<=10	1
x<=5	y<=5	1
x>10	y<=5	1

步骤四：将tagX转成横轴，tagY转成纵轴

横轴：x<=5,5<x<=10,x>10 
(由于纵轴需要占用一个空间所以需要虚拟一个顶级横轴)
---> (y/x)|x<=5|5<x<=10|x>10 

纵轴：y<=5,5<y<=10,y>10

需要将tagY group by tagX通过case when 横向平铺

SQL如下：

select 
tagY as 'y/x', 
sum(case when tagX ='x<=5' then cnt else 0 end) as 'x<=5',
sum(case when tagX ='5<x<=10' then cnt else 0 end) as '5<x<=10',
sum(case when tagX ='x>10' then cnt else 0 end) as 'x>10'
from(
	步骤三sql
)m
group by tagY

即：

select 
tagY as 'y/x', 
sum(case when tagX ='x<=5' then cnt else 0 end) as 'x<=5',
sum(case when tagX ='5<x<=10' then cnt else 0 end) as '5<x<=10',
sum(case when tagX ='x>10' then cnt else 0 end) as 'x>10'
from(
	select tagX,tagY,count(1) as cnt from (
		select user 
		user,
		(case 
			when x<=5 then 'x<=5' 
			when x>5 and x<=10 then '5<x<=10' 
			when x>10 then 'x>10'
			else 'NULL' end) as tagX,
		(case 
			when y<=5 then 'y<=5' 
			when y>5 and y<=10 then '5<y<=10' 
			when y>10 then 'y>10'
			else 'NULL' end) as tagY
		from test01 
	)t
  group by tagX,tagY
)m
group by tagY

得最终结果如图：