Deduplicar: quedarse con una fila por clave

Avanzado

Primero se define la clave de negocio, es decir, las columnas que dicen cuándo dos filas representan el mismo hecho real (aquí: mismo oyente, misma canción, mismo instante). Después se numeran las filas de cada clave con row_number() y se conserva la número 1. El ORDER BY de la ventana decide cuál fila gana.

Sintaxis

WITH numeradas AS (
  SELECT *,
    row_number() OVER (
      PARTITION BY columnas_de_la_clave
      ORDER BY criterio_para_elegir, id
    ) AS n
  FROM tabla
)
SELECT * FROM numeradas WHERE n = 1;

Ejemplo

WITH numeradas AS (
  SELECT id, user_id, track_id, played_at,
    row_number() OVER (
      PARTITION BY user_id, track_id, played_at
      ORDER BY id
    ) AS n
  FROM plays
  WHERE user_id IN (1676, 2072)
    AND played_at >= '2025-08-02'
    AND played_at < '2025-08-03'
)
SELECT
  id,
  user_id,
  track_id,
  to_char(played_at, 'HH24:MI:SS') AS hora,
  n,
  CASE WHEN n = 1 THEN 'se conserva' ELSE 'se descarta' END AS decision
FROM numeradas
ORDER BY played_at, id;

Resultado

5 filas · Ritmo

5 filas · Ritmo
id	user_id	track_id	hora	n	decision
93395	1676	4670	10:35:32	1	se conserva
93396	1676	4670	10:35:32	2	se descarta
93506	2072	3205	17:47:01	1	se conserva
93532	2072	1660	19:36:08	1	se conserva
93533	2072	1660	19:36:08	2	se descarta

Cómo leerlo

Son las reproducciones de dos oyentes el 2 de agosto de 2025 (hora UTC). Las filas 93395 y 93396 son la misma reproducción registrada dos veces: comparten oyente, canción e instante y solo cambia el id. La primera de cada clave queda con n = 1 y se conserva; la reproducción única 93506 también tiene n = 1. Filtrar n = 1 deja una fila por reproducción real.

Error común

Así no

SELECT DISTINCT *
FROM plays
WHERE played_at >= '2025-08-02'
  AND played_at < '2025-08-03';

Deduplicar con SELECT DISTINCT *. DISTINCT compara todas las columnas, incluido el id, que es distinto en cada copia, así que no elimina nada. Aunque quitaras el id, dos registros del mismo hecho con un detalle distinto (un dispositivo NULL en una copia, por ejemplo) seguirían contando dos veces. Hay que decidir la clave de negocio y cuál fila gana.

En otros motores: PostgreSQL, Snowflake, BigQuery

PostgreSQL
DISTINCT ON es exclusivo de PostgreSQL: conserva la primera fila de cada clave según el ORDER BY, que debe empezar por las mismas columnas. Es más corto, pero no se puede llevar a otro motor.
```
SELECT DISTINCT ON (user_id, track_id, played_at)
  id, user_id, track_id, played_at
FROM plays
ORDER BY user_id, track_id, played_at, id;
```
SnowflakeBigQuery
Con QUALIFY row_number() OVER (PARTITION BY … ORDER BY …) = 1 se deduplica en una sola consulta, sin CTE.

Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.

WITH numeradas AS ( SELECT id, user_id, track_id, played_at, row_number() OVER ( PARTITION BY user_id, track_id, played_at ORDER BY id ) AS n FROM plays WHERE user_id IN (1676, 2072) AND played_at >= '2025-08-02' AND played_at < '2025-08-03' ) SELECT id, user_id, track_id, to_char(played_at, 'HH24:MI:SS') AS hora, n, CASE WHEN n = 1 THEN 'se conserva' ELSE 'se descarta' END AS decision FROM numeradas ORDER BY played_at, id;

user_id

track_id

hora

decision

93395

1676

4670