用ETL实现行转列
创建于 2022-11-01 / 最近更新于 2023-12-18 / 3249
字体:
[默认]
[大]
[更大]
背景
有时我们需要根据一定主键,将某个有固定分类的字段作为列名展开,将多行数据转成列数据,也就是实现行转列的效果。观远ETL里支持用行转列功能和SQL输入两种方式实现。
实操案例
原始数据
像这样一个5个字段9行的数据表。其中项目字段是有多个固定分类的。
部门 | SKU | 月份 | 项目 | 值 |
运营五部 | HHHH001 | 202012 | 目标单价 | 112 |
运营五部 | HHHH001 | 202012 | 目标日销 | 212 |
运营五部 | HHHH001 | 202012 | 目标转化 | 312 |
运营五部 | HHHH001 | 202011 | 目标单价 | 111 |
运营五部 | HHHH001 | 202011 | 目标日销 | 211 |
运营五部 | HHHH001 | 202011 | 目标转化 | 311 |
运营五部 | HHHH001 | 202010 | 目标单价 | 110 |
运营五部 | HHHH001 | 202010 | 目标日销 | 210 |
运营五部 | HHHH001 | 202010 | 目标转化 | 310 |
期望效果
部门 | SKU | 月份 | 目标单价 | 目标日销 | 目标转化 |
运营五部 | HHHH001 | 202011 | 111 | 211 | 311 |
运营五部 | HHHH001 | 202012 | 112 | 212 | 312 |
运营五部 | HHHH001 | 202010 | 110 | 210 | 310 |
解决方案
方法一:在ETL里添加「行转列」。
选择操作列和填充列
案例中我们要将项目和值两个字段对应的行转成列,项目是转换后的列名,值是新列中填充的数据
选择值的聚合类型
添加主键
新建列
新建列也就是设置新的列名和原始行的值之间的对应关系,如果不变也可以直接勾选上新建列的名称同原始行的值。
方法二:通过SQL处理
使用pivot函数,多个列名用英文逗号隔开。
SELECT * FROM input1 PIVOT(SUM(`[新列中填充的值]`)FOR `[需要转换的列]` in ('[转换的列名]','[转换的列名]'......))
最后得到的结果。
注意事项:
PIVOT函数里FOR 后面要直接跟字段名,如果带表名路径,提交会报错;
2. 不建议使用动态列名。以下图为例,创建动态列名时可以预览并运行成功,但是后续ETL定时自动运行时,列名一旦发生变化输出数据集会报错,制作的卡片也会出现字段丢失的提示。
6 人点赞过