SQL Server, Trucos

Creación de tablas numéricas auxiliares (Tally Tables) (y 2)

En la primera parte de este artículo explicábamos en qué consiste una tabla numérica auxiliar, así como varias técnicas para su creación. En esta segunda entrega continuaremos exponiendo formas adicionales de generar tablas numéricas, de modo que el lector disponga de un amplio abanico de opciones que pueda aplicar en las más variadas situaciones.

 

Pseudo Cursor + IDENTITY()

Una variante de la operación expuesta en el apartado sobre pseudo cursores de la primera parte consistiría en utilizar una sola sentencia para crear, al mismo tiempo, la tabla numérica y rellenarla de valores, utilizando para tal propósito la instrucción SELECT…INTO junto a la función IDENTITY(), que en este caso realizaría idéntico trabajo de generación de números que ROW_NUMBER() en el ejemplo anterior.


IF OBJECT_ID('Numeros','U') IS NOT NULL
BEGIN
DROP TABLE Numeros;
END

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

SELECT
IDENTITY(int,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

Al igual que en el apartado anterior, si nuestra versión de SQL Server no soporta el uso de OFFSET…FETCH, emplearemos TOP para especificar el número de filas a rellenar en la tabla numérica auxiliar.


..........
..........
SELECT TOP (30000)
IDENTITY(INT,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2;
..........
..........

Cruce múltiple de CTEs

A continuación es el turno de un interesante método desarrollado por Itzik Ben-Gan en su obra «Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions» [4], que se basa en el empleo de una serie de CTEs combinadas mediante cláusulas CROSS JOIN, para obtener el rango secuencial de números con el que rellenaremos nuestra tabla numérica.

La primera CTE, a la que daremos el nombre cteGenNumsBase (de generador base de números), se encarga de crear un conjunto de resultados que devuelve dos filas con el valor 1 en cada una de ellas, empleando un constructor de valores para tabla (Table Value Constructor) en forma de tabla derivada dentro de una cláusula FROM.


WITH
cteGenNumsBase AS (
SELECT NumBaseID
FROM (
VALUES
(1),
(1)
) AS tblNums(NumBaseID)
)
SELECT NumBaseID
FROM cteGenNumsBase;

 

TablasNumericasAuxiliaresTallyTable_07

A continuación agregaremos otra CTE con el nombre cteGenNums1 (de primer generador de números con CROSS JOIN), conteniendo una consulta contra la CTE cteGenNumsBase, que realizará una CROSS JOIN sobre esa misma CTE, obteniendo esta vez cuatro filas con el valor 1.


WITH
cteGenNumsBase AS (
SELECT NumBaseID
FROM (
VALUES
(1),
(1)
) AS tblNums(NumBaseID)
),
cteGenNums1 AS (
SELECT 1 AS NumID
FROM cteGenNumsBase AS tblGenNumsA
CROSS JOIN cteGenNumsBase AS tblGenNumsB
)
SELECT NumID
FROM cteGenNums1;

 

TablasNumericasAuxiliaresTallyTable_08

 

Aunque no hemos completado todavía el código de este ejemplo, observe el lector que estamos empezando a aplicar nuevamente la técnica del pseudo cursor, ya que en la CTE cteGenNums1 utilizamos la presencia de filas obtenida a partir de la CTE cteGenNumsBase para generar nuevas filas, doblando su número hasta cuatro. Para volver a doblar dicho número, tan sólo necesitamos añadir otra CTE que utilice la misma lógica en su código, y así sucesivamente hasta concluir con una CTE (que aquí llamamos cteGenNumsTotal), que como ya conocemos de anteriores ejemplos, mediante la función ROW_NUMBER() se encargaría de generar la secuencia de números. La unión de todos estos elementos es lo que Jeff Moden denomina en [3] CTEs cruzadas en cascada (CROSS JOINED Cascaded CTE).

Seguidamente vemos el código del ejemplo al completo, en el que utilizando cinco CTEs para generar filas a partir de la CTE base, podríamos obtener hasta 4.294.967.296 filas, cantidad que raramente nos encontraremos en situación de necesitar; de ahí que mediante la cláusula OFFSET…FETCH de ORDER BY, limitemos el número de registros obtenidos.


WITH
cteGenNumsBase AS (
--devuelve 2 filas
SELECT NumBaseID
FROM (
VALUES
(1),
(1)
) AS tblNums(NumBaseID)
),
cteGenNums1 AS (
--devuelve 4 filas
SELECT 1 AS NumID
FROM cteGenNumsBase AS tblGenNumsA
CROSS JOIN cteGenNumsBase AS tblGenNumsB
),
cteGenNums2 AS (
--devuelve 16 filas
SELECT 1 AS NumID
FROM cteGenNums1 AS tblGenNumsA
CROSS JOIN cteGenNums1 AS tblGenNumsB
),
cteGenNums3 AS (
--devuelve 256 filas
SELECT 1 AS NumID
FROM cteGenNums2 AS tblGenNumsA
CROSS JOIN cteGenNums2 AS tblGenNumsB
),
cteGenNums4 AS (
--devuelve 65.536 filas
SELECT 1 AS NumID
FROM cteGenNums3 AS tblGenNumsA
CROSS JOIN cteGenNums3 AS tblGenNumsB
),
cteGenNums5 AS (
--devuelve 4.294.967.296 filas
SELECT 1 AS NumID
FROM cteGenNums4 AS tblGenNumsA
CROSS JOIN cteGenNums4 AS tblGenNumsB
),
cteGenNumsTotal AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
FROM cteGenNums5
)
SELECT NumeroID
FROM cteGenNumsTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

El bloque de código anterior, aun cumpliendo su cometido, puede resultar un tanto sobrecargado debido a la extensión de los nombres utilizados y a la disposición de las sentencias; ello se debe a que el propósito principal es que la lógica aplicada resulte lo más comprensible al lector. Por dicho motivo, a continuación presentamos una versión más compacta del mismo, donde tras reducir los nombres de los identificadores, la colocación de las sentencias y las líneas de código, obtenemos la misma funcionalidad. Aprovechamos también para incluir aquí las sentencias de creación de la tabla numérica auxiliar.


IF OBJECT_ID('Numeros','U') IS NOT NULL
BEGIN
DROP TABLE Numeros;
END

CREATE TABLE Numeros
(
NumeroID int NOT NULL
);

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

Uno de los puntos fuertes de esta técnica reside en su excelente aprovechamiento de recursos, ya que durante la generación de las filas, el procesador de consultas del motor de SQL Server se detiene justo en el momento de alcanzar el número de registros especificado en ORDER BY…OFFSET…FETCH.

Como ya hemos explicado en ejemplos previos, para establecer el número de registros que necesitamos obtener, también podemos conseguir el mismo resultado mediante la cláusula TOP en caso de usar una versión de SQL Server que no implemente OFFSET…FETCH.


..........
..........
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT TOP (30000) NumeroID
FROM GNTotal;
..........
..........

De igual forma, si la versión de SQL Server con la que trabajamos no soporta constructores de valores para tabla, en la primera CTE utilizaremos dos consultas que devuelvan el valor 1, aplicando sobre ellas el operador UNION.


..........
..........
WITH
GNBase AS (SELECT 1 AS N UNION ALL SELECT 1 AS N),
..........
..........

 

Incluir el valor cero como primer registro

La tabla numérica auxiliar creada en todos los ejemplos mostrados hasta el momento contiene el valor 1 en su primera fila, pero podríamos encontrarnos con situaciones en las que necesitáramos que ese primer registro de la tabla fuera un cero.

Para conseguir tal propósito podemos añadir una consulta a la CTE final del grupo de CTEs cruzadas, en la que devolvamos un conjunto de resultados con el valor cero, que combinaremos mediante el operador UNION con la consulta ya existente, encargada de los cruces múltiples de las CTEs. Por último, para poder obtener el último número que necesitamos del intervalo, tenemos que incrementar en uno el valor indicado en el modificador FETCH de la cláusula ORDER BY.

Como ejemplo de lo que acabamos de explicar, si queremos crear una tabla numérica con el rango de números de 0 a 30000, el valor de FETCH deberá ser 30001.

TablasNumericasAuxiliaresTallyTable_09

 

TablasNumericasAuxiliaresTallyTable_10

 

Intervalo de números con valor de inicio mayor de 1

Otra situación con la que podríamos encontrarnos, relacionada con la primera fila de la tabla numérica, sería que su valor tuviera que ser superior a 1, debiendo especificar, adicionalmente, los límites inferior y superior de la tabla.

Este problema puede ser resuelto mediante el uso de un procedimiento almacenado, al que pasaremos dos parámetros con los mencionados límites de la tabla, los cuales utilizaremos dentro del código del procedimiento en los modificadores OFFSET…FETCH tal y como vemos a continuación.


IF OBJECT_ID('spGenerarTablaNumeros','P') IS NOT NULL
BEGIN
DROP PROCEDURE spGenerarTablaNumeros;
END
GO

CREATE PROCEDURE spGenerarTablaNumeros
@nPrimerNumero AS int,
@nUltimoNumero AS int
AS
BEGIN

IF OBJECT_ID('Numeros','U') IS NOT NULL
BEGIN
DROP TABLE Numeros;
END

CREATE TABLE Numeros
(
NumeroID int NOT NULL
);

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
ROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET (@nPrimerNumero - 1) ROWS
FETCH FIRST ((@nUltimoNumero + 1) - @nPrimerNumero) ROWS ONLY;

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID)

END
GO

Si ahora queremos crear una tabla numérica cuya primera y última fila sean respectivamente los valores 12537 y 28754, ejecutaríamos la siguiente sentencia.


EXECUTE spGenerarTablaNumeros 12537,28754;

TablasNumericasAuxiliaresTallyTable_11

Para lograr mediante la cláusula TOP el mismo resultado realizaremos la siguiente modificación al código del procedimiento almacenado.


..........
..........
INSERT INTO Numeros (NumeroID)
SELECT TOP((@nUltimoNumero + 1) - @nPrimerNumero) (@nPrimerNumero - 1) +
NumeroID
FROM GNTotal
..........
..........

Una vez completado este ejemplo concluimos el presente artículo en el que a lo largo de sus dos entregas hemos realizado una introducción a las tablas numéricas auxiliares o tally tables, presentando algunas de las técnicas más destacadas en la creación de las mismas. En un próximo artículo expondremos ejemplos prácticos del uso de esta herramienta confiando en que resulte de utilidad al lector.

 

Referencias

[1] Jeff Moden. «The «Numbers» or «Tally» Table: What it is and how it replaces a loop». http://www.sqlservercentral.com/articles/T-SQL/62867/

[2] Itzik Ben-Gan. «Microsoft SQL Server 2012 T-SQL Fundamentals». http://blogs.msdn.com/b/microsoft_press/archive/2012/07/16/new-book-microsoft-sql-server-2012-t-sql-fundamentals.aspx

[3] Jeff Moden. «Hidden RBAR: Counting with Recursive CTE’s». http://www.sqlservercentral.com/articles/T-SQL/74118/

[4] Itzik Ben-Gan. «Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions». http://blogs.msdn.com/b/microsoft_press/archive/2012/04/26/new-book-microsoft-sql-server-2012-high-performance-t-sql-using-window-functions.aspx

2 Comentarios

  1. anonymous

    Después de los artículos dedicados a la creación de tablas numéricas auxiliares

  2. anonymous

    Después de explicar en el anterior artículo el modo de creación de tablas de dimensión

Deja un comentario

Tema creado por Anders Norén