Mostrando postagens com marcador SQL. Mostrar todas as postagens
Mostrando postagens com marcador SQL. Mostrar todas as postagens

sexta-feira, 22 de março de 2019

Fourier em SQL (e Perl)

Dada uma nova ferramenta, resolvi experimentar no SQL. A transformada discreta de Fourier resume-se, basicamente, a um montão de multiplicações e somas. É fácil repetir em SQL. Já a FFT nem tanto. SQL simplesmente não se presta a recursões.

Outra desvantagem do SQL é a ausência de suporte a números complexos (Perl oferece o fantástico módulo Math::Complex que, inclusive, reimplementa todas as operações da linguagem). A DFT não exige muito, basta separar a conta em cos() e i*sin(). Mas se fôssemos implementar a FFT, seria preciso fazer a multiplicação por extenso. Isso só seria chato, resolver a recursão é que é o problema do SQL.

Mesmo assim, a DFT pode ser bem útil e, se vamos usá-la num banco, é porque não estamos com pressa mesmo.

Comecei por um algoritmo FFT em Perl para poder conferir os resultados do SQL. O script executa a FFT sobre a função sin(x), assim como a consulta (que escrevi para conferir os resultados do Perl).

DFT de sin(x)

Os dois deram o mesmo resultado, mas a implementação do FFT foi muito mais rápida, como esperado.

O grande porém da FFT é que precisamos ter um número de elementos que seja potência de 2. Com DFT, podemos usar qualquer quantidade. Então, resolvi experimentar numa tabela de pagamentos.

Usei dois anos de pagamentos e obtive o seguinte gráfico (que parece indicar que há um componente trimestral nos pagamentos):


Aprender a implemenar a DFT e a FFT é só o primeiro passo. Analisar os resultados e achar aplicações é o próximo passo.

sexta-feira, 6 de abril de 2018

Inflação Acumulada Com Funções Analíticas

Imagine ter uma tabela com os valores de inflação mês a mês:

create table ipca (
  data date,
  inflacao number(5,2)
);

Mas, além da inflação do mês, é preciso calcular a inflação acumulada dos últimos doze meses. Com funções analíticas, isso deve ser fácil. Entretanto, o SQL oferece sum() e count(), mas não uma função de agregação que multiplique os valores. Com um pouquinho de esperteza e logaritmos, isso pode ser superado. O segredo é somar os logaritmos e depois tirar o exponencial.

select data, inflacao, (exp(acumulada)-1)*100 from (
  select data, inflacao, 
    sum(ln(1+(inflacao/100)))
      over (order by data desc rows between current row and 11 following)
        as acumulada
  from ipca
)

A janela de classificação é autoexplicativa: ela ordena os índices por data decrescente e olha o registro atual e os onze anteriores a ele.

Um índice 0,23% vira 1,0023 (1+0,23/100) e um índice -0,05% vira 0,9995 (1-0,05/100). Por isso, substrai-se 1 do exponencial. Se o índice for 0, o 1 o neutraliza. A soma dos logaritmos desses valores corresponde à multiplicação.

segunda-feira, 28 de novembro de 2016

Transporte de um Tablespace para outro Diskgroup no Oracle

Um diskgroup de minha base (11g) estava perigosamente cheio, então resolvi transportar um tablespace pouco usado para um diskgroup mais lento.

Suponha que os datafiles do tablespace TSX estejam no diskgroup +DG1 e que serão transportados ao diskgroup +DG2. A tarefa é simples e envolve os seguintes passos para cada datafile (executados no RMAN):
  1. Fazer uma cópia no diskgroup destino: backup as copy datafile file# format '+DG2';
  2. Colocar o datafile atual offline: sql 'alter database datafile file# offline';
  3. Fazer a troca para o backup: switch datafile file# to copy;
  4. Fazer recover do novo datafile: recover datafile file#;
  5. Colocar o datafile online: sql 'alter database datafile file# online';
  6. Apagar datafile original: delete datafilecopy 'filename';
Se o tablespace tiver muitos arquivos, ou a tarefa tiver que ser executada em vários ambientes, vale a pena escrever uma consulta (ou uma função) para gerar o script:

select case step.n
           when 1 then
             'backup as copy datafile '||file#||' format ''+DG2'';'
           when 2 then
             'sql ''alter database datafile '||file#||' offline'';'
           when 3 then
             'switch datafile '||file#||' to copy;'
           when 4 then
             'recover datafile '||file#||';'
           when 5 then
             'sql ''alter database datafile '||file#||' online'';'
           when 6 then
             'delete datafilecopy '''||df.name||''';'
          end
       esac
from v$tablespace ts
     inner join v$datafile df on ts.ts#=df.ts#,
    (select rownum n from all_tables where rownum<7) step
where ts.name=:TABLESPACE_NAME
order by df.file#, step.n
E o resultado será algo assim:

backup as copy datafile 106 format '+DG2';
sql 'alter database datafile 106 offline';
switch datafile 106 to copy;
recover datafile 106;
sql 'alter database datafile 106 online';
delete datafilecopy '+DG1/mydb/datafile/tsx_data.265.122421389';
backup as copy datafile 138 format '+DG2';
sql 'alter database datafile 138 offline';
switch datafile 138 to copy;
recover datafile 138;
sql 'alter database datafile 138 online';
delete datafilecopy '+DG1/mydb/datafile/tsx_data.263.183291830';

quinta-feira, 15 de setembro de 2016

Melhorias ao SQL

Lendo um relato sobre um pobre programador que apagou uma tabela inteira, porque esqueceu o where num delete, fiquei a pensar que o SQL deveria ter sintaxe diferente para alguns casos.

Os updates e os deletes de tabelas inteiras deveriam indicar a intenção explicitamente:

update my_table set my_column=0 all rows;
delete other_table all rows;

E se alguém esquecesse do all rows ou do where, seria um erro de sintaxe. Aposto que essa mudança evitaria muito sofrimento.

quarta-feira, 1 de junho de 2016

Ajustando tablespaces com facilidade

Uma tarefa recorrente na manutenção de um banco de dados Oracle é o de colocar objetos nos tablespaces corretos. Para facilitar o trabalho, recorri ao metadados. A rotina abaixo procura todas as tabelas, todos os índices e todos os LOBs de um schema e verifica se estão nos tablespaces indicados.

create or replace procedure 
  ajustar_tablespaces(p_schema varchar2, 
                      p_table_ts varchar2, 
                      p_index_ts varchar2, 
                      p_lob_ts varchar2) is
begin

  --Indices
  for r in 
   (select 'alter index '||p_schema||'.'|| index_name 
         ||' rebuild tablespace '||p_index_ts cmd 
    from all_indexes
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_index_ts)
      and index_type='NORMAL')
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --Tabelas
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move tablespace '||p_table_ts cmd 
    from all_tables 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_table_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --LOBs
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move lob('||column_name||') store as (TABLESPACE '||p_lob_ts||')' cmd 
    from all_lobs 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_lob_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;
  
  --Reconstroi os indices
  for r in 
   (select 'alter index '||p_schema||'.'||index_name
         ||' rebuild' cmd  
    from all_indexes 
    where owner=p_schema 
      and status='UNUSABLE') 
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

end;
Então, para corrigir os tablespaces dos objetos de um schema, eu poderia fazer uma chamada como esta:

  exec ajustar_tablespaces('INFO', 'ts_data01', 'ts_index02', 'ts_lobs');

Os comandos são sendo impressos para que se possa acompanhar o andamento do trabalho, principalmente se os objetos forem grandes.

Abaixo, o mesmo código como um bloco a fim de facilitar execuções ad-hoc.

declare
  p_schema varchar2(100):='ABXY';
  p_table_ts varchar2(100):='DTBS1'; 
  p_index_ts varchar2(100):='ITBS1'; 
  p_lob_ts varchar2(100):='LTBS1';
begin

  --Indices
  for r in 
   (select 'alter index '||p_schema||'.'|| index_name 
         ||' rebuild tablespace '||p_index_ts cmd 
    from all_indexes
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_index_ts)
      and index_type='NORMAL')
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --Tabelas
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move tablespace '||p_table_ts cmd 
    from all_tables 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_table_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --LOBs
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move lob('||column_name||') store as (TABLESPACE '||p_lob_ts||')' cmd 
    from all_lobs 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_lob_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;
  
  --Reconstroi os indices
  for r in 
   (select 'alter index '||p_schema||'.'||index_name
         ||' rebuild' cmd  
    from all_indexes 
    where owner=p_schema 
      and status='UNUSABLE') 
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

end;

sexta-feira, 27 de maio de 2016

Flashback de código no Oracle

Depois de uma tentativas desastrada de parcialmente atualizar uma package, precisei usar o flashback para recuperar o código original. Isso é fácil no Oracle:

  select text 
  from all_source as of timestamp sysdate -1/24 --uma hora
  where owner=:owner and name=:package_name and type='PACKAGE BODY'

Infelizmente, o SQLDeveloper exporta o texto com linhas adicionais. Contornar isso com um pouco de Perl é simples:

  perl -ne "print if !/^$/" package.sql > package_sem_linhas_vazias.sql

Esta linha de Perl imprime todas as linhas do arquivo (último parâmetro) que tiverem alguma coisa (/^$/ é verdadeiro para linhas vazias).

sexta-feira, 15 de janeiro de 2016

Gotas de otimização Oracle

Há dois problemas de SQL comuns que vejo, com frequência, sendo resolvidos com complexidades e ineficiências desnecessárias.

O primeiro é o de precisar o número de registros de uma consulta junto com os dados. A solução ineficiente é a de executar a consulta duas vezes; usando count() primeiro e buscando os dados depois. As funções analíticas são pouco conhecidas ainda, infelizmente.


select count(1)
from pessoas;

select nome
from pessoas;


Usando a versão analítica de count(), as duas consultas podem ser unidas.

select nome, count(1) over ()
from pessoas


E ganha-se uma coluna com o total de linhas.

O segundo problema é o de retirar acentos. Já vi soluções com múltiplos ifs, com case, e até com muitos replace() aninhados.

A minha solução preferida é muito simples e fácil de ler:

select nome, translate(nome,
                'ÀÁÃÂÄÈÉÊËÙÚÛÜÌÍÎÏÒÓÔÕÖÇÑàáãâäèéêëùúûüìíîïòóôõöçñ',
                'AAAAAEEEEUUUUIIIIOOOOOCNaaaaaeeeeuuuuiiiiooooocn') 
from pessoas;


Talvez haja uma solução mais curta (sei que há em Java), mas não creio que haja uma tão legível.

segunda-feira, 2 de junho de 2014

Procurando um valor no Oracle

Uma aplicação de ERP estava gerando um erro e eu precisava encontrar o registro no banco de dados (Oracle). As tabelas têm nomes inescrutáveis, então decidi que eu tinha que pesquisar todas para encontrar o valor que enxergava no monitor.

A solução foi bastante simples; com a ajuda dos metadados, percorri todas as colunas de tipos interessantes a procura do valor.


declare
  s varchar2(4096);
begin
  for i in (select * 
            from all_tab_cols 
            where owner='XY' 
                  and data_type in ('NUMBER','VARCHAR2') 
            order by table_name) 
  loop
    begin
      execute immediate 'select '||i.column_name
                     ||' from xy.'||i.table_name
                     ||' where '||i.column_name||'=''145960''' 
              into s;
      dbms_output.put_line(i.table_name||'.'||i.column_name);
    exception when no_data_found then null;
    end;
  end loop;
end;
Isto é, determinei que no esquema XY apenas interessam as colunas NUMBER e VARCHAR2 e pesquisei coluna por coluna todas as tabelas. Por sorte, XY tem, ao todo, pouco mais que 1 milhão de registros. O valor que eu procurava era 145960, como se pode deduzir da consulta gerada para cada coluna. Se a consulta não retorna nada, o Oracle lança o erro no_data_found. Por isso, o dbms_output.put_line() só é executado quando o valor for encontrado.

Em poucos segundos, encontrei a tabela e a coluna. Devido ao sucesso fácil, não encontrei motivos para transformar a consulta numa procedure ou numa function.

sábado, 2 de março de 2013

Simplificando a construção de consultas dinâmicas

Um problema recorrente é o de construir consultas para formulários com campos opcionais. Em grande parte dos casos, as consultas são geradas dinamicamente antes de serem submetidas ao banco de dados. Isto é, a lógica da consulta fica escondida na lógica da aplicação com cada cláusula sendo adicionada ou não conforme o valor digitado pelo usuário (e um if na aplicação).

Penso que, em muitos casos, isso pode ser resolvido com pequenas alterações no SQL. Apresento uma tabela de pessoas (PESSOA) como exemplo. Ela possui as seguintes colunas:
  • NOME;
  • NASCIMENTO (uma data);
  • DEPARTAMENTO (um número).
Vou começar pela data. Suponha que o usuário possa pesquisar todas as pessoas pela data de nascimento, mas que o campo é opcional. A consulta mais óbvia é:

select *
from pessoa
where nascimento=:data_nascimento
Evidentemente, se o usuário não digitar uma data, a aplicação deverá retirar a cláusula. Mas, para evitar isso, podemos reescrever a consulta assim:

select *
from pessoa
where 
nascimento between nvl(:data_nascimento, to_date('1000', 'YYYY')) 
               and nvl(:data_nascimento, to_date('3000', 'YYYY'))
Neste caso, se a data de nascimento não for informada, a consulta retornará todas as pessoas que nasceram entre o ano 1000 e o ano 3000 (a segunda data poderia muito bem ser sysdate) e isso deve bastar, a menos que se trate de uma base de dados do Vaticano. Se o formulário permite informar um período (duas datas), então a consulta já está preparada para recebê-las.

Com relação aos nomes, podemos escrever o seguinte:

select *
from pessoa
where 
nome like nvl(:nome, '%')
O que mais se vê é isto, no entanto:
(:nome is null or nome like :nome) 
Isso é um pouco mais eficiente, mas menos no estilo da programação incondicional. E, se o espírito hacker bater:

select *
from pessoa
where nome between nvl(:nome, chr(0)) and nvl(:nome, chr(255))
Entre os caracteres 0 e 255 estão todas as letras do alfabeto latino, inclusive com acentos, no código ASCII. Este mesmo truque pode ser usado para o departamento:

select *
from pessoa
where departamento between nvl(:dept, -1) and nvl(:dept, 1000)
Evidemente, todos os departementos devem ter códigos entre -1 e 1000; conhecer os dados é importante para poder usar este tipo de truque, mas isso é importante de qualquer maneira. A união de todas as cláusulas é:

select *
from pessoa
where
nome like nvl(:nome, '%') 
and nascimento between nvl(:data_nascimento, to_date('1000', 'YYYY')) 
                   and nvl(:data_nascimento, to_date('3000', 'YYYY'))
and departamento between nvl(:dept, -1) and nvl(:dept, 1000)
Agrega-se um pouco de complexidade à consulta (que pode, por outro lado, ser facilmente inserida numa procedure), mas simplifica-se o código da aplicação.

Os detratores da programação incondicional levantarão as bandeiras da legibilidade e do desempenho, mas todos sabemos que o mais importante é eliminar os ifs dos programas.

terça-feira, 20 de novembro de 2012

SQL Gaussiano

É bem conhecida a história de quando o professor de matemática de Gauss pediu que a turma somasse todos os números até 100. O mestre esperava ter um pouco de paz enquanto as crianças trabalhassem na soma. O pequeno Gauss, no entanto, resolveu a questão rapidamente.

A solução era simples. Ele somou 1 com 100, depois 2  com 99, então 3 com 98 e assim por diante. Vindo pelas pontas, a soma é sempre 101 e depois de 50 somas, os números encontram-se em 50 e 51. O total, portanto, é 50 vezes 101.

Esse pequeno truque pode ser usado no SQL para encontrar sequências de números ou de datas. Considere os seguintes números: 1; 2; 3; 5; 6; 8; 9; e 10. Se cada um estiver numa linha de uma tabela, eu quero uma consulta que gere o seguinte resultado:

   1   3
   5   6
   8 10

É necessária uma sequência decrescente para fazer o jogo com a nossa sequência crescente. Isso podemos resolver facilmente com funções analíticas. Na consulta abaixo, já somo cada número ao seu equivalente.


select n, n+row_number() over (order by n desc)
from numeros
order by n


Isso vai produzir as seguintes linhas:

19
29
39
510
610
811
911
1011

E todos os números consecutivos compartilham o mesmo valor na segunda coluna. O que nos resta é agrupá-los e encontrar o máximo e mínimo de cada grupo. Assim, chegamos ao nosso objetivo:


select min(n), max(n) from (
  select n, n+row_number() over (order by n desc) grupo
  from numeros
)
group by grupo
order by 1


O resultado é, conforme esperado, este:

   1   3
   5   6
   8 10

O mesmo pode ser feito com datas, basta tratar cada uma como o número de dias a partir da primeira data.

quarta-feira, 17 de outubro de 2012

Relatórios no Oracle

Considere uma tabela PESSOAS com estas colunas:

  (NASC DATE, SEXO VARCHAR2(1), NOME VARCHAR2(100))

A coluna NASC guarda a data de nascimento e a coluna SEXO pode conter 'M' ou 'F'. Se eu quiser montar um relatório com o número de aniversários para cada dia do ano, vou ter que fazer uns contorcionismos, mas se eu quiser, ademais, separar o número de aniversários por sexo, a consulta vai ficar ilegível.

Escondido na documentação da Oracle estava um tipo de join que permite resolver esse problema com relativa facilidade: o partitioned outer join. Esse join tem a seguinte forma:

select *
from a 
     left outer join b partition by (cz) on a.ca=b.ca

E isso significa que será executado um cross-join dos valores distintos de CZ com as linhas da tabela A e, a seguir, o resultado desse cross-join será usado no outer-join com a tabela B.

De volta ao problema original, inicio com uma contagem dos aniversários por dia e por sexo:

  select
    to_char(nasc,'MMDD') nasc, 
    sexo, 
    count(1) total
  from pessoas
  group by to_char(nasc,'MMDD'), sexo

Além disso, preciso de uma tabela com todos os dias do ano:

  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367

E junto tudo com o partitioned outer join:

select dia, sexo, nvl(total, 0)
from (
  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367
) dias
  left outer join (
    select to_char(nasc,'MMDD') nasc, sexo, count(1) total
    from pessoas
    group by to_char(nasc,'MMDD'), sexo) p partition by (sexo) on p.nasc=dia
order by dia, sexo

Essa consulta produz duas linhas para cada dia do ano: uma para o número de aniversários de homens e outro para o número de aniversários de mulheres:

0101F2
0101M0
0102F1
0102M3

Armado desta nova ferramenta, decidi resolver um problema ainda maior: enumerar os aniversariantes de cada dia. Comecei com uma lista dos aniversariantes para cada dia:

  select nasc, sexo, substr(SYS_CONNECT_BY_PATH(nome,', '),3) nomes  
  from (
    select 
      nasc, nome, sexo,
      row_number() over (partition by nasc, sexo order by nome) ordem,
      row_number() over (partition by nasc, sexo order by nome desc) medro 
    from (
      select to_char(nasc, 'MMDD') nasc, sexo, nome from pessoas
    ) 
  )
  where medro=1
  start with ordem=1
  connect by prior nasc=nasc and prior sexo=sexo and prior ordem=ordem-1

Juntando com a lista dos dias do ano, descubro o seguinte:

select dia, sexo, nvl(nomes,'-') nomes
from (
  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367
) dias
  left outer join (
    select nasc, sexo, substr(SYS_CONNECT_BY_PATH(nome,', '),3) nomes  
    from (
      select 
        nasc, nome, sexo,
        row_number() over (partition by nasc, sexo order by nome) ordem,
        row_number() over (partition by nasc, sexo order by nome desc) medro 
      from (
        select to_char(nasc, 'MMDD') nasc, sexo, nome from pessoas
      ) 
    )
    where medro=1
    start with ordem=1
    connect by prior nasc=nasc and prior sexo=sexo and prior ordem=ordem-1
  ) p partition by (sexo) on p.nasc=dia
order by dia, sexo

Não é das coisas mais simples, mas ele produz bastante para o seu tamanho. O resultado é parecido com:

0101FHelena, Maria
0101M-
0102FFernanda
0102MHenrique, João, Pedro

Por enquanto, apenas o Oracle oferece esse join.

sexta-feira, 10 de agosto de 2012

Geodemografia meridional analítica

Ignore um pouco o brilhantismo do título e considere esta tabela:

CREATE TABLE MUNICIPIOS (NOME VARCHAR2(50), POPULACAO INTEGER)

Com os dados populacionais dos municípios gaúchos, exercitei um pouco as funções analíticas do Oracle (ou do Postgresql). Temos atualmente 496 municípios (em breve, com o retorno de Pinto Bandeira, serão 497). Em primeiro lugar, enumerei os municípios por ordem decrescente de população e, usando uma função analítica, adicionei uma coluna com a soma de todos os municípios com população menor que o corrente.

select nome, 
       populacao, 
       sum(populacao) 
         over (order by populacao asc rows unbounded preceding)
from municipios
order by populacao desc

Isso nos ajuda a descobrir que os 282 menores municípios têm o primeiro milhão de habitantes. O próximo milhão habita 98 municípios. O terceiro, 45 municípios. O quarto, 28. O quinto, 16. O sexto, 11. Transformar a soma em percentil é fácil (para quem tem senso prático, pelo menos):

select nome, 
       populacao, 
       sum(populacao/10732770) 
         over (order by populacao asc rows unbounded preceding)
from municipios
order by populacao desc

Os 22 maiores municípios (4% do total) são ocupados por 50% da população gaúcha. E os 55 menores municípios (11% do total) têm apenas 1% da população. Os 293 menores municípios (59% do total) têm apenas 10% da população. A média por município é de 21.639 habitantes, mas apenas 76 têm mais que isso (15%). A mediana é de apenas 5.697 habitantes. Se estipulássemos 6 mil habitantes (metade da capacidade da Baixada Melancólica, sede do glorioso Inter de Santa Maria) como o mínimo necessário para constituir um município, restariam apenas 236.

segunda-feira, 9 de abril de 2012

Juntando linhas

Tenho uma tabela de permissões que tem a seguinte forma:

CREATE TABLE PERMISSOES (
  OBJETO VARCHAR2(100),
  USUARIO VARCHAR2(100)
)
Eu preciso de um relatório que enumere todos os usuários para cada objeto, colocando todos os autorizados numa só linha. A minha solução, um pouco complicada, foi juntar as facilidades de consultas hierárquicas do Oracle com um pouco de funções analíticas.

O cerne da consulta é o seguinte SELECT:

select objeto, 
       row_number() over (partition by objeto order by usuario) ordem,
       row_number() over (partition by objeto order by usuario desc) medro,
       usuario
from permissoes
order by 1, 2, 3 desc
Isso produz uma lista de todos os objetos com os respectivos usuários numerados alfabeticamente tanto crescentemente como decrescentemente. Considere as seguintes linhas:

OBJETOUSUARIO
COFREHeráclito
COFREXenofonte
COMPUTADORHeródoto
COMPUTADORHeráclito
COMPUTADORPlatão

A consulta produzirá o resultado abaixo:

OBJETOORDEMMEDROUSUARIO
COFRE12Heráclito
COFRE21Xenofonte
COMPUTADOR13Heráclito
COMPUTADOR22Heródoto
COMPUTADOR31Platão

Envolvendo essa consulta com um SELECT hierárquico, podemos agregar os helenos.

  select objeto, substr(sys_connect_by_path(usuario, ','),2) autorizados 
  from (
    select objeto, 
           row_number() over (partition by objeto order by usuario) ordem,
           row_number() over (partition by objeto order by usuario desc) medro,
           usuario
    from permissoes
  )
  where medro=1
  connect by prior ordem=ordem-1 and prior objeto=objeto
  start with ordem=1
Então, uso a coluna ORDEM para simular uma hierarquia e a coluna MEDRO para eliminar todas as linhas exceto a última (que possui todos os usuarios relacionados a um objeto).

O resultado é o seguinte:

OBJETOAUTORIZADOS
COFREHeráclito,Xenofonte
COMPUTADORHeráclito,Heródoto,Platão

A função sys_connect_by_path() aglutina toda a hierarquia e neste caso indiquei uma vírgula como separador. No entanto, ela coloca uma vírgula também no início e para resolver isso adicionei a função substr().

segunda-feira, 27 de fevereiro de 2012

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.

segunda-feira, 13 de fevereiro de 2012

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.

sexta-feira, 10 de fevereiro de 2012

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.

sexta-feira, 25 de novembro de 2011

Diversões numéricas com SQL III

Todos os inteiros são produzidos por uma combinação única de potências de primos. O que interessa-me saber é quais números têm uma quantidade maior de primos na sua composição.

Comecei com uma pequena consulta para fatorar um único inteiro.

with integers as (select rownum as n from all_tables where rownum<100)
select n
from integers i1
where mod(168, n)=0 
      and n between 2 and 168-1 
      and not exists (select 1 
                      from integers i2 
                      where i2.n>1 
                            and i2.n<i1.n 
                            and mod(i1.n, i2.n)=0)
Parece Lisp, não? E 168 tem três primos na sua composição: 2, 3 e 7. Adicionando uma tabela, posso procurar os inteiros que tenham o maior número de fatores primos. Aproveitei também para acelerar a busca usando a raiz quadrada de cada inteiro como limitador.

with integers as (select rownum as n from all_tables where rownum<10000)
select count(i1.n), i3.n
from integers i1, integers i3
where mod(i3.n, i1.n)=0 
      and i1.n between 2 and sqrt(i3.n)
      and not exists (select 1 
                      from integers i2 
                      where i2.n>1 
                            and i2.n<=sqrt(i1.n)
                            and mod(i1.n, i2.n)=0)
group by i3.n
order by 1 desc
Nenhum inteiro menor que 10.000 tem mais que 5 fatores primos. O inteiro 7770, por exemplo, tem os fatores 2, 3, 5, 7 e 37.

Como os priminhos baixinhos estavam aparecendo muito, resolvi procurar apenas os inteiros cujos fatores primos sejam maiores que 10. Descobri, então, que apenas 1059 inteiros menores que 10.000 têm todos os fatores primos maiores que 10. O 7429, por exemplo, tem os fatores primos 17, 19 e 23. Além disso, nenhum desses 1059 tem mais que 3 fatores primos.

quinta-feira, 24 de novembro de 2011

Diversões numéricas com SQL II

Nenhuma diversão numérica seria completa se os primos não aparecessem. Então, resolvi logo descobrir se é possível selecioná-los.

with integers as (select rownum as n 
                  from all_tables 
                  where rownum<100)
select n 
from integers i1
where i1.n>1 
      and not exists (select 1 
                      from integers i2 
                      where i2.n>1 
                            and i2.n<sqrt(i1.n) 
      and mod(i1.n,i2.n)=0)
Parece bom. E com isso podemos procurar relações interessantes, como primos gêmeos.

select n, previous from (
  with integers as (select rownum as n 
                    from all_tables 
                    where rownum<100)
  select n, lag(n, 1, n) over (order by n) previous 
  from integers i1
  where i1.n>1 
        and not exists (select 1 
                        from integers i2 
                        where i2.n>1 
                              and i2.n<sqrt(i1.n) 
        and mod(i1.n,i2.n)=0)
) where n=previous+2
Usando a função lag(), podemos selecionar todas as duplas de primos separados por apenas um inteiro. Com isso, descobri que 3001 e 2999 são primos gêmeos, assim como 9001 e 8999.

quarta-feira, 23 de novembro de 2011

Diversões numéricas com SQL

Contar com SQL é fácil; achar uma utilidade custou-me um pouco. Comecei com uma consulta bastante simples para contar em base 2.

with bits as (select rownum-1 as bit from all_tables where rownum<3)
select b1.bit, b2.bit
from bits b1, bits b2
O resultado é uma tabela bastante simples, com os quatro números possíveis de dois dígitos binários.

A primeira aplicação (embora redundante, porque o Oracle já me oferece alternativa) é a de converter números para a base 16 (que é uma tarefa bastante comum na minha ponta da internet).

Minha nem tão elegante solução é esta:

select byte1||byte2 from (
  with bytes as (select decode(rownum-1, 
                        least(rownum-1,9), to_char(rownum-1), 
                        chr(rownum-1+65-10)) as byte 
                 from all_tables where rownum<17)
  select b1.byte byte1, b2.byte byte2, rownum-1 valor
  from bytes b1, bytes b2
) where valor=68
A consulta produz, conforme esperado, o valor 44.

quarta-feira, 8 de junho de 2011

Transformando colunas em linhas

Um problema que de tempos em tempos surge é o de transformar colunas em linhas no SQL. Os bancos que têm facilidades de XML, como o DB2 e o Oracle, oferecem a possibilidade de resolver isso através do SQL/XML, que é uma extensão padronizada do SQL.

A consulta abaixo mostra como é simples usar essa extensão:


SELECT *
FROM XMLTABLE ('ROWSET/ROW/*' PASSING
     DBMS_XMLGEN.GETXMLTYPE('select 17,25,39 from dual') 
     COLUMNS val VARCHAR2(8) PATH '.'
) X

 17 
 25 
 39 

A rotina DBMS_XMLGEN.GETXMLTYPE recebe uma consulta e retorna um XML com a seguinte forma:


<ROWSET>
 <ROW>
  <_x0031_7>17</_x0031_7>
  <_x0032_5>25</_x0032_5>
  <_x0033_9>39</_x0033_9>
 </ROW>
</ROWSET>

Com XMLTABLE, realiza-se o caminho inverso. A expressão 'ROWSET/ROW/*' nos indica que queremos todos os nodos filhos de nodos do tipo ROW. Depois da chamada a DBMS_XMLGEN.GETXMLTYPE há uma descrição dos tipos para os quais as colunas devem ser convertidas.

O primeiro select retornava linhas de apenas uma coluna. Para extrair o nome de cada coluna, é preciso fazer o seguinte:


SELECT x.column_value.getrootelement(), x.column_value.extract('//./text()')
FROM XMLTABLE ('ROWSET/ROW/*' PASSING
     DBMS_XMLGEN.GETXMLTYPE('select 17 as A,25 as B,39 as C from dual')
) X

 A  17 
 B  25 
 C  39 

Essa consulta funciona no Oracle 10g. O DB2 e o Oracle 11g tem suporte melhor para o XPath e, portanto, permitem maiores estrepolias.