ClickHouse 实现数组交并差计算

计算数组交集

函数：arrayIntersect(arr1,arr2,...) 功能说明：计算 arr1,arr2等数组元素交集。 返回值：交集元素子数组。 SQL 实例：

?

SELECT

    arrayIntersect([1, 2, 3], [4, 5, 6]) AS noIntersect,

    arrayIntersect([1, 2, 3], [2, 3, 4, 5, 6]) AS hasIntersect

FORMAT Vertical

　　输出： noIntersect: []  ；    hasIntersect: [3,2]

计算数组并集

组合使用函数来实现arrayDistinct(arrayConcat(a, b))。 SQL 实例：

?

SELECT

    [1, 2] AS a,

    [2, 3] AS b,

    arrayDistinct(arrayConcat(a, b)) AS res

FORMAT Vertical

　　输出： a: [1,2]  b: [2,3]     res: [1,2,3]

计算数组差集

差集的实现要有一些技术含量了（感觉 ClickHouse 后面应该内置数组差集计算函数，实现类似arrayExcept() 函数），需要使用数组交集函数arrayIntersect() 结合高阶函数 arrayMap()和 arrayFilter()来组合实现。 SQL 实例：

?

SELECT

    arrayIntersect([1, 2, 3], [4, 5, 6]) AS noIntersect,

    arrayIntersect([1, 2, 3], [2, 3, 4, 5, 6]) AS hasIntersect

FORMAT Vertical

SELECT

    [1, 2] AS a,

    [2, 3] AS b,

    arrayFilter(x -> (x IS NOT NULL), arrayMap(x -> multiIf(x NOT IN arrayIntersect(a, b), x, NULL), a)) AS res

FORMAT Vertical

　　输出： a: [1,2] b: [2,3]  res: [1]

 

另外可以使用 交（INTERSECT）、并（UNION）、差（EXCEPT）的SQL子句关键字 实现

另外， ClickHouse 中有集合交（INTERSECT）、并（UNION）、差（EXCEPT）的SQL子句关键字，可以实现数组的交并差运算。实例 SQL 如下。

交集SQL：

?

SELECT a.i

FROM

(

    SELECT arrayJoin([1, 2]) AS i

) AS a

INTERSECT

SELECT b.i

FROM

(

    SELECT arrayJoin([2, 3]) AS i

) AS b

　　输出：2

并集 SQL：

?

SET union_default_mode = 'ALL';

SELECT DISTINCT t.i

FROM

(

    SELECT a.i

    FROM

    (

        SELECT arrayJoin([1, 2]) AS i

    ) AS a

    UNION

    SELECT b.i

    FROM

    (

        SELECT arrayJoin([2, 3]) AS i

    ) AS b

) AS t

　　输出： 1 2 3

差集 SQL：

?

SELECT a.i

FROM

(

    SELECT arrayJoin([1, 2]) AS i

) AS a

EXCEPT

SELECT b.i

FROM

(

    SELECT arrayJoin([2, 3]) AS i

) AS b

　　输出：1       注：上述[1,2]和[2,3]分别表示要求差集的数组

数据函数解释：

arrayIntersect表示多个数组求交集

arrayConcat表示连接多个数组为一个数组

arrayDistinct表示将多个数组元素去重组合为一个数组

arrayMap表示使用lambda函数转换

arrayFilter表示使用lambda函数过滤

参考：https://clickhouse.tech/docs/zh/sql-reference/functions/higher-order-functions/

https://cloud.tencent.com/developer/article/1961960