Início / coding / Perguntas / 79063200
Accepted
John Beasley
Asked: 2024-10-08 02:29:11 +0800 CST

Excluir registros que tenham 3 ou mais datas dentro de uma série de colunas de data

  • 772

Estou usando o phpMyAdmin que usa o MySQL versão 5.7.

Tenho uma tabela de banco de dados chamada condensedque contém milhões de e-mails exclusivos com várias "datas de envio", entre outras colunas. Alguns dos e-mails terão uma data de envio NULA.

Focando apenas nas colunas emaile send_dateaqui.

Aqui está um exemplo da tabela:

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email2 | 2024-06-01  | 2024-06-16  |  2024-06-05 | 2024-06-19  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------

Preciso excluir qualquer coisa que tenha mais de 3 datas de envio no mês de junho.

Portanto, se um e-mail tiver uma data de envio em 3 ou colunas dentro do mês de junho, não retorne esses registros.

Usando meu exemplo acima, os resultados devem ser parecidos com estes:

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------

Os resultados acima foram excluídos email2porque tinham 4 datas dentro do mês de junho.

email1atende aos critérios porque tem apenas 2 datas no mês de junho e 1 em maio.

email3, email4e email5atendem aos critérios porque têm apenas 1 data no mês de junho e 1 em maio.

Posso escrever a consulta abaixo:

SELECT 
  * FROM 
`condensed` 
WHERE 
(
  `a_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `b_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `c_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30' 
  // remaining date columns     
)

A consulta acima exclui tudo dentro do intervalo de datas.

Não tenho certeza de como incorporar a contagem para excluir qualquer coisa que tenha mais de 3 datas dentro do intervalo de datas especificado (que neste caso é junho).

Por favor ajude e obrigado.

mysql

2 respostas

  1. Best Answer

    Os valores contêm NULLs - então não podemos concatenar ou somar as expressões com a maioria das funções/operadores.

    Eu recomendo que você concatene as datas usando CONCAT_WS() - essa função pula NULLs. Então conte a quantidade de substrings correspondentes.

    Para um mês específico, use algo como

    SELECT LENGTH(dateslist) - LENGTH(REPLACE(dateslist, '2024-06-', '2024-06')) AS amount, ...
FROM ( SELECT CONCAT_WS(',', a_last_sent, b_last_sent, ..., x_last_sent) AS dateslist, ...
       ...
       )
...

    Para intervalo personalizado, use UNION, REPLACEs aninhados (a versão 5.7 não oferece suporte à substituição de expressão regular) ou registre e use de acordo com a função definida pelo usuário.

    Claro que você também pode usar vários COALESCE().

    Você também pode desarticular seus dados com várias UNIONs e então contá-los da maneira comum.

    ATUALIZAR

    Você se importaria em fornecer um exemplo de como a COALESCE ou a UNION funcionariam neste caso? – John Beasley

    Usando COALESCE. Código editado da resposta de Roemer

    ...
WHERE 
      (COALESCE(`a_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30')
    + (COALESCE(`b_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30')
    + (COALESCE(`c_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30') 
...

    Usando UNION:

    ...
FROM ( SELECT email, a_last_sent FROM table WHERE a_last_sent IS NOT NULL
       UNION ALL
       SELECT email, b_last_sent FROM table WHERE b_last_sent IS NOT NULL
       UNION ALL
       SELECT email, c_last_sent FROM table WHERE c_last_sent IS NOT NULL
       UNION ALL
       ...
       ) AS unpivotted_data
...
    • 1

  2. Use o fato de que TRUE conta como 1 ao fazer cálculos com ele no MySQL.

    SELECT * FROM `condensed` 
WHERE 
    (IFNULL(`a_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30')
    + (IFNULL(`b_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30')
    + (IFNULL(`c_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30') 
    // remaining date columns ... BTW bad table structure   
    <= 3;
    • -1

relate perguntas