segunda-feira, 16 de agosto de 2010

Regressão linear em SQL

Uma das tarefas mais divertidas e inúteis para economistas amadores (como eu) passarem o tempo é procurar maneiras de prever o futuro. E uma das maneiras mais simples de se fazer isso é usando a regressão linear (finalmente uma entrada da Wikipédia cuja versão em português é melhor que a versão inglesa!).

O objetivo é simples: dada uma série, encontrar uma reta que descreva a tendência. Com essa reta, pode-se imaginar para que lado as medidas estão indo.

Decidi usar uma tabela de IDHs municipais para testar meu SQL. A tabela tem, entre outras colunas, ANO, IDHM (indíce de desenvolvimento municipal) e MUNICIPIO.

Conforme o artigo da Wikipédia, o objetivo é encontrar os termos a e b da equação y=a+bx. As equações para cada termo são trabalhosas, então decidi montar a consulta em 3 partes. Na primeira, calculo os pedaços das equações; na segunda calculo a e b; na última, prevejo o futuro.


select a+2010*b from (
select (x2*y-xy*x)/(n*x2-x*x) a, (n*xy-x*y)/(n*x2-x*x) b from (
select count(1) n, sum(ano) x, sum(ano*ano) x2,
sum(idhm) y, sum(idhm*idhm) y2, sum(ano*idhm) xy
from indices_municipais
where municipio=130
)
)

Eu tenho os dados de 1991 e 2001. Com essa pequena consulta, projeto a reta e descubro qual a tendência para 2010. Trocando o 2010 por 1991 e 2001 pude verificar que estava tudo certo; a consulta produziu os valores que já estavam na base. Como eu só tinha duas medidas, a reta tinha que passar exatamente por elas!

A consulta não é tão complicada e só o select mais de dentro muda, então resolvi escrever uma função parametrizada para poder repetir o experimento em outras tabelas com mais facilidade.


create or replace function regressao_linear (
valor number,
x varchar2,
y varchar2,
tabela varchar2,
restricao varchar2)
return number is
resultado number;
begin
execute immediate '
select a+'||valor||'*b from (
select (x2*y-xy*x)/(n*x2-x*x) a, (n*xy-x*y)/(n*x2-x*x) b from (
select count(1) n, sum('||x||') x, sum('||x||'*'||x||') x2,
sum('||y||') y, sum('||y||'*'||y||') y2, sum('||x||'*'||y||') xy
from '||tabela||'
where '||restricao||'
)
)
' into resultado;

return resultado;
end;


Os parâmetros x e y são os nomes das colunas; tabela não preciso explicar e restricao é tudo que vai na cláusula where. E valor é o x que se quer projetar além dos dados. A interpolação de strings no Oracle é mesmo pavorosa e essa multiplicação de x e y não ajuda. Mas agora posso usar a função e fazer de conta que é trivial; a sujeira foi toda para debaixo do tapete.

Para executar a consulta inicial usando a nova função, basta fazer o seguinte:

select regressao_linear(2010, 'ano', 'idhm',
'indices_municipais', 'municipio=130')
from dual


Esse município tem IDHs 0,746 e 0,822 para 1991 e 2001. O valor retornado pela função é 0,906. Quando sair o censo eu vou conferir e cobrar do prefeito se não estiver conforme o esperado.

3 comentários:

  1. Regressão linear é a primeira das funções de aprendizado de máquina.
    Eles estão usando ?
    Que tal colocar o ODM na jogada ?

    ResponderExcluir
  2. É melhor que eles não saibam, porque certamente vão querer estragar a brincadeira.

    ResponderExcluir
  3. Acredito que está incorreto a informação que você passou sobre o valor obtido através do cálculo para o IDHM de 2010 ( baseando-se nos resultados dos anos de 1991 (0.746) e 2001 (0.822) apenas ).

    Conforme simulação abaixo, baseada no seu código SQL ao gerar a tendência para o ano de 2010

    como dito no artigo, obtive o valor de 0.8904.

    -- Início do exemplo

    select a+2012*b from (
    select (x2*y-xy*x)/(n*x2-x*x) a, (n*xy-x*y)/(n*x2-x*x) b from (
    select count(1) n, sum(ano) x, sum(ano*ano) x2,
    sum(idhm) y, sum(idhm*idhm) y2, sum(ano*idhm) xy
    from
    -- simulação da tabela de idhm - INICIO
    (
    select '1991' as ano,
    0.746 as idhm
    from dual

    union all

    select '2001' as ano,
    0.822 as idhm
    from dual

    )
    -- simulação da tabela de idhm - FINAL
    )
    )

    -- Final do exemplo

    Agora fazendo o mesmo cálculo para o ano de 2012, obtive o valor informado no artigo 0.9056 ou 0.906 (com arredondando).


    ANO 1991 2001 2010 2011 2012
    IDHM(Tendência) 0.746 0.822 0.8904 0.898 0.9056

    ResponderExcluir