clickhouse學習筆記（五）SQL操作

目錄

一、增

二、刪改

三、查詢以及各種子句

1、with子句

?a、表達式為常量

b、表達式為函數調用

?c、表達式為子查詢

2、from子句?

3、array join子句?

a、INNER ARRAY JOIN

b、LEFT ARRAY JOIN

c、數組的一些函數

groupArray

??groupUniqArray

arrayFlatten?

splitByChar?

arrayJoin?

arrayMap

?嵌套類型

4、 join子句

連接精度

?連接類型

注意事項

5、WHERE 與 PREWHERE 子句?

6，GROUP BY 子句

? WITH ROLLUP

WITH CUBE

WITH TOTALS

7、having子句

8、ORDER BY子句

NULLS LAST

NULLS FIRST

9、limit by? 子句

10、 limit 子句

11、select和distinct子句?

一、增

?INSERT語句支持三種語法范式

第一種使用values格式

-- 中括號表示里面的內容可以省略
INSERT INTO [db.]table_name [(col1, col2, col3...)] VALUES (val1, val2, val3, ...), (val1, val2, val3, ...), ...

使用 VALUES 格式的語法寫入數據時，還支持加入表達式或函數，例如：

INSERT INTO partizion_v2 VALUES('matsuri', toString(1+2), now())

第二種使用自定格式的語法：

INSERT INTO [db.]table_name [(col1, col2, col3...)] FORMAT format_name data_set

例子如下

INSERT INTO partition_v2 FORMAT CSV \'mea', 'www.mea.com', '2019-01-01''nana', 'www.nana.com', '2019-02-01''matsuri', 'www.matsuri.com', '2019-03-01'

第三種使用select子句形式的語法：

INSERT INTO [db.]table_name [(col1, col2, col3...)] SELECT ...

二、刪改

ClickHouse 不是以事務為中心的數據庫系統，它主要設計用于在線分析處理（OLAP）場景，強調的是高性能的讀取和聚合查詢，而不是復雜的事務處理。因此，ClickHouse 不支持傳統意義上的 DELETE 和 UPDATE 操作，也不支持事務特性。

然而，ClickHouse 提供了一種稱為 Mutation 的機制，允許用戶進行類似 DELETE 和 UPDATE 的操作。Mutation 語句的執行是一個異步的后臺過程，語句被提交之后就會立即返回。所以這并不代表具體邏輯已經執行完畢，它的具體執行進度需要通過 system.mutations 系統表查詢

Mutation 是通過 ALTER TABLE 語句實現的，使用方法例如：

DELETE 語句的完整語法如下所示：

ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
案例如下：刪除id=xxx的
ALTER TABLE partition_v2 DELETE WHERE ID ='xxx'

UPDATE 支持在一條語句中同時定義多個修改字段，但是分區鍵和主鍵不能作為修改字段。修改語句如下：

ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr

三、查詢以及各種子句

注意：clickhouse對于sql語句的解析是大小寫敏感的，ClickHouse 的類型也大小寫敏感，比如：UInt8 不可以寫成 uint8，String 不可以寫成 string； 但關鍵字大小寫不敏感，例如select a 和select A 意義不同，但是min max等大小寫不敏感

1、with子句

格式為：with 表達式? as var? ?

作用就是增加可讀性和可維護性

一個子句可以為多個表達式起名例如：WITH 1 AS a, 2 AS b SELECT a + b;

?a、表達式為常量

常量可以是整數，字符串，浮點數，甚至數組，都可以

使用方法例如：

with 10 as startselect number from system.numbers  where number>start limit 7
b、表達式為函數調用
WITH SUM(data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format
FROM system.columns
GROUP BY database
ORDER BY bytes DESC/*
┌─database─┬─format───┐
│ system   │ 5.32 GiB │
│ default  │ 0.00 B   │
└──────────┴──────────┘
*/

如果不使用 WITH 子句，那么 SELECT 里面出現的就是?formatReadableSize(SUM(data_uncompressed_bytes))，這樣讀起來不是很方便，所以使用 WITH 子句將里面的聚合函數調用起一個名字叫 bytes，那么后面的查詢直接使用 bytes 即可。

?c、表達式為子查詢

-- SELECT sum(data_uncompressed_bytes) FROM system.columns 會得到一個數值
-- 因此本質上和表達式為常量是類似的，只不過多了一個計算的過程
WITH (SELECT sum(data_uncompressed_bytes) FROM system.columns) AS total_bytes
SELECT database, (sum(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC
/*
┌─database─┬─database_disk_usage─┐
│ system   │                 100 │
│ default  │                   0 │
└──────────┴─────────────────────┘
*/

注意：表達式只能返回的數據不能超過 1 行，否則會拋出異常，且不可以放在from后面作為臨時表使用，如果需要多個值可以放在一個容器（列表、集合、字典等等）里面

而postgresql是可以返回任何數據，行數不限，并且可以放在from后面當臨時表，

與postgresql的命名也不同，clickhouse別名在as后面，postgresql在as前面

2、from子句?

from子句表示從何處讀取數據，目前支持3種形式

從數據表中讀取SELECT name FROM people從子查詢中讀取SELECT max_id FROM (SELECT max(id) AS max_id FROM people)從表函數中讀取SELECT number FROM numbers(N) -- 會返回 0 到 N - 1

3、array join子句?

首先造一個包含array數組字段的測試表

CREATE TABLE t1 (title String,value Array(UInt8)
) ENGINE = Memory();-- 然后寫入數據
INSERT INTO t1 VALUES ('food', [1, 2, 3]), ('fruit', [3, 4]), ('meat', []);-- 查詢
SELECT * FROM t1;
/*
┌─title─┬─value───┐
│ food ?│ [1,2,3] │
│ fruit │ [3,4] ? │
│ meat ?│ [] ? ? ?│
└───────┴─────────┘
*/

?在一條 SELECT 語句中，只能存在一個 ARRAY JOIN（使用子查詢除外），目前支持 INNER 和 LEFT 兩種 JOIN 策略：

a、INNER ARRAY JOIN

ARRAY JOIN 在默認情況下使用的是 INNER JOIN 策略，例如下面的語句：

SELECT title, value FROM t1 ARRAY JOIN value;
/*
┌─title─┬─value─┐
│ food ?│ ? ? 1 │
│ food ?│ ? ? 2 │
│ food ?│ ? ? 3 │
│ fruit │ ? ? 3 │
│ fruit │ ? ? 4 │
└───────┴───────┘
*/

從查詢結果可以發現，最終的數據基于 value 數組被展開成了多行，并且排除掉了空數組，同時會自動和其它字段進行組合（相當于按行合并）。在使用 ARRAY JOIN 時，如果還想訪問展開前的數組字段，那么只需為原有的數組字段添加一個別名即可，例如：

?-- 如果不給 ARRAY JOIN 后面的 value 起一個別名，那么 value 就是展開后的結果
-- 如果給 ARRAY JOIN 后面的 value 起一個別名 val，那么 value 就還是展開前的數組字段
-- 而 val 才是展開后的結果，所以再反過來，讓 val 出現在 SELECT 中即可
SELECT title, value, val FROM t1 ARRAY JOIN value AS val;
/*
┌─title─┬─value───┬─val─┐
│ food ?│ [1,2,3] │ ? 1 │
│ food ?│ [1,2,3] │ ? 2 │
│ food ?│ [1,2,3] │ ? 3 │
│ fruit │ [3,4] ? │ ? 3 │
│ fruit │ [3,4] ? │ ? 4 │
└───────┴─────────┴─────┘
*/

?我們看到 ClickHouse 的確是當之無愧的最強 OLAP 數據庫，不單單是速度快，最重要的是，它提供的查詢語法也很方便。如果你用過 Hive 的話，會發現這里特別像里面的 lateral view explode 語法。

b、LEFT ARRAY JOIN

ARRAY JOIN 子句支持 LEFT 連接策略，例如執行下面的語句：

SELECT title, value, val FROM t1 LEFT ARRAY JOIN value AS val;
/*
┌─title─┬─value───┬─val─┐
│ food ?│ [1,2,3] │ ? 1 │
│ food ?│ [1,2,3] │ ? 2 │
│ food ?│ [1,2,3] │ ? 3 │
│ fruit │ [3,4] ? │ ? 3 │
│ fruit │ [3,4] ? │ ? 4 │
│ meat ?│ [] ? ? ?│ ? 0 │
└───────┴─────────┴─────┘
*/

在改為 LEFT 連接查詢后，可以發現，在 INNER JOIN 中被排除掉的空數組出現在了返回的結果集中。但此時的 val 是零值。

c、數組的一些函數

有如下表

SELECT * FROM t2;
/*
┌─────────dt─┬─cash───────┐
│ 2020-01-01 │ [10,10,10] │
│ 2020-01-02 │ [20,20,20] │
│ 2020-01-01 │ [10,10,10] │
│ 2020-01-02 │ [20,20] ? ?│
│ 2020-01-03 │ [] ? ? ? ? │
│ 2020-01-03 │ [30,30,30] │
└────────────┴────────────┘
*/

groupArray

它是把多行數據合并成一個數組，相當于是聚合函數的一種

SELECT dt, groupArray(cash) FROM t2 GROUP BY dt;
/*
┌─────────dt─┬─groupArray(cash)────────┐
│ 2020-01-01 │ [[10,10,10],[10,10,10]] │
│ 2020-01-02 │ [[20,20,20],[20,20]] ? ?│
│ 2020-01-03 │ [[],[30,30,30]] ? ? ? ? │
└────────────┴─────────────────────────┘
*/

??groupUniqArray

在組合的時候會對元素進行去重

SELECT dt, groupUniqArray(cash) FROM t2 GROUP BY dt;
/*
┌─────────dt─┬─groupUniqArray(cash)─┐
│ 2020-01-01 │ [[10,10,10]] ? ? ? ? │
│ 2020-01-02 │ [[20,20],[20,20,20]] │
│ 2020-01-03 │ [[],[30,30,30]] ? ? ?│
└────────────┴──────────────────────┘
*/

arrayFlatten?

類似于flatmap，將數組扁平化

SELECT dt,?groupArray(cash),arrayFlatten(groupArray(cash)) FROM t2 GROUP BY dt;
SELECT dt, groupUniqArray(cash) FROM t2 GROUP BY dt;
/*
┌─────────dt─┬─groupUniqArray(cash)─┐─arrayFlatten(groupArray(cash))─┐
│ 2020-01-01 │ [[10,10,10]，[10,10,10]] ? ? ? ? │ [10,10,10,10,10,10] ? ? ? ? │
│ 2020-01-02 │ [[20,20],[20,20,20]] ? ? ? ? ? ? │ [20,2020,20,20]    │
│ 2020-01-03 │ [[],[30,30,30]] ? ? ? ? ? ? ? ? ?│ [30,30,30] ? ?     │
└────────────┴──────────────────────------------┘──────────────────────┘
*/

splitByChar?

將字符串按照指定字符分割成數組：

SELECT splitByChar('^', 'komeiji^koishi');
/*
┌─splitByChar('^', 'komeiji^koishi')─┐
│ ['komeiji','koishi'] ? ? ? ? ? ? ? │
└────────────────────────────────────┘
*/

arrayJoin?

該函數和 ARRAY JOIN 子句的作用非常類似：

?SELECT * FROM t1;
/*
┌─title─┬─value───┐
│ food ?│ [1,2,3] │
│ fruit │ [3,4] ? │
│ meat ?│ [] ? ? ?│
└───────┴─────────┘
*/select title ,arrayjoin(value) from t1;/*
┌─title─┬─arrayjoin(value)─┐
│ food ?│ ? ? 1 │
│ food ?│ ? ? 2 │
│ food ?│ ? ? 3 │
│ fruit │ ? ? 3 │
│ fruit │ ? ? 4 │
└───────┴───────┘
*/

arrayMap

對數組中的每一個元素都以相同的規則進行映射：

-- arrayMap(x -> x * 2, value) 表示將 value 中的每一個元素都乘以 2，然后返回一個新數組
-- 而 mapV 就是變換過后的新數組，直接拿來用即可
SELECT title, arrayMap(x -> x * 2, value) AS mapV, v
FROM t1 LEFT ARRAY JOIN mapV as v
/*
┌─title─┬─mapV────┬─v─┐
│ food  │ [2,4,6] │ 2 │
│ food  │ [2,4,6] │ 4 │
│ food  │ [2,4,6] │ 6 │
│ fruit │ [6,8]   │ 6 │
│ fruit │ [6,8]   │ 8 │
│ meat  │ []      │ 0 │
└───────┴─────────┴───┘
*/-- 另外展開的字段也可以不止一個
SELECT title, arrayMap(x -> x * 2, value) AS mapV, v,value, v_1
FROM t1 LEFT ARRAY JOIN mapV as v, value AS v_1
/*
┌─title─┬─mapV────┬─v─┬─value───┬─v_1─┐
│ food  │ [2,4,6] │ 2 │ [1,2,3] │   1 │
│ food  │ [2,4,6] │ 4 │ [1,2,3] │   2 │
│ food  │ [2,4,6] │ 6 │ [1,2,3] │   3 │
│ fruit │ [6,8]   │ 6 │ [3,4]   │   3 │
│ fruit │ [6,8]   │ 8 │ [3,4]   │   4 │
│ meat  │ []      │ 0 │ []      │   0 │
└───────┴─────────┴───┴─────────┴─────┘
*/
?嵌套類型

在寫入嵌套數據類型時，記得同一行數據中各個數組的長度需要對齊，而對多行數據之間的數組長度沒有限制，否則會報錯：如下

CREATE TABLE t3(title String,nested Nested(v1 UInt32,v2 UInt64)
) ENGINE = Log();-- 接著寫入測試數據
-- 在寫入嵌套數據類型時，記得同一行數據中各個數組的長度需要對齊，而對多行數據之間的數組長度沒有限制
INSERT INTO t3
VALUES ('food', [1, 2, 3], [10, 20, 30]),('fruit', [4, 5], [40, 50]),('meat', [], [])
INSERT INTO t3
VALUES ('food', [1, 2, 3], [10, 20, 30,40]),('fruit', [4, 5], [40, 50]),('meat', [], [])當數組大小不同時如上，會報錯，當然不同行的數組大小可以不同，例如food和fruit
SQL 錯誤 [190]: ClickHouse exception, code: 190, host: 192.168.81.15, port: 8123; Code: 190. DB::Exception: Elements 'nested.v1' and 'nested.v2' of Nested data structure 'nested' (Array columns) have different array sizes. (SIZES_OF_ARRAYS_DOESNT_MATCH) (version 22.1.3.7 (official build))

對嵌套類型數據的訪問，ARRAY JOIN 既可以直接使用字段列名：

-- nested 只有 v1 和 v2
-- 所以 ARRAY JOIN nested.v1, nested.v2 等價于 ARRAY JOIN nested
SELECT title, nested.v1, nested.v2 FROM t3 ARRAY JOIN nested.v1, nested.v2
/*
┌─title─┬─nested.v1─┬─nested.v2─┐
│ food  │         1 │        10 │
│ food  │         2 │        20 │
│ food  │         3 │        30 │
│ fruit │         4 │        40 │
│ fruit │         5 │        50 │
└───────┴───────────┴───────────┘
*/

?嵌套類型也支持 ARRAY JOIN 部分嵌套字段，可以看到，在這種情形下，只有被 ARRAY JOIN 的數組才會展開。

SELECT title, nested.v1, nested.v2 FROM t3 ARRAY JOIN nested.v1
/*
┌─title─┬─nested.v1─┬─nested.v2──┐
│ food  │         1 │ [10,20,30] │
│ food  │         2 │ [10,20,30] │
│ food  │         3 │ [10,20,30] │
│ fruit │         4 │ [40,50]    │
│ fruit │         5 │ [40,50]    │
└───────┴───────────┴────────────┘
*/

?在查詢嵌套類型時也能夠通過別名的形式訪問原始數組：

SELECT title, nested.v1, nested.v2, n.v1, n.v2  
from t3 ARRAY JOIN nested AS n;
/*
┌─title─┬─nested.v1─┬─nested.v2──┬─n.v1─┬─n.v2─┐
│ food  │ [1,2,3]   │ [10,20,30] │    1 │   10 │
│ food  │ [1,2,3]   │ [10,20,30] │    2 │   20 │
│ food  │ [1,2,3]   │ [10,20,30] │    3 │   30 │
│ fruit │ [4,5]     │ [40,50]    │    4 │   40 │
│ fruit │ [4,5]     │ [40,50]    │    5 │   50 │
└───────┴───────────┴────────────┴──────┴──────┘
*/

4、 join子句

JOIN 子句可以對左右兩張表的數據進行連接，它的語法包含連接精度和連接類型兩部分。

連接精度

連接精度決定了 JOIN 查詢在連接數據時所使用的策略，目前支持 ALL、ANY 和 ASOF 三種類型（還有兩種類型SEMI 和 ANTI只能用在left join和right join上面）。如果不主動聲明，則默認是 ALL

舉個例子有如下表數據：

SELECT * FROM tbl_1;
/*
┌─id─┬─code1─┬─count─┐
│ ?1 │ A001 ?│ ? ?30 │
│ ?2 │ A002 ?│ ? ?28 │
│ ?3 │ A003 ?│ ? ?32 │
└────┴───────┴───────┘
*/

SELECT * FROM tbl_2;
/*
┌─id─┬─code2─┬─count─┐
│ ?1 │ B001 ?│ ? ?35 │
│ ?1 │ B001 ?│ ? ?29 │
│ ?3 │ B003 ?│ ? ?31 │
│ ?4 │ B004 ?│ ? ?38 │
└────┴───────┴───────┘
*/

下面進行測試

SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 AS t1 
ALL INNER JOIN tbl_2 AS t2
ON t1.id = t2.id;
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/
-- 一切正常，跟一般的關系型數據庫是類似的，但如果將 ALL 改成 ANY
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 AS t1 
ANY INNER JOIN tbl_2 AS t2
ON t1.id = t2.id;
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/

除了 ALL 和 ANY 之外還有一個 ASOF，ALL 還是 ANY，在連接的時候必須是等值連接。但 ASOF 表示模糊連接，例如??t1.id >= t2.id 例子如下

SELECT t1.id, t1.code1, t2.code2, t1.count AS count1, t2.count AS count2
FROM tbl_1 AS t1 
ASOF INNER JOIN tbl_2 AS t2
ON t1.id = t2.id AND t1.count > t2.count;
/*
┌─id─┬─code1─┬─code2─┬─count1─┬─count2─┐
│  1 │ A001  │ B001  │     30 │     29 │
│  3 │ A003  │ B003  │     32 │     31 │
└────┴───────┴───────┴────────┴────────┘
*/

SEMI 和 ANTI

我們之前說連接精度不止 ALL、ANY、ASOF 三種，還有 SEMI 和 ANTI，只不過這兩個比較特殊，因為它們只能用在 LEFT JOIN 和 RIGHT JOIN 上面，所以我們單獨介紹。

  • t1 SEMI LEFT JOIN t2 USING(id)：遍歷 t1 中的 id，如果存在于 t2 中，則輸出
  • t1 SEMI RIGHT JOIN t2 USING(id)：遍歷 t2 中的 id，如果存在于 t1 中，則輸出
  • t1 ANTI LEFT JOIN t2 USING(id)：遍歷 t1 中的 id，如果不存在于 t2 中，則輸出
  • t1 ANTI RIGHT JOIN t2 USING(id)：遍歷 t2 中的 id，如果不存在于 t1 中，則輸出

這個 SEMI 的功能貌似有些重復了，因為我們使用 ALL 和 ANY 完全可以取代。其實如果你用過 hive 的話，會發現 SEMI LEFT JOIN 和 ANTI LEFT JOIN 是 IN/EXISTS 的一種更加高效的實現：

結論：

1，如果左表內的一行數據，在右表中有多行數據與之連接匹配，那么當連接精度為 ALL，會返回右表中全部連接的數據；

2，當連接精度為 ANY，會僅返回右表中第一行連接的數據

3，如果連接精度為 ASOF，那么允許在等值連接條件后面追加一個非等值連接，所以上面的 t1.id = t2.id 是等值連接，t1.count > t2.count 是非等值連接。但需要注意的是：使用非等值連接時，這個非等值可以是 >、>=、<、<=，但不能是 !=；并且對于 ASOF 而言，連接條件必須是等值連接和非等值連接的組合，兩者缺一不可。

?連接類型
-- 省略連接精度，默認為 ALL
-- 左連接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 LEFT JOIN tbl_2 t2 
USING(id); -- 等價于 t1.id = t2.id
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  2 │ A002  │       │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/-- 右連接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 RIGHT JOIN tbl_2 t2 
USING(id);
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
┌─id─┬─code1─┬─code2─┐
│  4 │       │ B004  │
└────┴───────┴───────┘
*/-- 全連接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 FULL JOIN tbl_2 t2 
USING(id);
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  2 │ A002  │       │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
┌─id─┬─code1─┬─code2─┐
│  4 │       │ B004  │
└────┴───────┴───────┘
*/

和關系型數據庫類似，但有一點區別，就是當沒有與之匹配的記錄時，會使用對應類型的空值進行補全，而不是 Null。這里沒有指定連接精度，默認為 ALL

注意事項

最后，還有兩個關于 JOIN 查詢的注意事項。

1. 關于性能

????????最后，還有兩個關于 JOIN 查詢的注意事項。為了能夠優化 JOIN 查詢性能，首先應該遵循左大右小的原則，即數據量小的表要放在右側。這是因為在執行 JOIN 查詢時，無論使用的是哪種連接方式，右表都會被全部加載到內存中與左表進行比較。

其次，JOIN 查詢目前沒有緩存的支持，這意味著每一次 JOIN 查詢，即便是連續執行相同的 SQL，也都會生成一次全新的執行計劃。如果應用程序會大量使用 JOIN 查詢，則需要進一步考慮借助上層應用側的緩存服務或使用 JOIN 表引擎來改善性能。

最后，如果是在大量維度屬性補全的查詢場景中，則建議使用字典代替 JOIN 查詢。因為在進行多表的連接查詢時，查詢會轉換成兩兩連接的形式，而這種滾雪球式的查詢很可能帶來性能問題。

2. 空值策略

????????在之前的介紹中，連接查詢的空值（那些未被連接的數據）是由默認值填充的，這與其他數據庫所采取的策略不同（由Null 填充）。連接查詢的空值策略通過 join_use_nulls 參數指定的，默認為 0。當參數值為 0 時，空值由數據類型的默認值填充；而當參數值為 1 時，空值由 Null 填充。

5、WHERE 與 PREWHERE 子句?

????????除了 WHERE，ClickHouse 還支持全新的 PREWHERE 子句，PREWHERE 目前只能用于 MegeTee 系列的表引擎，它可以看作對是 WHERE 的一種優化，其作用與 WHERE 相同，均是用來過濾數據。但它們的不同之處在于。使用 PREWHERE 時，首先只會讀取 PREWHERE 指定的列字段數據，用于數據過濾的條件判斷。待數據過濾之后再讀取 SELECT 聲明的列字段以補全其余屬性。所以在一些場合下，PREWHERE 相比 WHERE 而言，處理的數據量更少，性能更高。

既然 WHERE 子句性能更優，那么是否需要將所有的 WHERE 子句都替換成 PREWHERE 子句呢？其實大可不必，因為 ClickHouse 實現了自我優化的功能，會在條件合適的情況下將 WHERE 替換為 PREWHERE。如果想開啟這項特性，只需要將 optimize_move_to_prewhere 設置為 1 即可，當然默認就為 1，即開啟狀態。

6，GROUP BY 子句

聚合查詢能配合 WITH ROLLUP、WITH CUBE、WITH TOTALS 三種修飾符獲取額外的匯總信息

造測試表

CREATE TABLE sales_data(product String,channel String,amount int) ENGINE = Log();-- 接著寫入測試數據
INSERT INTO sales_data
VALUES ('桔子', '淘寶', 248175),('香蕉', '淘寶', 252148),('蘋果', '店面', 246198),('香蕉', '店面', 256602),('桔子', '店面', 245029),('蘋果', '淘寶', 252908),('蘋果', '京東', 252057),('桔子', '京東', 251795),('香蕉', '京東', 245904)

? WITH ROLLUP

????????GROUP BY 子句加上 WITH ROLLUP 選項時，首先按照全部的分組字段進行分組匯總；然后從右往左依次去掉一個分組字段再進行分組匯總，被去掉的字段顯示為零值；最后，將所有的數據進行一次匯總，所有的分組字段都顯示為零值。

select product,channel,sum(amount)
from  sales_data
group by product,channel 
with rollup查詢結果如下桔子	淘寶	248175
香蕉	淘寶	252148
蘋果	店面	246198
香蕉	店面	256602
桔子	店面	245029
蘋果	淘寶	252908
蘋果	京東	252057
桔子	京東	251795
香蕉	京東	245904
香蕉		    754654
桔子		    744999
蘋果		    7511632250816

????????我們注意到，多了四條數據，上面三條，就是按照 product、channel 匯總之后，再單獨按 product 匯總，而此時會給對應的 channel 設為零值（這里是空字符串，關系型數據庫中為 Null）。同理最后一條數據是全量匯總，不需要指定 product 和 channel，所以顯示為 product 和 channel 都顯示為零值。我們看到這就相當于按照 product 單獨聚合然后再自動拼接在上面了，排好序，并且自動將 channel 賦值為零值，同理最后一條數據也是如此

WITH CUBE

????????CUBE 代表立方體，它用于對分組字段進行各種可能的組合，能夠產生多維度的交叉統計結果，CUBE 通常用于數據倉庫中的交叉報表分析

select product,channel,sum(amount)
from  sales_data
group by product,channel 
with CUBE 桔子	淘寶	248175
香蕉	淘寶	252148
蘋果	店面	246198
香蕉	店面	256602
桔子	店面	245029
蘋果	淘寶	252908
蘋果	京東	252057
桔子	京東	251795
香蕉	京東	245904
香蕉		    754654
桔子		    744999
蘋果		    751163淘寶	753231京東	749756店面	7478292250816

????????CUBE 返回了更多的分組數據，其中不僅包含了 ROLLUP 匯總的結果，還包含了相當于按照 channel 進行聚合的記錄。因此隨著分組字段的增加，CUBE 產生的組合將會呈指數級增長

WITH TOTALS

只包含一個全局匯總的結果

select product,channel,sum(amount)
from  sales_data
group by product,channel 
with TOTALS桔子	淘寶	248175
香蕉	淘寶	252148
蘋果	店面	246198
香蕉	店面	256602
桔子	店面	245029
蘋果	淘寶	252908
蘋果	京東	252057
桔子	京東	251795
香蕉	京東	2459042250816

7、having子句

HAVING 子句要和 GROUP BY 子句同時出現，不能單獨使用


select product,channel,sum(amount) as cnt 
from  sales_data
group by product,channel 
having  cnt>250000香蕉	淘寶	252148
香蕉	店面	256602
蘋果	淘寶	252908
蘋果	京東	252057
桔子	京東	251795

8、ORDER BY子句

????????在 MergeTree 表引擎中也有 ORDER BY 參數用于指定排序鍵，這個的作用域是分區內，所以當查詢時如果有多個分區就不保證順序了，所以需要order by。在使用時可以定義多個排序鍵，每個排序鍵后需緊跟ASC 或者DESC，不寫默認為asc

例如

SELECT * FROM tbl ORDER BY v1 ASC, v2 DESC;
SELECT * FROM tbl ORDER BY v1, v2 DESC;
NULLS LAST

null值排在最后，無論升序還是降序

寫法如下

select arrayJoin([1,22,NULL,3,-1]) as v order by v asc-1
1
3
22
NULL
NULLS FIRST

null值排在第一，無論升序還是降序，寫法如上

9、limit by? 子句

????????LIMIT BY 運行于 ORDER BY 之后和 LIMIT 之前，它能夠按照指定分組，最多返回前 n 行數據（少于 n 行則按照實際數量返回），常用于 分組TOP N 的查詢場景。LIMIT BY 語法規則如下：

表數據
香蕉	店面	256602
蘋果	淘寶	252908
香蕉	淘寶	252148
蘋果	京東	252057
桔子	京東	251795
桔子	淘寶	248175
蘋果	店面	246198
香蕉	京東	245904
桔子	店面	245029

?

select product,channel,sum(amount) as cnt 
from  sales_data
group by product,channel 
order by cnt desc 
limit 1 by channel香蕉	店面	256602
蘋果	淘寶	252908
蘋果	京東	252057

LIMIT BY 也可以指定偏移量，因為不一定從一條開始選擇，而指定偏移量有兩種方式：

  • 一種方式如上：limit 條數? by? 維度
  • 另一種方式：limit 條數，偏移量（從第幾條開始）by 維度? 案例如下
select product,channel,sum(amount) as cnt 
from  sales_data
group by product,channel 
order by cnt desc 
limit 1 ,1 by channel香蕉	淘寶	252148
桔子	京東	251795
蘋果	店面	246198

10、 limit 子句

用法有三種如下

LIMIT N
LIMIT N OFFSET M
LIMIT M, N

limit 與limit by的區別

11、select和distinct子句?

支持正則查詢，例如下面會選擇以 n 開頭和包含字母 p 的字段：

SELECT COLUMNS('^n'), COLUMNS('p') FROM system.databases

distinct和group by 執行后雖然結果相同，但是distinct的執行計劃更簡單，而且在不使用order? by子句時，distinct在limit n滿足條件時立刻結束查詢，后面的就不查了，group by會分組執行完后在limit?

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/41862.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/41862.shtml
英文地址，請注明出處：http://en.pswp.cn/web/41862.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Java 如何在volatile內部調用接口

Java 如何在volatile內部調用接口

在Java中&#xff0c;volatile 關鍵字通常用于確保變量的可見性和有序性&#xff0c;而不是用來修飾接口或方法調用的。volatile 修飾的變量會被立即同步到主存&#xff0c;并且在每次訪問時都會從主存中重新讀取&#xff0c;而不是從緩存中讀取。這意味著對volatile變量的修改…
閱讀更多...
2005-2023年各省居民人均消費支出、城鎮居民人均消費支出、農村居民人均消費支出數據（無缺失）

2005-2023年各省居民人均消費支出、城鎮居民人均消費支出、農村居民人均消費支出數據（無缺失）

2005-2023年各省居民人均消費支出、城鎮居民人均消費支出、農村居民人均消費支出數據&#xff08;無缺失&#xff09; 1、時間&#xff1a;2005-2023年 2、來源&#xff1a;國家統計局、統計年鑒 3、范圍&#xff1a;31省 4、指標&#xff1a;全體居民人均消費支出、城鎮居…
閱讀更多...
探索 Linux 的 /etc/hosts 文件：基礎知識與實用指南

探索 Linux 的 /etc/hosts 文件：基礎知識與實用指南

探索 Linux 的 /etc/hosts 文件&#xff1a;基礎知識與實用指南 在 Linux 和 Unix 系統中&#xff0c;/etc/hosts 文件扮演著一個至關重要的角色。它是系統的本地 DNS&#xff08;域名系統&#xff09;解析器&#xff0c;負責將主機名映射到 IP 地址。在網絡和系統管理中&…
閱讀更多...
Linux中軟件yum安裝與編譯安裝

Linux中軟件yum安裝與編譯安裝

yum安裝與編譯安裝是Linux系統中常見的兩種軟件安裝方式&#xff0c;它們在多個方面存在顯著的區別。以下是對這兩種安裝方式的詳細比較&#xff1a; yum安裝 定義與特點&#xff1a; 定義&#xff1a;YUM&#xff08;Yellow dog Updater, Modified&#xff09;是Linux類系統…
閱讀更多...
Windows netstat命令詳解，Windows查看網絡連接

Windows netstat命令詳解，Windows查看網絡連接

「作者簡介」&#xff1a;冬奧會網絡安全中國代表隊&#xff0c;CSDN Top100&#xff0c;就職奇安信多年&#xff0c;以實戰工作為基礎著作 《網絡安全自學教程》&#xff0c;適合基礎薄弱的同學系統化的學習網絡安全&#xff0c;用最短的時間掌握最核心的技術。 netstat 常用來…
閱讀更多...
rocketmq實現限流

rocketmq實現限流

目錄 問題背景 技術方向 方案確認 消息隊列&#xff08;√&#xff09; 分布式鎖&#xff08;&#xff09; 方案實現 監控方向 業務方向 問題背景 公司郵件服務token有 分鐘內超200封的熔斷機制&#xff0c;當前token被熔斷后&#xff0c;系統發郵件操作會被忽略&…
閱讀更多...
python中的原子操作簡介

python中的原子操作簡介

深入理解Python中的原子操作 在現代編程中&#xff0c;多線程是提高程序執行效率的常用技術。然而&#xff0c;當多個線程并發執行時&#xff0c;如何確保數據的一致性和操作的正確性成為了一個關鍵問題。原子操作&#xff08;Atomic Operation&#xff09;便是解決這一問題的…
閱讀更多...
責任鏈模式(大話設計模式)C/C++版本

責任鏈模式(大話設計模式)C/C++版本

責任鏈模式 C #include <iostream> #include <memory>using namespace std; // 請求類 struct Request {std::string requestType; // 請求類型int number; // 該請求類型的數量std::string requestContent; // 請求內容 };// 抽象經理類 clas…
閱讀更多...
MySQL學習記錄 —— ?? CentOS7.9環境下的MySQL8.4 安裝和配置

MySQL學習記錄 —— ?? CentOS7.9環境下的MySQL8.4 安裝和配置

文章目錄 1、安裝和配置2、MySQL 包位置3、主要程序介紹 本篇開始在之前mysql博客的基礎上繼續延伸&#xff0c;適合有一定基礎的mysql使用者閱讀 環境 &#xff1a;CentOS 7.9 root 用戶&#xff0c;MySQL 8.4 1、安裝和配置 看一下當前系統版本 cat /etc/redhat-release應當…
閱讀更多...
前端重點之：Vue+websocket通信詳細用法和websocket心跳機制的使用，websocket斷開實時監測，websocket實時通信

前端重點之：Vue+websocket通信詳細用法和websocket心跳機制的使用，websocket斷開實時監測，websocket實時通信

今年年初找工作，好多gou面試官總喜歡問關于websocket通信的使用方式，此次又用到了，在此做個總結：主要包含websocket的具體使用方法，和重點：（心跳機制的使用），就是主要是前端實時監測websocket是否有斷連和數據的處理 在前端開發中，WebSocket 是一種常見的技術，用于…
閱讀更多...
淺談序列化及文本格式

淺談序列化及文本格式

序列化及文本格式 需求背景 軟件項目在開發過程中&#xff0c;將大量初始化配置項在一定程度上保存在配置文件中。肯定有很多人有疑問&#xff0c;為什么不將這些信息放在軟件內存中。開機時與用戶交互進行確認&#xff1f;這肯定是一個好想法&#xff0c;但是如果配置太多或…
閱讀更多...
眾所周知沃爾瑪1P是怎么運營？

眾所周知沃爾瑪1P是怎么運營？

??沃爾瑪的1P模式&#xff0c;即第一方供應商模式&#xff0c;是其獨特的采購策略。在這種模式下&#xff0c;供應商先將商品賣給沃爾瑪&#xff0c;由沃爾瑪負責庫存管理和銷售。沃爾瑪通過強大的采購和物流能力控制庫存&#xff0c;確保商品品質&#xff0c;為客戶提供更加…
閱讀更多...
FPGA問題

FPGA問題

fpga 問題 第一道坎&#xff0c;安裝軟件&#xff1b;沒有注冊&#xff0c;無法產生sop文件&#xff0c;無法下載 沒有相應的庫的quartus ii版本&#xff0c;需要另下載 第二道坎&#xff0c;模擬器的下載&#xff0c;安裝&#xff1b; 第三道&#xff0c;verilog 語法&#x…
閱讀更多...
deepspeed huggingface傳入參數 optimizer和lr_scheduler測試

deepspeed huggingface傳入參數 optimizer和lr_scheduler測試

Trainer中 首先&#xff1a; WarmupDecayLR --lr_scheduler_type linear WarmupLR --lr_scheduler_type constant_with_warmup 1 TrainArgument不傳lr_scheduler_type、optim&#xff0c;warmup_steps15 ds config文件中定義如下&#xff1a; 注意&#xff1a;如果不在Trai…
閱讀更多...
LangChain（四）工具調用的底層原理！給大模型按上雙手吧！（新手向）

LangChain（四）工具調用的底層原理！給大模型按上雙手吧！（新手向）

背景 經過前面三篇的內容&#xff0c;我想大家對于大模型的構建、Langchain的優勢、Chain的構建有了相當程度的理解&#xff08;雖然只是最簡單的示例&#xff0c;但是足夠有代表性&#xff09;。 后續Chain的使用將會更加豐富多彩&#xff0c;您會了解Langchain開發的大模型…
閱讀更多...
14-31 劍和詩人5 - 使用 AirLLM 和分層推理在單個 4GB GPU 上運行 LLama 3 70B

14-31 劍和詩人5 - 使用 AirLLM 和分層推理在單個 4GB GPU 上運行 LLama 3 70B

利用分層推理實現大模型語言(LLM) 大型語言模型 (LLM) 領域最近取得了顯著進展&#xff0c;LLaMa 3 70B 等模型突破了之前認為可能實現的極限。然而&#xff0c;這些模型的龐大規模給其部署和實際使用帶來了巨大挑戰&#xff0c;尤其是在資源受限的設備上&#xff0c;例如內存…
閱讀更多...
怎么壓縮pdf文件的大小？減小PDF文件大小的四種方法

怎么壓縮pdf文件的大小？減小PDF文件大小的四種方法

怎么壓縮pdf文件的大小&#xff1f;文件大小不僅影響傳輸速度&#xff0c;還可能涉及存儲空間的管理。當處理大型PDF文件時&#xff0c;可能會面臨電子郵件附件限制或云存儲容量不足的問題。此外&#xff0c;過大的文件在瀏覽和加載時也會導致延遲&#xff0c;影響閱讀體驗。這…
閱讀更多...
3款自己電腦就可以運行AI LLM的項目

3款自己電腦就可以運行AI LLM的項目

AnythingLLM、LocalGPT和PrivateGPT都是與大語言模型&#xff08;LLM&#xff09;相關的項目&#xff0c;它們允許用戶在本地環境中與文檔進行交互&#xff0c;但它們在實現方式和特點上存在一些差異。AnythingLLM使用Pinecone和ChromaDB來處理矢量嵌入&#xff0c;并使用OpenA…
閱讀更多...
【C語言】return 關鍵字詳解

【C語言】return 關鍵字詳解

在C語言中&#xff0c;return是一個關鍵字&#xff0c;用于從函數中返回值或者結束函數的執行。它是函數的重要組成部分&#xff0c;負責將函數的計算結果返回給調用者&#xff0c;并可以提前終止函數的執行。 主要用途和原理&#xff1a; 返回值給調用者&#xff1a; 當函數執…
閱讀更多...
mysql數據庫創建用戶并授權某個庫的所有權限

mysql數據庫創建用戶并授權某個庫的所有權限

這個就直接上語句吧&#xff01;只是注意要用管理員帳號執行&#xff0c;比如root去執行。 -- 創建新用戶&#xff08;替換new_user為您的用戶名&#xff0c;password為您的密碼&#xff09; CREATE USER new_user% IDENTIFIED BY password; -- 授予權限&#xff08;替換data…
閱讀更多...
最新文章

Copyright @ 2022~2023