文档章节

SQL老司机,在SQL中计算 array & map & json数据

阿里云云栖社区
 阿里云云栖社区
发布于 06/22 17:41
字数 847
阅读 7
收藏 0

摘要: 场景 通常,我们处理数据,一列数据类型要么是字符串,要么是数字,这些都是primitive类型的数据。

场景

通常,我们处理数据,一列数据类型要么是字符串,要么是数字,这些都是primitive类型的数据。在某些比较复杂的业务场景下,我们会在一列中使用复杂的格式,例如数组array, 对象(map),json等格式来表示复杂的数据,例如:

__source__:  11.164.232.105
__tag__:__hostname__:  vm-req-170103232316569850-tianchi111932.tc
__topic__:  TestTopic_4
array_column:  [1,2,3]
double_column:  1.23
map_column:  {"a":1,"b":2}
text_column:  商品

array_column 是数组类型。假如,我们希望统计array_column中所有数值的汇总值,那么我们得遍历每一行的数组中的每一个元素。

unnest语法

  • unnest( array) as table_alias(column_name)
    表示把array类型展开成多行,行的名称为column_name。
  • unnest(map) as table(key_name, value_name)

    表示把map类型展开成多行,key的名称为key_name, value的名称为value_name
    

注意,由于unnest接收的是array或者map类型的数据,如果用户的输入是个字符串类型,那么要先转化成json类型,然后再转化成array类型或map类型,转化的方式是cast(json_parse(array_column) as array(bigint))

遍历数组每一个元素

使用SQL把array展开成多行:

* | select  array_column, a   from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)

上述SQL把数组展开成多行数字,unnest( cast( json_parse(array_column) as array(bigint) ) ) as t(a),unnest语法把数组展开,以t来命名新生成的表,使用a来引用展开后的列。结果如下图:

  • 统计数组中的每个元素的和
* | select   sum(a)    from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)

  • 按照数组中的每个元素进行group by计算
* | select   a, count(1)    from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)     group by a

遍历Map

  • 遍历Map中的元素
* | select  map_column , a,b    from log, unnest( cast( json_parse(map_column)   as map(varchar, bigint) ) ) as  t(a,b)

  • 按照Map的key进行group by 统计
* | select   key,  sum(value)    from log, unnest( cast( json_parse(map_column)   as map(varchar, bigint) ) ) as  t(key,value)    GROUP  BY  key

格式化显示histogram,numeric_histogram的结果

1.histogram

histogram函数类似于count group by 语法。语法参考文档

通常我们看到histogram的结果如下:

* | select histogram(method)

是一串json,无法配置视图展示,我们可以用unnest语法,把json展开成多行配置视图,例如:

* | select  key , value  from( select histogram(method) as his from log) , unnest(his ) as t(key,value)

接下来,可以配置可视化视图:

2. numeric_histogram

numeric_histogram语法是为了把数值列分配到多个桶中去,相当于对数值列进行group by,具体语法参考文档

* | select numeric_histogram(10,Latency)

numeric_histogram的输出如下:

为了格式化展示该结果,我们这样写SQL:

* |  select key,value from(select numeric_histogram(10,Latency) as his from log) , unnest(his) as t(key,value)

结果如下:

同时配置柱状图的形式展示:

原文链接

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

© 著作权归作者所有

共有 人打赏支持
阿里云云栖社区
粉丝 42
博文 603
码字总数 1318975
作品 0
朝阳
SQL老司机,在SQL中计算 array & map & json数据

场景 通常,我们处理数据,一列数据类型要么是字符串,要么是数字,这些都是primitive类型的数据。在某些比较复杂的业务场景下,我们会在一列中使用复杂的格式,例如数组array, 对象(map),j...

云雷
06/19
0
0
日志OLAP:在SQL中使用UDF, lambda函数使用案例

场景 日志服务内置了20+类SQL函数。面对用户复杂的业务场景,例如使用json来沉淀业务数据,普通的SQL函数可能就无法满足需求,需要一些用户自定义处理逻辑。为了处理json类的业务数据,我们可...

云雷
06/26
0
0
日志服务支持Json类型数据

JSON数据作为一种通用类型的数据类型,其自解析、灵活的特性,使其能够很好满足复杂场景下数据的记录需求,在很多日志内容中格式不固定的部分往往都是以json的形式进行记录,如将一次http请求...

suntingtao
02/09
0
0
2、创建DataFrame的方式

1:DataFrame其实它是通过RDD的map方法读取每一条数据,然后把他存到“case class”中。最后通过这个RDD的toDF方法产生的。 由于case class 中有属性字段,而且这些字段的类型都有了,是不是...

刘付kin
2016/11/29
7
0
利用php数组函数进行函数式编程

因为一个BUG, 我在一个摇摇欲坠,几乎碰一下就会散架的项目中某一个角落中发现下面这样一段代码 这段程序与那个BUG有密切的关系。 我来回反复的捉摸这段代码, 发现这段代码实现了两个功能 ...

科技探索者
2017/11/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring Boot 使用parent方式引用时 获取值属性方式默认@

采用官网提供的 引入依赖时,application.properties中的值变量,无法通过${}获取,这是由于Spring Boot 已经将字符方式调整为 <resource.delimiter>@</resource.delimiter>。故如果在,工程中...

翱翔fly
15分钟前
1
0
HTTPS证书验证流程及SSL证书生成步骤【附nginx开启https配置】

------------------------------------------------------------ HTTPS证书验证流程(极简化版) 1.客户端向服务端请求证书(server.crt) 2.服务端下发证书(server.crt) 3.客户端用预制的...

xiaomin0322
16分钟前
1
0
预编译sql语句和参数化sql能有效防止——sql注入攻击——

预编译和参数化处理sql原理类似: 参数化处理sql 是将参数进行了字符串化处理,把参数中的单引号,井号#,等都做了转义处理,进而在执行sql语句时不会被当做sql中的定界符和指定符来执行。 ...

fang_faye
17分钟前
1
0
springboot最全配置解释

​​​​​​​# ----------------------------------------# 核心属性# ----------------------------------------# 文件编码banner.charset= UTF-8# 文件位置banner.locati...

废柴大叔
18分钟前
72
1
(大致了解下)从单机到2000万QPS: 知乎Redis平台发展与演进之路

导读:知乎存储平台团队基于开源Redis 组件打造的知乎 Redis 平台,经过不断的研发迭代,目前已经形成了一整套完整自动化运维服务体系,提供很多强大的功能。本文作者是是该系统的负责人,文...

Ocean_K
25分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部