Clickhouse官方文档

ClickHouse的命令常见用法

1. 选择查询

1.1 整体结构

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]

1.2 WITH子句

• 使用常量表达式做变量

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound

• 从SELECT子句列表中逐出sum(bytes)表达式结果

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s

• 使用子查询的结果作为标量

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10

• 在子查询中重用表达式

WITH ['hello'] AS hello
SELECT
    hello,
    *
FROM
(
    WITH ['hello'] AS hello
    SELECT hello
)

1.3 JOIN子句

• 语法：

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

• JOIN的类型
  • INNER JOIN：只返回匹配的行
  • LEFT OUTER JOIN: 除了匹配的行之外，还返回左表中的非匹配行。
  • RIGHT OUTER JOIN:除了匹配的行之外，还返回右表中的非匹配行。
  • FULL OUTER JOIN:除了匹配的行之外，还会返回两个表中的非匹配行。
  • CROSS JOIN:产生整个表的笛卡尔积。
• 使用建议
  • 处理空单元格：使用join_use_nulls
  • 使用USING:指定的列 USING 两个子查询中必须具有相同的名称，并且其他列必须以不同的方式命名。
  • 对于多个 JOIN 单个子句 SELECT 查询:通过以所有列 * 仅在联接表时才可用，而不是子查询。

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

1.4 LIMIT子句

• LIMIT m:允许选择结果中的起始的m行

1.5 ORDER BY子句

• 降序排序：DESC
• 升序排序：ASC（默认）
• 与NAN、NULL的排序：首先是值，然后是NaN,然后是NULL

1.6 WHERE子句

• 必须包含一个表达式与UInt8类型，通常是一个带有比较和逻辑运算符的表达式。

1.7 DISTINCT子句

• 去重
• 可以与GROUP BY,ORDER BY 一起使用

SELECT DISTINCT a FROM t1 ORDER BY b ASC

1.8 FORMAT 子句

• 格式：FORMAT后面就是具体的格式
• 输入输出格式文档：https://clickhouse.com/docs/zh/interfaces/formats

SELECT * FROM nestedt FORMAT TSV

二、SQL语法

2.1 关键字大小写敏感

2.2 数字

• 首先，当做64位的有符号整数，使用stroull函数
• 如果失败，解析称64位无符号整数，依旧使用stroull函数
• 如果还失败，再解析成浮点型数值，使用函数strod
• 数据类型的链接：https://clickhouse.com/docs/zh/operations/system-tables/data_type_families#system_tables-data_type_families

三、函数

3.1 算数函数

• 适用于UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32x,Int64,Float32,Float64
• 加：plus(a,b)
• 减：minus(a,b)
• 乘：multiply(a,b)
• 除：divide(a,b)
• 向下取整的除(按绝对值)：intDiv(a,b)
• 它在除以0或将最小附属除以-1时返回0
• 取余：modulo(a,b)
• 除以0时结果返回0：moduloOrZero(a,b)
• 绝对值：abs(a)
• 最大公约数：lcm(a,b)
• 比较两个值并返回最大值：max2(value1,value2)
• 比较两个值并返回最小值：min2(value1,value2)

3.2 比较函数

• 等于：a=b和a==b运算符
• 不等于：a!=b和a<>b运算符
• 其他：<,>,<=,>=

3.3 逻辑函数

• 和：and
• 或：OR
• 非：NOT
• 异或：XOR

3.4 类型转换函数

• 整形之间的转换：toInt8(expr),toInt16(expr),toInt32(expr),toInt64(expr)
• 转整形失败就返回0：toInt(8|16|32|64)OrZero
  • 案例：toInt64OrZero('123abc123')
• 转整形失败就返回NULL:toInt(8|16|32|64)OrNull

• 转换到UInt类型：toUInt8(expr),toUInt16(expr),toUInt32(expr),toUInt64(expr)
• 转整形失败就返回0：toUInt(8|16|32|64)OrZero
• 转整形失败就返回NULL:toUInt(8|16|32|64)OrNull

• 转浮点型：toFloat(32|64)
• 转浮点型失败就返回0：toFloat(32|64)OrZero
• 转浮点型失败就返回NULL：toFloat(32|64)OrNull

• 转日期：toDate；toDateTime
• 转日期失败就返回0：toDateOrZero；toDateTimeOrZero
• 转日期失败就返回NULL：toDateOrNull；toDateTimeOrNull

• 转为Decimal(32|64|128)类型，其中精度为5，表示小数位的精度，value为数字或字符串：toDecimal32(value, S)
• 转失败就为0：toDecimal(32|64|128)OrZero

• 转换为字符串:toString()
• 将String类型的参数转换为FixedString(N),表示具有固定长度N的字符串：toFixedString(s,N)

• 当内容在找到第一个零字节处被截断：toStringCutToZero(s)

• 将'x'转换为't'数据类型： CAST(x,T)，CAST（x AS t）

• 数值类型的值转换为Interval类型的数据:toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second)

• 把String类型的时间日期转换为DateTime数据类型:parseDateTimeBestEffort(time_string [, time_zone])--time_string — 字符类型的时间和日期;time_zone — 字符类型的时区。
• 无法解析时返回NULL：parseDateTimeBestEffortOrNull
• 无法解析时返回0：parseDateTimeBestEffortOrZero

3.5 条件函数

• if函数：

  • 语法结构：SELECT if(cond,then,else)
  • 解释：如果条件cond的计算结果为非零值，则返回then的结果，如果cond为零或NULL，则返回else表达式的结果。
  • 案例：SELECT if(1,plus(2,2),plus(2,6))

• 三元运算符：

  • 语法结果：cond? then:else
  • 解释：cond!=0则返回then,cond=0则返回else

3.6 时间日期函数

• 返回服务器的时区：timeZone()--String类型
• 将Date或DateTime转换为指定的失去：toTimeZone(value,timezone)--DateTime类型

案例：

SELECT
    toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
    toTypeName(time_utc) AS type_utc,
    toInt32(time_utc) AS int32utc,
    toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
    toTypeName(time_yekat) AS type_yekat,
    toInt32(time_yekat) AS int32yekat,
    toTimeZone(time_utc, 'US/Samoa') AS time_samoa,
    toTypeName(time_samoa) AS type_samoa,
    toInt32(time_samoa) AS int32samoa
FORMAT Vertical;

实现结果：

Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

• toYear():获取Date或DateTime的年
• toMonth():获取Date或DateTime的月
• toHour():获取Date或DateTime的小时
• toMinute():获取Date或DateTime的分钟
• toSecond():获取Date或DateTime的秒

案例：

SELECT 
    toYear(now()),toQuarter(now()),toMonth(now()),toHour(now()),toMinute(now()),toSecond(now())

效果：

• toStartOfYear（）：将Date或DateTime向前取整到本年的第一天。 返回Date类型。
• toStartOfQuarter（）：将Date或DateTime向前取整到本季度的第一天。 返回Date类型。
• toStartOfMonth（）：将Date或DateTime向前取整到本月的第一天。 返回Date类型。
• toStartOfDay（）:将DateTime向前取整到今天的开始。
• toTime():将DateTime中的日期转换为一个固定的日期。

• date_trunc():将Date或DateTime按指定的单位向前取整到最接近的时间点。
  • date_trunc(unit,value[,timezone])
  • unit:second;minute;hour；day;week;month;quanter;year;
  • value: DateTime
  • timezone:返回值的时区

SELECT now(), date_trunc('hour', now());

• date_diff:返回两个日期或具有时间值的日期之间的差值。
  • date_diff('unit', startdate, enddate, [timezone])
  • unit:second;minute;hour；day;week;month;quanter;year;
  • startdate:Date或者DateTime
  • enddate:Date或者DateTime
  • timezone:返回值的时区

SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'));

• now():返回当前日期和时间
• today():返回当前日期
• yesterday():返回昨天的日期

• addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters：将一段时间间隔添加到Date/DateTime，然后返回Date/DateTime
  • 加上一段时间

WITH
    toDate('2018-01-01') AS date,
    toDateTime('2018-01-01 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time

┌─add_years_with_date─┬─add_years_with_date_time─┐
│          2019-01-01 │      2019-01-01 00:00:00 │
└─────────────────────┴──────────────────────────┘

• subtractYears,subtractMonths,subtractWeeks,subtractDays,subtractours,subtractMinutes,subtractSeconds,subtractQuarters：将Date/DateTime减去一段时间间隔，然后返回Date/DateTime。

WITH
    toDate('2019-01-01') AS date,
    toDateTime('2019-01-01 00:00:00') AS date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time

┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐
│               2018-01-01 │           2018-01-01 00:00:00 │
└──────────────────────────┴───────────────────────────────┘

• formatDateTime（）：根据给定的格式字符串来格式化时间。
  • formatDateTime(Time, Format[, Timezone])
    • %F:对应%Y-%m-%d，案例2018-01-02
    • %G：四位年份-2018
    • %Q：季度（1-4）
    • %Y：年
      案例代码：

SELECT formatDateTime(toDate('2010-01-04'), '%F'),formatDateTime(toDate('2010-01-04'), '%G'),formatDateTime(toDate('2010-01-04'), '%Q'),formatDateTime(toDate('2010-01-04'), '%Y')

执行结果：

• dateName:返回日期的指定部分。(返回String类型)
  • dateName(date_part, date)
  • date_part:'year','quarter','month','week', 'dayofyear', 'day', 'weekday', 'hour', 'minute', 'second'。
  • date:日期

这个函数用不了

3.7