创建普通视图

语法:

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

这些示例演示了不同类型的物化视图创建，您可以根据具体需求选择适合的创建方式。