Запросы к хранилищу данных
Завершив загрузку данных в таблицы измерений и фактов в хранилище данных, вы сможете выполнять запросы по этим таблицам в формате SQL и анализировать содержащиеся в них данные. Синтаксис Transact-SQL, используемый для запросов по таблицам в выделенном пуле SQL Synapse, аналогичен стандартному синтаксису SQL в SQL Server и Базе данных SQL Azure.
Агрегирование мер по атрибутам измерения
Почти все задачи аналитики данных в хранилище данных включают агрегирование числовых мер из таблиц фактов по атрибутам в таблицах измерений. В силу особенностей схем типа "звезда" или "снежинка" для такого типа агрегирования в запросах используются предложения 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;
Результаты этого запроса содержат количество проданных товаров для каждой категории продуктов:
| КатегорияПродукта | ItemsSold |
|---|---|
| Аксессуары | 28271 |
| Bits and pieces | 5368 |
| ... | ... |
Примечание.
Предложения JOIN для объединения FactSales и DimProduct, а также DimProduct и DimCategory являются здесь обязательными, даже если запросе не возвращает полей из таблицы 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;
Этот запрос секционирует продукты по группам на основе категорий, а в каждой секции категорий распределяет положения продуктов на основе его цены в каталоге. Таблица с результатами этого запроса может выглядеть следующим образом:
| КатегорияПродукта | НаименованиеПродукта | ПрейскурантнаяЦена | RowNumber | Rank | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Аксессуары | Мини-приложение | 8.99 | 1 | 1 | 1 | 1 |
| Аксессуары | Knicknak | 8,49 | 2 | 2 | 2 | 1 |
| Аксессуары | Sprocket | 5,99 | 3 | 3 | 3 | 2 |
| Аксессуары | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Аксессуары | Spangle | 2,99 | 5 | 5 | 4 | 3 |
| Аксессуары | 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 в списке с сортировкой по ценам. Следующий после них продукт в этом списке имеет положение 5 при использовании RANK (что означает, что существуют четыре продукта дороже него) и 4 при использовании DENSE_RANK (что означает, что существуют три более высокие цены).
Сведения о ранжирующих функциях см. в статье Ранжирующие функции (Transact-SQL) в документации по Azure Synapse Analytics.
Получение приблизительного количества
Основной целью хранилища данных заключается поддержка моделей аналитических данных и отчетов для предприятия, но аналитикам и специалистам по обработке и анализу данных часто нужно выполнять первоначальное исследование данных, чтобы грубо оценить масштаб и распределение этих данных.
Например, следующий запрос использует функцию 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.