Duplicados

Query Ejercicio:

insert into platzi.alumnos (id, nombre, apellido, email, colegiatura, fecha_incorporacion, carrera_id, tutor_id) values (1000, 'Pamelina', null, '[email protected]', 4800, '2020-04-26 10:18:51', 12, 16);

La cláusula HAVING es una implemetacion que el sistema gestor de bases de datos SQL crea para complementar el condicionante WHERE, ya que el condicionante WHERE no permite utilizar funciones de agregación como SUM, MAX, MIN o AVG, es decir, con un condicionante WHERE no podemos crear consultas válidas que sean capaz de devolvernos los datos de los clientes que compraron más de 1000 productos durante un año, por ejemplo.

Tener esto en cuenta por si tampoco entendieron nada y este es su primer curso de base de datos 😉

En el ejercicio de encontrar un DUPLICADO, se está haciendo una partición de la tabla de todos los valores en filas de cada una de las variables o columnas de la tabla a excepción del id, el cual no puede ser igual y no se repite al ser una primary key.

Cuando se aplica esta partición en cada valor se hace único los valores, es decir, cada row viene siendo una partición, y al utilizar la función agregada que en este caso es ROW_NUMBER() va a contar los valores de cada una de las particiones haciendo que se reinicie los “números de fila” cuando salta de partición en partición. por eso, cuando encuentra dos valores iguales, enumera los dos valores, dejando como valor en su row un 2.

Por si necesitan el código para insertar una fila

insert into platzi.alumnos (id, nombre, apellido, email, colegiatura, fecha_incorporacion, carrera_id, tutor_id) values (1001, 'Pamelina', null, '[email protected]', 4800, '2020-04-26 10:18:51', 12, 16);

Por lógica no puede existir 2 correos iguales, por lo tanto realice la actividad en base a eso

• Primero certifique que existían correos duplicados.

SELECT email,COUNT(*)
FROM platzi.alumnos
GROUP BY email
HAVING COUNT(*) > 1;

De esa forma obtuve el nombre del correo repetido, en base a ello, se que puedo realizar la agrupación por partición utilizando unicamente el correo.

• Ahora valido que obtengo el id del correo duplicado.

SELECT id 
FROM (
	SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS row
	FROM platzi.alumnos
) AS duplicados
WHERE duplicados.row > 1;

Una vez certificado que obtengo el id del dato duplicado, procedo a borrar el registro.

DELETE FROM platzi.alumnos
where id = (
SELECT id 
FROM (
	SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS row
	FROM platzi.alumnos
) AS duplicados
WHERE duplicados.row > 1
);

¡Ánimo vas por la mitad!

Solucion al reto

DELETE
    FROM platzi.alumnos
    WHERE id IN (SELECT id FROM (
        SELECT *
            FROM (SELECT id, ROW_NUMBER() OVER(
            PARTITION BY 
                nombre, apellido, 
                email, colegiatura,
                fecha_incorporacion, carrera_id,
                tutor_id
            ORDER BY id ASC) AS row
            FROM platzi.alumnos
    ) AS duplicados
    WHERE duplicados.row > 1) AS duplicados_id);

Para SQL Server:

-- METODO 1
-- MOSTRAR DUPLICADO A PARTIR DEL ID
USE [platzi]
SELECT *
FROM platzi.alumnos AS ou
WHERE 
(
	SELECT COUNT(*)
	FROM platzi.alumnos AS inr
	WHERE ou.id = inr.id
) > 1
GO


-- METODO 2
-- MOSTRAR DUPLICADO COMPARANDO TODOS LOS CAMPOS, EXCEPTO EL ID
USE [platzi]
SELECT CONCAT(platzi.alumnos.nombre,',',
			  platzi.alumnos.apellido,',',
			  platzi.alumnos.email,',',
			  platzi.alumnos.colegiatura,',',
			  platzi.alumnos.fecha_incorporacion,',',
			  platzi.alumnos.carrera_id,',',
			  platzi.alumnos.tutor_id), COUNT(*)
FROM platzi.alumnos
GROUP BY CONCAT(platzi.alumnos.nombre,',',
			  platzi.alumnos.apellido,',',
			  platzi.alumnos.email,',',
			  platzi.alumnos.colegiatura,',',
			  platzi.alumnos.fecha_incorporacion,',',
			  platzi.alumnos.carrera_id,',',
			  platzi.alumnos.tutor_id)
HAVING COUNT(*) >1
ORDER BY COUNT(*) DESC
GO


-- METODO 3
-- MOSTRAR DUPLICADO COMPARANDO TODOS LOS CAMPOS, EXCEPTO EL ID CON SUBCONSULTA
USE [platzi]
SELECT *
FROM 
(
	SELECT ROW_NUMBER() OVER(PARTITION BY nombre,apellido,email,colegiatura,
									   fecha_incorporacion,carrera_id,tutor_id 
						  ORDER BY id ASC) AS row, *
	FROM platzi.alumnos
) AS duplicados
WHERE duplicados.row > 1
ORDER BY row DESC

Soy bastante nuevo en este mundo así que tuve que hacer una especie de corrida en frío para entenderlo y poder crear una imagen en mi cabeza de lo que sucede en las subconsultas. Dejo esto que es lo que veo en mi mente sobre el código de la case por si a alguien le sirve. Me corrigen los expertos por fa c:

Para los que usan MySQL Workbench, pueden encontrar los duplicados usando CONCAT_WS de la siguiente forma :

Por si quieren entender mejor como funciona el PARTITION BY, me sirvió mucho para comprender:

15. Duplicados

SELECT *
FROM platzi.alumnos AS ou
WHERE (
	SELECT COUNT(*)
	FROM platzi.alumnos AS inr
	WHERE ou.id = inr.id
) > 1;

SELECT (platzi.alumnos.*)::text, COUNT(*)
FROM platzi.alumnos 
GROUP BY platzi.alumnos.*
HAVING COUNT(*) > 1;

SELECT (
	   platzi.alumnos.nombre,
	   platzi.alumnos.apellido,
	   platzi.alumnos.email,
	   platzi.alumnos.colegiatura,
	   platzi.alumnos.fecha_incorporacion,
 	   platzi.alumnos.carrera_id,
	   platzi.alumnos.tutor_id
	   )::text, COUNT(*)
FROM platzi.alumnos 
GROUP BY  platzi.alumnos.nombre,
	   platzi.alumnos.apellido,
	   platzi.alumnos.email,
	   platzi.alumnos.colegiatura,
	   platzi.alumnos.fecha_incorporacion,
 	   platzi.alumnos.carrera_id,
	   platzi.alumnos.tutor_id
HAVING COUNT(*) > 1;

SELECT *
FROM (
	SELECT id,
	ROW_NUMBER() OVER(
		PARTITION BY
			nombre,
			apellido,
	email,
	colegiatura,
	fecha_incorporacion,
	carrera_id,
	tutor_id
	ORDER BY id ASC
	)AS row,
	*
	FROM platzi.alumnos
)AS duplicados
WHERE duplicados.row > 1;

mismo resultado mucho mas fácil:

select nombre, apellido, email, colegiatura, fecha_incorporacion, carrera_id, tutor_id, count(nombre)
from platzi.alumnos
group by nombre, apellido, email, colegiatura, fecha_incorporacion, carrera_id, tutor_id
having count()>1

Para encontrar duplicados basados en sus datos principales: nombre, apellido y email.

select (nombre,apellido,email)::text as datos_usuarios, count(*) as cantidad 
from platzi.alumnos
group by (nombre,apellido,email)
order by cantidad desc```

Ejercicio cumplido…

DELETE FROM platzi.alumnos
WHERE id IN (
SELECT id
FROM (
	SELECT id,
	ROW_NUMBER() over(
		PARTITION BY
			nombre,
			apellido,
			email,
			colegiatura,
			fecha_incorporacion,
			carrera_id,
			tutor_id
		ORDER BY id ASC
	)AS row
	
	FROM platzi.alumnos
)AS duplicados
WHERE duplicados.row > 1);```

Para entender de manera mas simple lo que esta pasando en el ultimo query, les recomendo que corran esta query:

SELECT *, ROW_NUMBER() OVER(PARTITION BY colegiatura) AS row
FROM platzi.alumnos;

Asi se dan cuenta como funciona a menor escala para luego interpretar lo demas, espero ser de ayuda!

No me sirvieron los ejemplos en workbench 😦

Solución al reto anterior

SELECT * 
    FROM (
        SELECT *, 
			DATE_PART('YEAR', fecha_incorporacion) AS anio_incorporacion,
			DATE_PART('MONTH', fecha_incorporacion) AS mes_incorporacion
        FROM platzi.alumnos
    ) AS alumnos_con_anio
    WHERE anio_incorporacion = 2018 AND mes_incorporacion = 5;```

La solucion en MySQL WorkBench seria:

<SELECT *
FROM (
	SELECT *, YEAR(fecha_incorporacion) AS Ano, MONTH(fecha_incorporacion) AS Mes
    FROM platzi.alumnos
    ) AS Fechas
WHERE Ano=2018 AND Mes=5;>

Mi solución al reto (postgreSQL):

DELETE FROM platzi.alumnos
WHERE id IN(
SELECT table_alumnos.id
FROM (
	SELECT
		ROW_NUMBER() OVER(
			PARTITION BY
			a1.nombre,
			a1.apellido,
			a1.email,
			a1.colegiatura,
			a1.fecha_incorporacion,
			a1.carrera_id,
			a1.tutor_id
			ORDER BY a1.id ASC
		) AS row_alumnos, a1.*
	FROM platzi.alumnos as a1
) AS table_alumnos
WHERE table_alumnos.row_alumnos>1
)

¡Hola! aquí dejo una alternativa al reto de la clase anterior mucho más sencilla y con menos líneas de código, en el caso de que no nos interese crear nuevas columnas para el año y el mes.
Esta solución se enfoca en obtener la respuesta a la pregunta sin modificar la tabla en el proceso de consulta:

SELECT *
FROM platzi.alumnos
WHERE (EXTRACT(YEAR FROM fecha_incorporacion)) = 2019
AND
(EXTRACT(MONTH FROM fecha_incorporacion)) = 5;

soluciòn para eliminar el campo duplicado

DELETE
FROM platzi.alumnos
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER () OVER (
PARTITION BY
nombre,
apellido,
email,
colegiatura,
fecha_incorporacion,
carrera_id,
tutor_id
ORDER BY id ASC
) AS row
FROM platzi.alumnos
) AS duplicados
WHERE duplicados.row >1);

-- Tarea -> Eliminar los duplicados de la tabla platzi.alumnos
DELETE
FROM platzi.alumnos
WHERE id IN (SELECT id
FROM (
	SELECT --id,
	ROW_NUMBER() OVER(
		PARTITION BY
			nombre,
			apellido,
			email,
			colegiatura,
			fecha_incorporacion,
			carrera_id,
			tutor_id
		ORDER BY id ASC
		) AS ROW, *
		FROM platzi.alumnos
) AS duplicados
WHERE duplicados.row > 1);

Lo que paso en el último query fue que en vez de contar los rows, como lo solíamos hacer, ahora los estamos separando de acuerdo a ciertas características especificadas en la Partición. Eso significa que ahora cada row con un name, email, lastname, career_id, tutor_id, etc. único van a tener un valor de 1 en la columna row. Cada vez que haya otra tupla igual, ese valor va a aumentar en uno

Para la tarea ocupé los dos métodos mezclados, tal vez en la práctica nada recomendado pero para fines de practicar me pareció útil.

SELECT *
FROM platzi.alumnos

WHERE(EXTRACT(MONTH FROM fecha_incorporacion)) = 05
and
(DATE_PART('YEAR', fecha_incorporacion)) = 2018
;```

Solucion:

DELETE
    FROM platzi.alumnos
    WHERE id IN (SELECT id FROM (
        SELECT *
            FROM (SELECT id, ROW_NUMBER() OVER(
            PARTITION BY 
                nombre, apellido, 
                email, colegiatura,
                fecha_incorporacion, 
		carrera_id,
                tutor_id
            ORDER BY id ASC) AS row
            FROM platzi.alumnos
    ) AS duplicados
    WHERE duplicados.row > 1) AS duplicados_id);

Reto de la clase

DELETE
FROM platzi.alumnos
Where id IN (
	SELECT id
	FROM (
		SELECT 
			id,
			ROW_NUMBER() OVER (
				PARTITION BY 
					nombre,
					apellido,
					email,
					fecha_incorporacion,
					carrera_id,
					tutor_id
				ORDER BY id ASC
			) AS row,
			nombre,
			apellido,
			email,
			fecha_incorporacion,
			carrera_id,
			tutor_id
		FROM platzi.alumnos
	) AS duplicados
	WHERE duplicados.row > 1);

Solución al reto

DELETE FROM platzi.alumnos
WHERE id = (
    SELECT id
    FROM (
        SELECT
        ROW_NUMBER() OVER(
            PARTITION BY
                nombre,
                apellido,
                email,
                colegiatura,
                fecha_incorporacion,
                carrera_id,
                tutor_id
            ORDER BY id ASC
            AS row,
        *
        FROM platzi.alumnos
    ) AS duplicados
    WHERE duplicados.row > 1
);

SELECT *
FROM platzi.alumnos
WHERE EXTRACT(MONTH FROM fecha_incorporacion) = 5
  AND EXTRACT(YEAR FROM fecha_incorporacion) = 2018;

Ejercicio de tarea:

DELETE FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM (
		SELECT id,
		ROW_NUMBER() OVER(
			PARTITION BY
				nombre,
				apellido,
				email,
				colegiatura,
				fecha_incorporacion,
				carrera_id,
				tutor_id
			ORDER BY id ASC
		) AS row
		FROM platzi.alumnos
	) AS duplicados
	WHERE duplicados.row > 1
);

Reto resuelto, bastante intuitivo.

DELETE FROM alumnos
WHERE id IN (
	SELECT id FROM (SELECT ROW_NUMBER() OVER(PARTITION BY nombre,apellido,email,colegiatura,fecha_incorporacion,carrera_id,tutor_id ORDER BY id) AS count_row, 
							* FROM alumnos) AS duplicados
	WHERE count_row > 1
)

ELIMINACION DE LOS REGISTROS DUPLICADOS

CONSULTA DE LOS REGISTROS DUPLICADOS:

Mi solucion al ejercicio:

Después de tantos intentos pude entenderle, esta es mi solución:

-- Eliminar duplicados con subquery
DELETE FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM (
		SELECT id,
		ROW_NUMBER() OVER(
			PARTITION BY
				nombre,
				apellido,
				email,
				colegiatura,
				fecha_incorporacion,
				carrera_id,
				tutor_id
			ORDER BY id ASC
		)AS row
		FROM platzi.alumnos
	) AS duplicados
	WHERE duplicados.row > 1); 

Solución al reto:

DELETE FROM platzi.alumnos
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
            ROW_NUMBER() OVER (
                PARTITION BY nombre, apellido, email, colegiatura, fecha_incorporacion, carrera_id, tutor_id
                ORDER BY id
            ) AS row_num
        FROM platzi.alumnos
    ) AS subquery
    WHERE subquery.row_num > 1
);

mi aporte

DELETE FROM platzi.alumnos
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                   PARTITION BY 
				   nombre, 
				   apellido, 
				   email, 
				   colegiatura, 
				   fecha_incorporacion, 
				   carrera_id, 
				   tutor_id
                   ORDER BY id ASC
               ) AS row
        FROM platzi.alumnos
    ) AS duplicado
    WHERE duplicado.row > 1
);