Microsoft Azure SQL是一个强大的、完全托管的数据库平台，旨在实现高性能查询、关系数据存储和分析。对于具有后端的典型Web应用程序，在考虑到需要一个可以在垂直和水平两个方向上扩展的托管数据库时，Azure SQL是一个不错的选择。
一个应用软件每天生成用户指标，这些指标可用于报告或分析。 Azure SQL是存储和查询这些数据的一个很好选择，条件如下：
- 分析查询需要与其他表进行连接（在UI上应用筛选器）
- 您想要将历史和事务数据结合在一起
- 数据量不是非常大，查询性能可以通过调整进行管理
让我们以一个运行Azure SQL的酒店预订网站为例。我们希望看到一个UI仪表板，用于跟踪用户活动，比如在网站上的点击、访问酒店描述页面、已预订房间等。
假设所有这些遥测数据都是以结构化存储的方式每天 为每个用户 存储在 数据库中，我们正在使用后台作业（如 Apache Airflow）将这些数据导入我们的数据库。
以下是用户表的架构和用于存储每日指标的表。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
age INT ,
city VARCHAR(100),
country VARCHAR(100),
currency VARCHAR(10),
last_login VARCHAR,
hotel_preference VARCHAR(100)
);
CREATE TABLE daily_user_metrics (
id BIGINT IDENTITY PRIMARY KEY, -- Unique identifier for the record
user_id BIGINT NOT NULL, -- Foreign key to the users table
clicks INT DEFAULT 0, -- Number of total site clicks
visits INT DEFAULT 0, -- Number of visits to the hotel page
bookings INT DEFAULT 0, -- Number of bookings
reviews INT DEFAULT 0, -- Number of reviews
cancellations INT DEFAULT 0, -- Number of cancellations
date_created DATE, -- Daily metrics for each user
);
您可以从上述表格中得出许多见解。
让我们考虑一个特定的例子。我们需要按照日期分组，按降序聚合30至40岁在纽约市的客户的每日活动指标。以下是查询：
SELECT
date_created,
SUM(clicks) AS total_clicks,
SUM(visits) AS total_visits,
SUM(bookings) AS total_bookings,
SUM(reviews) AS total_reviews,
SUM(cancellations) AS total_cancellations,
FROM
daily_user_metrics m
INNER JOIN users u on m.user_id = u.id
WHERE
u.age BETWEEN 30 and 40
and u.city ='New York'
and m.date_created BETWEEN :startDate and :endDate
GROUP BY
date_created
ORDER BY
date_created DESC
现在，我们可以分析这些在纽约市、年龄在30至40岁之间的用户群体的每日趋势。这个表大多数时候性能良好，我们可以轻松执行跨多个月份的范围查询。最终，我们的需求增加了。现在我们想要跟踪用户行为在一个周或月的范围内。但是我们的表是按日存储数据的。我们现在有两个选择：
- 查询表，并根据要求将
date_created按周或月分组。 - 创建一些视图，按用户每周或每月汇总数据。请看下面的查询：
CREATE VIEW weekly_user_metrics AS
SELECT
DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start, -- Start of the week (Sunday)
SUM(clicks) AS total_clicks,
SUM(visits) AS total_visits,
SUM(bookings) AS total_bookings,
SUM(reviews) AS total_reviews,
SUM(cancellations) AS total_cancellations,
FROM
daily_user_metrics m
INNER JOIN users u on m.user_id = u.id
WHERE
u.age BETWEEN 30 and 40
and u.city ='New York'
and m.date_created BETWEEN :startDate and :endDate
GROUP BY
DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) -- Group by week start
ORDER BY
DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) DESC -- Sort by latest week
然而，需要考虑的一个重要事项是视图仅提供对基础查询的抽象，该查询简单地查询基础表。物化视图是接下来会想到的。然而，它们需要手动刷新或按计划刷新，因此无法提供实时数据。
为了解决这些问题，Azure SQL Server提供了一个被称为索引视图的强大功能。索引视图是存储在数据库中的视图的物理表示，具有唯一的聚集索引。对基础表的更改会自动更新索引视图以保持同步。它使用聚集索引，根据索引键的顺序组织视图中的数据。
索引视图非常适合需要实时数据且查询涉及复杂多表连接的情况。对于我们的用例，其中现有数据很少更新但经常查询，并且我们有基于范围的查询并且希望进行有序检索，索引视图也非常合适。
在决定是否使用索引视图之前，有一些事项需要考虑。索引视图不能包含非确定性函数。非确定性函数是指在相同输入下，即使在相同的参数和相同的数据库条件下执行，该函数也不总是返回相同的结果。此外，索引视图是一个实际的结构，需要存储空间，类似于物化视图。
创建索引视图的语法类似于上面的视图创建查询。但是，在创建索引视图时不能包含非确定性函数。在上面的视图查询中，DATEADD(DAY, -(DATEPART(WEEKDAY, date) - 1), date) AS week_start这一行取决于会话特定的SET DATEFIRST设置，该设置确定了一周的第一天。这被视为非确定性，因为它会对不同条件产生不同的结果。
考虑到以上事项，我们可以通过使列具有确定性来消除非确定性计算。我们在基础表中添加一个week_start列，并预先计算并填充表中的week_start 值，以供每日数据拉取使用。因此，日期D1至D7的行属于W1，D8至D14的行属于W2，依此类推。
现在，我们可以继续使用以下SQL创建索引视图。
ALTER TABLE daily_user_metrics ADD week_start DATE;-- Populate this column with first day of the week going forward
CREATE VIEW dbo.weekly_user_metric_aggregations_view
WITH SCHEMABINDING
AS
SELECT
user_id,
week_start,
SUM(clicks) AS total_clicks,
SUM(visits) AS total_visits,
SUM(bookings) AS total_bookings,
SUM(reviews) AS total_reviews,
SUM(cancellations) AS total_cancellations,
COUNT_BIG(*) AS row_count --SQL Server requires COUNT_BIG(*) in indexed views to handle scenarios where the count exceeds the range of an INT data type.
FROM
dbo.daily_user_metrics
GROUP BY
user_id,
week_start;
CREATE UNIQUE CLUSTERED INDEX IX_weekly_user_metric_aggregations_view
ON dbo.weekly_user_metric_aggregations_view (user_id, week_start);
创建完此索引视图后，我们可以按以下方式查询它：
SELECT
week_start,
SUM(total_clicks) AS total_clicks,
SUM(total_visits) AS total_visits,
SUM(total_bookings) AS total_bookings,
SUM(total_reviews) AS total_reviews,
SUM(total_cancellations) AS total_cancellations,
FROM
weekly_user_metric_aggregations_view mv
INNER JOIN users u on mv.user_id = u.id
WHERE
u.age BETWEEN 30 and 40
and u.city ='New York'
and m.date_created BETWEEN :startDate and :endDate
GROUP BY
week_created
ORDER BY
week_created DESC
结论
索引视图的查询速度将比基本视图或直接查询表的速度快得多，特别适用于数据增长到百万行或更多时的大型数据集。最终用户将获得低延迟体验，并且数据库查询将得到优化。由于我们在1行中聚合了一周的数据，因此聚合所需的时间减少了1/7。
Source:
https://dzone.com/articles/indexed-view-for-aggregating-metrics