EXCEPT 절 - ClickHouse Documentation

EXCEPT 절은 두 번째 쿼리의 결과를 제외하고 첫 번째 쿼리의 결과에만 포함된 행을 반환합니다.

두 쿼리는 컬럼 수, 컬럼 순서, 데이터 타입이 서로 같아야 합니다.
EXCEPT의 결과에는 중복 행이 포함될 수 있습니다. 이를 원하지 않으면 EXCEPT DISTINCT를 사용하십시오.
괄호를 지정하지 않으면 여러 EXCEPT SQL 문은 왼쪽에서 오른쪽 순서로 실행됩니다.
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 논리만 다를 뿐 비슷한 방식으로 사용할 수 있는 경우가 많으며, 공통 컬럼(또는 여러 컬럼)을 가진 두 테이블이 있을 때 모두 유용합니다. 예를 들어, 거래 가격과 거래량이 포함된 과거 암호화폐 데이터 수백만 행이 있다고 가정해 보겠습니다:

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라는 테이블(table)이 있다고 가정해 보겠습니다:

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

이는 우리가 보유한 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` 사용하기