clickhouse函数数组实践

Myth丶恋晨 2023-07-04 11:15 103阅读 0赞

一个字段值按照一个标记拆分成数组，然后用函数从数组中获取数据

如果依赖java程序解析字段，字段一般存json，如果依赖sql解析一般字段存a|b|c标识分割，用函数变成数组获取

原始数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][]

用|分割--不会丢失行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1][]

统计：哪个库导出指标量，库属于哪个部分

SELECT "所属部分","所属库",  
COUNT(  
  "指标代码"  
)  
FROM  
(SELECT  arrayElement(splitByChar('|',event\_property\_value),1) as "指标代码", arrayElement(splitByChar('|',event\_property\_value),2) as "指标名", arrayElement(splitByChar('|',event\_property\_value),3) as "所属库", arrayElement(splitByChar('|',event\_property\_value),4) as "所属部分" FROM  cms.daily\_new\_clientrpt\_slave  WHERE event\_property\_code != '' AND event\_property\_code='101101000800016006' ORDER BY  day DESC) T GROUP BY "所属库","所属部分"

效果图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2][]

关于多级分组 group by "所属库","所属部分"  ---前面的是一级分组，一级分组相同才按二级分组，如果一级分组已是最细，二级分组只是一个值而已

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200210142358976.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200210142541159.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200210142839437.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5,size_16,color_FFFFFF,t_70