En este artículo, vamos a ver cómo podemos convertir el rango de fechas en registros por día, pero antes de eso, debemos tener una idea clara de dónde nos será útil.
Supongamos que tenemos una base de datos donde queremos almacenar todos los registros de detalles del proyecto de los estudiantes de una clase en particular. Allí podemos ver cuánto tiempo le tomó a un estudiante completar su proyecto, pero se guarda en un formulario de rango de fechas. Por ejemplo, Student-1 comenzó a hacer el proyecto el 20 de noviembre de 2021 y lo completó el 24 de noviembre de 2021. Entonces, ¿cuánto tiempo le tomó completarla? 5 días, sencillo. Sin embargo, el cálculo no parecería tan sencillo cuando estamos trabajando con millones de datos. Es por eso que queremos convertir esto del rango de fechas a registros por día. Como podemos aplicar fácilmente funciones agregadas en filas separadas, y esto simplificaría nuestro análisis.
Aquí vamos a utilizar una “tabla de conteo” o “tabla de números ”.
Tabla de números/tabla de conteo: una tabla de números se conoce como la «navaja suiza» de SQL Server. Se puede usar en lugar de los bucles con operaciones basadas en conjuntos más rápidas, expandir conjuntos de datos, insertar datos de prueba y muchas otras cosas más. Tendrá una sola columna, con números consecutivos desde 0 o 1 hasta el número más alto que pueda necesitar.
Sintaxis:
--create a table CREATE TABLE Sample(SampleId int not null) DECLARE @NoOfRows int SET @NoOfRows = 1000000 --We can add as many rows as you want ; WITH P0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows P1 AS (SELECT 1 AS C FROM Pass0 AS A, P0 AS B), --4 rows P2 AS (SELECT 1 AS C FROM Pass1 AS A, P1 AS B), --16 rows P3 AS (SELECT 1 AS C FROM Pass2 AS A, P2 AS B), --256 rows P4 AS (SELECT 1 AS C FROM Pass3 AS A, P3 AS B), --65536 rows P5 AS (SELECT 1 AS C FROM Pass4 AS A, P4 AS B), --4,294,967,296 rows Tally AS (SELECT row_number() over(ORDER BY C) AS Number FROM P5) INSERT Sample (SampleId) SELECT Number FROM Tally WHERE Number <= @NoOfRows
Nota: la siguiente sintaxis fue inventada por Itzik Ben-Gan , un gurú de T-SQL
Dado que estamos usando una cláusula WITH , se puede convertir en INSERT y SELECT . En la última línea, WHERE Número <= @NoOfRows se puede ajustar fácilmente y podemos poner cualquier rango.
Ahora, veamos cómo una consulta SQL convierte el rango de fechas en registros por día.
Paso 1: Cree una base de datos y utilícela
Use el siguiente comando para crear una base de datos y usarla.
Consulta:
CREATE DATABASE GFG_Demo; USE GFG_Demo;
Producción:
Paso 2: definición de la tabla
Tenemos la siguiente tabla de trabajo realizado en la base de datos.
Consulta:
CREATE TABLE WorkDone (ID VARCHAR(10) NOT NULL , StartDate DATE NOT NULL , EndDate DATE NOT NULL);
Producción:
Paso 3: Insertar valores
El siguiente comando se utiliza para insertar valores en la tabla.
Consulta:
INSERT INTO WorkDone(ID,StartDate,EndDate)
VALUES('S1', '2021-01-02','2021-01-06'),
('S2', '2021-02-03','2021-02-07'),
('S3', '2021-03-02','2021-03-09'),
('S4', '2021-01-05','2021-01-11'),
('S5', '2021-02-22','2021-02-26'),
('S6', '2021-03-12','2021-03-17'),
('S7', '2021-01-05','2021-01-13'),
('S8', '2021-02-05','2021-02-09'),
('S9', '2021-03-13','2021-03-18'),
('S10', '2021-01-07','2021-01-12'),
('S11', '2021-02-06','2021-02-10'),
('S12', '2021-03-12','2021-03-19'),
('S13', '2021-01-12','2021-01-15'),
('S14', '2021-02-20','2021-02-24'),
('S15', '2021-03-10','2021-03-15'),
('S16', '2021-01-21','2021-01-28'),
('S17', '2021-02-21','2021-02-26'),
('S18', '2021-03-11','2021-03-16'),
('S19', '2021-01-23','2021-01-28'),
('S20', '2021-02-12','2021-02-16'),
('S21', '2021-03-08','2021-03-10'),
('S20', '2021-01-08','2021-01-14'),
('S21', '2021-02-05','2021-02-09'),
('S22', '2021-03-06','2021-03-09'),
('S23', '2021-01-14','2021-01-16'),
('S24', '2021-02-22','2021-02-26'),
('S25', '2021-03-05','2021-03-13');
Producción:
Paso 5: Ver datos de la tabla
Consulta:
SELECT * FROM WorkDone;
Producción:
Paso 6: Ampliación del intervalo de fechas
Consulta:
WITH S00(N) AS (SELECT 1 UNION ALL SELECT 1), S02(N) AS (SELECT 1 FROM S00 a, S00 b) , S04(N) AS (SELECT 1 FROM S02 a, S02 b) , S08(N) AS (SELECT 1 FROM S04 a, S04 b) , S16(N) AS (SELECT 1 FROM S08 a, S08 b) , S32(N) AS (SELECT 1 FROM S16 a, S16 b) , CteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM S32) , DateRange AS (SELECT SeparatedDate = DATEADD(DAY,N - 1,'2021-01-01') FROM CteTally WHERE N <= 365 ) SELECT * FROM WorkDone w JOIN DateRange d ON d.SeparatedDate >= w.[StartDate] AND d.SeparatedDate <= w.[EndDate];
Aquí, en el primer CTE S00 se juntan dos filas, por eso nos da 2 filas. El segundo CTE S02 se une en cruz consigo mismo y da como resultado 4 filas. Se continúa con el mismo proceso para el resto de las filas, lo que da como resultado 16, 256, 65536,… filas, y la última dará como resultado 2^32 filas.
Nota: 2^32 es el número más alto que puede contener un entero en SQL Server.
- La función de ventana ROW_NUMBER se utiliza para asignar un número a cada fila.
- Ahora, ¿qué pasa con la subconsulta que estamos usando en ORDER BY en la cláusula OVER ? Se utiliza para engañar al servidor para que no clasifique el conjunto de datos. Ordenar tantas filas podría deteriorar seriamente el rendimiento de la consulta. Esa consulta se usa para asignar un número secuencial único para cada fila, comenzando con el número 1.
- Recuerde, se usa para generar números, no fechas. Por eso estamos usando la función DATEADD para transformar los números en fechas. Esto generará todas las fechas del año 2021.
- Para explorar nuestros datos de muestra, tenemos que unir la tabla de conteo a la tabla de trabajo realizado mediante una combinación de rango.
Producción:
Aquí, vemos que se ha expandido con éxito. Sin embargo, no están en el orden correcto, podemos ordenarlos fácilmente agregando la función ORDER BY al final de la consulta. Como hemos mencionado antes, las tablas de conteo son expansivas. Podemos establecer nuestro límite deseado y no afectará tanto el rendimiento.