阿里云云原生大数据计算服务 MaxCompute行转列及列转行最佳实践-云淘科技

背景信息

行转列与列转行的示意图如下。

• 行转列

  将多行数据转换成一行显示，或将一列数据转换成多列显示。

• 列转行

  将一行数据转换成多行显示，或将多列数据转换成一列显示。

示例数据

为便于理解后续代码示例，本文为您提供源数据，并基于源数据提供相关转换示例。

• 创建用于实现行转列的源表并插入数据，命令示例如下。

  CREATE TABLE rowtocolumn (name string, subject string, result bigint);
  INSERT INTO TABLE rowtocolumn VALUES 
  ('张三' , '语文' , 74),
  ('张三' , '数学' , 83),
  ('张三' , '物理' , 93),
  ('李四' , '语文' , 74),
  ('李四' , '数学' , 84),
  ('李四' , '物理' , 94);

  查询表rowtocolumn中的数据，命令示例如下：

  SELECT * FROM rowtocolumn;
  --返回结果。
  +------------+------------+------------+
  | name       | subject    | result     |
  +------------+------------+------------+
  | 张三        | 语文        | 74         |
  | 张三        | 数学        | 83         |
  | 张三        | 物理        | 93         |
  | 李四        | 语文        | 74         |
  | 李四        | 数学        | 84         |
  | 李四        | 物理        | 94         |
  +------------+------------+------------+

• 创建用于实现列转行的源表并插入数据，命令示例如下。

  CREATE TABLE columntorow (name string, chinese bigint, mathematics bigint, physics bigint);
  INSERT INTO TABLE columntorow VALUES 
  ('张三' , 74, 83, 93),
  ('李四' , 74, 84, 94);

  查询表columntorow中的数据，命令示例如下：

  SELECT * FROM columntorow;
  --返回结果。
  +------------+------------+-------------+------------+
  | name       | chinese    | mathematics | physics    |
  +------------+------------+-------------+------------+
  | 张三        | 74         | 83          | 93         |
  | 李四        | 74         | 84          | 94         |
  | 张三        | 74         | 83          | 93         |
  | 李四        | 74         | 84          | 94         |
  +------------+------------+-------------+------------+

行转列示例

您可以通过如下两种方法实现行转列：

• 方法一：使用case when表达式，灵活提取各科目（subject）的值作为单独的列，命令示例如下。

  SELECT name AS 姓名,
         max(case subject when '语文' then result end) AS 语文,
         max(case subject when '数学' then result end) AS 数学,
         max(case subject when '物理' then result end) AS 物理 
  FROM rowtocolumn 
  GROUP BY name;

  返回结果如下。

  +--------+------------+------------+------------+
  | 姓名   | 语文      | 数学     | 物理      |
  +--------+------------+------------+------------+
  | 张三   | 74       | 83       | 93        |
  | 李四   | 74       | 84       | 94        |
  +--------+------------+------------+------------+

• 方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT和WM_CONCAT函数合并科目和成绩为一列，然后通过KEYVALUE函数解析科目（subject）的值作为单独的列。命令示例如下。

  SELECT name AS 姓名,
         keyvalue(subject, '语文') AS 语文,
         keyvalue(subject, '数学') AS 数学,
         keyvalue(subject, '物理') AS 物理
  FROM(
       SELECT name, wm_concat(';',concat(subject,':',result))as subject 
       FROM rowtocolumn
       GROUP BY name);

  返回结果如下。

  +--------+------------+------------+------------+
  | 姓名   | 语文      | 数学      | 物理      |
  +--------+------------+------------+------------+
  | 张三   | 74       | 83        | 93        |
  | 李四   | 74       | 84        | 94        |
  +--------+------------+------------+------------+

说明

在实际业务开发过程中，如果您遇到行转列需求，还可以通过Lateral View、EXPLODE函数、INLINE函数以及TRANS_ARRAY函数将单行数据转为多行数据。

列转行示例

您可以通过如下两种方法实现列转行：

• 方法一：使用union all，将各科目（chinese、mathematics、physics）整合为一列，命令示例如下。

  --解除order by必须带limit的限制，方便列转行SQL命令对结果按照姓名排序。
  SET odps.sql.validate.orderby.limit=false;
  --列转行SQL。
  SELECT name AS 姓名, subject AS 科目, result AS 成绩 
  FROM(
       SELECT name, '语文' AS subject, chinese AS result FROM columntorow 
       UNION all 
       SELECT name, '数学' AS subject, mathematics AS result FROM columntorow 
       UNION all 
       SELECT name, '物理' AS subject, physics AS result FROM columntorow) 
  ORDER BY name;

  返回结果如下。

  +--------+--------+------------+
  | 姓名   | 科目   | 成绩       |
  +--------+--------+------------+
  | 张三   | 语文   | 74         |
  | 张三   | 数学   | 83         |
  | 张三   | 物理   | 93         |
  | 李四   | 语文   | 74         |
  | 李四   | 数学   | 84         |
  | 李四   | 物理   | 94         |
  +--------+--------+------------+

• 方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT函数拼接各科目和成绩，然后基于TRANS_ARRAY和SPLIT_PART函数逐层拆解科目和成绩作为单独的列。命令示例如下。

  说明

  当您的待转换数据包含有NULL值时，使用此方式转换结果会不符合预期，因为CONCAT函数任一参数为NULL，返回结果就为NUL。您可以在使用方法二前先使用NVL函数，将NULL值转换为其他特殊值（例如0），NVL函数详情请参见NVL；或使用上述方法一进行转换。

  SELECT name AS 姓名,
         split_part(subject,':',1) AS 科目,
         split_part(subject,':',2) AS 成绩
  FROM(
         SELECT trans_array(1,';',name,subject) AS (name,subject) 
         FROM(
              SELECT name,
          concat('语文',':',chinese,';','数学',':',mathematics,';','物理',':',physics) AS subject 
              FROM columntorow)tt)tx;

  返回结果如下。

  +--------+--------+------------+
  | 姓名   | 科目   | 成绩       |
  +--------+--------+------------+
  | 张三   | 语文   | 74         |
  | 张三   | 数学   | 83         |
  | 张三   | 物理   | 93         |
  | 李四   | 语文   | 74         |
  | 李四   | 数学   | 84         |
  | 李四   | 物理   | 94         |
  +--------+--------+------------+

相关文档

您也可以通过PIVOT关键字实现行转换为列，通过UNPIVOT关键字实现列转换为行，请参见PIVOT、UNPIVOT。