none
Obter resultado mais atual, sem dados repetidos. RRS feed

  • Question

  • Cometi um equívoco, esqueci de uma parte bem importante no filtro, que sem ela não irá funcionar, vou tentar ser mais objetivo e refazer a pergunta.

    Preciso obter o resultado que está na cor verde:

    Preciso que siga as regras:

    • motivo_envio: Tem que ser TROCA%
    • status: Tem que ser ANOMALIA
    • imei: Tem que ser único

    Critério:

    • data_importacao: Tem que ser a maior (mais recente)

    OBS:

    • Se caso o registro mais atual daquele IMEI estiver com o STATUS igual PENDENTE ou LIBERADO não quero pegar esse resultado, somente se o mais atual estiver com ANOMALIA

    Exemplificando:

    1. IMEI 1111111111 aparece 2 vezes, a primeira vez que apareceu estava com STATUS igual ANOMALIA, mas depois ele apareceu com STATUS igual PENDENTE, ou seja não quero mais, porque esse IMEI o status mais recente dele foi PENDENTE e não ANOMALIA.
    2. IMEI 2222222222 é a mesma coisa do IMEI 1111111111, aparece 2 vezes, a primeira com STATUS igual ANOMALIA mas depois aparece com o STATUS mais recente de PENDENTE, então não preciso dele também.
    3. IMEI 3333333333 e 4444444444 também não preciso, pois o STATUS mais atual é PENDENTE, só precisaria se o STATUS dele fosse ANOMALIA.
    4. IMEI 5555555555 aparece pela primeira vez com STATUS igual ANOMALIA e depois aparece pela segunda vez com o STATUS de ANOMALIA, então preciso dele (o que for o mais recente).

    Criei um arquivo com o CREATE e INSERT desses registros para poder facilitar:

    https://pastebin.com/raw/44f2cECt

    Peço desculpas pelo erro, esqueci de mencionar essa parte importante e crucial para esse filtro, desde já agradeço a ajuda!



    • Edited by Sup3r-Us3r Friday, August 30, 2019 10:48 PM
    Thursday, August 29, 2019 12:05 AM

Answers

  • Deleted
    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:07 AM
    Thursday, August 29, 2019 12:14 AM
  • Boa noite,

    Experimente fazer uns testes mais ou menos dessa forma:

    with CTE_RN as
    (
        select
            *,
            row_number() over(partition by imei order by data_alteracao desc) as RN
        from Tabela
        where 
            motivo_envio like 'TROCA%'
    )
    
    select * from CTE_RN 
    where RN = 1

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:07 AM
    Thursday, August 29, 2019 12:19 AM
  • Sup3r-Us3r,

    Quando você se refere as datas, qual das três seria a utilizada como parâmetro de alteração?

    Tomei como base os dados que você compartilhou, realizei a importação para minha máquina e estão dispostos conforme a Figura 1 ilustra a seguir:

    Desta forma, elaborei um simples exemplo fazendo uso do comando Group By em conjunto com a função de agregação Max() para obter a maior data para cada coluna data, veja o exemplo abaixo:

    Select GSM, CPF, Contrato, Motivo_Envio, 
               Max(data_ativacao) As DataAtivacao, 
               Max(data_importacao) As DataImportacao, 
               Max(data_alteracao) As DataAlteracao, 
    	   status, 
    	   imei 
    From Dados
    Group By GSM, CPF, Contrato, Motivo_Envio, status, imei
    Order By DataAtivacao Desc

    Após executar o select acima temos o seguinte resultado apresentado na Figura 2 abaixo:

    Ressalto que este é somente um exemplo, uma das diversas possibilidades que podemos aplicar, como também, não garanto que o resultado obtido seja o que você deseja.

    Espero ter ajudado.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:04 AM
    • Unmarked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:05 AM
    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:06 AM
    Thursday, August 29, 2019 12:29 AM
    Moderator
  • Deleted
    • Marked as answer by Sup3r-Us3r Friday, August 30, 2019 3:33 PM
    Friday, August 30, 2019 2:45 PM
  • Opa gapimex, lembro de você também, já me ajudou e muito em outra dúvida que tinha, e trouxe mais uma vez uma solução para esse meu problema, agradeço a você e ao José, uma dúvida, eu posso usar esse cenário (essa query que você criou) todas as vezes que eu precisar obter o registro mais atual, seguindo o critério da especificada o ROW_NUMBER OVER() ou RANK() OVER() serve para qual finalidade? criar uma ranking literalmente para cada linha encontrada tipo isso?

    Coluna | Ranking

    A | 1

    B | 2

    C | 3

    e o PARTITION BY ele meio que separa cada linha para poder ser trabalhada de forma independente? o DESC serviu para usar pegar o maior para o menor correto?poderia me explicar a lógica disso por gentileza? desde já agradeço a atenção!

    Sup3r-Us3r,

    As Funções Row_Number() e Rank() você perguntou são definidas como funções de ranqueamento de dados que nos permitem estabelecer ordem de classificação de dados mas que muitas vezes não podem ser tratadas como determinísticas. Estas funções de classificação retornam um valor de classificação para cada linha em uma partição. Dependendo da função usada, algumas linhas podem receber o mesmo valor que outras.

    Segundo a documentação official Microsoft:

    Rank: Retorna a classificação de cada linha na partição de um conjunto de resultados. A classificação de uma linha é um valor mais o número de classificações que vêm antes da linha em questão.
    Se duas ou mais linhas empatarem em uma classificação, cada linha empatada receberá a mesma classificação.

    Row_Number(): Definem a saída de um resultado de números, mais especificamente, retorna o número sequencial de uma linha dentro de uma partição de um conjunto de resultados, começando em 1 para a primeira linha em cada partição. ROW_NUMBER e RANK são semelhantes.

    - Em ROW_NUMBER todos os números em sequência linhas (por exemplo 1, 2, 3, 4, 5); e
    - RANK fornece o mesmo valor numérico para as faixas (por exemplo 1, 2, 2, 4, 5), já a
    ROW_NUMBER é um valor temporário calculado quando a consulta é executada.

    Em relação ao Over() é mais conhecida como uma cláusula que você pode utilizar para as funções de agregação, ranking e analísticas, servindo com um element capaz de determinar o particionamento dos dados, bem como, as formas de ordenação aplicada a coluna.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:55 AM
    Thursday, August 29, 2019 1:17 AM
    Moderator

All replies

  • Deleted
    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:07 AM
    Thursday, August 29, 2019 12:14 AM
  • Boa noite,

    Experimente fazer uns testes mais ou menos dessa forma:

    with CTE_RN as
    (
        select
            *,
            row_number() over(partition by imei order by data_alteracao desc) as RN
        from Tabela
        where 
            motivo_envio like 'TROCA%'
    )
    
    select * from CTE_RN 
    where RN = 1

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:07 AM
    Thursday, August 29, 2019 12:19 AM
  • Sup3r-Us3r,

    Quando você se refere as datas, qual das três seria a utilizada como parâmetro de alteração?

    Tomei como base os dados que você compartilhou, realizei a importação para minha máquina e estão dispostos conforme a Figura 1 ilustra a seguir:

    Desta forma, elaborei um simples exemplo fazendo uso do comando Group By em conjunto com a função de agregação Max() para obter a maior data para cada coluna data, veja o exemplo abaixo:

    Select GSM, CPF, Contrato, Motivo_Envio, 
               Max(data_ativacao) As DataAtivacao, 
               Max(data_importacao) As DataImportacao, 
               Max(data_alteracao) As DataAlteracao, 
    	   status, 
    	   imei 
    From Dados
    Group By GSM, CPF, Contrato, Motivo_Envio, status, imei
    Order By DataAtivacao Desc

    Após executar o select acima temos o seguinte resultado apresentado na Figura 2 abaixo:

    Ressalto que este é somente um exemplo, uma das diversas possibilidades que podemos aplicar, como também, não garanto que o resultado obtido seja o que você deseja.

    Espero ter ajudado.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:04 AM
    • Unmarked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:05 AM
    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:06 AM
    Thursday, August 29, 2019 12:29 AM
    Moderator
  • Muito obrigado José, você me ajudou uma vez e me ajudou hoje novamente, agradeço de coração, deu certinho, em relação as perguntas que me fez, gsm seria o número de telefone, que pode ou não se repetir, o cpf e contrato também pode ser variáveis, mas o que decide mesmo esse filtro seria o imei, pois ele tem que ser único, e caso não for preciso obter o registro mais atual seguindo aquelas colunas de datas, a correta seria de data_ativacao, por exemplo nesse cenário que mostrei teve 3 registros, 2 se repetindo e nesse que se repetiu precisou do mais atual porque o imei é único e não pode se repetir, agora o outro registro já teve imei único, ele não se repetiu, não sei se deu para compreender bem, é um pouco confuso rsrs, mas agradeço a atenção, tenha uma excelente noite, vlw!
    Thursday, August 29, 2019 12:52 AM
  • Opa gapimex, lembro de você também, já me ajudou e muito em outra dúvida que tinha, e trouxe mais uma vez uma solução para esse meu problema, agradeço a você e ao José, uma dúvida, eu posso usar esse cenário (essa query que você criou) todas as vezes que eu precisar obter o registro mais atual, seguindo o critério da especificada o ROW_NUMBER OVER() ou RANK() OVER() serve para qual finalidade? criar uma ranking literalmente para cada linha encontrada tipo isso?

    Coluna | Ranking

    A | 1

    B | 2

    C | 3

    e o PARTITION BY ele meio que separa cada linha para poder ser trabalhada de forma independente? o DESC serviu para usar pegar o maior para o menor correto?poderia me explicar a lógica disso por gentileza? desde já agradeço a atenção!

    Thursday, August 29, 2019 1:01 AM
  • Deleted
    Thursday, August 29, 2019 1:09 AM
  • Opa gapimex, lembro de você também, já me ajudou e muito em outra dúvida que tinha, e trouxe mais uma vez uma solução para esse meu problema, agradeço a você e ao José, uma dúvida, eu posso usar esse cenário (essa query que você criou) todas as vezes que eu precisar obter o registro mais atual, seguindo o critério da especificada o ROW_NUMBER OVER() ou RANK() OVER() serve para qual finalidade? criar uma ranking literalmente para cada linha encontrada tipo isso?

    Coluna | Ranking

    A | 1

    B | 2

    C | 3

    e o PARTITION BY ele meio que separa cada linha para poder ser trabalhada de forma independente? o DESC serviu para usar pegar o maior para o menor correto?poderia me explicar a lógica disso por gentileza? desde já agradeço a atenção!

    Sup3r-Us3r,

    As Funções Row_Number() e Rank() você perguntou são definidas como funções de ranqueamento de dados que nos permitem estabelecer ordem de classificação de dados mas que muitas vezes não podem ser tratadas como determinísticas. Estas funções de classificação retornam um valor de classificação para cada linha em uma partição. Dependendo da função usada, algumas linhas podem receber o mesmo valor que outras.

    Segundo a documentação official Microsoft:

    Rank: Retorna a classificação de cada linha na partição de um conjunto de resultados. A classificação de uma linha é um valor mais o número de classificações que vêm antes da linha em questão.
    Se duas ou mais linhas empatarem em uma classificação, cada linha empatada receberá a mesma classificação.

    Row_Number(): Definem a saída de um resultado de números, mais especificamente, retorna o número sequencial de uma linha dentro de uma partição de um conjunto de resultados, começando em 1 para a primeira linha em cada partição. ROW_NUMBER e RANK são semelhantes.

    - Em ROW_NUMBER todos os números em sequência linhas (por exemplo 1, 2, 3, 4, 5); e
    - RANK fornece o mesmo valor numérico para as faixas (por exemplo 1, 2, 2, 4, 5), já a
    ROW_NUMBER é um valor temporário calculado quando a consulta é executada.

    Em relação ao Over() é mais conhecida como uma cláusula que você pode utilizar para as funções de agregação, ranking e analísticas, servindo com um element capaz de determinar o particionamento dos dados, bem como, as formas de ordenação aplicada a coluna.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marked as answer by Sup3r-Us3r Thursday, August 29, 2019 1:55 AM
    Thursday, August 29, 2019 1:17 AM
    Moderator
  • Obrigado Junior, esclareceu minha dúvida agora, obrigado pela atenção, explicação e pelo seu tempo!
    Thursday, August 29, 2019 2:11 AM
  • José, eu testei o código e funcionou certinho, testei em 2K de dados, com as explicações suas e dos demais que contribuirão na solução desse problema, consegui obter o que precisava, agradeço a sua atenção e a dos demais, muito obrigado! tenha uma excelente noite!
    Thursday, August 29, 2019 2:21 AM
  • Obrigado Junior, esclareceu minha dúvida agora, obrigado pela atenção, explicação e pelo seu tempo!

    Sup3r-Us3r,

    Disponha, estamos sempre por aqui.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Thursday, August 29, 2019 5:38 PM
    Moderator
  • José cometi um equívoco, esqueci de uma parte bem importante no filtro, que sem ela não irá funcionar, vou tentar ser mais objetivo e refazer a pergunta.

    Preciso obter o resultado que está na cor verde:

    Preciso que siga as regras:

    • motivo_envio: Tem que ser TROCA%
    • status: Tem que ser ANOMALIA
    • imei: Tem que ser único

    Critério:

    • data_importacao: Tem que ser a maior (mais recente)

    OBS:

    • Se caso o registro mais atual daquele IMEI estiver com o STATUS igual PENDENTE ou LIBERADO não quero pegar esse resultado, somente se o mais atual estiver com ANOMALIA

    Exemplificando:

    1. IMEI 1111111111 aparece 2 vezes, a primeira vez que apareceu estava com STATUS igual ANOMALIA, mas depois ele apareceu com STATUS igual PENDENTE, ou seja não quero mais, porque esse IMEI o status mais recente dele foi PENDENTE e não ANOMALIA.
    2. IMEI 2222222222 é a mesma coisa do IMEI 1111111111, aparece 2 vezes, a primeira com STATUS igual ANOMALIA mas depois aparece com o STATUS mais recente de PENDENTE, então não preciso dele também.
    3. IMEI 3333333333 e 4444444444 também não preciso, pois o STATUS mais atual é PENDENTE, só precisaria se o STATUS dele fosse ANOMALIA.
    4. IMEI 5555555555 aparece pela primeira vez com STATUS igual ANOMALIA e depois aparece pela segunda vez com o STATUS de ANOMALIA, então preciso dele (o que for o mais recente).

    Criei um arquivo com o CREATE e INSERT desses registros para poder facilitar:

    https://pastebin.com/raw/44f2cECt

    Peço desculpas pelo erro, esqueci de mencionar essa parte importante e crucial para esse filtro, desde já agradeço a ajuda!




    • Edited by Sup3r-Us3r Friday, August 30, 2019 10:49 PM
    Thursday, August 29, 2019 11:59 PM
  • Experimente acrescentar o filtro para a coluna Status no final da query:

    with CTE_RN as
    (
        select
            *,
            row_number() over(partition by imei order by data_alteracao desc) as RN
        from Tabela
        where 
            motivo_envio like 'TROCA%'
    )
    
    select * from CTE_RN 
    where RN = 1 and Status = 'ANOMALIA'

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    Friday, August 30, 2019 2:57 AM
  • Já realizei esse teste, mas com isso irá pegar todas as ANOMALIA, no caso precisaria verificar se o STATUS mais atual está como PENDENTE, se estiver não pode pegar.

    • Se caso o registro mais atual daquele IMEI estiver com o STATUS igual PENDENTE ou LIBERADO não quero pegar esse resultado, somente se o mais atual estiver com ANOMALIA
    Friday, August 30, 2019 3:08 AM
  • Na amostra de dados que você postou, as linhas que estão com o status 'Pendente' estão sem a data de alteração, por isso o status mais recente de todos os IMEIs é 'ANOMALIA'.

    A query que sugeri primeiro classifica as linhas por IMEI considerando a data de alteração (na CTE_RN) para depois verificar se a última linha (RN = 1) está com o status 'ANOMALIA'.


    Assinatura: http://www.imoveisemexposicao.com.br

    Friday, August 30, 2019 12:47 PM
  • Deleted
    Friday, August 30, 2019 2:11 PM
  • Informei coluna errada, a correta seria: data_importacao

    O critério que define o status atual daquele IMEI é a data_importacao mesmo, e referente a regra preciso pegar somente se a linha mais atual estiver com ANOMALIA.

    Desculpe pelos erros na informação. Em resumo se o status atual daquele IMEI for PENDENTE ou LIBERADO não preciso desse resultado, somente se for ANOMALIA o mais atual.

    Friday, August 30, 2019 2:30 PM
  • Deleted
    • Marked as answer by Sup3r-Us3r Friday, August 30, 2019 3:33 PM
    Friday, August 30, 2019 2:45 PM
  • Nesse caso experimente trocar a coluna data_alteracao pela coluna data_importacao depois do Order By.

    Assinatura: http://www.imoveisemexposicao.com.br

    Friday, August 30, 2019 2:46 PM