Gerando sequências de números inteiros com SQL
Neste artigo vamos ver como fazer para criar sequências de números inteiros utilizando SQL, os exemplos a seguir foram feitos utilizando MySQL e SQL Server. Estas sequências não serão gravadas em tabelas, serão geradas por consultas.
Vamos começar com algo simples. Criar uma sequência de 0 a 9. Para isso podemos usar UNION e juntar diversos SELECT retornando um número entre 0 a 9. Nossa consulta ficaria assim:
SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
Você deve estar se perguntando como seria para o caso de ser necessário retornar uma sequência entre 0 a 99. Neste caso, podemos repetir a consulta acima 2 vezes e agrupar os resultados fazendo uma multiplicação. Nossa consulta para retornar um número inteiro entre 0 a 99 ficaria desta forma:
SELECT d1+d2*10 AS gerador FROM
( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2
No exemplo acima, temos duas tabelas com conteúdos idênticos chamadas t1 e t2. Ambas são compostas de SELECTs que retornam um número, e estes SELECTs são unidos utilizando UNION, como vimos no primeiro exemplo. Juntamos estas duas tabelas t1 e t2 dentro de um SELECT onde somamos o campo da primeira tabela com o campo da segunda tabela multiplicado por 10.
Como resultados, obtivemos uma sequência entre 0 a 99. Perceba que os resultados não vieram ordenados.
Seguindo o mesmo principio, podemos obter uma sequência maior adicionando mais tabelas e multiplicando por outros valores. No exemplo a seguir, vamos criar uma sequência de inteiros entre 0 a 9999. Desta vez, vamos gerar o resultado ordenado.
select * from (
SELECT d1+d2*10+d3*100+d4*1000 AS gerador FROM
( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2,
( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t3,
( SELECT 0 AS d4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t4
) tabela order by gerador
Agora vamos ver como podemos chegar neste resultado de outras formas, de modo a simplificar o nosso trabalho. Uma outra alternativa para não ser necessário multiplicar o resultado é gerar os números já multiplicados dentro das tabelas auxiliares. O exemplo anterior ficaria da seguinte forma:
select * from (
SELECT d1+d2+d3+d4 AS gerador FROM
( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
( SELECT 0 AS d2 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) t2,
( SELECT 0 AS d3 UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400
UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900 ) t3,
( SELECT 0 AS d4 UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000
UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 8000 UNION SELECT 9000 ) t4
) tabela order by gerador
Uma forma mais elegante de resolver este tipo de problema é utilizando uma Commom Table Expression, ou CTE. Ela é um resultset temporário que pode ser reaproveitado várias vezes dentro do contexto atual. Existe uma pequena diferença na forma de implementar nosso gerador de números sequenciais de acordo com o tipo de banco de dados que estamos trabalhando. Vamos iniciar pelo MySQL.
WITH RECURSIVE numeros AS (
select 0 as numero
union all
select numero + 1
from numeros
where numero < 999)
select * from numeros;
No exemplo anterior, criamos um CTE chamado numeros. Dentro deles, fizemos o UNION de duas consultas, a primeira retorna um campo chamado numero com o valor zero. A segunda pega este CTE numeros que criamos e adiciona mais 1 no campo existente onde o número for menor que 999. Como criamos a CTE como RECURSIVE, ela vai repetir essa soma até atingir o valor informado no filtro WHERE.
Ao efetuar o SELECT em nossa CTE, temos os inteiros de 0 a 999 ordenados.
Caso você aumente o parâmetro utilizado no filtro WHERE, vai perceber que uma mensagem de erro vai ocorrer. A mensagem de erro que vai aparecer vai informar que existe um limite para a recursividade, por padrão o limite são 1000 iterações. Para gerar a lista de inteiros entre 0 a 9999 será necessário alterar este valor padrãi, para isso vamos rodar um comando antes de executar a nossa CTE.
SET SESSION cte_max_recursion_depth = 10000;
WITH recursive numeros AS (
select 0 as numero
union all
select numero + 1
from numeros
where numero < 9999)
select * from numeros;
Essa alteração se aplica apenas a sessão atual.
No SQL Server, a forma de aplicar a recursividade é um pouco diferente. Para atingir o mesmo resultado, nossa CTE ficaria da seguite forma:
WITH numeros AS (
select 0 as numero
union all
select numero + 1
from numeros
where numero < 9999)
select * from numeros OPTION (MAXRECURSION 10000);
Perceba que a alteração não foi dentro da CTE mas na hora de consumir. O parâmetro OPTION acrescentado no SELECT permite informar qual a quantidade de interações que vai ser utilizada.