Microsoft Azure SQL是一個強大的、完全受管理的數據庫平台，旨在用於高性能查詢、關聯數據存儲和分析。對於具有後端的典型Web應用程序來說，當我們希望考慮一個可以在垂直和水平兩個方向上擴展的受管理數據庫時，它是一個不錯的選擇。
一個應用程序軟件每天生成用戶指標，這些指標可用於報告或分析。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 提供了一個稱為 Indexed View 的重要功能。一個 Indexed View 是存儲在數據庫中的視圖的物理表示，具有唯一的叢集索引。對基礎表的更改會自動更新 Indexed View 以保持同步。它使用一個叢集索引，根據索引鍵的順序對視圖中的數據進行組織。
Indexed View 非常適合需要實時數據且查詢涉及複雜多表連接的情況。它也適用於現有數據很少更新但經常查詢的用例，並且我們有基於範圍的查詢且想要有序檢索的情況。
在決定是否使用 Indexed View 之前，有一些要考慮的事項。Indexed View 不能包含非確定性函數。非確定性函數是一種在相同輸入下並在相同數據庫條件下執行時，並不總是返回相同結果的函數。此外，Indexed View 是一個實際需要存儲的結構，類似於 Materialized View。
創建 Indexed View 的語法與上面的創建視圖查詢類似。但是，在創建 Indexed View 時不能使用非確定性函數。在上面的視圖查詢中的 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