clickhouse之函数（二）数组

谁借莪１个温暖的怀抱￠ 2023-07-01 04:27 18阅读 0赞

字段分隔，数组操作示例：

SELECT  arrayElement(splitByChar(',',colleague),1)FROM daily\_new\_clientrpt\_master  WHERE colleague != '' ORDER BY  collect\_timing DESC

SELECT  arrayElement(splitByChar(',',colleague),1),sum(stay\_time) FROM daily\_new\_clientrpt\_master  WHERE colleague != '' GROUP BY  arrayElement(splitByChar(',',colleague),1)

# ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][] #

# array #

## arrayElement(arr, n), operator arr\[n\] ##

从array中获取索引为n的元素，索引从1开始，支持负数，-1指最后一位；索引超过大小返回类型的默认值

## has(arr, elem) ##

arr中是否有elem元素，0无1有

## hasAll(set, subset) ##

检查arr是否是另一个arr的子集；0无1有，空arr是如何arr的子集，null是一个值，数组中元素顺序无关紧要

## hasAny（arr1，arr2） ##

数组是否有交集，0无1有，null是一个值，元素顺序无关

## countEqual(arr, x) ##

返回等于x的arr中元素的个数，null独立值处理，

arrayEnumerate（arr）

通常与array join一起使用。它允许在数据array join后对每个数组只计数一次

<table> 
 <tbody> 
  <tr> 
   <td> <pre><code>SELECT
    count() AS Reaches,
    countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
    GoalsReached,
    arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10</code></pre> </td> 
   <td> <pre><code>SELECT
    sum(length(GoalsReached)) AS Reaches,
    count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)</code></pre> </td> 
  </tr> 
  <tr> 
   <td>reach为转换次数(应用数组连接后接收的字符串)</td> 
   <td>Hits为页面浏览量(数组连接前的字符串)</td> 
  </tr> 
 </tbody> 
</table>

## arrayEnumerateUniq(arr, ...)？ ##

返回与源数组相同大小的数组，指示每个元素在具有相同值的元素中的位置

1.  `Returns an array the same size as the source array, indicating for each element what its position is among elements with the same value. For example: arrayEnumerateUniq([10, 20, 10, 30]) = [1, 1, 2, 1].`
2.   
3.  `This function is useful when using ARRAY JOIN and aggregation of array elements. Example:`
4.   
5.  `SELECT`
6.  `Goals.ID AS GoalID,`
7.  `sum(Sign) AS Reaches,`
8.  `sumIf(Sign, num = 1) AS Visits`
9.  `FROM test.visits`
10. `ARRAY JOIN`
11. `Goals,`
12. `arrayEnumerateUniq(Goals.ID) AS num`
13. `WHERE CounterID = 160656`
14. `GROUP BY GoalID`
15. `ORDER BY Reaches DESC`
16. `LIMIT 10`
17. `┌──GoalID─┬─Reaches─┬─Visits─┐`
18. `│ 53225 │ 3214 │ 1097 │`
19. `│ 2825062 │ 3188 │ 1097 │`
20. `│ 56600 │ 2803 │ 488 │`
21. `│ 1989037 │ 2401 │ 365 │`
22. `│ 2830064 │ 2396 │ 910 │`
23. `│ 1113562 │ 2372 │ 373 │`
24. `│ 3270895 │ 2262 │ 812 │`
25. `│ 1084657 │ 2262 │ 345 │`
26. `│ 56599 │ 2260 │ 799 │`
27. `│ 3271094 │ 2256 │ 812 │`
28. `└─────────┴─────────┴────────┘`
29. `In this example, each goal ID has a calculation of the number of conversions (each element in the Goals nested data structure is a goal that was reached, which we refer to as a conversion) and the number of sessions. Without ARRAY JOIN, we would have counted the number of sessions as sum(Sign). But in this particular case, the rows were multiplied by the nested Goals structure, so in order to count each session one time after this, we apply a condition to the value of the arrayEnumerateUniq(Goals.ID) function.`
30.  
31. `The arrayEnumerateUniq function can take multiple arrays of the same size as arguments. In this case, uniqueness is considered for tuples of elements in the same positions in all the arrays.`
32.  
33. `SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]) AS res`
34. `┌─res───────────┐`
35. `│ [1,2,1,1,2,1] │`
36. `└───────────────┘`
37. `This is necessary when using ARRAY JOIN with a nested data structure and further aggregation across multiple elements in this structure.`
38.

arrayPopBack（array）移除最后一位；arrayPopFront（array）移除第一位

arrayPushBack（array，single\_value）尾部添加原色，类型，添加null时，数组元素类型转为nullable

arrayPushFront（array，single\_value）

arrayResize(array, size\[, extender\])改变数组长度，size小于原数组 从右边开始截，长用extender或默认值填充，返回数组

arraySlice(array, offset\[, length\])返回数组，offset值1从左边开始，负数右边，length负数\[offset, array\_length - length)，省略返回offset开始到最后的元素数组

arrayUniq（arr，…）计算数组中不同元素的个数，如传递了多个参数，则计算多个数组中对应位置的不同元组元素的数量

# 分割合并 #

splitByChar（separator，s）

splitByString(separator, s)、arrayStringConcat(arr\[, separator\]) 分隔符默认为空，返回字符串

alphaTokens(s)从a-z和A-Z中返回连续字节 SELECT alphaTokens('abca1abc')  \['abca','abc'\]

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