Consultar datos
Una vez que las tablas de dimensiones y hechos de un almacenamiento de datos se rellenan con datos, puede usar T-SQL para consultarlas y analizarlas. T-SQL admite SELECT, , JOINfunciones de agregado, funciones de ventana y mucho más, lo que proporciona las herramientas para extraer, filtrar, agrupar y resumir los datos.
Agregar medidas por atributos de dimensión
Un patrón común consiste en unir una tabla de hechos a una o varias tablas de dimensiones y, a continuación, agregar una medida numérica agrupada por un atributo de dimensión.
La consulta siguiente agrega los importes de ventas por año y trimestre de las tablas FactSales y 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;
Los resultados tienen un aspecto similar a la tabla siguiente:
| AñoCalendario | CalendarQuarter | Ventas totales |
|---|---|---|
| 2024 | 1 | 25980.16 |
| 2024 | 2 | 27453.87 |
| 2024 | 3 | 28527,15 |
| 2024 | 4 | 31083.45 |
| 2025 | 1 | 34562.96 |
| 2025 | 2 | 36162.27 |
| ... | ... | ... |
Puede combinar varias tablas de dimensiones para segmentar los resultados mediante atributos adicionales. La consulta siguiente amplía el ejemplo anterior para desglosar las ventas trimestrales por ciudad mediante 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.
| AñoCalendario | CalendarQuarter | Ciudad | Ventas totales |
|---|---|---|---|
| 2024 | 1 | Ámsterdam | 5982.53 |
| 2024 | 1 | Berlín | 2826.98 |
| 2024 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2024 | 2 | Ámsterdam | 7163.93 |
| 2024 | 2 | Berlín | 8191.12 |
| 2024 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2024 | 3 | Ámsterdam | 7261.92 |
| 2024 | 3 | Berlín | 4202.65 |
| 2024 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2024 | 4 | Ámsterdam | 8262.73 |
| 2024 | 4 | Berlín | 5373.61 |
| 2024 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2025 | 1 | Ámsterdam | 7261.28 |
| 2025 | 1 | Berlín | 3648.28 |
| 2025 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Combinaciones en un esquema de copo de nieve
Al usar un esquema de copo de nieve, las dimensiones pueden normalizarse parcialmente, lo que requiere varias combinaciones para relacionar las tablas de hechos con las 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:
| Categoría de Producto | ItemsSold |
|---|---|
| Accesorios | 28271 |
| Ropa | 5368 |
| ... | ... |
Nota
Ambas JOIN cláusulas son necesarias para atravesar la cadena de relaciones de FactSales a DimCategory a través de DimProduct, aunque no aparezcan campos de DimProduct en los resultados.
Uso de funciones de categoría
Otra consulta analítica común particiona los resultados por un atributo de dimensión y los clasifica 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 crear particiones de datos entre categorías, cada una de las cuales devuelve un valor 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 de la misma manera que RANK, pero cuando varias filas tienen la misma clasificación, las filas posteriores se clasifican sin espacios; los vínculos no consumen posiciones adicionales.
-
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 particiona los productos por categoría, clasificando cada producto dentro de su partición por precio de lista. Los resultados tienen un aspecto similar a la tabla siguiente:
| CategoríaDeProducto | ProductName | Precio de lista | RowNumber | Rango | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Accesorios | Botella de agua | 8,99 | 1 | 1 | 1 | 1 |
| Accesorios | Banda de cabeza | 8,49 | 2 | 2 | 2 | 1 |
| Accesorios | Calentadores de brazo | 5,99 | 3 | 3 | 3 | 2 |
| Accesorios | Correa de tobillo | 5,99 | 4 | 3 | 3 | 2 |
| Accesorios | Insole | 2,99 | 5 | 5 | 4 | 3 |
| Accesorios | Cordón de zapato | 0,25 | 6 | 6 | 5 | 4 |
| Ropa | Gorra de correr | 7,49 | 1 | 1 | 1 | 1 |
| Ropa | Manga de compresión | 6,99 | 2 | 2 | 2 | 1 |
| Ropa | Bolsa de gimnasio | 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 Calentadores de brazos y Pulsera de tobillo tienen el mismo precio de lista, y ambos están ocupando el tercer lugar en el ranking de precios más altos. 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 el módulo Uso de funciones integradas y GROUP BY en Transact-SQL.
Recuperación de un recuento aproximado
Aunque el propósito 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 para determinar la escala básica y la distribución de los datos.
Por ejemplo, la consulta siguiente usa la COUNT función para recuperar el número de ventas de cada año:
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:
| AñoCalendario | Pedidos |
|---|---|
| 2023 | 239870 |
| 2024 | 284741 |
| 2025 | 309272 |
| ... | ... |
El volumen de datos de un almacenamiento de datos puede significar que incluso las consultas simples para contar el número de registros que cumplen los criterios especificados tardan mucho tiempo 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 tiene una tasa de error máxima de 2% con 97% probabilidad, por lo que los resultados de esta consulta podrían ser similares a la tabla siguiente:
| AñoCalendario | ApproxOrders |
|---|---|
| 2023 | 235552 |
| 2024 | 290436 |
| 2025 | 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.