Consulta de un almacenamiento de datos
Cuando las tablas de dimensiones y hechos de un almacenamiento de datos se han cargado con datos, puede usar SQL para consultar las tablas y analizar los datos que contienen. La sintaxis de Transact-SQL usada para consultar tablas en un grupo de SQL dedicado de Synapse es similar a la sintaxis de SQL que se usa en SQL Server o Azure SQL Database.
Agregación de medidas por atributos de dimensión
La mayoría de los análisis de datos con un almacenamiento de datos implican agregar medidas numéricas en tablas de hechos por atributos en tablas de dimensiones. Debido a la forma en que se implementa un esquema de estrella o copo de nieve, las consultas para realizar este tipo de agregación se basan en cláusulas JOIN para conectar tablas de hechos a tablas de dimensiones y una combinación de funciones y cláusulas GROUP BY de agregado para definir las jerarquías de agregación.
Por ejemplo, la siguiente instrucción SQL consulta las tablas FactSales y DimDate en un almacenamiento de datos hipotético para agregar los importes de ventas por año y 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;
Los resultados del ejemplo de código serían similares a la tabla siguiente:
| CalendarYear | CalendarQuarter | Ventas totales |
|---|---|---|
| 2020 | 1 | 25980.16 |
| 2020 | 2 | 27453.87 |
| 2020 | 3 | 28527.15 |
| 2020 | 4 | 31083.45 |
| 2021 | 1 | 34562.96 |
| 2021 | 2 | 36162.27 |
| ... | ... | ... |
Puede combinar tantas tablas de dimensiones como sea necesario para calcular las agregaciones que necesita. Por ejemplo, el código siguiente amplía el ejemplo anterior para desglosar los totales de ventas trimestrales por ciudad en función de los detalles de la dirección del cliente en la tabla 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;
Esta vez, los resultados incluyen un total de ventas trimestrales para cada ciudad:
| CalendarYear | CalendarQuarter | City | Ventas totales |
|---|---|---|---|
| 2020 | 1 | Ámsterdam | 5982.53 |
| 2020 | 1 | Berlín | 2826.98 |
| 2020 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2020 | 2 | Ámsterdam | 7163.93 |
| 2020 | 2 | Berlín | 8191.12 |
| 2020 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2020 | 3 | Ámsterdam | 7261.92 |
| 2020 | 3 | Berlín | 4202.65 |
| 2020 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2020 | 4 | Ámsterdam | 8262.73 |
| 2020 | 4 | Berlín | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Ámsterdam | 7261.28 |
| 2021 | 1 | Berlín | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Combinaciones en un esquema de copo de nieve
Cuando se utiliza un esquema de copo de nieve, las dimensiones pueden normalizarse parcialmente, lo que necesita varias combinaciones para relacionar tablas de hechos con dimensiones de copo de nieve. Por ejemplo, supongamos que el almacenamiento de datos incluye una tabla de dimensiones DimProduct desde la que las categorías de productos se han normalizado en una tabla DimCategory independiente. Una consulta para agregar elementos vendidos por categoría de producto podría ser similar al ejemplo siguiente:
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;
Los resultados de esta consulta incluyen el número de artículos vendidos para cada categoría de producto:
| ProductCategory | ItemsSold |
|---|---|
| Accessories | 28271 |
| Piezas | 5368 |
| ... | ... |
Nota
Las cláusulas JOIN para FactSales y DimProduct y para DimProduct y DimCategory son necesarias, aunque la consulta no devuelva campos de DimProduct.
Uso de funciones de categoría
Otro tipo común de consulta analítica es particionar los resultados en función de un atributo de dimensión y clasificar los resultados dentro de cada partición. Por ejemplo, es posible que quiera clasificar las tiendas cada año por sus ingresos de ventas. Para lograr este objetivo, puede usar funciones de clasificación de Transact-SQL, como ROW_NUMBER, RANK, DENSE_RANK y NTILE. Estas funciones permiten particionar los datos en categorías, cada una de las cuales devuelve un valor específico que indica la posición relativa de cada fila dentro de la partición:
- ROW_NUMBER devuelve la posición ordinal de la fila dentro de la partición. Por ejemplo, la primera fila está numerada como 1, la segunda como 2, etc.
- RANK devuelve la posición clasificada de cada fila en los resultados ordenados. Por ejemplo, en una partición de almacenes ordenados por volumen de ventas, el almacén con el volumen de ventas más alto se clasifica como 1. Si varias tiendas tienen los mismos volúmenes de ventas, se clasificarán igual y la clasificación asignada a las tiendas posteriores refleja el número de tiendas que tienen mayores volúmenes de ventas, incluidos los vínculos.
- DENSE_RANK clasifica las filas de una partición del mismo modo que RANK, pero cuando varias filas tienen la misma clasificación, las filas posteriores omiten las posiciones de clasificación.
- NTILE devuelve el percentil especificado en el que cae la fila. Por ejemplo, en una partición de almacenes ordenados por volumen de ventas,
NTILE(4)devuelve el cuartil en el que el volumen de ventas de una tienda lo coloca.
Por ejemplo, considere la siguiente consulta:
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;
La consulta divide los productos en agrupaciones en función de sus categorías y, dentro de cada partición de categoría, la posición relativa de cada producto se determina en función de su precio de lista. Los resultados de esta consulta podrían ser similares a la siguiente tabla:
| ProductCategory | ProductName | ListPrice | RowNumber | Rango | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Accessories | Widget | 8,99 | 1 | 1 | 1 | 1 |
| Accessories | Knicknak | 8,49 | 2 | 2 | 2 | 1 |
| Accessories | Piñón | 5,99 | 3 | 3 | 3 | 2 |
| Accessories | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessories | Lentejuela | 2,99 | 5 | 5 | 4 | 3 |
| Accessories | Badabing | 0,25 | 6 | 6 | 5 | 4 |
| Piezas | Flimflam | 7,49 | 1 | 1 | 1 | 1 |
| Piezas | Snickity wotsit | 6,99 | 2 | 2 | 2 | 1 |
| Piezas | Brida | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Nota
Los resultados de ejemplo muestran la diferencia entre RANK y DENSE_RANK. Tenga en cuenta que en la categoría Accesorios, los productos Piñón y Doodah tienen el mismo precio de lista; y son clasificados como el tercer producto con el precio más alto. El siguiente producto más alto tiene un valor RANK de 5 (hay cuatro productos más caros que) y un valor DENSE_RANK de 4 (hay tres precios más altos).
Para obtener más información sobre las funciones de clasificación, consulte Funciones de clasificación (Transact-SQL) en la documentación de Azure Synapse Analytics.
Recuperación de un recuento aproximado
Aunque el objetivo de un almacenamiento de datos es principalmente admitir modelos de datos analíticos e informes para la empresa, los analistas de datos y los científicos de datos a menudo necesitan realizar alguna exploración de datos inicial, solo para determinar la escala básica y la distribución de los datos.
Por ejemplo, la consulta siguiente usa la función COUNT para recuperar el número de ventas de cada año en un almacenamiento de datos 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;
Los resultados de esta consulta pueden ser similares a los siguientes:
| CalendarYear | Orders (Pedidos) |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
El volumen de datos de un almacenamiento de datos puede significar que incluso las consultas sencillas para contar el número de registros que cumplen los criterios especificados pueden tardar un tiempo considerable en ejecutarse. En muchos casos, no se requiere un recuento preciso: bastará con una estimación aproximada. También se puede utilizar la función APPROX_COUNT_DISTINCT como se muestra en el ejemplo siguiente:
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;
La función APPROX_COUNT_DISTINCT usa un algoritmo HyperLogLog para recuperar un recuento aproximado. Se garantiza que el resultado tenga una tasa de error máxima del 2 % con una probabilidad del 97 %, por lo que los resultados de esta consulta con los mismos datos hipotéticos que antes podrían tener un aspecto similar a la tabla siguiente:
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
Los recuentos son menos precisos, pero siguen siendo suficientes para una comparación aproximada de ventas anuales. Con un gran volumen de datos, la consulta que usa la función APPROX_COUNT_DISTINCT se completa más rápidamente y la precisión reducida puede ser un equilibrio aceptable durante la exploración de datos básica.
Nota
Consulte la documentación de la función APPROX_COUNT_DISTINCT para obtener más detalles.