查询数据仓库

10 分钟

当数据仓库中的维度表和事实数据表已加载数据后，你可以使用 SQL 查询这些表并分析它们包含的数据。用于查询 Synapse 专用 SQL 池中的表的 Transact-SQL 语法类似于 SQL Server 或 Azure SQL 数据库中使用的 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	City	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	Rank	DenseRank	Quartile
Accessories	小组件	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 产品具有相同的标价，都被列为第 3 高价产品。下一个高价产品的 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 函数文档。

查询数据仓库

按维度属性聚合度量值

雪花型架构中的联接

使用排名函数

检索近似计数

反馈