【MySQL】mysql | 数据处理 | 行转列 | 一种行转列的处理思路

一、说明

        1、mysql数据库

        2、行转列场景，对于不同行业来说，使用频率不一样

        3、本文档仅提供一种行转列的思路，仅做参考

二、开始

1、假设场景

1）以财务数据为例，但非专业的财务数据

2）财务数据有： 年，月，科目，金额，子公司编码，子公司名称

3）创建存储表结构
create table t_ke_mu_money(
id int primary key auto_increment comment '主键自增',
year int comment '年',
month int comment '月',
ke_mu varchar(32) default '' comment '科目',
money decimal(15,3) default 0.0 comment '金额',
org_code varchar(32) default '' comment '子公司编码',
org_name varchar(32) default '' comment '子公司名称'
) comment '科目金额'
4）插入测试数据
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,8,'现金', 100, '01', '子公司01');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,8,'资产', 200, '01', '子公司01');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,8,'人员工资', 300, '01', '子公司01');

insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,7,'现金', 90, '01', '子公司01');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,7,'资产', 200, '01', '子公司01');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,7,'人员工资', 290, '01', '子公司01');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,7,'现金', 90, '02', '子公司02');
insert into t_ke_mu_money (year,month,ke_mu,money, org_code,org_name) values (2022,7,'资产', 200, '02', '子公司02');

5）需求

a> 获取所有子公司2022年7月，现金金额与人员工资金额；一行展示一个子公司的现金金额，人员工资金额

b> 可能存在多条相同的记录；保留一条即可

c> 展示所有子公司

~~

2、解决分析

1）行转列很明确了，首先想到查询然后通过left join关联

2）数据可能重复，需要考虑去重

3）行转列可以通过一个大sql处理，但是我这边选择用临时表

4）考虑到去重，去重方案有很多，可以选择删除重复数据，也可以选择不删除；需求中没有说明是否允许删除原始数据，所以我这边选择不动原始数据的方案

5）大致处理思路： 优先去重，然后获取子公司的全集，然后创建临时表，最后查询并转换成目标数据结构

3、解决步骤

1）优先去重，创建去重后的数据临时表
drop table if exists t_tmp_ke_mu_money_dis;
create table t_tmp_ke_mu_money_dis
as
select year, month,ke_mu ,money, org_code, org_name
from t_ke_mu_money
group by year, month,ke_mu, money , org_code, org_name
;
说明1： 使用group by分组去重；不考虑相同年月，相同科目，金额不同的场景（需求已经明确说过金额相同，即业务数据提供方保证数据的正确性）
2）获取所有子公司全集，并创建临时表
drop table if exists t_tmp_ke_mu_org_dis;
create table t_tmp_ke_mu_org_dis
as
select org_code, org_name
from t_tmp_ke_mu_money_dis
group by org_code, org_name
;
说明1： 展示所有科目，即科目为最左数据基础，left join时使用
说明2： 如果不以所有科目为数据基础，当某一科目不存在时，就会导致left join右边有数据的表关联不上
3）获取7月份，并创建临时表
drop table if exists t_tmp_ke_mu_money_2022_7;
create table t_tmp_ke_mu_money_2022_7
as
select ke_mu,money, org_code, org_name
from t_tmp_ke_mu_money_dis
where year = 2022 and month = 7
4）获取7月份现金数据，并创建临时表
drop table if exists t_tmp_ke_mu_money_2022_7_xian_jin;
create table t_tmp_ke_mu_money_2022_7_xian_jin
as
select money as xj_money,org_code, org_name
from t_tmp_ke_mu_money_2022_7
where ke_mu = '现金'
5）获取7月份人员工资，并创建临时表
drop table if exists t_tmp_ke_mu_money_2022_7_ren_yuan_gong_zi;
create table t_tmp_ke_mu_money_2022_7_ren_yuan_gong_zi
as
select money as rygz_money,org_code, org_name
from t_tmp_ke_mu_money_2022_7
where ke_mu = '人员工资'
6）最终查询
select org.org_code as org_code, org.org_name as org_name, xj_money,rygz_money, 2022 as year, 7 as month
from t_tmp_ke_mu_org_dis org
left join t_tmp_ke_mu_money_2022_7_xian_jin xj
on org.org_code = xj.org_code
left join t_tmp_ke_mu_money_2022_7_ren_yuan_gong_zi rygz
on org.org_code = rygz.org_code
说明1： 查询子公司全集
说明2： 关联“现金”
说明3： 关联“人员工资”
说明4： on 的条件，均以org.org_code为左边条件；org即子公司org_code全集
7）结果示意图
a> 原始数据
select * from t_ke_mu_money

b> 需求结果

select org.org_code as org_code, org.org_name as org_name, xj_money,rygz_money, 2022 as year, 7 as month
from t_tmp_ke_mu_org_dis org
left join t_tmp_ke_mu_money_2022_7_xian_jin xj
on org.org_code = xj.org_code
left join t_tmp_ke_mu_money_2022_7_ren_yuan_gong_zi rygz
on org.org_code = rygz.org_code

~~

4、为什么使用临时表？

1）临时表，不改动原始数据；对源数据算是一个保护

2）临时表，可以随时删除，随时重建

3）临时表，优先过滤出需要处理的数据，不需要从原数据全表扫描

4）临时表，整个流程是模块化的，比如现在需求的是2022年7月的数据，如果需要2022年8月的数据；整个流程的sql做个微调就可以直接使用而且不影响2022年7月的数据

5）我的业务场景需要临时表，因为使用kettle写同步数据的脚本；每次的源数据都是全量修改的，源数据表删除后重建；临时表不影响已经处理过的数据（视图的话，数据就没了，表没了，视图也就没了）；源数据调整，只需要重新执行kettle脚本即可（因为我的临时表也是先删除，再重建，跟业务场景保持一致）

5、临时表可以换成视图吗？

1）这个根据自己的业务场景判断即可

2）我的场景需要用临时表；假如你的场景不存在源数据表的删除与重建，就可以使用视图

3）视图，动态维护数据，也不错