Transformando colunas em linhas III

Uma forma mais simples de transformar colunas em linhas é usar a função decode (do Oracle). Considere a seguinte tabela:

 CREATE TABLE PESSOAS (
    NOME VARCHAR2(200),
    NOME_PAI VARCHAR2(200),
    NOME_MAE VARCHAR2(200)
  )

Se for necessário produzir uma lista com todos os nomes, pode-se usar o decode da seguinte maneira:

select decode(n, 1, nome, 2, nome_pai, 3, nome_mae) 
from pessoas, 
     (select rownum n from pessoas where rownum<4)

A consulta com rownum serve apenas para produzir três linhas com os números 1, 2 e 3. Essa pequena tabela multiplica a tabela de pessoas por 3. O decode() então indica uma coluna diferente para cada valor de n. Assim, colocamos cada valor de coluna numa linha separada.

Antes que seja tarde

Hoje encontrei uma conta deveras interessante envolvendo a velocidade atual dos computadores. O objetivo dela é descobrir qual a distância que a luz percorre durante um ciclo de máquina.

1. A velocidade da luz é cerca de 300.000km/s ou 3*10^8m/s
2. Uma máquina de 3GHz (bem comum hoje em dia) executa um ciclo a cada 1/3.000.000.000s ou 1/3*10^9s
3. Logo, em um ciclo de máquina a luz consegue andar 0,1m ou 10cm.

Isso significa que se eu levantar uma placa com uma conta (por exemplo, 1034*564), uma máquina moderna consegue calcular o resultado antes que a luz rebatida pela placa chegue a uma pessoa do outro lado da sala.

Transformando colunas em linhas II

No artigo anterior, usei expressões regulares para transformar strings com valores separados por vírgula em linhas de uma consulta. Outra utilidade dessa técnica é transformar colunas de uma consulta em linhas.

Por exemplo, considere uma tabela de pessoas que inclui também os nomes dos pais:

  CREATE TABLE PESSOAS (
    NOME VARCHAR2(200),
    NOME_PAI VARCHAR2(200),
    NOME_MAE VARCHAR2(200)
  )

Para transformar isto numa única consulta que retorne os nomes de todas as pessoas referenciadas, podemos juntar com vírgula os nomes linha a linha e depois separá-los com uma expressão regular:

select trim(trailing ',' from nome) from (
  select regexp_substr(
            nome||','||nome_pai||','||nome_mae||','
         ,'[^,]+,',1,n.n) nome
  from pessoas,
      (select rownum n from all_tables where rownum<4) n
)

Deixei a concatenação sozinha numa linha, para evitar a confusão de vírgulas dentro e fora de strings.

A expressão "[^,]+," busca cada pedaço do string que não contenha vírgulas, mas que termine com uma. Por isso, é preciso adicionar uma vírgula ao último item. Por fim, tiram-se as vírgulas e apresentam-se os nomes, linha a linha.

Transformando textos em linhas

Uma função que não se acha no SQL é a split(). Por exemplo, para transformar o string '12,34,56,78,90' em linhas de uma consulta, podemos escrever um pequeno bloco de PL/SQL no Oracle. No entanto, quero usar apenas o SQL.

Minha solução é a seguinte:

select trim(trailing ',' from item) from (
  select regexp_substr('12,34,56,78,90','\d+,',1,n.n) item
  from dual,
    (select rownum n 
     from all_tables 
     where rownum<length(translate('12,34,56,78,90', ',1234567890', ','))+1) n
) 

A função regexp_substr() permite indicar qual ocorrência da busca queremos mostrar, então, junto a tabela dual (que só tem uma linha) com n linhas de all_tables a fim de mostrar todas as ocorrências. Cada uma vira uma linha. Além disso, uso o mesmo string na consulta a all_tables para encontrar o número de vírgulas e somar um, produzindo o número total de elementos na lista.

Para testar mais um pouco minha pequena solução, criei uma tabela dos campões do mundo de futebol:

CREATE TABLE CAMPEOES (
  PAIS VARCHAR2(100), 
  CAMPEONATOS VARCHAR2(200)
)

E dentro dessa tabela coloquei o seguinte:

PAIS	CAMPEONATOS
Uruguai	1930,1950
Alemanha	1954,1974,1990
Itália	1934,1938,1982,2006
Argentina	1978,1986
Inglaterra	1966
Espanha	2010
França	1998
Brasil	1958,1962,1970,1994,2002

Para enumerar os campeões ano a ano, executei a seguinte consulta:

select pais, trim(trailing ',' from item) from (
  select pais, regexp_substr(campeonatos||',','\d+,',1,n.n) item
  from campeoes,
    (select rownum n from all_tables where rownum<10) n
) where item is not null
order by 2

Uruguai	1930
Itália	1934
Itália	1938
Uruguai	1950
Alemanha	1954
Brasil	1958
Brasil	1962
Inglaterra	1966
Brasil	1970
Alemanha	1974
Argentina	1978
Itália	1982
Argentina	1986
Alemanha	1990
Brasil	1994
França	1998
Brasil	2002
Itália	2006
Espanha	2010

Modelei ao banco ao problema, mas a solução serve também para quando a modelagem for o problema.

Sexo e SOPA

Quando estamos no colégio nos ensinam um monte de simplificações e mentiras com o objetivo de nos dar uma idéia de como o mundo funciona. Uma mentira que apenas recentemente revelou-se para mim é a de que o sexo tem o objetivo de conferir maior variabilidade genética a uma população.

A semelhança que o nosso genoma tem com os outros mamíferos é impressionante. Mesmo com o pequeno rato, temos 80% dos genes com comum. Com os chimpanzés temos algo como 1% de diferença. Entre dois seres humanos, a diferença é minúscula. Então, não é variabilidade que procuramos, mas correção de erros!

A radiação e poluição com as quais convivemos todos os dias vão, pouco a pouco, minando nossa estrutura. Não há como um organismo viver por muito tempo sem uma correção. Nada melhor que o sexo. Comparamos o projeto de um com o projeto do outro, separamos os defeitos e criamos uma cópia.

Claro, ninguém vai contar para as crianças que seus organismos estão se decompondo aos poucos e que o melhor que têm a fazer é procriar. Como adultos, essa idéia também não é muito agradável (a parte sobre decompor, isto é).

Então, estamos nós neste planeta, dependendo vitalmente da nossa capacidade de copiar a nós mesmos enquanto algumas pessoas querem implantar leis como SOPA, PIPA e ACTA para colocar na cadeia quem copia! Não é natural e tampouco é saudável ensinar às crianças que compartilhar é feio.

Sempre achei que essa conversa de propriedade intelectual fosse conversa para consumidor dormir. Agora tenho a prova biológica!