¿Cómo puedo (o puedo) SELECCIONAR DISTINTO en varias columnas?

Necesito recuperar todas las filas de una tabla donde 2 columnas combinadas son todas diferentes. Entonces quiero todas las ventas que no tengan otras ventas que hayan ocurrido el mismo día por el mismo precio. Las ventas que son únicas según el día y el precio se actualizarán a un estado activo.

Entonces estoy pensando:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

Pero me duele el cerebro al ir más allá de eso.

Sep 10 '08 22:09 sheats

SELECT DISTINCT a,b,c FROM t

es aproximadamente equivalente a:

SELECT a,b,c FROM t GROUP BY a,b,c

Es una buena idea acostumbrarse a la sintaxis GROUP BY, ya que es más poderosa.

Para tu consulta lo haría así:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )

Sep 10 '2008 15:09 Joel Coehoorn

Si reúne las respuestas hasta el momento, las limpia y mejora, llegará a esta consulta superior:

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

Que es mucho más rápido que cualquiera de ellos. Aumenta el rendimiento de la respuesta actualmente aceptada en un factor de 10 a 15 (en mis pruebas en PostgreSQL 8.4 y 9.1).

Pero esto todavía está lejos de ser óptimo. Utilice una NOT EXISTS(anti)semiunión para obtener un rendimiento aún mejor. EXISTSes SQL estándar, ha existido desde siempre (al menos desde PostgreSQL 7.2, mucho antes de que se hiciera esta pregunta) y se ajusta perfectamente a los requisitos presentados:

UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT FROM sales s1                     -- SELECT list can be empty for EXISTS
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   )
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

db<>violín aquí
_{Antiguo sqlfiddle}

Clave única para identificar la fila

Si no tiene una clave principal o única para la tabla ( iden el ejemplo), puede sustituirla con la columna del sistema ctidpara esta consulta (pero no para otros fines):

   AND    s1.ctid <> s.ctid

_{Cada tabla debe tener una clave principal. Agregue uno si aún no tenía uno. Sugiero una serialo una IDENTITYcolumna en Postgres 10+.}

Relacionado:

Generación de secuencia en orden.
Columna de tabla de incremento automático

¿Cómo es esto más rápido?

La subconsulta en la EXISTSanti-semi-unión puede dejar de evaluarse tan pronto como se encuentre el primer duplicado (no tiene sentido buscar más). Para una tabla base con pocos duplicados, esto es sólo ligeramente más eficiente. Con muchos duplicados, esto se vuelve mucho más eficiente.

Excluir actualizaciones vacías

Para filas que ya tienenstatus = 'ACTIVE' esta actualización no se cambiará nada, pero aún así se insertará una nueva versión de fila con el costo total (se aplican excepciones menores). Normalmente, no quieres esto. Agregue otra WHEREcondición como la que se muestra arriba para evitar esto y hacerlo aún más rápido:

si statusesta definidoNOT NULL , puedes simplificar a:

AND status <> 'ACTIVE';

El tipo de datos de la columna debe admitir el <>operador. Algunos tipos comojson no. Ver:

¿Cómo consultar una columna json en busca de objetos vacíos?

Sutil diferencia en el manejo de NULL

Esta consulta (a diferencia de la respuesta actualmente aceptada por Joel ) no trata los valores NULL como iguales. Las siguientes dos filas (saleprice, saledate)calificarían como "distintas" (aunque parecen idénticas al ojo humano):

(123, NULL)
(123, NULL)

También pasa en un índice único y en casi cualquier otro lugar, ya que los valores NULL no se comparan iguales según el estándar SQL. Ver:

Crear restricción única con columnas nulas

OTOH GROUP BY, DISTINCTo DISTINCT ON ()trate los valores NULL como iguales. Utilice un estilo de consulta apropiado según lo que desee lograr. Aún puede usar esta consulta más rápida en IS NOT DISTINCT FROMlugar de =para cualquiera o todas las comparaciones para que la comparación NULL sea igual. Más:

Cómo eliminar filas duplicadas sin identificador único

Si todas las columnas que se comparan están definidas NOT NULL, no hay lugar para desacuerdos.

Sep 28 '2012 00:09 Erwin Brandstetter

El problema con su consulta es que cuando usa una cláusula GROUP BY (que esencialmente hace usando distinta) solo puede usar columnas que agrupa o agrega funciones. No puede utilizar la identificación de la columna porque existen valores potencialmente diferentes. En su caso, siempre hay un solo valor debido a la cláusula HAVING, pero la mayoría de los RDBMS no son lo suficientemente inteligentes como para reconocerlo.

Sin embargo, esto debería funcionar (y no necesita unirse):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

También puedes usar MAX o AVG en lugar de MIN, solo es importante usar una función que devuelva el valor de la columna si solo hay una fila coincidente.

Sep 10 '2008 16:09 Christian Berg