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

灰灰鼠精灵

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

　　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)
　　结果如下：

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

　　原文链接