1、mysql数据库
2、行转列场景,对于不同行业来说,使用频率不一样
3、本文档仅提供一种行转列的思路,仅做参考
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> 展示所有子公司
~~
1)行转列很明确了,首先想到查询然后通过left join关联
2)数据可能重复,需要考虑去重
3)行转列可以通过一个大sql处理,但是我这边选择用临时表
4)考虑到去重,去重方案有很多,可以选择删除重复数据,也可以选择不删除;需求中没有说明是否允许删除原始数据,所以我这边选择不动原始数据的方案
5)大致处理思路: 优先去重,然后获取子公司的全集,然后创建临时表,最后查询并转换成目标数据结构
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 = 74)获取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~~
1)临时表,不改动原始数据;对源数据算是一个保护
2)临时表,可以随时删除,随时重建
3)临时表,优先过滤出需要处理的数据,不需要从原数据全表扫描
4)临时表,整个流程是模块化的,比如现在需求的是2022年7月的数据,如果需要2022年8月的数据;整个流程的sql做个微调就可以直接使用而且不影响2022年7月的数据
5)我的业务场景需要临时表,因为使用kettle写同步数据的脚本;每次的源数据都是全量修改的,源数据表删除后重建;临时表不影响已经处理过的数据(视图的话,数据就没了,表没了,视图也就没了);源数据调整,只需要重新执行kettle脚本即可(因为我的临时表也是先删除,再重建,跟业务场景保持一致)
1)这个根据自己的业务场景判断即可
2)我的场景需要用临时表;假如你的场景不存在源数据表的删除与重建,就可以使用视图
3)视图,动态维护数据,也不错