INTERSECT 子句

INTERSECT 子句仅返回同时出现在第一个查询和第二个查询结果中的行。两个查询的列数量、顺序和类型必须一致。INTERSECT 的结果中可以包含重复行。

如果未使用圆括号，多个 INTERSECT 子句按照从左到右的顺序执行。INTERSECT 运算符的优先级高于 UNION 和 EXCEPT 子句。

SELECT 列1 [, 列2 ]
FROM 表1
[WHERE 条件]

INTERSECT

SELECT 列1 [, 列2 ]
FROM 表2
[WHERE 条件]

该条件可以根据您的需求设置为任意表达式。

示例

下面是一个简单的示例，用来求 1 到 10 与 3 到 8 这两个数字区间的交集：

SELECT number FROM numbers(1,10) INTERSECT SELECT number FROM numbers(3,8);

结果：

┌─数字─┐
│    3 │
│    4 │
│    5 │
│    6 │
│    7 │
│    8 │
└──────┘

当你有两个表，并且它们共享一个或多个相同的列时，INTERSECT 非常有用。只要两个查询的结果集包含相同的列，你就可以对它们的结果求交集。比如，假设我们有几百万行历史加密货币数据，其中包含交易价格和交易量：

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;

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

现在假设我们有一张名为 holdings 的表，列出了我们持有的各类加密货币及其对应的持币数量：

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

INSERT INTO holdings VALUES
   ('比特币', 1000),
   ('比特币', 200),
   ('以太坊', 250),
   ('以太坊', 5000),
   ('DOGEFI', 10);
   ('比特币钻石', 5000);

我们可以使用 INTERSECT 来回答诸如 "我们持有哪些币种的成交价格曾高于 100 美元？" 这样的问题：

SELECT crypto_name FROM holdings
INTERSECT
SELECT crypto_name FROM crypto_prices
WHERE price > 100

结果：

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

这意味着在某个时点，Bitcoin 和 Ethereum 的价格曾经高于 $100，而 DOGEFI 和 Bitcoin Diamond 从未高于$ 100（至少在本示例所用的数据中是如此）。

INTERSECT DISTINCT

请注意，在上一个查询中，我们有多笔比特币和以太坊持仓的成交价格都高于 100 美元。去掉这些重复行（因为它们只是重复我们已经知道的内容）可能会更好一些。你可以在 INTERSECT 后添加 DISTINCT 关键字，以消除结果中的重复行：

SELECT crypto_name FROM holdings
INTERSECT DISTINCT
SELECT crypto_name FROM crypto_prices
WHERE price > 100;

结果：

┌─加密货币名称─┐
│ Bitcoin     │
│ Ethereum    │
└─────────────┘

另请参阅

UNION
EXCEPT

示例​

INTERSECT DISTINCT​

示例

INTERSECT DISTINCT