dremio数据湖sql行列转换及转置

server/2024/9/23 7:22:24/

1、行转列 (扁平化)

数据准备 表 aa

1.1 cross join unnest

在Dremio中,UNNEST 函数用于将数组或复杂类型的列(如JSON、Map或Array类型)中的值“炸裂”(分解)成多行.

sql">with aa as (
select '上海' as city, ARRAY['浦东新区','黄浦区']        as area
union 
select '北京' as city, ARRAY['朝阳区','海淀区','昌平区'] as area
)
select city,area_b
from aa
CROSS JOIN UNNEST(aa.area) AS t(area_b)

1.2  flatten

将复合值分解为多行。FLATTEN 函数采用一 LIST 列并生成横向视图(即,包含引用 FROM 子句中它前面的其他表的相关性的内联视图)。

表达式的数据类型 必须为 LIST

sql">with aa as (
select '上海' as city,ARRAY['浦东新区', '黄浦区'] as areas
union 
select '北京' as city,ARRAY['朝阳区', '海淀区','昌平区'] as areas
)
SELECT city,FLATTEN(areas) AS area
FROM aa;

两种函数 都能 把 数组 行转为列,结果如下

然而我们的初始数据可能是 带有分割符的字符串 而不是 数组

dremio 提供 字符 转换 为 数组正则表达式函数  REGEXP_SPLIT ( ) 实例如下:

sql">with aa as(
select '上海' as id,'浦东新区,黄浦区'       as name
union
select '北京' as id,'朝阳区,海淀区,昌平区'  as name
)SELECT REGEXP_SPLIT(name, 'r(?<=,)(?<=,$)', 'ALL', 1) AS "list"
from aa

结果:

更多dremio 图标类型识别:dremio数据类型图标识别

2 、列转行 

数据准备

2.1  LISTAGG

 将一组行连接成一个字符串列表,并在它们之间放置一个分隔符。 返回字符类型

sql">with aa as (
select '上海' as city,'浦东区新' as area union select '上海' as city,'黄浦区' as area
union 
select '北京' as city,'朝阳区' as area   union select '北京' as city,'海淀区' as area
)
select city,LISTAGG(DISTINCT area, ' | ')
WITHIN GROUP (ORDER BY area) "city_list"
FROM aa
group by city

返回结果:

2.2 ARRAY_AGG

将提供的表达式聚合到一个数组中。返回数组类型

sql">with aa as(
select '上海' as id,'浦东新区'  as name union select '上海' as id,'黄浦区'  as name
union
select '北京' as id,'朝阳区'  as name union select '北京' as id,'海淀区'  as name
union
select '北京' as id,'昌平区'  as name
)
SELECT id, ARRAY_AGG(name) 
FROM aa 
GROUP BY id 

结果:

3、转置 ,反转置

数据准备:

3.1 转置 ( PIVOT )

sql">SELECT *
FROM aa
PIVOT (SUM(sales) FOR region IN ('North', 'South', 'East', 'West'))
order by product

结果:

3.2 反转置 (UNPIVOT

数据准备:

sql">SELECT product, region, sales
FROM aa
UNPIVOT (sales FOR region IN (North, South, East, West)) 
order by product 

结果:


http://www.ppmy.cn/server/36302.html

相关文章

设计模式之模板模式

模板模式&#xff08;Template Method Pattern&#xff09;是行为设计模式之一&#xff0c;它定义了一个操作中的算法骨架&#xff0c;而将一些步骤延迟到子类中实现。模板方法使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤&#xff0c;从而达到复用算法框架…

支付宝商户池!

什么是支付宝商户池&#xff1f; 支付宝商户池&#xff0c;作为支付宝生态系统中的一个重要组成部分&#xff0c;扮演着连接商家与消费者的桥梁角色。它是一个涵盖了众多商家信息的数据库&#xff0c;这些商家通过支付宝平台提供各种服务或商品&#xff0c;从而满足消费者的多…

《Redis使用手册之散列》

《Redis使用手册之散列》 **《Redis使用手册之散列》****散列简介****HSET&#xff1a;为字段设置值****HSETNX&#xff1a;只在字段不存在的情况下为它设置值****HGET&#xff1a;获取字段的值****HINCRBY&#xff1a;对字段存储的整数值执行加法或减法操作****HINCRBYFLOAT&a…

自定义指令基本用法

自定义指令基本用法 1.自定义指令案例一&#xff1a;输入框自动聚焦2.自定义指令生命周期和参数-修饰符3.封装全局自定义指令3.1 创建directive目录&#xff0c;创建index.js&#xff0c;创建format-xx文件3.1 main.js文件引入并传入全局对象3.3 组件中引用 自定义指令 Vue可以…

单目标问题的烟花优化算法求解matlab仿真,对比PSO和GA

目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.本算法原理 5.完整程序 1.程序功能描述 单目标问题的FW烟花优化算法求解matlab仿真,对比PSO和GA。最后将FW&#xff0c;GA&#xff0c;PSO三种优化算法的优化收敛曲线进行对比。 2.测试软件版本以及运行…

商城数据库88张表结构完整示意图51~60(十三)

五十一&#xff1a; 五十二&#xff1a; 五十三&#xff1a; 五十四&#xff1a; 五十五&#xff1a; 五十六&#xff1a; 五十七&#xff1a; 五十八&#xff1a; 五十九&#xff1a; 六十&#xff1a;

【前端】HTML实现个人简历信息展示页面

文章目录 前言一、 综合案例&#xff1a;个人简历信息展示页面 前言 这篇博客仅仅是对HTML的基本结构进行了一些说明&#xff0c;关于HTML的更多讲解以及CSS、Javascript部分的讲解可以关注一下下面的专栏&#xff0c;会持续更新的。 链接&#xff1a; Web前端学习专栏 下面我…

基于DE1-SOC的Nios V工程——My First Nios V

本教程演示如何在DE1-SOC开发板上创建一个基于Nios V 的SOPC系统&#xff0c;并在系统上运行Nios V软件工程。 一、Nios V简介 Nios V处理器是基于RISC-V规范的软知识产权(IP)处理器。 Nios V处理器系统相当于一个微控制器&#xff08;或“芯片级计算机”&#xff09;&#…