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 😉

Saludos, mi respuesta:

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.

Más información del having => https://www.postgresqltutorial.com/postgresql-having/

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);

¡Á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);

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
);

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

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:

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:

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);```

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;

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
);

Mi solución

DELETE FROM platzi.alumnos
WHERE id=(
	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);

SQL SERVER
De esta forma puedo eliminar mas de un registro duplicado a la vez

DECLARE @DuplicateIds VARCHAR(MAX);

SELECT @DuplicateIds = STRING_AGG(Id, ',') WITHIN GROUP (ORDER BY Id)
FROM (
    SELECT Id,
        ROW_NUMBER() OVER (
            PARTITION BY Nombre, Apellido, Email ORDER BY Id ASC
        ) AS row
    FROM Platzi.Alumnos
) AS Duplicados
WHERE Duplicados.row > 1;

SELECT @DuplicateIds

DELETE FROM Platzi.Alumnos WHERE Id IN (SELECT VALUE FROM string_split(@DuplicateIds, ','));

Reto de la clase

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 ASC) row,id
	FROM alumnos
	) duplicados
	WHERE row > 1
)

-- Otra forma
DELETE FROM alumnos
WHERE id IN
(
	SELECT MAX(id) FROM alumnos
	GROUP BY nombre,apellido,email,colegiatura,fecha_incorporacion, carrera_id, tutor_id
	HAVING COUNT(id) > 1
)

Esta fue mi solución:

SELECT nombre,fecha_incorporacion
FROM alumnos
WHERE ( EXTRACT(YEAR FROM fecha_incorporacion)=2018 AND EXTRACT(MONTH FROM fecha_incorporacion)=05);

Creo yo, que esto es bueno de p´ractica, pero si ya estamos en un trabajo , o nosotros iniciamos la DB de algún trabajo, creo que la mayoria de cosas tienen un identificador único, entonces esta practica es algo exagerada, pero es bueno que la sepamos, en casos extremos.

Por dar ejemplo una persona tiene su DNI (En México seria tu INE, donde tienes un número único que te identifica como persona), un auto tiene un no. serie, una laptop también, aunque entiendo que aún así puede haber error de dedo, lo prudente seria que si vas entrando hagas un chequeo al Datatype de cada tabla, y saber si ya tienen configurado el Unique en alguna columna, sino, primero verifica duplicados con estos comandos que vimos y luego activa en alguna columna prudente el Unique, así si alguien más ingresa un dato duplicado, ya sea error de dedo o intencional, lo botará porque ya existe uno, haciendo así una revisión más fácil de los demás datos, pues ya tienes un gran filtro de seguridad anti duplicado.

Casi que no!
Reto: ¿Cómo se realiza el borrado del dúplicado de esta tabla con esta ultmina versión del query?

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);

Eliminar filas duplicadas en modo hardcore:

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
);

Mis soluciones del reto

-- Traemos solo los usuarios que se incorporaron en mayo del 2018
SELECT *
    FROM PLATZI.ALUMNOS
    WHERE (DATE_PART("YEAR", FECHA_INCORPORACION)) = 2018
        AND (DATE_PART("MONTH", FECHA_INCORPORACION)) = 5;
-- Otra solución
SELECT *
    FROM PLATZI.ALUMNOS
    WHERE (EXTRACT(YEAR
    FROM FECHA_INCORPORACION) = 2018) AND( EXTRACT (MONTH
    FROM FECHA_INCORPORACION)= 5 );

La forma en que yo lo hice

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

Aqui tengo la respuesta:

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

SELECT *
FROM platzi.alumnos
WHERE(DATE_PART('MONTH', fecha_incorporacion)) = 05;

SELECT *
FROM platzi.alumnos
WHERE(EXTRACT(DAY FROM fecha_incorporacion)) = 18;

que les parece 😁

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 = 2019 and mes_incorporacion = 05;

Select *
from platzi.alumnos
where (extract(YEAR FROM fecha_incorporacion)) = 2018 and
(extract(MONTH FROM fecha_incorporacion)) = 5;

Como opinión personal creo que es una sesión con bastantes elementos para haberla desarrollado en una sola clase, la verdad me resultó bastante complejo entender el código y su funcionalidad predisamente por la cantidad de elementos que trae, lo digo como alguien que apenas está aprendiendo SQL

Mi resolución al reto:

DELETE FROM platzi.alumnos
WHERE platzi.alumnos.id IN (
	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
);

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

select (nombre,apellido,email,colegiatura,fecha_incorporacion,carrera_id,tutor_id)::text as conta, count() from platzi.alumnos
group by conta HAVING COUNT()>1