Início / dba / Perguntas / 303813
Accepted
Nuno
Asked: 2021-12-11 16:32:03 +0800 CST

Filtre as linhas em que o texto dessa linha atual está contido no texto da linha seguinte

  • 772

Gostaria de saber se é possível, em um SELECT, filtrar as linhas em que o texto dessa linha atual está contido no texto da linha seguinte.

Por exemplo, imagem que registramos "pesquisas" realizadas por um usuário, "enquanto ele digita":

1  something I searched
2  another search
3  this
4  this is a
5  this is a third
6  this is a third search
7  fourth
8  fourth search
9  here's a ry
10 here's a typo

As linhas 3-6 são efetivamente as mesmas - apenas o usuário estava um pouco "lento" digitando, então salvamos várias vezes.
Linhas 7-8 o mesmo também.
As linhas 9-10 são as mesmas, mas houve um erro de digitação.

Estou procurando fazer um SELECTque retornaria apenas as seguintes linhas:

1  something I searched
2  another search
6  this is a third search
8  fourth search
9  here's a ry
10 here's a typo

Algo assim é possível?

Observação - não há nenhuma garantia de que os IDs estarão em uma sequência para um usuário específico. Então, não posso simplesmente JOINcom a própria tabela, pelo id = id+1. Tudo o que posso fazer é ORDER BY idcolocá-los em ordem.

mysql mariadb

1 respostas

  1. Best Answer

    Você vai querer usar uma função de janela, especificamente a LEAD()função faria um ótimo uso aqui assim:

    -- Gets the following row's search text and compare it to the current row's search text to see if the following row starts with the current row's
WITH _SearchesMarkedAsRedundant
(
    SELECT 
        id, 
        userId,
        searchText,             
        IF(LEAD(searchText) OVER (PARTITION BY userId ORDER BY id) LIKE CONCAT(searchText, '%'), 1, 0) ​IsRedundantSearch
    FROM Searches
)

-- Returns only the rows whose following row did not start with this row's search text
SELECT id, userId, searchText
FROM _SearchesMarkedAsRedundant
WHERE IsRedundantSearch = 0

    E se você quiser remover globalmente pesquisas redundantes, independentemente de qual usuário inseriu a pesquisa, você pode remover a PARTITION BYcláusula da função de janela assim IF(LEAD(searchText) OVER (userId ORDER BY id) LIKE CONCAT(searchText, '%'), 1, 0) ​IsRedundantSearch.

    O acima irá realizar a saída do seu exemplo fornecido. Observe que ele analisa especificamente apenas a próxima 1 linha (classificada pela coluna id) e verifica se essa linha começa com o mesmo texto de pesquisa da linha atual. Você pode fazer uma verificação completa de conteúdo adicionando outro símbolo curinga %na frente do searchTextoperador CONCAT()assim CONCAT('%', searchText, '%').

    A LEAD()função de janela é a chave para esta solução aqui, pois pode antecipar a coluna inserida por quantas linhas você especificar. O número padrão de linhas que antecipa é 1, mas se você quisesse olhar para a 3ª linha à frente em vez da próxima linha, então LEAD(searchText, 3)anteciparia por 3 linhas.

    • 2

relate perguntas