如何使用SELECT语句查询数据_云数据库 ClickHouse-阿里云帮助中心

基本语法

SELECT语句基本格式如下：

SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]

除了SELECT的表达式列表（expr_list），所有子句均为可选。本文对部分子句进行说明。更详细的说明，请参见查询语法。

简单查询语句示例：

SELECT
    OriginCityName,
    DestCityName,
    count(*) AS flights,
    bar(flights, 0, 20000, 40)
FROM ontime_distributed 
WHERE Year = 1988
GROUP BY OriginCityName, DestCityName 
ORDER BY flights DESC 
LIMIT 20;

SAMPLE子句

SAMPLE子句的作用为近似查询，仅适用于*MergeTree类型的表且建表时已指定采样表达式。

SAMPLE子句可以使用SAMPLE k来表示，其中k可以是0到1的小数值，或者是一个足够大的正整数值。

当k为0到1的小数时，查询将使用'k'作为百分比选取数据。例如，SAMPLE 0.1查询只会检索数据总量的10%。当k为一个足够大的正整数时，查询将使用'k'作为最大样本数。例如，SAMPLE 10000000查询只会检索最少10,000,000行数据。

示例：

SELECT
    Title,
    count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1
WHERE
    CounterID = 34
    AND toDate(EventDate) >= toDate('2013-01-29')
    AND toDate(EventDate) <= toDate('2013-02-04')
    AND NOT DontCountHits
    AND NOT Refresh
    AND Title != ''
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000;

在上述示例中，仅查询10%的数据。此时count()的结果只是10%数据量的计数结果，想要统计所有数据的计数结果，需要将count()的结果手动乘以10，才能得到近似查询的结果。

SAMPLE 10000000不适用于上述示例的场景。因为不知道样本数占总数的百分比，所以无法判断应该将结果乘以几。

如果我们能够看到所有可能存在表中的数据，那么在建表时使用相同的采样表达式就能够得到相同的结果。换句话说，系统在不同的时间，不同的服务器，不同表上总以相同的方式对数据进行采样。因此，使用相同的采样率得到的结果总是一致的。

例如，我们可以使用采样的方式获取到与不进行采样相同的用户ID的列表。这将表明，您可以在IN子查询中使用采样，或者使用采样的结果与其他查询进行关联。

ARRAY JOIN子句

ARRAY JOIN子句作用是与数组类型或Nested数据类型的字段进行Join。它与arrayJoin函数作用相似，但它的功能更广泛。

ARRAY JOIN本质上等同于INNER JOIN数组。

示例：

创建测试表arrays_test并插入数据：

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory

INSERT INTO arrays_test VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', [])

查询表arrays_test。

SELECT * FROM arrays_test;

返回结果：

┌─s───────┬─arr─────┐
│ Hello   │ [1,2]   │
│ World   │ [3,4,5] │
│ Goodbye │ []      │
└─────────┴─────────┘

使用ARRAY JOIN子句查询表arrays_test。

SELECT * FROM SELECT s, arr FROM arrays_test ARRAY JOIN arr;

返回结果：

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

Join语句Null的处理

JOIN语句中的Null处理，请参考join_use_nulls、Nullable、Null。

WHERE子句

WHERE子句用于过滤数据。WHERE子句必须包含一个UInt8类型的表达式。该表达式通常是条件表达式。

在支持索引的表中，WHERE子句决定了查询语句是否使用索引。

PREWHERE子句

PREWHERE仅支持*MergeTree系列引擎。PREWHERE子句与WHERE子句作用相似，不同之处在于表数据的读取。使用PREWHERE时，首先只读取PREWHERE表达式需要的列，然后再根据PREWHERE的执行结果读取其他列。

如果过滤条件中有少量不适合索引过滤的列，但是这些过滤条件又有较强的过滤能力，此时可以使用PREWHERE，减少读取的数据量。例如，在一个查询很多列时，在PREWHERE子句中对少量列进行过滤。

说明

在一个查询中可以同时指定PREWHERE和WHERE，在这种情况下，PREWHERE优先于WHERE执行。
PREWHERE不适合用于已经存在于索引中的列，因为当列已经存在于索引中的情况下，只有满足索引的数据块才会被读取。
如果将'optimize_move_to_prewhere'设置为1，并且在查询中不包含PREWHERE，则系统将自动的把适合PREWHERE表达式的部分从WHERE中抽离到PREWHERE中。

WITH TOTALS修饰符

如果Group By子句指定了WITH TOTALS修饰符，返回结果将多出一行。这一行包含所有关键列的默认值（零或者空值），以及所有行的聚合结果。

该行仅在JSON*, TabSeparated*, Pretty*输出格式中与其他行分开输出。

示例：

创建测试表t1并插入数据：

CREATE TABLE default.t1
(
    `a` Int32,
    `b` Int32,
    `c` String,
    `d` Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY (a, b)
SETTINGS index_granularity = 8192;

INSERT INTO t3(a, b, c, d) INSERT INTO t1(a, b, c, d) VALUES (3,4,'a','2022-09-01'),(12,7,'b','2022-07-01'),(21,4,'c','2022-05-01'),(11,1,'d','2022-06-01');

将表t1中的数据按列d进行分组，并返回列a分组汇总的结果。

select sum(a) from t1 group by d;

返回结果：

┌─sum(a)─┐
│   21   │
│   11   │
│   12   │
│   3    │
└────────┘

使用WITH TOTALS修饰符将表t1中的数据按列d进行分组，并返回列a分组汇总的结果。

 select sum(a) from t1 group by d with totals;

返回结果：

┌─sum(a)─┐
│   21   │
│   11   │
│   12   │
│   3    │
└────────┘
Totals:
┌─sum(a)─┐
│   47   │
└────────┘