En el presente artículo, que finaliza el monográfico sobre generación de fechas aleatorias, vamos a utilizar la distribución Weibull, expuesta también por Dwain Camps, para lo que crearemos una función con 3 parámetros: un valor de escala, otro de forma y un número aleatorio, todos ellos de tipo float. Seguidamente vemos su código fuente, que hemos escrito directamente como función de tipo table-valued, puesto que como función escalar adolecería de los mismos problemas de rendimiento que ya observamos en la anterior entrega con la función Gaussian.
CREATE FUNCTION fnAleatorioWeibullTbl( @nAlpha float, @nBeta float, @nAleatorio float ) RETURNS TABLE AS RETURN ( SELECT POWER((-1. / @nAlpha) * LOG(1. - @nAleatorio), 1. / @nBeta) AS Aleatorio )
Al igual que en la distribución Gaussian, utilizaremos el operador CROSS APPLY para ejecutar esta función, pero esta vez nos encontraremos con un problema debido al tipo de los datos obtenidos. En primer lugar, haciendo uso de la presencia de filas en la tabla INEPadron, vamos a generar los números aleatorios. Como fnAleatorioWeibullTbl devuelve valores float, los convertiremos a int y haremos un recuento de los números distintos obtenidos.
WITH cteDatos AS ( SELECT CONVERT(int,aw.Aleatorio) AS Aleatorio FROM INEPadron CROSS APPLY dbo.fnAleatorioWeibullTbl( 1.0, 1.5, RAND(CHECKSUM(NEWID())) ) AS aw ) SELECT Aleatorio, COUNT(*) AS Recuento FROM cteDatos GROUP BY Aleatorio ORDER BY Aleatorio;
Parece evidente que tenemos un problema, ya que el rango de números obtenido no se acerca en absoluto al intervalo que necesitaríamos de 1-365 (366).
Una posible solución consistiría en tomar los 3 primeros dígitos de la parte decimal, correspondientes al número devuelto por la función fnAleatorioWeibullTbl, y dividirlo entre el número de días del año utilizando el operador módulo (%), para obtener, de esa forma, un número en el intervalo requerido, que después utilizaríamos para generar la fecha.
WITH cteDatos AS ( SELECT aw.Aleatorio FROM INEPadron CROSS APPLY fnAleatorioWeibullTbl( 1.0, 1.5, RAND(CHECKSUM(NEWID())) ) AS aw ) SELECT Aleatorio, (CONVERT(int,(CONVERT(decimal(5,4), Aleatorio) * 1000)) % 365) AS NumeroDias FROM cteDatos;
Lamentablemente, esta técnica no funciona correctamente, ya que para aquellos valores cuya parte entera es mayor de cero, y los 3 dígitos decimales se encuentran en el intervalo antes mencionado, el número resultante es diferente, como vemos en la siguiente figura.
En el siguiente bloque de código podemos comprobar, paso a paso, las operaciones realizadas. Ello nos sirve para confirmar el problema antes mencionado: cuando la parte entera es mayor de cero, se incluye el dígito de la parte entera junto a los decimales, con lo que el resultado de la operación no es el esperado.
WITH cteDatos AS ( SELECT aw.Aleatorio FROM INEPadron CROSS APPLY fnAleatorioWeibullTbl( 1.0, 1.5, RAND(CHECKSUM(NEWID())) ) AS aw ) SELECT Aleatorio, (CONVERT(decimal(5,4), Aleatorio) * 1000) AS Mover_Decimales_A_Parte_Entera, CONVERT(int,(CONVERT(decimal(5,4), Aleatorio) * 1000)) AS Convertir_Decimales_A_Int, (CONVERT(int,(CONVERT(decimal(5,4), Aleatorio) * 1000)) % 365) AS Calcular_Numero_Dias FROM cteDatos;
Para obligar a que el número de decimales utilizado sea siempre 3, en primer lugar dividiremos el número aleatorio entre 1 empleando el operador módulo (%). De esta forma igualamos a 0 la parte entera de todos los números. El resto de la operación permanece sin alterar.
WITH cteDatos AS ( SELECT aw.Aleatorio FROM INEPadron CROSS APPLY fnAleatorioWeibullTbl( 1.0, 1.5, RAND(CHECKSUM(NEWID())) ) AS aw ) SELECT Aleatorio, CONVERT(decimal(5,4), Aleatorio) % 1 AS Entero_A_Cero, ((CONVERT(decimal(5,4), Aleatorio) % 1) * 1000) AS Mover_Decimales_A_Parte_Entera, CONVERT(int,((CONVERT(decimal(5,4), Aleatorio) % 1) * 1000)) AS Convertir_Decimales_A_Int, (CONVERT(int,((CONVERT(decimal(5,4), Aleatorio) % 1) * 1000)) % 365) AS Calcular_Numero_Dias FROM cteDatos;
Una vez solventado el problema del cálculo del número de días, integraremos esta operación en el bloque de código que se ocupa de cargar los datos en la tabla INEPadron.
DECLARE @dtFechaInicio AS date; DECLARE @dtFechaFin AS date; DECLARE @nNumDiasAnualidad AS int; SET @dtFechaInicio = '2017'; SET @dtFechaFin = EOMONTH(@dtFechaInicio,11); SET @nNumDiasAnualidad = DATEDIFF(day,@dtFechaInicio,@dtFechaFin) + 1; TRUNCATE TABLE INEPadron; INSERT INTO INEPadron WITH (TABLOCK) ( ProvinciaResidenciaID, MunicipioResidenciaID, SexoID, ProvinciaNacimientoID, MunicipioPaisNacimientoID, NacionalidadID, Edad, TamMunicipioResidenciaID, TamMunicipioNacimientoID, Fecha ) SELECT SUBSTRING(Datos,1,2), SUBSTRING(Datos,1,2) + IIF( LEN(SUBSTRING(Datos,3,3)) = 0, '999', SUBSTRING(Datos,3,3) ), CONVERT(int,SUBSTRING(Datos,6,1)), SUBSTRING(Datos,7,2), SUBSTRING(Datos,7,2) + IIF( LEN(SUBSTRING(Datos,9,3)) = 0, '999', SUBSTRING(Datos,9,3) ), SUBSTRING(Datos,12,3), CONVERT(int,SUBSTRING(Datos,15,3)), SUBSTRING(Datos,18,2), SUBSTRING(Datos,20,2), CONVERT( date, DATEADD( day, (CONVERT(int,((CONVERT(decimal(5,4), aw.Aleatorio) % 1) * 1000)) % nNumDiasAnualidad), @dtFechaInicio ) ) FROM INEPadronOrigen CROSS APPLY dbo.fnAleatorioWeibullTbl( 1.0, 1.5, RAND(CHECKSUM(NEWID())) ) AS aw;
Antes de representar gráficamente los datos que acabamos de generar, revisemos la información que acerca de esta distribución existe en Wikipedia, y más en concreto, el gráfico correspondiente a los mismos valores de los parámetros que hemos utilizado en nuestra función (1.0 para escala y 1.5 para forma).
A continuación construiremos un modelo de Power BI, que de igual forma que en las anteriores distribuciones, visualice un gráfico de línea con la cantidad de filas asignadas para cada fecha.
Debido a los ajustes y cálculos realizados al tomar los valores devueltos por nuestra función fnAleatorioWeibullTbl, hemos introducido en el proceso un factor de distorsión que se refleja en la gráfica, que si bien muestra inicialmente un trazado ligeramente similar al existente en el enlace de Wikipedia, se ve abruptamente alterado en el descenso de fechas asignadas alrededor de finales del mes de septiembre.
Conclusiones
El presente artículo finaliza el monográfico sobre generación de fechas aleatorias, donde hemos mostrado diversas técnicas para la producción de números aleatorios sobre una fuente de datos de gran volumen. Confiamos en que pueda resultar de utilidad al lector en sus propios procesos de creación de datos de prueba.
Enlaces de interés
Dwain Camps: http://www.sqlservercentral.com/articles/SQL+Uniform+Random+Numbers/91103/
Weibull distribution (Wikipedia): https://en.wikipedia.org/wiki/Weibull_distribution
Documentación SQL Server 2016: https://docs.microsoft.com/en-us/sql/index?view=sql-server-2016
INE (Instituto Nacional de Estadística): http://www.ine.es/
DAX Guide: https://dax.guide/
Deja un comentario