观远 BI

用ETL实现行转列

创建于 2022-11-01 / 最近更新于 2023-12-18 / 3241
字体: [默认] [大] [更大]

背景

      有时我们需要根据一定主键,将某个有固定分类的字段作为列名展开,将多行数据转成列数据,也就是实现行转列的效果。观远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里添加「行转列」。

image.png

  1. 选择操作列和填充列

案例中我们要将项目和值两个字段对应的行转成列,项目是转换后的列名,值是新列中填充的数据

image.png

  1. 选择值的聚合类型

image.png

  1. 添加主键

image.png

  1. 新建列

新建列也就是设置新的列名和原始行的值之间的对应关系,如果不变也可以直接勾选上新建列的名称同原始行的值。

image.png

方法二:通过SQL处理

image.png

  1. 使用pivot函数,多个列名用英文逗号隔开。

SELECT * FROM input1
PIVOT(SUM(`[新列中填充的值]`)FOR `[需要转换的列]` in ('[转换的列名]','[转换的列名]'......))

图片.png

  1. 最后得到的结果。

image.png

注意事项:

  1. PIVOT函数里FOR 后面要直接跟字段名,如果带表名路径,提交会报错;


图片.png

2. 不建议使用动态列名。以下图为例,创建动态列名时可以预览并运行成功,但是后续ETL定时自动运行时,列名一旦发生变化输出数据集会报错,制作的卡片也会出现字段丢失的提示。

图片.png

7 人点赞过