开发者社区> julian.zhou> 正文

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

简介: 1. 简介 在数据库SQL处理中,常常有行转列(Pivot)和列转行(Unpivot)的数据处理需求。本文以示例说明在Data Lake Analytics(https://www.aliyun.com/product/datalakeanalytics)中,如何使用SQL的一些技巧,达到行转列(Pivot)和列转行(Unpivot)的目的。
+关注继续查看

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       |
+------+------------------------------+

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【SQL开发实战技巧】系列(二十六):数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
本篇文章讲解的主要内容是:***ROLLUP、UNION ALL是如何分别做分组合计的以及如何通过CUBE 、GROUPING、GROUPING_ID 识别哪些行是做汇总的结果行***
8 0
【SQL开发实战技巧】系列(十五):查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
本篇文章讲解的主要内容是:***计算部门中那个工资等级的员工最多、通过返回部门10最大工资所在行的员工名称小案例来讲解max/min() keep() over()、通过查询工资最高的人小案例来介绍fisrt_value、last_value、通过计算各个部门的工资合计以及各个部门合计工资占总工资的比例小案例来介绍如何计算百分比及ratio_to_report分析函数的使用***
9 0
【SQL系列】检索在另一张表中没有对应行的行
【SQL系列】检索在另一张表中没有对应行的行
14 0
【SQL系列】查找相同的行
【SQL系列】查找相同的行
22 0
【SQL系列】将一张表中的行复制到另一张表中
【SQL系列】将一张表中的行复制到另一张表中
31 0
【SQL系列】从表中随机返回n行数据
【SQL系列】从表中随机返回n行数据
14 0
【SQL系列】查找满足多个条件的行
【SQL系列】查找满足多个条件的行
18 0
SQl课程实验(外连接,聚集函数,分组,行数限制)
文章目录 1.看完这篇文章你会得到什么 2.实验内容 1. 查询没有任何学生选学的课程编号和课程名称及学分 2. 查询每个班学生入学成绩最高分和最低分的差距,列名为“最大分差” 3. 查询至少选修了三门课程的学生编号 4.统计各个班级男生和女生的平均年龄及人数。 5.查询至少被两名学生选修的课程名称 6. 查询计算机学院至少有两门课的成绩在80以上的女学生学号、姓名和出生日期及其期末成绩的最高分 7. 查询所选课程期末成绩平均分在85分以上的“女”学生学号、姓名、期末成绩平均分 8. 查询“马克思主义基本原理”期末成绩排在6-10名的学生学号、姓名和期末成绩
18 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
42 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
39 0
+关注
文章
问答
来源圈子
更多
相关文档: 云原生数据湖分析
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载