iT邦幫忙

2022 iThome 鐵人賽
DAY 14
0
Software Development

ClickHouse:時序資料庫建置與運行系列 第 14 篇

day14-SQL使用與操作方法介紹(五)

14th鐵人賽
1193 瀏覽

  • 分享至 

  • xImage
    •  

前言

從上個章節可以知道在SELECT語句中後面可以接的從句(clause)，在本章節中將會繼續介紹其他的從句。

GROUP BY clause

GROUP BY從句是用來SELECT查詢語句的聚合模式，其運作的方式如下：

  • GROUP BY從句包含了單一或是多個描述式，這個清單扮演的是「群組鍵」。當每個描述式可以參考成為「鍵之描述」(key expressions)。
  • 在SELECT、HAVING與ORDER BY從句必須以鍵之描述、非鍵之描述上用聚合函數為基礎來進行計算。換句話說，每個從資料表中選擇到的欄位必須在鍵之描述或是聚合函數中用到，上述兩者其一，並非兩個都需要使用。
  • 聚合SELECT查詢語句結果會從來源資料表指定的群組鍵當作獨特的欄位，並只會出現一次，分組之後進行計算後得到查詢結果後的資料表出來。
  • 當我們要在資料表將資料以欄位的數量而不是以欄位名稱進行分組時，需要在資料庫設定檔中啟用enable_positional_arguments之設定。

對於空值的處理，ClickHouse會將NULL空值當作值來處理，即會判斷NULL==NULL，下列是有關於此空值處理的範例：

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

針對上述的資料表執行SELECT sum(x), y FROM t_null_big GROUP BY ySQL敘述之後，會得到下列的查詢結果：

┌─sum(x)─┬────y─┐
│      4 │    2 │
│      3 │    3 │
│      5 │ ᴺᵁᴸᴸ │
└────────┴──────┘

由此可見，針對y欄位進行分組，NULL空值也是一組，透過SUM(x)的聚合函數運算之後，NULL這一組的x欄位的總和為5。

ROLLUP是用來計算鍵描述的部分小計(subtotals)，這個會以GROUP BY清單中的順序進行決定，部分總計的筆數的欄位會顯示在結果資料表中最後一欄欄位。

部分小計會以相反的順序計算，首先為欄位中的最後一個鍵表達式計算小計，接著為前一個鍵表達式計算小計，依此類推，直到到第一個鍵表達式為止。

將在部分小計的行中，會將已經分組的鍵表達式的值設置為0或空行。

考慮下面的資料表：

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

接著我們使用下列的SQL查詢：

SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);

從上述的SQL查詢中可以得知，GROUP BY有三個鍵表達式，結果會有四個資料表，已由右至左為：

  • GROUP BY year, month, day
  • GROUP BY year, month (day欄位會填入0)
  • GROUP BY year (month與day欄位會填入0)
  • 以及總計，即會將year、month與day欄位都填入0。
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

CUBE會用來計算在GROUP BY清單中，針對鍵描述式的每個組合小計，小計的欄位會放在每個結果的資料表最後面。

考慮下列的資料表：

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

接著使用的SQL查詢如下：

SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);

從上述的SQL查詢得知，會有8個結果的資料表，GROUP BY的組合如下：

  • GROUP BY year, month, day
  • GROUP BY year, month (day欄位填成0)
  • GROUP BY year, day (month欄位填成0)
  • GROUP BY year (month與day欄位填成0)
  • GROUP BY month, day (year欄位填成0)
  • GROUP BY month (year與day欄位填成0)
  • GROUP BY day (year與month欄位填成0)
  • 以及總和，即會將year、month與day欄位都填成0。

相關輸出的結果資料表如下：

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │     0 │   5 │       2 │
│ 2019 │     0 │   5 │       1 │
│ 2020 │     0 │  15 │       2 │
│ 2019 │     0 │  15 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   5 │       2 │
│    0 │    10 │  15 │       1 │
│    0 │    10 │   5 │       1 │
│    0 │     1 │  15 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   0 │       4 │
│    0 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   5 │       3 │
│    0 │     0 │  15 │       3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

而我們也可以把上述的SQL查詢語句寫成如下SQL：

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;

若在GROUP BY從句使用了GROUPING SETS的話，則是定義分組的集合，與上述的ROLLUP與CUBE可以寫成等價的SQL語句，相關的範例如下：

-- SQL查詢1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

-- SQL查詢2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
    (year, month, day),
    (year, month),
    (year),
    ()
);

從上面的SQL查詢範例可以知道，SQL查詢1可以等價於SQL查詢2的SQL語句。

LIMIT BY clause

LIMIT n BY expressions之從句可以讓SQL語句的結果對於某個欄位之獨特值分別輸出前n筆資料，相關的語法如下所示：

  • LIMIT [offset_value, ]n BY expressions
  • LIMIT n OFFSET offset_value BY expressions

以下是一個關於此從句的範例，首先先利用下列的SQL來建立一個資料表：

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

透過ClickHouse客戶端執行上述的SQL的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;

CREATE TABLE limit_by
(
    `id` Int,
    `val` Int
)
ENGINE = Memory

Query id: 93bcc88c-d2f7-443e-980a-d5ca5e8f3bb2

Ok.

0 rows in set. Elapsed: 0.007 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

INSERT INTO limit_by FORMAT Values

Query id: 4fb2c151-94fb-450b-8d7b-49cc9b214b39

Ok.

5 rows in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

上述的SQL是建立一個將資料存放到記憶體的資料表，這將會是一個暫時的資料表，接著寫入五筆資料進去，建立好資料表之後，執行下列的SQL：

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

執行上述的SQL所得到的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

SELECT *
FROM limit_by
ORDER BY
    id ASC,
    val ASC
LIMIT 2 BY id

Query id: 5dc088d6-4c05-4b15-8d5f-0eb0764712c6

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

4 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述執行的SQL可以知道，以id欄位來說，獨特的id有1與2，因此查詢結果的資料表為分別輸出各id為1與2的前2筆資料出來。

接著執行下列的SQL：

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id

執行上述的SQL並輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id

SELECT *
FROM limit_by
ORDER BY
    id ASC,
    val ASC
LIMIT 1, 2 BY id

Query id: 8e37086f-10e8-473f-b35e-904670200f86

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

3 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

從上述的輸出訊息來看，針對id欄位有1與2這兩個獨特的值，因為加入了OFFSET的值為1，因此需要忽略1筆資料後的前2筆資料出來，因此以id欄位中的1的資料來說，(1, 10)之這筆資料會忽略，接著印出(1, 11)與(1, 12)這兩筆資料出來，(2, 20)這筆資料會忽略，接著因為只剩一筆資料，因此印出前兩筆資料出來只會印出(2, 21)這筆資料。

HAVING clause

HAVING從句允許過濾由GROUP BY從句產生的聚合SQL查詢結果，其類似於WHERE從句，但不同的是WHERE從句會在進行聚合計算之前執行，而HAVING從句會在聚合計算之後執行。

如果聚合函數沒有使用的話，則HAVING從句無法使用，這時候可以改用WHERE從句。

結論

從本章節中，我們可以知道SELECT語句中的GROUP BY、LIMIT BY與HAVING從句的用法，下一章節中將會其他SELECT語句中的從句用法介紹。

參考資料


上一篇
day13-SQL使用與操作方法介紹(四)
下一篇
day15-SQL使用與操作方法介紹(六)
系列文
ClickHouse:時序資料庫建置與運行 共 30 篇
  1. 26
    day26-ClickHouse 客戶端連線介面方式(五)
  2. 27
    day27-ClickHouse 第三方客戶端連線介面方式
  3. 28
    day28-設定資料庫SSL憑證安全連線
  4. 29
    day29-設定資料庫叢集分片與複製節點
  5. 30
    day30-ClickHouse資料庫的故障排除整理
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22212 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙