創建普通視圖

語法:

CREATE [ORREPLACE] VIEW [IFNOTEXISTS]  [db.]table_name [ON CLUSTER cluster_name] AS SELECT ...

普通視圖不存儲任何數據。 他們只是在每次訪問時從另一個表執行讀取。換句話說，普通視圖只不過是一個保存的查詢。 從視圖中讀取時，此保存的查詢用作FROM子句中的子查詢。

例如，假設您已經創建了一個視圖：

CREATE VIEW view AS SELECT...

并寫了一個查詢：

SELECT a, b, c FROM view

這個查詢完全等同于使用子查詢：

SELECT a, b, c FROM (SELECT...)

以下是一些創建普通視圖的示例：

示例1：基于查詢創建普通視圖

CREATE VIEW my_view AS
SELECT column1, column2
FROM my_table
WHERE condition

示例2：基于多個表的JOIN操作創建普通視圖

CREATE VIEW my_joined_view AS
SELECT t1.column1, t2.column2
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.id

示例3：使用聚合函數創建普通視圖

CREATE VIEW my_aggregated_view AS
SELECT column1, COUNT(*) AS count
FROM my_table
GROUP BY column1

示例4：基于子查詢創建普通視圖

CREATE VIEW my_subquery_view AS
SELECT column1, column2
FROM (
SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS rn
FROM my_table
) subquery
WHERE rn = 1

這些示例演示了不同類型的普通視圖創建，您可以根據具體需求選擇適合的創建方式。普通視圖僅提供了對查詢結果的封裝，不會存儲實際數據，每次查詢視圖時都會重新執行視圖定義的查詢邏輯。

創建物化視圖

CREATE MATERIALIZED VIEW [IFNOTEXISTS] [db.]table_name [ON CLUSTER] [TO [db.]name] [ENGINE=engine] [POPULATE] AS SELECT ...

物化視圖存儲由相應的SELECT管理。

創建不帶 TO [db].[table]的物化視圖時，必須指定 ENGINE – 用于存儲數據的表引擎。

使用 TO [db].[table] 創建物化視圖時，不得使用 POPULATE。

一個物化視圖的實現是這樣的：當向SELECT中指定的表插入數據時，插入數據的一部分被這個SELECT查詢轉換，結果插入到視圖中。

云數據庫ClickHouse中的物化視圖更像是插入觸發器。 如果視圖查詢中有一些聚合，則它僅應用于一批新插入的數據。 對源表現有數據的任何更改（如更新、刪除、刪除分區等）都不會更改物化視圖。

如果指定 POPULATE，則在創建視圖時將現有表數據插入到視圖中，就像創建一個 CREATE TABLE ... AS SELECT ...一樣。 否則，查詢僅包含創建視圖后插入表中的數據。 我們不建議使用POPULATE，因為在創建視圖期間插入表中的數據不會插入其中。

SELECT 查詢可以包含 DISTINCT、GROUP BY、ORDER BY、LIMIT……，相應的轉換是在每個插入數據塊上獨立執行的。 例如，如果設置了 GROUP BY，則在插入期間聚合數據，但僅在插入數據的單個數據包內。 數據不會被進一步聚合。 例外情況是使用獨立執行數據聚合的 ENGINE，例如 SummingMergeTree。

在物化視圖上執行ALTER查詢有局限性，因此可能不方便。 如果物化視圖使用構造 TO [db.]name，你可以 DETACH視圖，為目標表運行 ALTER，然后 ATTACH先前分離的（DETACH）視圖。

物化視圖受optimize_on_insert設置的影響， 在插入視圖之前合并數據。

視圖看起來與普通表相同。 例如，它們列在 SHOW TABLES查詢的結果中。

刪除視圖,使用DROP VIEW. DROP TABLE也適用于視圖。

示例1：基于查詢創建物化視圖

CREATE MATERIALIZED VIEW my_view
ENGINE = MergeTree()
ORDER BY date
AS
SELECT date, sum(value) AS total
FROM my_table
GROUP BY date

示例2：基于條件篩選創建物化視圖

CREATE MATERIALIZED VIEW my_filtered_view
ENGINE = MergeTree()
ORDER BY date
AS
SELECT date, sum(value) AS total
FROM my_table
WHERE value > 100
GROUP BY date

示例3：基于JOIN操作創建物化視圖

CREATE MATERIALIZED VIEW my_joined_view
ENGINE = MergeTree()
ORDER BY date
AS
SELECT t1.date, t1.value, t2.name
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.id

示例4：使用預聚合函數創建物化視圖

CREATE MATERIALIZED VIEW my_aggregated_view
ENGINE = MergeTree()
ORDER BY date
AS
SELECT date, avg(value) AS average, max(value) AS maximum
FROM my_table
GROUP BY date

這些示例演示了不同類型的物化視圖創建，您可以根據具體需求選擇適合的創建方式。