DLA SQL技巧:行、列转换和JSON数据列展开
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
本文为云栖社区原创内容,未经允许不得转载。
转载自:https://segmentfault.com/a/1190000019533899