Consultar um data warehouse
Quando as tabelas de dimensões e de fatos em um data warehouse tiverem sido carregadas com dados, você poderá usar o SQL para consultar as tabelas e analisar os dados que elas contêm. A sintaxe Transact-SQL usada para consultar tabelas em um pool de SQL dedicado do 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 agregar as medidas numéricas em tabelas de fatos segundo os atributos em tabelas de dimensões. Devido à maneira como um esquema em estrela ou floco de neve é implementado, as consultas para executar esse tipo de agregação dependem de cláusulas JOIN para conectar tabelas de fatos a tabelas de dimensões e de uma combinação de funções de agregação e cláusulas GROUP BY para definir as hierarquias de agregação.
Por exemplo, o seguinte SQL 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 à seguinte tabela:
| 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ões forem necessárias para calcular as agregações de que precisa. Por exemplo, o seguinte código estende o exemplo anterior para dividir os totais de vendas trimestrais por cidade com base nos detalhes do 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 de vendas trimestrais para cada cidade:
| CalendarYear | CalendarQuarter | City | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amsterdã | 5982.53 |
| 2020 | 1 | Berlim | 2826.98 |
| 2020 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amsterdã | 7163.93 |
| 2020 | 2 | Berlim | 8191.12 |
| 2020 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amsterdã | 7261.92 |
| 2020 | 3 | Berlim | 4202.65 |
| 2020 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amsterdã | 8262.73 |
| 2020 | 4 | Berlim | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdã | 7261.28 |
| 2021 | 1 | Berlim | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Junções em um esquema floco de neve
Ao usar um esquema floco de neve, as dimensões podem estar parcialmente normalizadas, exigindo várias junções para relacionar tabelas de fatos a dimensões de floco de neve. Por exemplo, suponha que o data warehouse inclui uma tabela de dimensões DimProduct da qual as categorias de produto foram normalizadas em uma tabela DimCategory separada. Uma consulta para agregar itens vendidos por categoria de produto pode ser semelhante ao seguinte exemplo:
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 dessa consulta incluem o número de itens vendidos para cada categoria de produto:
| ProductCategory | ItemsSold |
|---|---|
| Acessórios | 28271 |
| Peças e partes | 5368 |
| ... | ... |
Observação
Cláusulas JOIN para FactSales e DimProduct e para DimProduct e DimCategory são necessárias, embora 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, talvez você queira 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_RANK e NTILE. Essas funções permitem particionar 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 como 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 pelo 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 seguintes refletirá o número de lojas que têm volumes de vendas mais altos – incluindo empates.
- DENSE_RANK classifica as linhas em uma partição da mesma forma que RANK, mas quando várias linhas têm a mesma classificação, as linhas seguintes nas posições de classificação ignoram empates.
- NTILE retorna o percentil especificado em que a linha se enquadra. Por exemplo, em uma partição de lojas ordenadas pelo volume de vendas,
NTILE(4)retorna o quartil em que o volume de vendas de uma loja a coloca.
Por exemplo, considere a consulta abaixo:
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 particiona 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 listado. Os resultados dessa consulta poderiam ser semelhantes à seguinte tabela:
| ProductCategory | ProductName | ListPrice | RowNumber | Rank | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Acessórios | Widget | 8,99 | 1 | 1 | 1 | 1 |
| Acessórios | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Acessórios | Roda dentada | 5,99 | 3 | 3 | 3 | 2 |
| Acessórios | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Acessórios | Spangle | 2,99 | 5 | 5 | 4 | 3 |
| Acessórios | Badabing | 0,25 | 6 | 6 | 5 | 4 |
| Peças e partes | Flimflam | 7,49 | 1 | 1 | 1 | 1 |
| Peças e partes | Snickity wotsit | 6,99 | 2 | 2 | 2 | 1 |
| Peças e partes | Flange | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Observação
Os resultados de exemplo demonstram a diferença entre RANK e DENSE_RANK. Observe que, na categoria Acessórios, os produtos Roda dentada e Doodah têm o mesmo preço listado; e ambos são classificados como o 3º produto com preço mais alto. 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 as 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 a finalidade de um data warehouse seja principalmente dar suporte a modelos de dados analíticos e relatórios para a empresa, analistas e cientistas de dados geralmente precisam fazer alguma exploração inicial dos dados apenas para determinar a escala básica e a distribuição deles.
Por exemplo, a seguinte consulta usa a função COUNT 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 à seguinte tabela:
| CalendarYear | Pedidos |
|---|---|
| 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, use a função APPROX_COUNT_DISTINCT, conforme mostrado no seguinte exemplo:
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 função APPROX_COUNT_DISTINCT usa um algoritmo HyperLogLog para recuperar uma contagem aproximada. É garantido que o resultado tenha uma taxa de erro máxima de 2% com probabilidade de 97%, portanto, os resultados dessa consulta com os mesmos dados hipotéticos de antes podem ser semelhantes à seguinte tabela:
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
As contagens são menos exatas, mas ainda são suficientes para uma comparação aproximada das vendas anuais. Com um grande volume de dados, a consulta que usa a função APPROX_COUNT_DISTINCT é concluída mais rapidamente e a exatidão reduzida pode ser uma compensação aceitável durante a exploração básica de dados.
Observação
Consulte a documentação da função APPROX_COUNT_DISTINCT para obter mais detalhes.