526互联

Clickhouse官方文档

发布时间 2023-10-31 17:39:01作者: Trouvaille_fighting

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