Consultar um armazém de dados
Quando as tabelas de dimensões e fatos em um data warehouse tiverem sido carregadas com dados, você poderá usar SQL para consultar as tabelas e analisar os dados que elas contêm. A sintaxe Transact-SQL usada para consultar tabelas em um pool SQL dedicado Synapse é semelhante ao SQL usado no SQL Server ou no Banco de Dados SQL do Azure.
Agregação de medidas por atributos de dimensão
A maioria das análises de dados com um data warehouse envolve a agregação de medidas numéricas em tabelas de fato por atributos em tabelas de dimensão. Devido à forma como um esquema de estrela ou floco de neve é implementado, as consultas para executar esse tipo de agregação dependem JOIN de cláusulas para conectar tabelas de fatos a tabelas de dimensão e uma combinação de funções agregadas e GROUP BY cláusulas para definir as hierarquias de agregação.
Por exemplo, o SQL a seguir consulta as tabelas FactSales e DimDate em um data warehouse hipotético para agregar valores de vendas por ano e trimestre:
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;
Os resultados dessa consulta seriam semelhantes à tabela a seguir:
| 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 |
| ... | ... | ... |
Você pode unir quantas tabelas de dimensão forem necessárias para calcular as agregações necessárias. Por exemplo, o código a seguir estende o exemplo anterior para dividir os totais de vendas trimestrais por cidade com base nos detalhes de endereço do cliente na tabela 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;
Desta vez, os resultados incluem um total trimestral de vendas para cada cidade:
| CalendarYear | CalendarQuarter | City | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amesterdão | 5982.53 |
| 2020 | 1 | Berlim | 2826.98 |
| 2020 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amesterdão | 7163.93 |
| 2020 | 2 | Berlim | 8191.12 |
| 2020 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amesterdão | 7261.92 |
| 2020 | 3 | Berlim | 4202.65 |
| 2020 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amesterdão | 8262.73 |
| 2020 | 4 | Berlim | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amesterdão | 7261.28 |
| 2021 | 1 | Berlim | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Junta-se em um esquema de flocos de neve
Ao usar um esquema de flocos de neve, as dimensões podem ser parcialmente normalizadas; exigindo várias junções para relacionar tabelas de fatos com dimensões de flocos de neve. Por exemplo, suponha que seu data warehouse inclua uma tabela de dimensões DimProduct a partir da qual as categorias de produtos foram normalizadas em uma tabela DimCategory separada. Uma consulta para agregar itens vendidos por categoria de produto pode ser semelhante ao exemplo a seguir:
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;
Os resultados desta consulta incluem o número de itens vendidos para cada categoria de produto:
| ProductCategory | ItensVendidos |
|---|---|
| Accessories | 28271 |
| Bits e pedaços | 5368 |
| ... | ... |
Nota
As cláusulas JOIN para FactSales e DimProduct e para DimProduct e DimCategory são necessárias, mesmo que nenhum campo de DimProduct seja retornado pela consulta.
Usando funções de classificação
Outro tipo comum de consulta analítica é particionar os resultados com base em um atributo de dimensão e classificar os resultados dentro de cada partição. Por exemplo, você pode querer classificar as lojas a cada ano pela receita de vendas. Para atingir essa meta, você pode usar funções de classificação Transact-SQL, como ROW_NUMBER, RANK, DENSE_RANKe NTILE. Essas funções permitem que você particione os dados em categorias, cada uma retornando um valor específico que indica a posição relativa de cada linha dentro da partição:
- ROW_NUMBER retorna a posição ordinal da linha dentro da partição. Por exemplo, a primeira linha é numerada como 1, a segunda 2 e assim por diante.
- RANK retorna a posição classificada de cada linha nos resultados ordenados. Por exemplo, em uma partição de lojas ordenadas por volume de vendas, a loja com o maior volume de vendas é classificada como 1. Se várias lojas tiverem os mesmos volumes de vendas, elas serão classificadas da mesma forma, e a classificação atribuída às lojas subsequentes reflete o número de lojas que têm volumes de vendas mais altos - incluindo empates.
- DENSE_RANK classifica linhas em uma partição da mesma forma que RANK, mas quando várias linhas têm a mesma classificação, as linhas subsequentes são posições de classificação ignoram empates.
- NTILE retorna o percentil especificado no qual a linha cai. Por exemplo, em uma partição de lojas ordenadas por volume de vendas,
NTILE(4)retorna o quartil em que o volume de vendas de uma loja o coloca.
Por exemplo, considere a consulta seguinte:
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;
A consulta divide os produtos em agrupamentos com base em suas categorias e, dentro de cada partição de categoria, a posição relativa de cada produto é determinada com base em seu preço de tabela. Os resultados dessa consulta podem ser semelhantes à tabela a seguir:
| ProductCategory | ProductName | PreçoListado | Número da linha | Posição | DenseRank | Quartil |
|---|---|---|---|---|---|---|
| Accessories | Widget | 8.99 | 1 | 1 | 1 | 1 |
| Accessories | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Accessories | Roda dentada | 5,99 | 3 | 3 | 3 | 2 |
| Accessories | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessories | Emaranhado | 2.99 | 5 | 5 | 4 | 3 |
| Accessories | Badabing | 0.25 | 6 | 6 | 5 | 4 |
| Bits e pedaços | Flimflam | 7.49 | 1 | 1 | 1 | 1 |
| Bits e pedaços | Snickity wotsit | 6.99 | 2 | 2 | 2 | 1 |
| Bits e pedaços | Flange | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Nota
Os resultados da amostra demonstram a diferença entre RANK e DENSE_RANK. Note-se que na categoria Acessórios , os produtos Sprocket e Doodah têm o mesmo preço de tabela, e ambos estão classificados como o 3º produto mais caro. O próximo produto com preço mais alto tem um RANK de 5 (há quatro produtos mais caros do que ele) e um DENSE_RANK de 4 (há três preços mais altos).
Para saber mais sobre funções de classificação, consulte Funções de classificação (Transact-SQL) na documentação do Azure Synapse Analytics.
Recuperando uma contagem aproximada
Embora o objetivo de um armazém de dados seja principalmente dar suporte a modelos de dados analíticos e relatórios para a empresa; Analistas de dados e cientistas de dados geralmente precisam realizar alguma exploração inicial de dados, apenas para determinar a escala básica e a distribuição dos dados.
Por exemplo, a consulta a seguir usa a COUNT função para recuperar o número de vendas para cada ano em um data warehouse hipotético:
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;
Os resultados dessa consulta podem ser semelhantes à tabela a seguir:
| CalendarYear | Encomendas |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
O volume de dados em um data warehouse pode significar que até mesmo consultas simples para contar o número de registros que atendem aos critérios especificados podem levar um tempo considerável para serem executadas. Em muitos casos, uma contagem precisa não é necessária - uma estimativa aproximada será suficiente. Nesses casos, você pode usar a APPROX_COUNT_DISTINCT função como mostrado no exemplo a seguir:
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;
A APPROX_COUNT_DISTINCT função usa um algoritmo HyperLogLog para recuperar uma contagem aproximada. É garantido que o resultado tenha uma taxa de erro máxima de 2% com 97% de probabilidade, de modo que os resultados desta consulta com os mesmos dados hipotéticos de antes podem ser semelhantes à tabela a seguir:
| CalendarYear | Aprox. Encomendas |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
As contagens são menos precisas, mas ainda assim suficientes para uma comparação aproximada das vendas anuais. Com um grande volume de dados, a consulta usando a função é concluída mais rapidamente, e a precisão reduzida pode ser uma compensação aceitável durante a APPROX_COUNT_DISTINCT exploração de dados básicos.
Nota
Consulte a documentação da função APPROX_COUNT_DISTINCT para obter mais detalhes.