Criando uma tabela com uma seqüência de números – Parte I

Boa Noite Pessoal,

Hoje demonstrarei como criar uma tabela com uma seqüência de números (Ex: todos os números de 1 a 10). Esse tipo de tabela auxiliar pode ser extremamente útil em diversas situações de pesquisas mais apuradas.

Alguns SGBDs como o ORACLE possuem alguns dados gravados em uma tabela especial chamada DUAL. Essa tabela possui dados úteis, mas sem relação com o negócio e desnecessário para o autogerenciamento (para isso existem os objetos de catálogo). Infelizmente o SQL Server não possui algo do tipo e não podemos "criar" dados do nada.

O comando TSQL abaixo cria uma tabela com 10 registros compreendendo todos os números de 0 a 9.

SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9

Se é possível produzir uma tabela com 100 números, para produzir uma tabela com 100 números basta combinar a tabela de 10 números com ela mesma uma única vez já que afina 10 x 10 é igual a 100. Para facilitar essa combinação, utilizarei uma CTE.

WITH Nums (Num) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9)

SELECT * FROM Nums AS N1 CROSS JOIN Nums AS N2

Temos como total um conjunto de 100 registros (ou 100 número), mas não necessariamente os números de 1 a 100. Podemos fazer alguns truques para obter esses números. Ex:

WITH Nums (Num) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9)

SELECT (N1.Num * 10) + N2.Num + 1
FROM Nums AS N1 CROSS JOIN Nums AS N2

Se estiveremos utilizando o SQL Server 2005, esse truque pode ser dispensado, pois, com a função ROW_NUMBER podemos gerar uma coluna com a seqüência desejada. Podemos inclusive combinar tabelas com menor quantidade de registros. No exemplo acima, o ROW_NUMBER se aplica da seguinte forma:

WITH Nums (Num) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9),

Numeros (Pos) AS (

SELECT ROW_NUMBER() OVER (ORDER BY N1.Num ASC)
FROM Nums AS N1 CROSS JOIN Nums AS N2)

SELECT Pos FROM Numeros

Uma vez que temos os números de 0 a 100, podemos transformar isso em uma FUNCTION para gerar uma relação de números até 100. Ex:

CREATE FUNCTION dbo.FNRetNum (@Limite TINYINT)
RETURNS TABLE
AS RETURN
(

WITH Nums (Num) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9)

SELECT ROW_NUMBER() OVER (ORDER BY N1.Num ASC) AS Num
FROM Nums AS N1 CROSS JOIN Nums AS N2
WHERE (N1.Num * 10) + N2.Num + 1 <= @Limite)

– Retornar os números de 1 a 3
SELECT Num FROM dbo.FNRetNum(3)

– Retornar os números de 1 a 8
SELECT Num FROM dbo.FNRetNum(8)

– Retornar os números de 1 a 9
SELECT Num FROM dbo.FNRetNum(9)

Se for necessário aumentar o intervalo basta combinar a tabela com ela mais uma vez. Ao invés de N1 e N2 somente, adicionaríamos o N3 para produzir 1.000 números e o N4 para produzir 10.000 números. Essa é uma forma bem simplista de como produzir uma seqüência de números sem recorrer a cursores ou tabelas temporárias e Loops no TSQL. Se você desejar "misturar" um pouco as coisas, podemos nos utilizar das tabelas de sistema do SQL Server que sempre terão registros. Ex:

– Produzir uma tabela com 1.000 números
WITH Numeros (Num) AS (
SELECT ROW_NUMBER() OVER (ORDER BY object_id ASC)
FROM sys.all_objects)

SELECT Num FROM Numeros WHERE Num <= 1000

Em todo caso, esse tipo de construção pode levar a uma total confusão e falta de interpretação quando misturada a tabelas de negócio. Imagine aparecer uma tabela de sistema misturada a tabelas de negócio sem uma razão aparente ? Quem estivesse fazendo uma manutenção poderia achar bem peculiar e talvez reescrever a consulta de outra forma (mesmo que a contagem utilizando uma tabela de sistema fizesse diferença).

As consultas demonstradas até o momento utilizaram construções TSQL que montam a tabela de números em tempo de execução. Ainda que elas sejam eficientes, nada seria mais rápido do que ter uma tabela fisicamente armazenada no banco de dados com um intervalo de números (digamos de 0 a 100, de 0 a 1.000, de 0 a 10.000 e assim por diante). Quando essa tabela fosse requisitada ela já estaria pronta.

Estou certo de que alguns ao ler esse post devem estar se perguntado porque alguém desejaria ter uma tabela de números de 0 a 100. Qual seria a utilidade de uma tabela dessas ? Pra que serve uma tabela de seqüência de números ? Criar uma tabela com uma seqüencia de números pode revelar um exímio desenvolvedor em TSQL, mas posso adiantar aos curiosos posso adiantar que uma tabelas dessas é muito mais útil do que parece. Demonstrarei duas aplicações de sua utilização em breve.

[ ]s,

Gustavo

About these ads

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s