介绍几种ClickHouse的集合运算

实际应用中通常需要进行集合运算，如用户画像场景中，给同时复合两个条件集合的用户打标签。本文介绍intersect子句实现交集查询，另外还对比其他方法的实现逻辑。ClickHouse除了提供交集，还有并集和差集功能，实现逻辑一致，了解一个其他也都能理解并应用。

INTERSECT 子句

INTERSECT 子句实现计算两个查询的交集，但需要两个查询语句的列数量、类型和顺序一致，返回结果仅包括两个查询中重复的记录。

如果没有只从括号，多个INTERSECT 语句按照从左到右顺序执行。INTERSECT 优先级比union和except子句高。语法如下：

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

INTERSECT

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

注意：条件子句可以根据实际需求指定，每个子句可以不同。

举例

下面示例，第一个查询返回1_{10，第二个返回3}8，返回共同重复的记录：

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

-- 返回结果
┌─number─┐
│      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 │ 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，包含我们拥有加密货币的记录，以及币的数量：

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

现在通过intersect子句回答问题：我们拥有的哪个币交易价格大于100$?

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

-- 返回结果
┌─crypto_name─┐
│ Bitcoin     │
│ Bitcoin     │
│ Ethereum    │
│ Ethereum    │
└─────────────┘

我们注意到有重复记录，可以通过使用 INTERSECT DISTINCT 对结果去重：

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

-- 返回
┌─crypto_name─┐
│ Bitcoin     │
│ Ethereum    │
└─────────────┘

另外ClickHouse也支持 并集 和 差集：

• UNION
• EXCEPT

对应in子查询的SQL:

SELECT DISTINCT crypto_name FROM holdings
where crypto_name in (
SELECT  crypto_name FROM crypto_prices
WHERE price > 100)
;

SIPHASH64

假设有两个表包括大量相同数据（多列数据相同），每张表都有几百万数据，如果需要逐行比较，可以使用sipHash64(*)哈希函数。与MD5相比，至少快好几倍，它转换每个输入参数作为字符串并计算它们的哈希值，然后采用下面过程合并哈希值：

• 获取哈希数组（每个参数的哈希值）
• 基于数组（包括上面数组中的第一和第二个值）生成哈希值
• 基于上一步中的哈希值和第一步中初始数组的第三个元素生成新的哈希值
• 重复上面的步骤，依此获取初始数组中余下的元素

下面通过示例进行说明。第一个表car:

SELECT * FROM cars

┌─year─┬─brand───┬─gearbox─┬─km_info─┐
│ 2008 │ Audi    │ Auto    │  130000 │
│ 2016 │ VW      │ Manual  │   85000 │
│ 2019 │ BMW     │ Auto    │   60000 │
│ 2020 │ Peugeot │ Auto    │   30000 │
└──────┴─────────┴─────────┴─────────┘

第二个表vehicle:

SELECT * FROM vehicle

┌─year─┬─type──┬─brand_name─┬─route─┬─duration─┬─fuel_type─┬─gear─┬─breakdown─┐
│ 2008 │ car   │ Audi       │ West  │ 1 Week   │ Gas       │ Auto │ Yes       │
│ 2010 │ car   │ Mercedes   │ South │ 30 Day   │ Gas       │ Auto │ No        │
│ 2019 │ car   │ BMW        │ South │ 1 Week   │ Diesel    │ Auto │ No        │
│ 2019 │ plane │ Airbus     │ North │ 1 Day    │ Avgas     │ Auto │ No        │
│ 2021 │ plane │ Airbus     │ East  │ 2 Day    │ Avgas     │ Auto │ No        │
└──────┴───────┴────────────┴───────┴──────────┴───────────┴──────┴───────────┘

现在需要查询两个表有一些共同列值相同的记录，但最后返回cars表的所有列信息，下面基于sipHash64函数实现：

SELECT * FROM cars
WHERE sipHash64(year, brand, gearbox) NOT IN (
    SELECT sipHash64(year, brand_name, gear)
    FROM vehicle)

┌─year─┬─brand───┬─gearbox─┬─km_info─┐
│ 2016 │ VW      │ Manual  │   85000 │
│ 2020 │ Peugeot │ Auto    │   30000 │
└──────┴─────────┴─────────┴─────────┘

当然也可以返回vehicle表所有字段：

SELECT * FROM vehicle
WHERE sipHash64(year, brand_name, gear) NOT IN (
    SELECT sipHash64(year, brand, gearbox)
    FROM cars)

┌─year─┬─type──┬─brand_name─┬─route─┬─duration─┬─fuel_type─┬─gear─┬─breakdown─┐
│ 2010 │ car   │ Mercedes   │ South │ 30 Day   │ Gas       │ Auto │ No        │
│ 2019 │ plane │ Airbus     │ North │ 1 Day    │ Avgas     │ Auto │ No        │
│ 2021 │ plane │ Airbus     │ East  │ 2 Day    │ Avgas     │ Auto │ No        │
└──────┴───────┴────────────┴───────┴──────────┴───────────┴──────┴───────────┘

我们发现，对多个字段相同的场景计算记录差集，使用sipHash更灵活。当然也可以去掉IN 前面的NOT实现交集查询。