es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Cómo crear una VISTA que combina y filtra el resultado de múltiples filas

17 Jul, 2023 Programación

Hola, tengo un problema con la tarea que debo hacer.
Tengo una tabla en SQL que muestra el registro de usuarios (subscriptores) con disparadores que agregan TIMESTAMP, ACCIÓN REALIZADA y NOMBRE del subscriptor, se ve algo así:

TABLA: audit_subscibers

ID Nombre Acción Hora
0 John Insertar un subscriptor 2020-1-1
1 John Eliminar un subscriptor 2020-3-1
2 Mark Insertar un subscriptor 2020-4-5
3 Andrew Insertar un subscriptor 2020-5-1
4 Andrew Actualizar un subscriptor 2020-5-15

Ahora necesito crear una VISTA que muestre SOLAMENTE los subscriptores (nombre) que han sido eliminados, junto con la FECHA DE ELIMINACIÓN y la FECHA DE INSERCIÓN, para lograr algo como esto:

‘John’ fue agregado (como cualquier otro miembro) pero también fue eliminado, sin tocar a los miembros que solo han sido insertados.

Nombre Fecha agregada Fecha eliminada
John 2020-1-1 2020-3-1
algún otro 2020-x-x 2020-y-y

¿Cómo lograr esto, tomando solo los subscriptores que tienen más de 1 entrada y una entrada debe indicar que el usuario ha sido ELIMINADO: Eliminar un subscriptor, y combinar el RESULTADO en una sola fila?

Tengo otra tarea similar, pero esta vez debo crear una vista (solo basada en la tabla audit_subscibers) que muestre SOLAMENTE los subscriptores que AÚN existen (tomar todos los subscrb. con “Insertar un subscriptor”, pero excluir de los resultados aquellos que tienen más filas que incluyen “Eliminar un subscriptor”).

Estoy realmente agradecido por las respuestas…

Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para crear la primera vista que muestra los suscriptores eliminados con su fecha de inserción y eliminación, puedes utilizar la siguiente consulta SQL:

    CREATE VIEW deleted_subscribers AS
SELECT 
  Nombre, 
  MIN(CASE WHEN `Acción` = 'Insertar un suscriptor' THEN `Tiempo` END) AS `Fecha de inserción`,
  MIN(CASE WHEN `Acción` = 'Eliminar un suscriptor' THEN `Tiempo` END) AS `Fecha de eliminación`
FROM 
  audit_subscribers
WHERE 
  `Nombre` IN (SELECT `Nombre` FROM `audit_subscribers` GROUP BY `Nombre` HAVING COUNT(*) > 1)
GROUP BY 
  `Nombre`
HAVING 
  COUNT(DISTINCT `Acción`) > 1;

    Esto creará una vista llamada deleted_subscribers que solo muestra los suscriptores que han sido eliminados, con su fecha de inserción y eliminación en columnas separadas. La cláusula WHERE filtra los suscriptores que solo tienen una entrada, y la cláusula HAVING asegura que los suscriptores seleccionados tengan tanto entradas de “Insertar un suscriptor” como de “Eliminar un suscriptor”.

    Para crear la segunda vista que muestra solo los suscriptores que aún existen, puedes utilizar la siguiente consulta SQL:

    CREATE VIEW active_subscribers AS
SELECT 
  `Nombre`, 
  MIN(`Tiempo`) AS `Fecha de inserción`
FROM 
  `audit_subscribers`
WHERE 
  `Acción` = 'Insertar un suscriptor' AND `Nombre` NOT IN (
    SELECT `Nombre` 
    FROM `audit_subscribers`
    WHERE 
      `Acción` = 'Eliminar un suscriptor'
      AND `Nombre` IS NOT NULL
  )
GROUP BY 
  `Nombre`;

    Esto creará una vista llamada active_subscribers que solo muestra los suscriptores que aún están activos (no han sido eliminados), con su fecha de inserción. La cláusula WHERE filtra los suscriptores que han sido eliminados, utilizando una subconsulta que selecciona solo los registros de “Eliminar un suscriptor” donde el Nombre no es NULL.

Comments are closed.