乐趣区

DLA-SQL技巧行列转换和JSON数据列展开

jiezi

1. 简介

在数据库 SQL 处理中，常常有行转列（Pivot）和列转行（Unpivot）的数据处理需求。本文以示例说明在 Data Lake Analytics（https://www.aliyun.com/product/datalakeanalytics）中，如何使用 SQL 的一些技巧，达到行转列（Pivot）和列转行（Unpivot）的目的。另外，DLA 支持函数式表达式的处理逻辑、丰富的 JSON 数据处理函数和 UNNEST 的 SQL 语法，结合这些功能，能够实现非常丰富、强大的 SQL 数据处理语义和能力，本文也以 JSON 数据列展开为示例，说明在 DLA 中使用这种 SQL 的技巧。

2. 行转列（Pivot）

2.1 样例数据

test_pivot 表内容：

+------+----------+---------+--------+
| id   | username | subject | source |
+------+----------+---------+--------+
| 1    | 张三     | 语文    | 60     |
| 2    | 李四     | 数学    | 70     |
| 3    | 王五     | 英语    | 80     |
| 4    | 王五     | 数学    | 75     |
| 5    | 王五     | 语文    | 57     |
| 6    | 李四     | 语文    | 80     |
| 7    | 张三     | 英语    | 100    |
+------+----------+---------+--------+

2.2 方法一：通过 CASE WHEN 语句

SQL 语句：

SELECT 
   username,
   max(CASE WHEN subject = '语文' THEN source END) AS ` 语文 `,
   max(CASE WHEN subject = '数学' THEN source END) AS ` 数学 `,
   max(CASE WHEN subject = '英语' THEN source END) AS ` 英语 `
FROM test_pivot
GROUP BY username
ORDER BY username;

结果：

+----------+--------+--------+--------+
| username | 语文   | 数学   | 英语   |
+----------+--------+--------+--------+
| 张三     | 60     | NULL   | 100    |
| 李四     | 80     | 70     | NULL   |
| 王五     | 57     | 75     | 80     |
+----------+--------+--------+--------+

2.3 方法二：通过 map_agg 函数

该方法思路上分为两个步骤：
第一步，通过 map_agg 函数把两个列的多行的值，映射为 map；
第二步，通过 map 的输出，达到多列输出的目的。

第一步 SQL：

SELECT username, map_agg(subject, source) kv
FROM test_pivot
GROUP BY username
ORDER BY username;

第一步输出：

+----------+-----------------------------------+
| username | kv                                |
+----------+-----------------------------------+
| 张三     | {语文 =60, 英语 =100}               |
| 李四     | {数学 =70, 语文 =80}                |
| 王五     | {数学 =75, 语文 =57, 英语 =80}       |
+----------+-----------------------------------+

可以看到 map_agg 的输出效果。

最终，该方法的 SQL：

SELECT
  username,
  if(element_at(kv, '语文') = null, null, kv['语文']) AS ` 语文 `,
  if(element_at(kv, '数学') = null, null, kv['数学']) AS ` 数学 `,
  if(element_at(kv, '英语') = null, null, kv['英语']) AS ` 英语 `
FROM (SELECT username, map_agg(subject, source) kv
  FROM test_pivot
  GROUP BY username
) t
ORDER BY username;

结果：

+----------+--------+--------+--------+
| username | 语文   | 数学   | 英语   |
+----------+--------+--------+--------+
| 张三     | 60     | NULL   | 100    |
| 李四     | 80     | 70     | NULL   |
| 王五     | 57     | 75     | 80     |
+----------+--------+--------+--------+

3. 列转行（Unpivot）

3.1 样例数据

test_unpivot 表内容：

+----------+--------+--------+--------+
| username | 语文   | 数学   | 英语   |
+----------+--------+--------+--------+
| 张三     | 60     | NULL   | 100    |
| 李四     | 80     | 70     | NULL   |
| 王五     | 57     | 75     | 80     |
+----------+--------+--------+--------+

3.2 方法一：通过 UNION 语句

SQL 语句：

SELECT username, subject, source
FROM (
  SELECT username, '语文' AS subject, ` 语文 ` AS source FROM test_unpivot WHERE ` 语文 ` is not null
  UNION
  SELECT username, '数学' AS subject, ` 数学 ` AS source FROM test_unpivot WHERE ` 数学 ` is not null
  UNION
  SELECT username, '英语' AS subject, ` 英语 ` AS source FROM test_unpivot WHERE ` 英语 ` is not null
)
ORDER BY username;

结果：

+----------+---------+--------+
| username | subject | source |
+----------+---------+--------+
| 张三     | 语文    | 60     |
| 张三     | 英语    | 100    |
| 李四     | 语文    | 80     |
| 李四     | 数学    | 70     |
| 王五     | 英语    | 80     |
| 王五     | 语文    | 57     |
| 王五     | 数学    | 75     |
+----------+---------+--------+

3.3 方法二：通过 CROSS JOIN UNNEST 语句

SQL 语句：

SELECT t1.username, t2.subject, t2.source
FROM test_unpivot t1
CROSS JOIN UNNEST (array['语文', '数学', '英语'],
  array[` 语文 `, ` 数学 `, ` 英语 `]
) t2 (subject, source)
WHERE t2.source is not null

结果：

+----------+---------+--------+
| username | subject | source |
+----------+---------+--------+
| 张三     | 语文    | 60     |
| 张三     | 英语    | 100    |
| 李四     | 语文    | 80     |
| 李四     | 数学    | 70     |
| 王五     | 语文    | 57     |
| 王五     | 数学    | 75     |
| 王五     | 英语    | 80     |
+----------+---------+--------+

4. JSON 数据列展开

JSON 数据的表达能力非常灵活，因此在数据库和 SQL 中，常常需要处理 JSON 数据，常常碰到稍复杂的需求，就是将 JSON 数据中的某些属性字段，进行展开转换，转成行、列的关系型表达。

4.1 基本思路和步骤

  • 使用 JSON 函数，对 JSON 字符串进行解析和数据提取；
  • 提取、转换为 ARRAY 或者 MAP 的数据结构，如有需要，可以使用 Lambda 函数式表达式进行转换处理；
  • 利用 UNNEST 语法进行列展开。

下面以多个示例说明。

4.2 用 UNNEST 对 MAP 进行关系型展开

SQL 示例：

SELECT t.m, t.n
FROM (SELECT MAP(ARRAY['foo', 'bar'], ARRAY[1, 2]) as map_data
)
CROSS JOIN unnest(map_data) AS t(m, n);

结果：

+------+------+
| m    | n    |
+------+------+
| foo  |    1 |
| bar  |    2 |
+------+------+

4.3 用 UNNEST 对 JSON 数据进行关系型展开

SQL 示例：

SELECT json_extract(t.a, '$.a') AS a, 
       json_extract(t.a, '$.b') AS b
FROM (SELECT cast(json_extract('{"x":[{"a":1,"b":2},{"a":3,"b":4}]}', '$.x') 
           AS array<JSON>) AS package_array
)
CROSS JOIN UNNEST(package_array) AS t(a);

结果：

+------+------+
| a    | b    |
+------+------+
| 1    | 2    |
| 3    | 4    |
+------+------+

SQL 示例：

SELECT t.m AS _col1, t.n AS _col2
FROM (SELECT cast(json_extract('{"x":[{"a":1,"b":2},{"a":3,"b":4}]}', '$.x') 
           AS array<JSON>) AS array_1, 
           cast(json_extract('{"x":[{"a":5,"b":6}, {"a":7,"b":8}, {"a":9,"b":10}, {"a":11,"b":12}]}', '$.x') 
           AS array<JSON>) AS array_2
)
CROSS JOIN UNNEST(array_1, array_2) AS t(m, n);

结果：

+---------------+-----------------+
| _col1         | _col2           |
+---------------+-----------------+
| {"a":1,"b":2} | {"a":5,"b":6}   |
| {"a":3,"b":4} | {"a":7,"b":8}   |
| NULL          | {"a":9,"b":10}  |
| NULL          | {"a":11,"b":12} |
+---------------+-----------------+

SQL 示例：

SELECT json_extract(t.m, '$.a') AS _col1, 
       json_extract(t.m, '$.b') AS _col2, 
       json_extract(t.n, '$.a') AS _col3, 
       json_extract(t.n, '$.b') AS _col4 
FROM (SELECT cast(json_extract('{"x":[{"a":1,"b":2},{"a":3,"b":4}]}', '$.x') 
           AS array<JSON>) AS array_1, 
           cast(json_extract('{"x":[{"a":5,"b":6}, {"a":7,"b":8}, {"a":9,"b":10}, {"a":11,"b":12}]}', '$.x') 
           AS array<JSON>) AS array_2
)
CROSS JOIN UNNEST(array_1, array_2) AS t(m, n);

结果：

+-------+-------+-------+-------+
| _col1 | _col2 | _col3 | _col4 |
+-------+-------+-------+-------+
| 1     | 2     | 5     | 6     |
| 3     | 4     | 7     | 8     |
| NULL  | NULL  | 9     | 10    |
| NULL  | NULL  | 11    | 12    |
+-------+-------+-------+-------+

4.4 结合 Lambda 表达式，用 UNNEST 对 JSON 数据进行关系型展开

SQL 示例：

SELECT count(*) AS cnt, 
       package_name 
FROM ( 
    SELECT t.a AS package_name 
    FROM (SELECT transform(packages_map_array, x -> Element_at(x, 'packageName')) 
               AS package_array 
        FROM (SELECT cast(Json_extract(data_json, '$.packages') 
                   AS array<map<VARCHAR, VARCHAR>>) AS packages_map_array
            FROM (SELECT json_parse(data) AS data_json
                FROM ( 
                    SELECT '{"packages": [
                                {
                                  "appName": "铁路 12306",
                                  "packageName": "com.MobileTicket",
                                  "versionName": "4.1.9",
                                  "versionCode": "194"
                                },
                                {
                                  "appName": "QQ 飞车",
                                  "packageName": "com.tencent.tmgp.speedmobile",
                                  "versionName": "1.11.0.13274",
                                  "versionCode": "1110013274"
                                },
                                {
                                  "appName": "掌阅",
                                  "packageName": "com.chaozh.iReaderFree",
                                  "versionName": "7.11.0",
                                  "versionCode": "71101"
                                }
                             ]
                           }'
                    AS data 
                )
            )
        ) 
    ) AS x (package_array)
    CROSS JOIN UNNEST(package_array) AS t (a)
)
GROUP BY package_name 
ORDER BY cnt DESC;

结果：

+------+------------------------------+
| cnt  | package_name                 |
+------+------------------------------+
|    1 | com.MobileTicket             |
|    1 | com.tencent.tmgp.speedmobile |
|    1 | com.chaozh.iReaderFree       |
+------+------------------------------+

本文作者：julian.zhou

阅读原文

本文为云栖社区原创内容，未经允许不得转载。

退出移动版