clause EXCEPT - ClickHouse Documentation

La clause EXCEPT renvoie uniquement les lignes renvoyées par la première requête et pas par la seconde.

Les deux requêtes doivent avoir le même nombre de colonnes, dans le même ordre et avec le même type de données.
Le résultat de EXCEPT peut contenir des lignes en double. Utilisez EXCEPT DISTINCT si ce n’est pas souhaité.
Plusieurs instructions EXCEPT sont exécutées de gauche à droite si aucune parenthèse n’est spécifiée.
L’opérateur EXCEPT a la même priorité que la clause UNION et une priorité inférieure à celle de la clause INTERSECT.

Syntaxe

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

EXCEPT

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

La condition peut être n’importe quelle expression, selon vos besoins. De plus, EXCEPT() peut être utilisé pour exclure des colonnes du résultat dans la même table, comme avec BigQuery (Google Cloud), en utilisant la syntaxe suivante :

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

Exemples

Les exemples de cette section illustrent l’utilisation de la clause EXCEPT.

Filtrer des nombres à l’aide de la clause `EXCEPT`

Voici un exemple simple qui renvoie les nombres de 1 à 10 qui ne figurent pas parmi les nombres de 3 à 8 :

Query

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

Response

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

Exclure des colonnes spécifiques avec `EXCEPT()`

EXCEPT() permet d’exclure rapidement des colonnes d’un résultat. Par exemple, si nous voulons sélectionner toutes les colonnes d’une table, à l’exception de quelques colonnes spécifiques, comme dans l’exemple ci-dessous :

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 │
   └─────────────────────────┴────────────┴─────────┴──────┴──────┴─────────┴─────────────┴────────────┘

Utilisation de `EXCEPT` et `INTERSECT` avec des données de cryptomonnaies

EXCEPT et INTERSECT peuvent souvent être utilisés de manière interchangeable, selon la logique booléenne employée, et tous deux sont utiles si vous avez deux tables qui partagent une colonne commune (ou plusieurs). Par exemple, supposons que nous ayons quelques millions de lignes de données historiques sur les cryptomonnaies contenant les prix des transactions et les volumes :

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 │
└────────────┴─────────────┴─────────────┴──────────┴──────────────┴───────────────┘

Supposons maintenant que nous ayons une table appelée holdings qui contient la liste des cryptomonnaies que nous possédons, ainsi que le nombre d’unités détenues :

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

Nous pouvons utiliser EXCEPT pour répondre à une question comme “Parmi les cryptomonnaies que nous possédons, lesquelles ne sont jamais passées sous les 10 $ ?” :

Query

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

Response

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

Cela signifie que, sur les quatre cryptomonnaies que nous possédons, seul le Bitcoin n’est jamais passé sous la barre des 10 $ (d’après les données limitées dont nous disposons dans cet exemple).

Utilisation de `EXCEPT DISTINCT`

Notez que, dans la requête précédente, le résultat contenait plusieurs avoirs en Bitcoin. Vous pouvez ajouter DISTINCT à EXCEPT pour supprimer les lignes en double du résultat :

Query

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

Response

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

Voir aussi

​Syntaxe

​Exemples

​Filtrer des nombres à l’aide de la clause EXCEPT

​Exclure des colonnes spécifiques avec EXCEPT()

​Utilisation de EXCEPT et INTERSECT avec des données de cryptomonnaies

​Utilisation de EXCEPT DISTINCT

Syntaxe

Exemples

Filtrer des nombres à l’aide de la clause `EXCEPT`

Exclure des colonnes spécifiques avec `EXCEPT()`

Utilisation de `EXCEPT` et `INTERSECT` avec des données de cryptomonnaies

Utilisation de `EXCEPT DISTINCT`