查詢資料倉儲

10 分鐘

當資料倉儲中的維度和事實資料表已載入資料時，您可以使用 SQL 來查詢資料表，並分析其包含的資料。在 Synapse 專用 SQL 集區中用來查詢資料表的 Transact-SQL 語法，類似於在 SQL Server 或 Azure SQL Database 中使用的 SQL。

依維度屬性彙總量值

大多數使用資料倉儲進行的資料分析，都涉及依維度資料表中的屬性來彙總事實資料表中的數值量值。由於實作星型或雪花式結構描述的方式，因此，執行這類彙總的查詢依賴 JOIN 子句來將事實資料表連線到維度資料表，以及彙總函式和 GROUP BY 子句的組合來定義彙總階層。

例如，下列 SQL 會查詢假設性資料倉儲中的 FactSales 和 DimDate 資料表，依年度和季度彙總銷售量：

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;

此查詢的結果可能看起來類似下表：

CalendarYear	CalendarQuarter	TotalSales
2020	1	25980.16
2020	2	27453.87
2020	3	28527.15
2020	4	31083.45
2021	1	34562.96
2021	2	36162.27
...	...	...

您可以視需要聯結多個維度資料表，以計算所需的彙總。例如，下列程式碼會擴充上一個範例，根據 DimCustomer 資料表中客戶的地址詳細資料，依城市細分每季銷售總額：

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        custs.City,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;

這次，結果會包含每個城市每季的銷售總額：

CalendarYear	CalendarQuarter	縣/市	TotalSales
2020	1	阿姆斯特丹	5982.53
2020	1	柏林	2826.98
2020	1	芝加哥	5372.72
...	...	...	..
2020	2	阿姆斯特丹	7163.93
2020	2	柏林	8191.12
2020	2	芝加哥	2428.72
...	...	...	..
2020	3	阿姆斯特丹	7261.92
2020	3	柏林	4202.65
2020	3	芝加哥	2287.87
...	...	...	..
2020	4	阿姆斯特丹	8262.73
2020	4	柏林	5373.61
2020	4	芝加哥	7726.23
...	...	...	..
2021	1	阿姆斯特丹	7261.28
2021	1	柏林	3648.28
2021	1	芝加哥	1027.27
...	...	...	..

雪花式結構描述中的聯結

使用雪花式結構描述時，維度可能會部分正規化；需要多個聯結，才能讓事實資料表與雪花式維度相關。例如，假設您的資料倉儲包含 DimProduct 維度資料表，其中產品類別已正規化為個別的 DimCategory 資料表。依產品類別彙總已售出項目的查詢可能看起來類似下列範例：

SELECT  cat.ProductCategory,
        SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;

此查詢的結果包括每個產品類別已售出的項目數：

ProductCategory	ItemsSold
Accessories	28271
Bits and pieces	5368
...	...

注意

適用於 FactSales 和 DimProduct 以及適用於 DimProduct 和 DimCategory 的 JOIN 子句都是必要的，即使查詢不會從 DimProduct 傳回任何欄位也一樣。

使用次序函數

另一種常見的分析查詢是根據維度屬性來分割結果，並為每個分割區內的結果「排名」。例如，您可能希望每年依商店的銷售營收來對商店進行排名。若要達成此目標，您可以使用 Transact-SQL 的「次序」函數，例如 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。這些函式可讓您透過類別來分割資料，每個均會傳回特定值，指出分割區內每個資料列的相對位置：

ROW_NUMBER 會傳回分割區內資料列的序數位置。例如，第一個資料列編號為 1、第二個為 2，依此類推。
RANK 會傳回排序結果中每個資料列的排名位置。例如，在依銷售量排序的商店分割區中，銷售量最高的商店排名為 1。如果有多家商店的銷售量相同，其排名會一樣，而指派給後續商店的排名則會反映銷售量較高 (包括銷售量相同) 的商店數量。
DENSE_RANK 為分割區中資料列排名的方式與 RANK 相同，但是若有多個資料列的排名相同，後續資料列的排名位置就會略過銷售量相同者。
NTILE 會傳回資料列所在的指定百分位數。例如，在依銷售量排序的商店分割區中，NTILE(4) 會傳回商店銷售量所在的四分位數。

例如，請考慮下列查詢：

SELECT  ProductCategory,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
        RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
        DENSE_RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
        NTILE(4) OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;

查詢會根據其類別來將產品分割為群組，並在每個類別分割區中，根據產品定價來決定每個產品的相對位置。此查詢的結果可能看起來類似下表：

ProductCategory	ProductName	ListPrice	RowNumber	順位	DenseRank	Quartile
Accessories	Widget	8.99	1	1	1	1
Accessories	Knicknak	8.49	2	2	2	1
Accessories	Sprocket	5.99	3	3	3	2
Accessories	Doodah	5.99	4	3	3	2
Accessories	Spangle	2.99	5	5	4	3
Accessories	Badabing	0.25	6	6	5	4
Bits and pieces	Flimflam	7.49	1	1	1	1
Bits and pieces	Snickity wotsit	6.99	2	2	2	1
Bits and pieces	Flange	4.25	3	3	3	2
...	...	...	...	...	...	...

注意

範例結果示範 RANK 與 DENSE_RANK 之間的差異。請注意，在 [Accessories] 類別中，Sprocket 和 Doodah 產品具有相同定價，且都排名為價格第三高的產品。下一個次高價格產品的 RANK 為 5 (有四個產品的價格超過它)，且 DENSE_RANK 為 4 (有三個較高的價格)。

若要深入了解次序函數，請參閱 Azure Synapse Analytics 文件中的次序函數 (Transact-SQL)。

擷取近似計數

儘管資料倉儲的用途主要是支援企業的分析資料模型和報表；資料分析師和資料科學家通常需要執行一些初始資料探索，就只是為了判斷資料的基本規模和分佈。

例如，下列查詢使用 COUNT 函式來擷取假設性資料倉儲中每年的銷售量：

SELECT dates.CalendarYear AS CalendarYear,
    COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

此查詢的結果可能看起來類似下表：

CalendarYear	訂單
2019	239870
2020	284741
2021	309272
...	...

資料倉儲中的資料量可能意味著，即使是計算符合指定準則之記錄數目的簡單查詢，可能也需要相當長的時間來執行。在許多情況下，不需要準確的計數，近似的估計值就已足夠。在這類情況下，您可以使用 APPROX_COUNT_DISTINCT 函式，如下列範例所示：

SELECT dates.CalendarYear AS CalendarYear,
    APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

APPROX_COUNT_DISTINCT 函式會使用 HyperLogLog 演算法來擷取近似計數。保證結果的最大錯誤率為 2% 且機率為 97%，因此，使用與之前相同的假設性資料進行的查詢結果可能看起來類似下表：

CalendarYear	ApproxOrders
2019	235552
2020	290436
2021	304633
...	...

計數較不正確，但仍足以大致比較每年銷售量。具有大量資料時，使用 APPROX_COUNT_DISTINCT 函式的查詢會更快完成，而降低的正確性可能是在基本資料探索期間可接受的取捨。

注意

如需詳細資訊，請參閱 APPROX_COUNT_DISTINCT 函式文件。

查詢資料倉儲

依維度屬性彙總量值

雪花式結構描述中的聯結

使用次序函數

擷取近似計數

意見反應