Clickhouse之常用函数操作 X

常用函数汇总

[日期]

注：所有的时间日期函数都可以在第二个可选参数中接受时区参数。示例：Asia / Yekaterinburg。在这种情况下，它们使用指定的时区而不是本地（默认）时区。

now()         // 2022-08-08 17:19:00  取当前时间
toYear()      // 2022                             取日志中的年份
toMonth()  // 8                                   取日子中的月份
today()      // 2022-08-08                   今天的日期
yesterday()  // 2022-08-07                昨天的日期
toDayOfYear()  // 100                         取一年中的第几天
toDayOfWeek()  // 1                           取一周中的第几天
toHour()           17                              取小时
toMinute()      22                                取分钟
toSecond()    33                                 取秒
toStartOfYear()    2022-01-01              取一年中的第一天
toStartOfMonth()   2022-08-01           取当月的第一天
toStartOfQuarter(time),



formatDatetime(now(), '%Y-%m-%d')   // 2022-08-08  指定时间格式
toYYYYMM()                              //202004              
toYYYYMMDD()                            //20200401
toYYYYMMDDhhmmss()                      //20200401172540


toUnixTimestamp(time)              将datetime格式转换成Unix时间戳
toQuarter(time)                       季度(一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）)

　　

未来日期:

-- 第一种，日期格式（指定日期，需注意时区的问题）
WITH
    toDate('2019-09-09') AS date,
    toDateTime('2019-09-09 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 0) AS add_years_with_date_time;

-- 第二种，日期格式（当前，本地时间）
WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    now() as now_time,-- 当前时间
    -- 之后1年
    addYears(date, 1) AS add_years_with_date,                  
    addYears(date_time, 1) AS add_years_with_date_time,

    -- 之后1月
    addMonths(date, 1) AS add_months_with_date,                 
    addMonths(date_time, 1) AS add_months_with_date_time,

    --之后1周
    addWeeks(date, 1) AS add_weeks_with_date,                   
    addWeeks(date_time, 1) AS add_weeks_with_date_time,

    -- 之后1天
    addDays(date, 1) AS add_days_with_date,                     
    addDays(date_time, 1) AS add_days_with_date_time,

    --之后1小时
    addHours(date_time, 1) AS add_hours_with_date_time,  

    --之后1分中       
    addMinutes(date_time, 1) AS add_minutes_with_date_time,

    -- 之后10秒钟     
    addSeconds(date_time, 10) AS add_seconds_with_date_time,

     -- 之后1个季度    
    addQuarters(date, 1) AS add_quarters_with_date,            
    addQuarters(date_time, 1) AS add_quarters_with_date_time;

过去日期:

WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractQuarters(date, 1) AS subtract_Quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
    subtractMonths(date, 1) AS subtract_Months_with_date,
    subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
    subtractWeeks(date, 1) AS subtract_Weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
    subtractDays(date, 1) AS subtract_Days_with_date,
    subtractDays(date_time, 1) AS subtract_Days_with_date_time,
    subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
    subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
    subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;

计算时间差值:

-- 第一种：指定时间计算差值示例
WITH
    toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
    toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
SELECT
    dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
    dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
    dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
    dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
    dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
    dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
    dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;

-- 第二种：本地当前时间示例
WITH
    now() as date_time
SELECT
    dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
    dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
    dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
    dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
    dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
    dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
    dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;

 聚合函数组合器

聚合函数的名称可以附加一个后缀。 这改变了聚合函数的工作方式。

• If

• Array

• ForEach

• OrDefault

• OrNull

• Resample

• State

• Merge

• MergeState

2. 创建测试表

sql

-- 创建雇员表
CREATE TABLE emp(
    `empno` UInt16 NOT NULL COMMENT '员工编码',
    `ename` String COMMENT '员工姓名',
    `job` String COMMENT '职位',
    `mgr_no` UInt16 COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` decimal(7, 2) COMMENT '月薪',
    `comm` decimal(7, 2) COMMENT '奖金',
    `deptno` UInt8 COMMENT '所属部门编号'
)
ENGINE = MergeTree()
order by empno;

-- 向雇员表写入数据
insert into emp (empno, ename, job, mgr_no, hiredate, sal, comm, deptno) values 
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30),
(7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

-- 创建部门表
create table dept(
    `deptno` UInt8 not null COMMENT '部门编码',
    `dname` String COMMENT '部门名称',
    `loc` String COMMENT '部门所在地点'
)
ENGINE = MergeTree()
order by deptno;

-- 向部门表写入数据
insert into default.dept (deptno, dname, loc) values 
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

3. If

• -If 可以加到任何聚合函数之后。

• 加了-If之后聚合函数需要接受一个额外的参数，一个条件（Uint8类型），如果条件满足，那聚合函数处理当前的行数据，

• 如果不满足，那返回默认值（通常是0或者空字符串）。

• 使用条件聚合函数，您可以一次计算多个条件的聚合，而无需使用子查询和 JOIN例如，在Yandex.Metrica，条件聚合函数用于实现段比较功能。

• sumIf(column, cond)

sql

-- 
-- 将编号为 10 的 部分工资求和
select sumIf(sal,deptno=10) from emp;
┌─sumIf(sal, equals(deptno, 10))─┐
│                           8750 │
└────────────────────────────────┘
-- 同 
select sum(sal) from emp where deptno=10;
┌─sum(sal)─┐
│     8750 │
└──────────┘

• countIf(cond)

sql

-- 统计工资大于两千多人数
select countIf(sal,sal>2000) from emp;

┌─countIf(sal, greater(sal, 2000))─┐
│                                6 │
└──────────────────────────────────┘

-- 同
select count(1) from emp where sal > 2000;
┌─count()─┐
│       6 │
└─────────┘

• avgIf(x, cond)

sql

select avgIf(sal,sal>2000) from emp;

┌─avgIf(sal, greater(sal, 2000))─┐
│                         3212.5 │
└────────────────────────────────┘

4. Array

• -Array后缀可以附加到任何聚合函数。
• 在这种情况下，聚合函数采用的参数 Array(T) 类型（数组）而不是 T 类型参数。
• 如果聚合函数接受多个参数，则它必须是长度相等的数组。
• 在处理数组时，聚合函数的工作方式与所有数组元素的原始聚合函数类似。
• 任何聚合函数都可以通过增加后缀 Array, 来使原来的参数类型 T 变为新的参数类型 Array(T)。

如果和 -If 组合，Array 必须先来，然后 If. 例： uniqArrayIf(arr, cond)， quantilesTimingArrayIf(level1, level2)(arr, cond)。由于这个顺序，该 cond 参数不会是数组。

示例1：

示例2：

• sumArray(arr) 对 Arrays 中的所有元素进行求和，即 sum(arraySum(arr))。

sql

select sumArray(array(1,2,3,4,5));
┌─sumArray(array(1, 2, 3, 4, 5))─┐
│                             15 │
└────────────────────────────────┘

select sum(arraySum(array(1,2,3,4,5)));
┌─sum(arraySum(array(1, 2, 3, 4, 5)))─┐
│                                  15 │
└─────────────────────────────────────┘

• uniqArray(arr) – 计算``arr`中唯一元素的个数。这可以是一个更简单的方法：
• uniq(arrayJoin(arr))，但它并不总是可以添加 arrayJoin 到查询。

sql

select uniqArray(array(1,1,2,2,3,3));

┌─uniqArray(array(1, 1, 2, 2, 3, 3))─┐
│                                  3 │
└────────────────────────────────────┘

• -If 和 -Array 可以组合使用，但是 Array 必须在前，If 在后。

sql

-- sumArrayIf
select sumArrayIf(array(number),number % 2 == 0) from (
  select number from numbers(5)
) t ;

┌─sumArrayIf(array(number), equals(modulo(number, 2), 0))─┐
│                                                       6 │
└─────────────────────────────────────────────────────────┘

5. -ForEach

将对 table 使用的聚合函数，转换为对数组的聚合函数。对数组的每一项进行处理，返回一个结果数组。

sql

with (array(1,2,3) ,array(4,5,6)) as a
select sumForEach(a1) from (
  select a.1 as a1
  union all
  select a.2 as a1
) t ;
┌─sumForEach(a1)─┐
│ [5,7,9]        │
└────────────────┘

with (array(1,2,3) ,array(4,5,6,7)) as a
select countForEach(a1) from (
  select a.1 as a1
  union all
  select a.2 as a1
) t ;
┌─countForEach(a1)─┐
│ [2,2,2,1]        │
└──────────────────┘

6. -OrDefault

• xxxOrDefault(x)

如果聚合函数没有输入值，则使用此组合器它返回其返回数据类型的默认值。 适用于可以采用空输入数据的聚合函数。

-OrDefault 可与其他组合器一起使用。

语法

sql

SELECT avg(number), avgOrDefault(number) FROM numbers(0)
┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

SELECT avgOrDefaultIf(x, x > 10) FROM (
    SELECT toDecimal32(1.23, 2) AS x
);
┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                                 0 │
└───────────────────────────────────┘

7. -OrNull

• xxxOrNull(x)

• 更改聚合函数的行为。

• 此组合器将聚合函数的结果转换为 可为空 数据类型。 如果聚合函数没有值来计算它返回 NULL.

• -OrNull 可与其他组合器一起使用。

语法

sql

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10;
┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

-- -OrNull 可与其他组合器一起使用。 当聚合函数不接受空输入时，它很有用
SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)
┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

8. -Resample

• xxxResample(start, end, step)(<aggfunction_params>, resampling_key)

• 允许您将数据划分为组，然后单独聚合这些组中的数据。 通过将一列中的值拆分为间隔来创建组。

参数

• start — resampling_key 开始值。
• stop — resampling_key 结束边界。 区间内部不包含 stop 值，即 [start, stop).
• step — 分组的步长。 The aggFunction 在每个子区间上独立执行。
• resampling_key — 取样列，被用来分组.
• aggFunction_params — aggFunction 参数。

返回值

• aggFunction 每个子区间的结果，结果为数组。

示例

考虑一下 people 表具有以下数据的表结构：

sql

-- 将工资从 1000 到 400 的人分组，分组工资间隔为 1000
select 
  groupArrayResample(1000,4000,1000)(sal,toUInt16(sal)) as salGroup
from emp;

┌─salGroup───────────────────────────────────────────────────────┐
│ [[1600,1250,1250,1500,1100,1300],[2975,2850,2450],[3000,3000]] │
└────────────────────────────────────────────────────────────────┘

select 
  countResample(1000,4000,1000)(sal,toUInt16(sal)) as countGroup,
  avgResample(1000,4000,1000)(sal,toUInt16(sal)) as avgGroup
from emp;
┌─countGroup─┬─avgGroup─────────────────────────────────────┐
│ [6,3,2]    │ [1333.3333333333333,2758.3333333333335,3000] │
└────────────┴──────────────────────────────────────────────┘

9. State

返回的不是结果值，返回的是中间状态。 这个是与 AggregatingMergeTree 来配合使用的。

如果应用此combinator，则聚合函数不会返回结果值（例如唯一值的数量 uniq 函数），但是返回聚合的中间状态（对于 uniq，返回的是计算唯一值的数量的哈希表）。 这是一个 AggregateFunction(...) 可用于进一步处理或存储在表中以完成稍后的聚合。

要使用这些状态，请使用:

• AggregatingMergeTree 表引擎。
• finalizeAggregation 功能。
• runningAccumulate 功能。
• -Merge combinator
• -MergeState combinator

10. Merge

聚合函数会把中间状态会为参数，进行 Merge，来完成聚合，返回最终的结果值。

如果应用此组合器，则聚合函数将中间聚合状态作为参数，组合状态以完成聚合，并返回结果值。

11. MergeState

与 -Merge 类似，但是返回的不是结果值，而是类似于 -State 的中间状态。

以与-Merge 相同的方式合并中间聚合状态。 但是，它不会返回结果值，而是返回中间聚合状态，类似于-State。

更多操作:

• https://blog.csdn.net/u012111465/article/details/85250030
• https://www.hnbian.cn/posts/962697d2.html