Оператор EXCEPT - ClickHouse Documentation

Оператор EXCEPT возвращает только те строки, которые присутствуют в результате первого запроса и отсутствуют в результате второго.

Оба запроса должны иметь одинаковое количество столбцов в одном и том же порядке и с одинаковыми типами данных.
Результат EXCEPT может содержать повторяющиеся строки. Используйте EXCEPT DISTINCT, если это нежелательно.
Несколько операторов EXCEPT выполняются слева направо, если скобки не указаны.
Оператор EXCEPT имеет тот же приоритет, что и оператор UNION, и более низкий приоритет, чем оператор INTERSECT.

Синтаксис

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

EXCEPT

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

Условием может быть любое выражение — в зависимости от ваших требований. Кроме того, EXCEPT() можно использовать, чтобы исключить столбцы из результата для той же таблицы, как и в BigQuery (Google Cloud), используя следующий синтаксис:

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

Примеры

Примеры в этом разделе демонстрируют использование оператора EXCEPT.

Фильтрация чисел с помощью оператора `EXCEPT`

Вот простой пример, который возвращает числа от 1 до 10, не входящие в диапазон от 3 до 8:

Query

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

Response

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

Исключение определённых столбцов с помощью `EXCEPT()`

EXCEPT() можно использовать, чтобы быстро исключить столбцы из результата. Например, если нужно выбрать все столбцы из таблицы, кроме нескольких отдельных столбцов, как показано в примере ниже:

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

Использование `EXCEPT` и `INTERSECT` с данными о криптовалютах

EXCEPT и INTERSECT во многих случаях можно использовать взаимозаменяемо, применяя разную булеву логику; оба оператора полезны, если у вас есть две таблицы с общим столбцом (или столбцами). Например, предположим, что у нас есть несколько миллионов строк исторических данных о криптовалютах, содержащих цены сделок и объём:

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

Теперь предположим, что у нас есть таблица holdings, в которой перечислены принадлежащие нам криптовалюты и количество монет:

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

Мы можем использовать EXCEPT, чтобы ответить на такой вопрос: “Какие монеты, которыми мы владеем, ни разу не торговались ниже $10?”:

Query

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

Response

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

Это означает, что из четырёх криптовалют, которыми мы владеем, только Bitcoin ни разу не опускался ниже $10 (судя по ограниченным данным, приведённым в этом примере).

Использование `EXCEPT DISTINCT`

Обратите внимание: в результате предыдущего запроса было несколько позиций Bitcoin. Вы можете добавить DISTINCT к EXCEPT, чтобы удалить из результата повторяющиеся строки:

Query

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

Response

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

См. также

​Синтаксис

​Примеры

​Фильтрация чисел с помощью оператора EXCEPT

​Исключение определённых столбцов с помощью EXCEPT()

​Использование EXCEPT и INTERSECT с данными о криптовалютах

​Использование EXCEPT DISTINCT

Синтаксис

Примеры

Фильтрация чисел с помощью оператора `EXCEPT`

Исключение определённых столбцов с помощью `EXCEPT()`

Использование `EXCEPT` и `INTERSECT` с данными о криптовалютах

Использование `EXCEPT DISTINCT`