简单地说，视图是 SQL 查询的一种别名。假设我们有一张表：
一个包含数据的简单表：
CREATE TABLE IF NOT EXISTS data_points
(
sensor varchar,
measure_time DATE,
value REAL,
PRIMARY KEY (city, measure_time)
);
|
在很多地方我们都会遇到类似下面这样的查询
查询：
SELECT sensor, round(avg(value)::numeric, 2)) AS value
WHERE date_part('year', measure_time) BETWEEN 2022 AND 2024
GROUP BY sensor;
|
然后我们可以用视图替换它们。为此，只需创建一个如下所示的视图：
视图：
CREATE OR REPLACE VIEW avg_value AS
SELECT sensor, round(avg(value)::numeric, 2)) AS value
WHERE date_part('year', measure_time) BETWEEN 2022 AND 2024
GROUP BY sensor;
|
该视图的使用方式与表相同：
SELECT * from avg_values
WHERE sensor like 'Wrocław%'
AND value > 1.1;
|
这意味着可以添加进一步的限制、聚合或仅选择某些列。
简单来说就是：在视图上执行查询与子查询是一样的。当然，这还不是全部，但对于我们来说绝对足够了。
其他类型的视图
除了常规视图之外，Postgres 还允许您创建：
- 物化视图 - 即那些在创建时将执行查询并将其结果保存在表中的视图。这样，以后我们就可以查询特定时刻的数据了。
- 临时视图——即那些仅在特定用户会话中可用且在完成后将被删除的视图。它们也不可供其他会话和用户使用。
用法
我们可以将视图视为应用程序的读取界面。因此，我们将数据存储模型（表）与读取它们的方式分开。我们可以随着应用程序的开发自由修改存储模型，而客户不会注意到这些变化。这可能与众所周知的 CQRS 方法有关，并且有一定的道理。
此外，常规（和具体化）视图可能仅对选定的用户可用。因此，我们可以在数据库级别限制对某些信息的访问。同时，在对 JPA 实体进行建模时，我们应该限制更新和保存所有列的可能性：
@Entity
@Table(name="avg_values")
public class AvgValue{
@Id
@Column(insertable = false, updatable=false)
private String sensor;
@Column(insertable = false, updatable=false)
private double value;
// gettery itd.
}
|
因此，在 Java 端我们无法在实体上调用INSERT/ 。UPDATE现在很简单，但视图也有一些局限性。
限制和问题
最简单的方法揭示了视图的一个主要问题。如果我们创建视图的查询返回大量数据，而我们只需要其中的一小部分，则WHERE在查询中添加附加子句可能会导致性能问题。例如，回到清单 4 中的查询，我们可以假设avg_values它将返回来自不同城市的许多传感器，而我们只需要来自弗罗茨瓦夫的传感器。我们不需要其余的一切。我们可以重塑视图：
CREATE OR REPLACE VIEW avg_value_wro AS
SELECT sensor, round(avg(value)::numeric, 2)) AS value
WHERE
date_part('year', measure_time) BETWEEN 2022 AND 2024
AND sensor like 'Wrocław%'
GROUP BY sensor;
|
此时我们可以将查询重构为：
SELECT * from avg_values_wro
WHERE value > 1.1;
|
用于搜索条件的数据空间value > 1.1比第一个示例中的要小。如果我们想改变城市怎么办？或者选择不同的日期范围？这就是视图的主要缺点变得明显的地方 - 你不能向它们传递参数。
参数化视图——如何实现？
当然，你不能直接这样做，因为 postgres 不允许你创建类似的东西。我们将使用返回表的命名函数来代替视图。这有点疯狂，但很有效。
CREATE OR REPLACE FUNCTION avg_value(strat_year numeric, end_year numeric)
RETURNS TABLE
(
o_sensor numeric,
o_value numeric
)
AS
$body$
SELECT sensor, round(avg(value)::numeric, 2)) AS value
WHERE date_part('year', measure_time) BETWEEN start_year AND end_year
GROUP BY sensor;
$body$
LANGUAGE sql;
|
当然，还有示例用法：
SELECT * from avg_values(2022, 2024)
WHERE sensor like 'Wrocław%'
AND value > 1.1;
|
此外，我们可以非常轻松地将函数转换为采用带有传感器名称的可选参数的形式：
CREATE OR REPLACE FUNCTION avg_value(strat_year numeric, end_year numeric, p_sensor text default '*')
RETURNS TABLE
(
o_sensor numeric,
o_value numeric
)
AS
$body$
SELECT sensor, round(avg(value)::numeric, 2)) AS value
WHERE date_part('year', measure_time) BETWEEN start_year AND end_year
AND sensor LIKE p_sensor
GROUP BY sensor;
$body$
LANGUAGE sql;
|
这允许您通过以下方式使用它：
SELECT * from avg_values(2022, 2024, 'Wrocław%')
WHERE value > 1.1;
|
JPA 中的模型
这里我们进入了“龙与裸女”的领域，所以我们必须施展一些魔法。由于无法像常规表一样调用该函数，因此访问它的唯一方法是通过本机 SQL 查询。该函数返回一个表作为结果，因此我们需要描述表的列如何映射到类中的字段。另外，该函数有一个可选参数，因此我们实际上需要两个查询。
@SqlResultSetMapping(
name = "AvgValuesMapping",
entities = @EntityResult(
entityClass = AvgValue.class,
fields = {
@FieldResult(name = "sensor", column = "sensor"),
@FieldResult(name = "value", column = "value")
}
)
)
@NamedNativeQuery(
name = "AvgValuesForYearRange",
query = "SELECT * FROM avg_value(:start_year, :end_year)",
resultSetMapping = "AvgValuesMapping"
)
@NamedNativeQuery(
name = "AvgValuesForYearRangeAndSensor",
query = "SELECT * FROM avg_value(:start_year, :end_year, :sensor)",
resultSetMapping = "AvgValuesMapping"
)
@Entity
public class AvgValue{
@Id
@Column(insertable = false, updatable=false)
private String sensor;
@Column(insertable = false, updatable=false)
private double value;
// gettery itd.
}
|
现在它应该可以正常运行了。
概括
为了获得 Postgres 中参数化视图的功能，我们使用了函数。由于视图与函数具有不同的调用语义，因此我们还需要更改它们在 JPA 端的调用方式。最后，我要补充一点，查询视图的结果就像查询结果一样，不必具有唯一的记录。同时，JPA要求每个实体都有唯一的标识符。这意味着使用带有视图的 ORM 可能意味着必须使用覆盖结果中大部分列的复合键。