mysql-二维矩阵表格sql实现

背景

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

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

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

形成如下图表格数据

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

select user,x,y from test01

如图

步骤二：维度划分打标记

将每条记录的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 

结果如图：

步骤三：取维度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转成纵轴

横轴：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

得最终结果如图：

参考：

　　https://blog.csdn.net/qq_25264951/article/details/56679120

　　https://blog.csdn.net/yoshiokayui/article/details/83564318

　　https://blog.csdn.net/m0_43430744/article/details/83444906

　　https://blog.csdn.net/John_Like_Girl/article/details/103144442