Início / dba / Perguntas / 335437
Accepted
hamilton9
Asked: 2024-02-02 07:06:12 +0800 CST

Instrução MySQL para atualizar eventos repetidos

  • 772

Preciso criar uma única instrução MySQL para acelerar o processo de atualização de eventos repetidos semanalmente em uma tabela de banco de dados que possuo.

Neste momento, o usuário está adicionando registros à tabela. Alguns registros são para eventos de data única e alguns registros são eventos que se repetem a cada 7 dias dentro de uma data de início e de término definidas. Ambos os tipos de registros são inseridos na mesma tabela.

Cada evento tem a data de início e uma data de término da data do evento inserida em dois campos: 'date_start' e 'date_end'. Para um evento de data única, o usuário insere a mesma data em 'date_start' e 'date_end'. Para um evento repetido, eles inserem a data da ocorrência do primeiro evento em 'data_início' e a data da última data da ocorrência semanal em 'data_fim'. Os eventos repetidos são marcados como 'Weekly_Event' conforme mostrado abaixo nos dados de exemplo.

Preciso pegar esta tabela e fazer várias cópias de todos os registros marcados como 'Weekly_Event'. Uma cópia do registro original para cada semana entre a data de início e de término.

No exemplo abaixo, um registro tem a data de início definida como 3 de maio e a data de término como 7 de junho. Preciso de uma instrução SQL que crie 6 novos registros a partir desse registro: 3 de maio, 10 de maio, 17 de maio, 24 de maio, 31 de maio, 7 de junho. Há também outro evento 'Weekly_Event' e um evento 'Single_Day' como exemplo.

MySQL version: 8.0.36
Table Type: InnoDB  
Collation: latin1_swedish_ci


CREATE TABLE `original_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `original_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 1, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day');

Após a operação, os registros ficariam principalmente parecidos com o exemplo abaixo. Quero que o date_start e o date_end gerados dos Weekly_Events sejam iguais porque cada um dos eventos recém-gerados acontece dentro do único dia atribuído.


CREATE TABLE `updated_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `updated_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 0, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 0, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day'),
(101, 1, '2024-05-03', '2024-05-03', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-03', 'Weekly_Event'),
(102, 1, '2024-05-10', '2024-05-10', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-10', 'Weekly_Event'),
(103, 1, '2024-05-17', '2024-05-17', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-17', 'Weekly_Event'),
(104, 1, '2024-05-24', '2024-05-24', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-24', 'Weekly_Event'),
(105, 1, '2024-05-31', '2024-05-31', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-31', 'Weekly_Event'),
(106, 1, '2024-06-07', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-06-07', 'Weekly_Event'),
(107, 1, '2024-05-09', '2024-05-09', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-09', 'Weekly_Event'),
(108, 1, '2024-05-16', '2024-05-16', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-16', 'Weekly_Event');

O 'id' para os novos eventos deve ser único e, idealmente, gerado automaticamente com base no próximo 'id' atual em 'original_shows_24'. No meu exemplo, estou assumindo que 'id' está em uso em 'original_shows_24' até 100 por outros registros.

Observe o 'status' dos eventos usados ​​para gerar os eventos repetidos. É definido como '0' nos registros que foram processados ​​('date_start' diferente de 'date_end' e 'category' = 'Weekly_Event')

Outro elemento do resultado final que precisa ser considerado: o slug gerado. O campo slug é usado para gerar o link para a subpágina exclusiva daquele evento, portanto ele também precisa ser atualizado nesta operação. Observe o formato nos dados de amostra, basicamente preciso acrescentar um traço ('-') e 'date_start' no final do slug para cada um dos novos registros gerados. Todas as entradas em 'slug' devem ser únicas.

Ele também precisará ser capaz de gerar a data correta conforme o mês muda. Por exemplo, o último evento nos dados de amostra é 7 de junho. Isso ocorre 7 dias após 31 de maio. Se o MySQL cuidar disso automaticamente nas operações de data, então isso é ótimo.

Criei apenas 'updated_shows_24' para mostrar a saída. Se a saída puder ser simplesmente colocada de volta em 'original_shows_24' (com o status dos registros apropriado alterado de '1' para '0'), isso é melhor. Além disso, 'updated_shows_24' mostra apenas os dois primeiros registros do outro evento repetido apenas para ilustrar a saída desejada. Como a data final desse evento repetido é 18/08/2024, haveria muito mais registros gerados pela solução do que apenas as duas UI incluídas.

Obrigado por qualquer ajuda.

mysql

1 respostas

  1. Best Answer

    DEMONSTRAÇÃO

    CREATE TABLE `original_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `original_shows_24` (`id`,`date_start`, `date_end`, `name`, `slug`) VALUES
(1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'weekly-event'),
(2, '2024-05-09', '2024-06-18', 'Another Weekly Event', 'another-weekly-event'),
(3, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'a-single-day-event');

SELECT * FROM original_shows_24;
    eu ia data_início data_fim nome lesma
    1 03/05/2024 07/06/2024 Nome do Evento Semanal evento semanal
    2 09/05/2024 18/06/2024 Outro evento semanal outro evento semanal
    3 02/05/2024 02/05/2024 Um evento regular de um único dia evento de um único dia 
    INSERT INTO original_shows_24 (date_start, date_end, name, slug)
WITH RECURSIVE cte AS (
  SELECT date_start, date_end, name, slug
  FROM original_shows_24
  WHERE date_start < date_end
  UNION ALL
  SELECT LEAST(date_end, date_start + INTERVAL 1 WEEK), date_end, name, slug
  FROM cte
  WHERE date_start < date_end
)
SELECT date_start, date_start, name, CONCAT(slug, '-', date_start)
FROM cte;

    SELECT * FROM original_shows_24 ORDER BY name, date_start, date_end DESC
    eu ia data_início data_fim nome lesma
    3 02/05/2024 02/05/2024 Um evento regular de um único dia evento de um único dia
    2 09/05/2024 18/06/2024 Outro evento semanal outro evento semanal
    5 09/05/2024 09/05/2024 Outro evento semanal outro evento semanal-2024-05-09
    7 16/05/2024 16/05/2024 Outro evento semanal outro evento semanal-2024-05-16
    9 23/05/2024 23/05/2024 Outro evento semanal outro evento semanal-2024-05-23
    11 30/05/2024 30/05/2024 Outro evento semanal outro evento semanal-2024-05-30
    13 06/06/2024 06/06/2024 Outro evento semanal outro evento semanal-2024-06-06
    15 13/06/2024 13/06/2024 Outro evento semanal outro evento semanal-2024-06-13
    16 18/06/2024 18/06/2024 Outro evento semanal outro evento semanal-2024-06-18
    1 03/05/2024 07/06/2024 Nome do Evento Semanal evento semanal
    4 03/05/2024 03/05/2024 Nome do Evento Semanal evento semanal-2024-05-03
    6 10/05/2024 10/05/2024 Nome do Evento Semanal evento semanal-2024-05-10
    8 17/05/2024 17/05/2024 Nome do Evento Semanal evento semanal-2024-05-17
    10 2024-05-24 2024-05-24 Nome do Evento Semanal evento semanal-2024-05-24
    12 31/05/2024 31/05/2024 Nome do Evento Semanal evento semanal-2024-05-31
    14 07/06/2024 07/06/2024 Nome do Evento Semanal evento semanal-2024-06-07

    violino

    Os dados originais foram editados e os intervalos foram ligeiramente reduzidos. Colunas desnecessárias para demonstração também foram removidas.

    ORDER BY fornece visibilidade do resultado.

    categorynão é usado porque a categoria do evento pode ser determinada por date_starte date_endigualdade.

    • 1

relate perguntas