Cláusula EXCEPT - ClickHouse Documentation

La cláusula EXCEPT devuelve solo aquellas filas que resultan de la primera consulta y no de la segunda.

Ambas consultas deben tener el mismo número de columnas, en el mismo orden y con el mismo tipo de dato.
El resultado de EXCEPT puede contener filas duplicadas. Use EXCEPT DISTINCT si esto no es deseable.
Varias cláusulas EXCEPT se ejecutan de izquierda a derecha si no se especifican paréntesis.
El operador EXCEPT tiene la misma prioridad que la cláusula UNION y menor prioridad que la cláusula INTERSECT.

Sintaxis

SELECT column1 [, column2 ]
FROM table1
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table2
[WHERE condition]

La condición puede ser cualquier expresión, según sus necesidades. Además, EXCEPT() puede usarse para excluir columnas del resultado en la misma tabla, como es posible en BigQuery (Google Cloud), con la siguiente sintaxis:

SELECT column1 [, column2 ] EXCEPT (column3 [, column4]) 
FROM table1 
[WHERE condition]

Ejemplos

Los ejemplos de esta sección muestran cómo usar la cláusula EXCEPT.

Filtrar números con la cláusula `EXCEPT`

Aquí tienes un ejemplo sencillo que devuelve los números del 1 al 10 que no pertenecen al conjunto de números del 3 al 8:

Query

SELECT number
FROM numbers(1, 10)
EXCEPT
SELECT number
FROM numbers(3, 6)

Response

┌─number─┐
│      1 │
│      2 │
│      9 │
│     10 │
└────────┘

Excluir columnas específicas con `EXCEPT()`

EXCEPT() puede usarse para excluir rápidamente columnas de un resultado. Por ejemplo, si queremos seleccionar todas las columnas de una tabla excepto algunas columnas específicas, como se muestra en el ejemplo siguiente:

Query

SHOW COLUMNS IN system.settings

SELECT * EXCEPT (default, alias_for, readonly, description)
FROM system.settings
LIMIT 5

Response

    ┌─field───────┬─type─────────────────────────────────────────────────────────────────────┬─null─┬─key─┬─default─┬─extra─┐
│ alias_for   │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ changed     │ UInt8                                                                    │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ default     │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ description │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ is_obsolete │ UInt8                                                                    │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ max         │ Nullable(String)                                                         │ YES  │     │ ᴺᵁᴸᴸ    │       │
│ min         │ Nullable(String)                                                         │ YES  │     │ ᴺᵁᴸᴸ    │       │
│ name        │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ readonly    │ UInt8                                                                    │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ tier        │ Enum8('Production' = 0, 'Obsolete' = 4, 'Experimental' = 8, 'Beta' = 12) │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ type        │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
│ value       │ String                                                                   │ NO   │     │ ᴺᵁᴸᴸ    │       │
    └─────────────┴──────────────────────────────────────────────────────────────────────────┴──────┴─────┴─────────┴───────┘

   ┌─name────────────────────┬─value──────┬─changed─┬─min──┬─max──┬─type────┬─is_obsolete─┬─tier───────┐
│ dialect                 │ clickhouse │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Dialect │           0 │ Production │
│ min_compress_block_size │ 65536      │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64  │           0 │ Production │
│ max_compress_block_size │ 1048576    │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64  │           0 │ Production │
│ max_block_size          │ 65409      │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64  │           0 │ Production │
│ max_insert_block_size   │ 1048449    │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64  │           0 │ Production │
   └─────────────────────────┴────────────┴─────────┴──────┴──────┴─────────┴─────────────┴────────────┘

Uso de `EXCEPT` e `INTERSECT` con datos de criptomonedas

EXCEPT e INTERSECT a menudo pueden usarse de forma indistinta con distinta lógica booleana, y ambos son útiles si tiene dos tablas que comparten una columna (o varias). Por ejemplo, supongamos que tenemos unos millones de filas de datos históricos de criptomonedas que contienen precios de transacción y volumen:

Query

CREATE TABLE crypto_prices
(
    trade_date Date,
    crypto_name String,
    volume Float32,
    price Float32,
    market_cap Float32,
    change_1_day Float32
)
ENGINE = MergeTree
PRIMARY KEY (crypto_name, trade_date);

INSERT INTO crypto_prices
   SELECT *
   FROM s3(
    'https://learn-clickhouse.s3.us-east-2.amazonaws.com/crypto_prices.csv',
    'CSVWithNames'
);

SELECT * FROM crypto_prices
WHERE crypto_name = 'Bitcoin'
ORDER BY trade_date DESC
LIMIT 10;

Response

┌─trade_date─┬─crypto_name─┬──────volume─┬────price─┬───market_cap─┬──change_1_day─┐
│ 2020-11-02 │ Bitcoin     │ 30771456000 │ 13550.49 │ 251119860000 │  -0.013585099 │
│ 2020-11-01 │ Bitcoin     │ 24453857000 │ 13737.11 │ 254569760000 │ -0.0031840964 │
│ 2020-10-31 │ Bitcoin     │ 30306464000 │ 13780.99 │ 255372070000 │   0.017308505 │
│ 2020-10-30 │ Bitcoin     │ 30581486000 │ 13546.52 │ 251018150000 │   0.008084608 │
│ 2020-10-29 │ Bitcoin     │ 56499500000 │ 13437.88 │ 248995320000 │   0.012552661 │
│ 2020-10-28 │ Bitcoin     │ 35867320000 │ 13271.29 │ 245899820000 │   -0.02804481 │
│ 2020-10-27 │ Bitcoin     │ 33749879000 │ 13654.22 │ 252985950000 │    0.04427984 │
│ 2020-10-26 │ Bitcoin     │ 29461459000 │ 13075.25 │ 242251000000 │  0.0033826586 │
│ 2020-10-25 │ Bitcoin     │ 24406921000 │ 13031.17 │ 241425220000 │ -0.0058658565 │
│ 2020-10-24 │ Bitcoin     │ 24542319000 │ 13108.06 │ 242839880000 │   0.013650347 │
└────────────┴─────────────┴─────────────┴──────────┴──────────────┴───────────────┘

Ahora supongamos que tenemos una tabla llamada holdings que contiene una lista de las criptomonedas que poseemos, junto con la cantidad de monedas:

Query

CREATE TABLE holdings
(
    crypto_name String,
    quantity UInt64
)
ENGINE = MergeTree
PRIMARY KEY (crypto_name);

INSERT INTO holdings VALUES
   ('Bitcoin', 1000),
   ('Bitcoin', 200),
   ('Ethereum', 250),
   ('Ethereum', 5000),
   ('DOGEFI', 10),
   ('Bitcoin Diamond', 5000);

Podemos usar EXCEPT para responder a una pregunta como “¿Qué monedas que tenemos nunca han cotizado por debajo de $10?”:

Query

SELECT crypto_name FROM holdings
EXCEPT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;

Response

┌─crypto_name─┐
│ Bitcoin     │
│ Bitcoin     │
└─────────────┘

Esto significa que, de las cuatro criptomonedas que tenemos, solo Bitcoin nunca ha bajado de $10 (según los datos limitados que tenemos aquí en este ejemplo).

Uso de `EXCEPT DISTINCT`

Ten en cuenta que en la consulta anterior había varias tenencias de Bitcoin en el resultado. Puedes añadir DISTINCT a EXCEPT para eliminar las filas duplicadas del resultado:

Query

SELECT crypto_name FROM holdings
EXCEPT DISTINCT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;

Response

┌─crypto_name─┐
│ Bitcoin     │
└─────────────┘

Vea también

​Sintaxis

​Ejemplos

​Filtrar números con la cláusula EXCEPT

​Excluir columnas específicas con EXCEPT()

​Uso de EXCEPT e INTERSECT con datos de criptomonedas

​Uso de EXCEPT DISTINCT

Sintaxis

Ejemplos

Filtrar números con la cláusula `EXCEPT`

Excluir columnas específicas con `EXCEPT()`

Uso de `EXCEPT` e `INTERSECT` con datos de criptomonedas

Uso de `EXCEPT DISTINCT`