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       |
+------+------------------------------+
目录
相关文章
|
11月前
|
SQL JSON Java
知识分享之Golang——在Goland中快速基于JSON或SQL创建struct
知识分享之Golang篇是我在日常使用Golang时学习到的各种各样的知识的记录,将其整理出来以文章的形式分享给大家,来进行共同学习。欢迎大家进行持续关注。 知识分享系列目前包含Java、Golang、Linux、Docker等等。
339 0
知识分享之Golang——在Goland中快速基于JSON或SQL创建struct
|
SQL 消息中间件 JSON
Flink SQL JSON Format 源码解析
用 Flink SQL 解析 JSON 格式的数据是非常简单的,只需要在 DDL 语句中设置 Format 为 json 即可,像下面这样: CREATE TABLE kafka_source ( funcName STRING, data ROW<snapshots ARRAY<ROW<content_type STRING,url STRING>>,audio ARRAY<ROW<content_type STRING,url STRING>>>, resultMap ROW<`result` MAP<STRING,STRING>,isSuccess BOOLEAN
|
SQL JSON API
Flink SQL 解析嵌套的 JSON 数据
在日常的开发中,最常用的数据格式是 JSON ,并且有的时候 JSON 的格式是非常复杂的(嵌套的格式),那在 Flink SQL 中进行解析的时候也会相当麻烦一点,下面将会演示如何在 DDL 里面定义 Map、Array、Row 类型的数据,以及在 SQL 里面如何获里面的值 数据格式如下: 以下数据完全是自己造的,没有任何实际含义
Flink SQL 解析嵌套的 JSON 数据
|
存储 SQL 缓存
10倍性能提升!DLA SQL推出基于Alluxio的数据湖分析加速功能
在存储计算分离的场景下,通过网络从远端存储读取数据是一个代价较大的操作,往往会带来性能的损耗。以OSS为例,OSS数据读取延时通常较本地磁盘大很多,同时OSS对单个用户使用的带宽上限做了限制,这都会对数据分析的延时造成影响。在云原生数据湖分析(DLA)SQL引擎中,我们通过引入本地缓存机制,将热数据缓存在本地磁盘,拉近数据和计算的距离,减少从远端读取数据带来的延时和IO限制,实现更小的查询延时和更高的吞吐。
10倍性能提升!DLA SQL推出基于Alluxio的数据湖分析加速功能
|
SQL NoSQL 关系型数据库
DLA一键发现Tablestore元数据,轻松开启数据湖SQL分析之旅
为了使用DLA标准SQL分析Tablestore数据,往往需要很多步骤才能在DLA中创建相应元数据映射,进而使用SQL去分析Tablestore数据。 现在,数据湖分析DLA发布了一个免费的新功能可以一键快速发现Tablestore元数据,自动创建DLA库表映射到Tablestore的实例和表上,仅倒一杯水的时间即可开启SQL分析Tablestore数据之旅。
DLA一键发现Tablestore元数据,轻松开启数据湖SQL分析之旅
|
SQL 缓存 分布式计算
爆料:DLA SQL新体验,新数据源,还有一元购(内含福利)
DLA SQL全新改版,包括虚拟集群展示优化,提交SQL到虚拟集群、通过虚拟集群过滤历史SQL、置默认虚拟集群等,让用户能更加便捷的使用SQL虚拟集群;此外还支持了Kudu、OceanBase数据源。DLA SQL新推出1元首购原价315元的DLA 1000CU时资源包,福利链接:https://common-buy.aliyun.com/?commodityCode=openanalytics_cu_bag#/buy
爆料:DLA SQL新体验,新数据源,还有一元购(内含福利)
|
SQL 分布式计算 大数据
9.29直播预告|数据湖分析DLA之Serverless SQL(兼容Presto)技术解析
本次分享将向您介绍DLA SQL基于Presto引擎做的一系列优化比如多Coordinator、租户隔离、Coonector方面的优化细节,以及为何DLA SQL比您自建Presto性价比更高的奥秘。
810 0
9.29直播预告|数据湖分析DLA之Serverless SQL(兼容Presto)技术解析
|
SQL 存储 分布式计算
使用DLA SQL(兼容Presto) CU版分析HDFS数据
最近,DLA SQL发布了CU版,支持访问用户自建的HDFS,使得挖掘HDFS上面数据的价值成为可能。
使用DLA SQL(兼容Presto) CU版分析HDFS数据
|
SQL 关系型数据库 MySQL
DLA SQL分析函数:SQL语句审计与分析的利器
1. 简介 Data Lake Analytics(https://www.aliyun.com/product/datalakeanalytics)最新release一组SQL内置函数,用来进行SQL语句的分析、信息提取,方便用户对SQL语句进行语法层面的审计、分析,可以应用于很多安全、数据库日志分析等场景。
1333 0
推荐文章
更多