ClickHouse 常用函数（工作笔记待补充）

ClickHouse 常用函数（工作笔记待补充）

1 时间函数

dateDiff

计算时间差：
select dateDiff('day',parseDateTimeBestEffort(toString(20210601)),today() ) as day_diff;

day_diff|
--------|
      30|

parseDateTimeBestEffort

select parseDateTimeBestEffort(toString(20210601));

parseDateTimeBestEffort(toString(20210601))|
-------------------------------------------|
                        2021-06-01 00:00:00|

toYear/toMonth/toYearWeek

获取日期的年月周

select 	parseDateTimeBestEffort(toString(20200101)) dayDate	, toYearWeek(dayDate, 3) weekInt,toYear(dayDate) yearInt, toMonth(dayDate) monthInt;

dayDate            |weekInt|yearInt|monthInt|
-------------------|-------|-------|--------|
2020-01-01 00:00:00| 202001|   2020|       1|

2 聚合函数

argMax

语法:argMax(arg, val)
计算 val 最大值对应的 arg 值。 如果 val 最大值存在几个不同的 arg 值，输出遇到的第一个值。
这个函数的Tuple版本将返回 val 最大值对应的元组。本函数适合和 SimpleAggregateFunction 搭配使用。
同一字段day的某一个值有多条记录，获取字段receivetime最大的一条记录

select argMax(reg.day, reg.receivetime) regDay,argMax(ul.day, reg.receivetime) activeDay,

SELECT argMax(user, salary), argMax(tuple(user, salary), salary), argMax(tuple(user, salary)) FROM salary;
┌─argMax(user, salary)─┬─argMax(tuple(user, salary), salary)─┬─argMax(tuple(user, salary))─┐
│ director             │ ('director',5000)                   │ ('director',5000)           │
└──────────────────────┴─────────────────────────────────────┴─────────────────────────────┘

uniqExact(x)

计算精确的排重值。
(case when dayDiff < 1 then NULL else IFNULL(uniqExact(activeuuid2), 0) end) retention2,

3 其他常用函数

full join

a full join b USING (column1,column2... )

groupArray

类似mysql的 group_concat()函数
作用：多行数据合成一行，得到数组字段
select pid,groupArray(chncode) chncodeArray,groupArray(chnname) chnnameArray from odsmysql_wan_promotion_channel_v3 where pid = 1 group by pid
 
pid|chncodeArray  |chnnameArray |
---|--------------|-------------|
  1|['1_2','1_58']|['测试','测试默认']|

if

控制条件分支。 与大多数系统不同，ClickHouse始终评估两个表达式 then 和 else。

语法
SELECT if(cond, then, else)
如果条件 cond 的计算结果为非零值，则返回表达式 then 的结果，并且跳过表达式 else 的结果（如果存在）。 如果 cond 为零或 NULL，则将跳过 then 表达式的结果，并返回 else 表达式的结果（如果存在）。

参数
cond – 条件结果可以为零或不为零。 类型是 UInt8，Nullable(UInt8) 或 NULL。
then - 如果满足条件则返回的表达式。
else - 如果不满足条件则返回的表达式。
返回值
该函数执行 then 和 else 表达式并返回其结果，这取决于条件 cond 最终是否为零。

示例
查询:
SELECT if(1, plus(2, 2), plus(2, 6))
结果:
┌─plus(2, 2)─┐
│          4 │
└────────────┘

multiIf

允许您在查询中更紧凑地编写CASE运算符。
multiIf(cond_1, then_1, cond_2, then_2...else)
参数:
cond_N — 函数返回then_N的条件。
then_N — 执行时函数的结果。
else — 如果没有满足任何条件，则为函数的结果。
该函数接受2N + 1参数。

返回值
该函数返回值«then_N»或«else»之一，具体取决于条件cond_N。

示例
SELECT
    left,
    right,
    multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result
FROM LEFT_RIGHT

┌─left─┬─right─┬─result──────────┐
│ ᴺᵁᴸᴸ │     4 │ Null value      │
│    1 │     3 │ left is smaller │
│    2 │     2 │ Both equal      │
│    3 │     1 │ left is greater │
│    4 │  ᴺᵁᴸᴸ │ Null value      │
└──────┴───────┴─────────────────┘

arrayJoin

这是一个非常有用的函数。
普通函数不会更改结果集的行数，而只是计算每行中的值（map）。
聚合函数将多行压缩到一行中（fold或reduce）。
’arrayJoin’函数获取每一行并将他们展开到多行（unfold）。
此函数将数组作为参数，并将该行在结果集中复制数组元素个数。
除了应用此函数的列中的值之外，简单地复制列中的所有值;它被替换为相应的数组值。
查询可以使用多个arrayJoin函数。在这种情况下，转换被执行多次。
请注意SELECT查询中的ARRAY JOIN语法，它提供了更广泛的可能性。

示例:

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
┌─dst─┬─\'Hello\'─┬─src─────┐
│   1 │ Hello     │ [1,2,3] │
│   2 │ Hello     │ [1,2,3] │
│   3 │ Hello     │ [1,2,3] │
└─────┴───────────┴─────────┘

案例

1 获取小时维度表

将数组的一行转换为多行数据

 SELECT toUInt8(arrayJoin([23 , 22 , 21 , 20 , 19 , 18 , 17 , 16 , 15 , 14 , 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0])) AS hour 
 
 hour|
----|
  23|
  22|
  21|
  20|
  19|
  18|
  17|
  16|
  15|
  14|
  13|
  12|
  11|
  10|
   9|
   8|
   7|
   6|
   5|
   4|
   3|
   2|
   1|
   0|

2 groupArray / groupUniqArray

Clickhouse> select province, groupArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupArray(city)─────────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang']                        │
│ Guangdong │ ['Guangzhou','Shenzhen','Dongguan','Zhuhai'] │
└───────────┴──────────────────────────────────────────────┘
 
插入一条重复的记录：
insert into t_city values('Hubei','Wuhan',now(),2);
 
可以看到Hubei有一个重复的wuhan
 
Clickhouse> select province, groupArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupArray(city)─────────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang','Wuhan']                │
│ Guangdong │ ['Guangzhou','Shenzhen','Dongguan','Zhuhai'] │
└───────────┴──────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.002 sec. 
 
可以使用函数groupUniqArray进行去重：
 
Clickhouse> select province, groupUniqArray(city) from t_city group by province;
 
SELECT 
    province, 
    groupUniqArray(city)
FROM t_city
GROUP BY province
 
┌─province──┬─groupUniqArray(city)─────────────────────────┐
│ Shanghai  │ ['Shanghai']                                 │
│ Hubei     │ ['Wuhan','Xiangyang']                        │
│ Guangdong │ ['Zhuhai','Dongguan','Guangzhou','Shenzhen'] │
└───────────┴──────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.003 sec. 

备注

1 同普通sql的区别

a. 同一级sql的字段可以继续使用

SELECT b.date day,
parseDateTimeBestEffort(toString(day)) dayDate,
toString(toYear(dayDate)) yearStr,
toMonth(dayDate) monthInt,
toWeek(dayDate) weekInt,
concat(yearStr,'-',IF(10>monthInt,'0',''),toString(monthInt),'月') AS monthStr,
concat(yearStr,'-',IF(10>weekInt,'0',''),toString(weekInt),'周') AS weekStr,
day AS period,

b. join 不能连接超过2个的子查询

报错：( ... ) a left join ( ... ) b left join ( ... ) c 
可以：( ( ... ) a left join ( ... ) b ) ab left join ( ... ) c 

ClickHouse官网

https://clickhouse.tech/docs