使用SELECT語句查詢資料 - ApsaraDB for ClickHouse

SELECT用於從一個表或者多個表中查詢資料。本文為您介紹SELECT的基本文法以及部分子句。

基本文法

在SELECT語句的文法中，除了運算式列表（expr_list），其他子句均為可選。更多資訊，請參見查詢文法。

文法

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
    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子句的作用是近似查詢。

重要

SAMPLE子句僅適用於*MergeTree類型的表且建表時已指定採樣運算式。

SAMPLE子句可以使用SAMPLE k來表示，其中k可以是比例，也可以是一個具體的數值。

k為比例時：
- k取值：0<k<1。
- 查詢將使用'k'作為百分比選取資料。例如，SAMPLE 0.1查詢只會檢索資料總量的10%。
- 可用於統計所有資料的近似值。計算公式：統計使用SAMPLE子句的結果的個數/k。
k為具體數值時：
- 查詢將使用'k'作為最大樣本數。例如，SAMPLE 10000000查詢只會檢索最多10,000,000行資料。
- 無法用於統計所有資料近似值。

樣本

以下為僅查詢10%資料的樣本。

count()的結果只是10%資料量的計數結果，想要統計所有資料的計數結果，需要將count()的結果手動乘以10，才能得到近似查詢的結果。

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;

ARRAY JOIN子句

ARRAY JOIN子句作用是與數群組類型或Nested資料類型的欄位進行JOIN操作。其作用與arrayJoin函數相似，但功能更廣泛，使用更靈活。

與數群組類型JOIN：將一行中的數組欄位展開為多行，每行包含數組中的一個元素，這樣就可以針對數組每個元素執行進一步的查詢或彙總操作。
與Nested類型JOIN：對於Nested類型的列，ARRAY 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 s, arr FROM arrays_test ARRAY JOIN arr;

結果集：

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

JOIN子句

JOIN語句用於根據特定的關聯條件，將兩個或多個表中的行進行組合，以構建更為複雜且富有價值的資料集。

JOIN語句中的Null處理，請參見join_use_nulls、Nullable、Null。
在多節點的社區版叢集中，查詢中使用多個分布式表JOIN或IN聯表查詢時，可能會報錯Exception: Double-distributed IN/JOIN subqueries is denied (distributed_product_mode = 'deny'). 。建議您在建表後，在業務使用過程中，避免對分布式表進行JOIN或IN子查詢，如果您期望使用此類子查詢，請參見分布式表如何可以使用子查詢。

WHERE子句

WHERE子句用過濾資料。

WHERE子句必須包含一個UInt8類型的運算式，該運算式通常是條件運算式。
在支援索引的表中，WHERE子句決定了查詢語句是否使用索引。

PREWHERE子句

PREWHERE子句與WHERE子句作用相似，用於查詢過濾資料。更多詳情，請參見PREWHERE。

說明

PREWHERE僅支援*MergeTree系列引擎。
在一個查詢中可以同時指定PREWHERE和WHERE，在這種情況下，PREWHERE優先於WHERE執行。
PREWHERE不適合用於已經存在於索引中的列，因為當列已經存在於索引中的情況下，只有滿足索引的資料區塊才會被讀取。
如果將'optimize_move_to_prewhere'設定為1，並且在查詢中不包含PREWHERE，則系統將自動的把適合PREWHERE運算式的部分從WHERE中抽離到PREWHERE中。

使用情境

過濾條件中有少量不適合索引過濾的列，但是這些過濾條件又有較強的過濾能力，此時可以使用PREWHERE，減少讀取的資料量。例如，在一個查詢中有很多列時，在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   │
└────────┘