Como condicionar o select em outra linha da tabela no Mysql?

MySQL

17/02/2020

Sou iniciante no que se refere à linguagem do MySql e estou tentando filtrar algumas informações que preciso retirar de uma tabela.

Basicamente eu tenho dados de indivíduos em anos seguidos (2003, 2004, 2005) todos em uma mesma tabela. Além desses dados, disponho de informações, por exemplo, da escolaridade (que pode mudar ano após ano, ou manter-se constante).

Porém alguns registros da minha base de dados são inconsistentes, por exemplo, selecionando os primeiros registros ordenados pelo cpf e pelo ano, temos (mudei o cpf por motivos de sigilo dos dados):

Ano      CPF        Escolaridade 
2004    10000000kkk 3
2005    10000000kkk 5
2003    10000000zzz 7
2005    10000000zzz 6
2003    10000000ttt 7
2005    10000000ttt 4
2004    10000000ppp 6
2005    10000000ppp 9
2004    10000000sss 7
2005    10000000sss 9
2005    10000000sss 9
2003    10000002xxx 6
2005    10000002xxx 7

A escolaridade é codificada:

ANALFABETO      1
ATE 5.A INC     2
5.A CO FUND     3
6. A 9. FUND    4
FUND COMPL      5
MEDIO INCOMP    6
MEDIO COMPL     7
SUP. INCOMP     8
SUP. COMP       9
MESTRADO        10
DOUTORADO       11
IGNORADO        -1

Minha dúvida é: como posso filtrar essas inconsistências? Alguém pode me ajudar?
Amanda Uceli

Amanda Uceli

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

17/02/2020

qual é a inconsistência?
GOSTEI 0
Anderson Gonçalves

Anderson Gonçalves

17/02/2020

Manda o teu código ai que as vezes explica melhor o que você quer.
GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

qual é a inconsistência?

Eu tenho indivíduos que diminuem a escolaridade de um ano para o outro, ou que mudam de forma desproporcional (em um ano são registrados com ensino fundamental, e no seguinte com ensino superior completo, por exemplo).
GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

Manda o teu código ai que as vezes explica melhor o que você quer.

Qual código? Da busca pela inconsistência?
Já fiz várias tentativas e nenhuma funcionou.
Tentativa 1:
select ano,  escolaridade, cpf    
from id2003to2005 k1, id2003to2005 k2
where k1.cpf = k2.cpf and k1.chave_2 != k2.chave_2 and ((k1.ano - k2.ano=1) and ((k1.escolaridade - k2.escolaridade>1 or k1.escolaridade - k2.escolaridade<0))
or ( (k1.ano - k2.ano>1) and (k1.escolaridade - k2.escolaridade>2 or k1.escolaridade - k2.escolaridade<0)));


Tentativa 2:
select k1.ano,  k1.escolaridade, k1.chave_2, k1.cpf, k2.ano,  k2.escolaridade, k2.chave_2, k3.cpf    
from id2003to2005 k1
inner join id2003to2005 k3
on k1.cpf=k3.cpf 
inner join id2003to2005 k2
where ((k1.ano - k2.ano=1) and ((k1.escolaridade - k2.escolaridade>1 or k1.escolaridade - k2.escolaridade<0))                                                                 
or ( (k1.ano - k2.ano>1) and (k1.escolaridade - k2.escolaridade>2 or k1.escolaridade - k2.escolaridade<0))) and k3.chave_2 != k1.chave_2;


Tentativa 3 (sugerida em outro fórum):
create temporary table  tabela_temporaria select MAX(ano) as ano, cpf, 0 as valor_atual, 0 as valor_anterior 
from id2003to2005 group by cpf;
update tabela_temporaria as T set valor_atual = (SELECT escolaridade from id2003to2005 as P WHERE P.ano = T.ano and P.cpf = T.cpf LIMIT 1) ;
update tabela_temporaria as T set valor_anterior = (SELECT escolaridade from id2003to2005 as P WHERE P.ano < T.ano and P.cpf = T.cpf ORDER BY ano DESC limit 1);
select * from tabela_temporaria WHERE valor_anterior > valor_atual or (abs(valor_atual - valor_anterior)> 2 and  valor_anterior>6) ;


Os querys ficam rodando por muito tempo e não finalizam. É importante dizer que tenho uma quantidade enorme de linhas, mais 100 milhões. Eu sei que com esse volume de dados tudo fica mais lento, mas essas tentativas não funcionaram mesmo.
GOSTEI 0
Mauricio Espido

Mauricio Espido

17/02/2020

Pelo que deu pra entender, você tem que comparar o próximo ano com o ano anterior e ver se o cpf (aluno) pulou um ano a mais ou regrediu. Você pode usar a função analítica LEAD (próximo registro) para esse caso.
Eu traria todos esses dados num select. E então passaria essa informação para uma tabela virtual e mandaria para uma function, lá você pode comparar
os dados e separar os divergentes.
No Oracle costumo montar uma type table e alimentar as linhas com a informação separada dentro da function.
GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

Pelo que deu pra entender, você tem que comparar o próximo ano com o ano anterior e ver se o cpf (aluno) pulou um ano a mais ou regrediu. Você pode usar a função analítica LEAD (próximo registro) para esse caso.
Eu traria todos esses dados num select. E então passaria essa informação para uma tabela virtual e mandaria para uma function, lá você pode comparar
os dados e separar os divergentes.
No Oracle costumo montar uma type table e alimentar as linhas com a informação separada dentro da function.


Então, eu preciso do próximo registro, mas o que eu quero é excluir da base de dados todos os indivíduos (todas as ocorrencias do cpf inconsistente) que apresentam incongruências nos registro de escolaridade. Eu preciso comparar a escolaridade do indivíduo entre os anos e retirar aqueles que apresentam a inconsistência.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

17/02/2020

talvez algo assim possa te ajudar (não sei se funciona porque nunca usei MySQL, fiz com base em buscas na internet).
-- COM CERTEZA ESTÃO ERRADOS, PORQUE A ESCOLARIDADE DO ANO ATUAL
-- É MAIOR QUE A ESCOLARIDADE DO ANO SEGUINTE
SELECT
    DISTINCT T.CPF
FROM (
SELECT 
    CPF, ANO, ESCOLARIDADE,
    LEAD(ESCOLARIDADE,1,ESCOLARIDADE) OVER (
            PARTITION BY CPF
            ORDER BY ANO, ESCOLARIDADE) PROX_ESCOLARIDADE
FROM
    GRADUACAO
) T
WHERE
    T.CPF IN (SELECT CPF FROM GRADUACAO GROUP BY CPF HAVING COUNT(*) > 1)
    AND T.ESCOLARIDADE > T.PROX_ESCOLARIDADE

agora precisa montar um script pra pegar os demais casos. aqueles
que mudam de forma desproporcional (em um ano são registrados com ensino fundamental, e no seguinte com ensino superior completo, por exemplo).

GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

talvez algo assim possa te ajudar (não sei se funciona porque nunca usei MySQL, fiz com base em buscas na internet).
-- COM CERTEZA ESTÃO ERRADOS, PORQUE A ESCOLARIDADE DO ANO ATUAL
-- É MAIOR QUE A ESCOLARIDADE DO ANO SEGUINTE
SELECT
    DISTINCT T.CPF
FROM (
SELECT 
    CPF, ANO, ESCOLARIDADE,
    LEAD(ESCOLARIDADE,1,ESCOLARIDADE) OVER (
            PARTITION BY CPF
            ORDER BY ANO, ESCOLARIDADE) PROX_ESCOLARIDADE
FROM
    GRADUACAO
) T
WHERE
    T.CPF IN (SELECT CPF FROM GRADUACAO GROUP BY CPF HAVING COUNT(*) > 1)
    AND T.ESCOLARIDADE > T.PROX_ESCOLARIDADE

agora precisa montar um script pra pegar os demais casos. aqueles
que mudam de forma desproporcional (em um ano são registrados com ensino fundamental, e no seguinte com ensino superior completo, por exemplo).



Emerson, sua proposta faz sentido, mas por alguma razão que eu desconheço, há um erro na sintaxe do comando. Já tentei descobrir onde o erro se encontra, mas não achei. Tentei criar uma tabela auxiliar apenas com a definição de T, mas segue apontando erro de sintaxe.

GOSTEI 0
Renato Neto

Renato Neto

17/02/2020

Amanda, normalmente o que eu faço nesses casos é identificar uma padronização nas inconsistências. Se houver mais de uma, eu crio grupos distintos das inconsistências para que eu consiga trata-las caso a caso. Eu entendo a omissão dos dados originais, mas no exemplo demonstrado, todos os CPFs, os últimos quatro digítos são letras. Eu procuraria entender se os dados estão sendo gravados dessa forma e a causa, dependendo da situação eu faria um tratamento de string (SUBSTRING) capturando o primeiro ao oitavo digito, separando números de letras. Resta saber, se apenas os oito primeiros dígitos são capazes de garantir a unicidade dos dados ou precisaria de uma associação com outro.

Entendido o problema e com as inconsistências tratadas eu partiria para a comparação, talvez utilizando um self-join ou com o auxilio de tabelas temporárias.
GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

Amanda, normalmente o que eu faço nesses casos é identificar uma padronização nas inconsistências. Se houver mais de uma, eu crio grupos distintos das inconsistências para que eu consiga trata-las caso a caso. Eu entendo a omissão dos dados originais, mas no exemplo demonstrado, todos os CPFs, os últimos quatro digítos são letras. Eu procuraria entender se os dados estão sendo gravados dessa forma e a causa, dependendo da situação eu faria um tratamento de string (SUBSTRING) capturando o primeiro ao oitavo digito, separando números de letras. Resta saber, se apenas os oito primeiros dígitos são capazes de garantir a unicidade dos dados ou precisaria de uma associação com outro.

Entendido o problema e com as inconsistências tratadas eu partiria para a comparação, talvez utilizando um self-join ou com o auxilio de tabelas temporárias.


Renato os cpfs são numéricos. Eu mudei o final porque se trata de dados reais e para os quais deve-se manter o sigilo.
No caso, como eu disse em uma resposta acima, eu estou lidando com um volume muito grande de dados (mais de 100 milhões de linhas), e qualquer análise individual é totalmente inviável. Eu tenho algumas possibilidades de inconsistências, mas ainda que eu verifique todas as possibilidade relativas à escolaridade, eu não estou conseguindo comparar a escolaridade do mesmo cpf ao longo dos anos. E é exatamente isso que eu preciso.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

17/02/2020

talvez algo assim possa te ajudar (não sei se funciona porque nunca usei MySQL, fiz com base em buscas na internet).
-- COM CERTEZA ESTÃO ERRADOS, PORQUE A ESCOLARIDADE DO ANO ATUAL
-- É MAIOR QUE A ESCOLARIDADE DO ANO SEGUINTE
SELECT
    DISTINCT T.CPF
FROM (
SELECT 
    CPF, ANO, ESCOLARIDADE,
    LEAD(ESCOLARIDADE,1,ESCOLARIDADE) OVER (
            PARTITION BY CPF
            ORDER BY ANO, ESCOLARIDADE) PROX_ESCOLARIDADE
FROM
    GRADUACAO
) T
WHERE
    T.CPF IN (SELECT CPF FROM GRADUACAO GROUP BY CPF HAVING COUNT(*) > 1)
    AND T.ESCOLARIDADE > T.PROX_ESCOLARIDADE

agora precisa montar um script pra pegar os demais casos. aqueles
que mudam de forma desproporcional (em um ano são registrados com ensino fundamental, e no seguinte com ensino superior completo, por exemplo).



Emerson, sua proposta faz sentido, mas por alguma razão que eu desconheço, há um erro na sintaxe do comando. Já tentei descobrir onde o erro se encontra, mas não achei. Tentei criar uma tabela auxiliar apenas com a definição de T, mas segue apontando erro de sintaxe.



Publique o erro apresentado.
GOSTEI 0
Amanda Uceli

Amanda Uceli

17/02/2020

talvez algo assim possa te ajudar (não sei se funciona porque nunca usei MySQL, fiz com base em buscas na internet).
-- COM CERTEZA ESTÃO ERRADOS, PORQUE A ESCOLARIDADE DO ANO ATUAL
-- É MAIOR QUE A ESCOLARIDADE DO ANO SEGUINTE
SELECT
    DISTINCT T.CPF
FROM (
SELECT 
    CPF, ANO, ESCOLARIDADE,
    LEAD(ESCOLARIDADE,1,ESCOLARIDADE) OVER (
            PARTITION BY CPF
            ORDER BY ANO, ESCOLARIDADE) PROX_ESCOLARIDADE
FROM
    GRADUACAO
) T
WHERE
    T.CPF IN (SELECT CPF FROM GRADUACAO GROUP BY CPF HAVING COUNT(*) > 1)
    AND T.ESCOLARIDADE > T.PROX_ESCOLARIDADE

agora precisa montar um script pra pegar os demais casos. aqueles
que mudam de forma desproporcional (em um ano são registrados com ensino fundamental, e no seguinte com ensino superior completo, por exemplo).



Emerson, sua proposta faz sentido, mas por alguma razão que eu desconheço, há um erro na sintaxe do comando. Já tentei descobrir onde o erro se encontra, mas não achei. Tentei criar uma tabela auxiliar apenas com a definição de T, mas segue apontando erro de sintaxe.



Publique o erro apresentado.


O programa acusa um erro de sintaxe no primeiro select do comando.
GOSTEI 0
POSTAR