EXCEPT 句 - ClickHouse Documentation

EXCEPT 句は、2 番目のクエリの結果を除いた、1 番目のクエリの結果の行だけを返します。

両方のクエリは、同じ順序で、同じ数のカラムと同じデータ型を持っている必要があります。
EXCEPT の結果には、重複する行が含まれる場合があります。これが望ましくない場合は、EXCEPT DISTINCT を使用してください。
括弧が指定されていない場合、複数の EXCEPT ステートメントは左から右に実行されます。
EXCEPT 演算子の優先順位は UNION 句と同じで、INTERSECT 句より低くなります。

構文

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

EXCEPT

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

条件には、要件に応じて任意の式を指定できます。さらに、BigQuery (Google Cloud) と同様に、次の構文を使用して、EXCEPT() で同じテーブルの結果からカラムを除外できます。

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 は、Boolean 論理は異なるものの、しばしば同じような場面で使えます。どちらも、共通のカラムを 1 つ以上持つ 2 つのテーブルがある場合に有用です。たとえば、売買価格と出来高を含む、数百万行規模の過去の仮想通貨データがあるとします。

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

**「保有しているコインのうち、これまで一度も $10 未満で取引されたことがないものはどれか？」**のような問いには、EXCEPT を使用して答えられます。

Query

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

Response

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

つまり、この例の限られたデータを見るかぎり、保有している4種類の暗号通貨のうち、Bitcoin だけは一度も10ドルを下回っていません。

`EXCEPT DISTINCT` の使用

前のクエリでは、結果に Bitcoin の保有データが複数含まれていました。EXCEPT に DISTINCT を追加すると、結果から重複した行を除去できます。

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` の使用