2023年2月16日 04:37:32go评论91阅读模式

英文:

Matching array elements from two tables and returning results in a new field with distinct values sharing at least one value using clickhouse

问题

你好，我有两个表，它们共享两个字段，'path'（单个字符串字段）和'arrKW'（字符串数组）。

示例表1：

create table test (path String, arrKW Array(String)) Engine=Memory as
select * from values (('folder/puntonet',['kw1','kw2']),
('folder/puntonet-2.0',['kw2','kw3']),
('folder/puntonet-4',['kw2','kw4']),
('folder/puntonet-5',['kw5','kw4']));

表1内容如下：

| path                  | arrKW       |
+---------------------+---------------+
| folder/puntonet     | ['kw1','kw2'] |
| folder/puntonet-2.0 | ['kw2','kw3'] |
| folder/puntonet-4   | ['kw2','kw4'] |
| folder/puntonet-5   | ['kw5','kw4'] |

示例表2：

create table test2 (path String, arrKW Array(String)) Engine=Memory as
select * from values (('folder/otherpuntonet',['kw1','kw2']),
('folder/otherpuntonet-2.0',['kw2','kw77']),
('folder/otherpuntonet-4',['kw2','kw77']),
('folder/puntonet-5',['kw5','kw4']));

表2内容如下：

| path                    | arrKW            |
+--------------------------+-----------------+
| folder/otherpuntonet     | ['kw1','kw2']   |
| folder/otherpuntonet-2.0 | ['kw2','kw77']  |
| folder/otherpuntonet-4   | ['kw2','kw77']  |
| folder/puntonet-5        | ['kw5','kw4']   |

期望的结果：

| path                 | arrKW         | arrResult (来自表2)                                                                                      |
+----------------------+---------------+---------------------------------------------------------------------------------------------+
| folder/otherpuntonet | ['kw1','kw2'] | ['folder/otherpuntonet-2.0','folder/otherpuntonet-4']                                   |
| folder/puntonet-2.0  | ['kw2','kw3'] | ['folder/otherpuntonet','folder/otherpuntonet-2.0','folder/otherpuntonet-4']            |
| folder/puntonet-4    | ['kw2','kw4'] | ['folder/otherpuntonet','folder/otherpuntonet-2.0','folder/otherpuntonet-4','folder/puntonet-5'] |
| folder/puntonet-5    | ['kw5','kw4'] | ['folder/otherpuntonet-4']                                                                  |

英文:

Hello I've two tables that share two fields, 'path' (single String field) and 'arrKW' (Array(String))

Exaple Table1

create table test (path String, arrKW Array(String))Engine=Memory as
select * from values ((&#39;folder/puntonet&#39;,[&#39;kw1&#39;,&#39;kw2&#39;]),
(&#39;folder/puntonet-2.0&#39;,[&#39;kw2&#39;,&#39;kw3&#39;]),
(&#39;folder/puntonet-4&#39;,[&#39;kw2&#39;,&#39;kw4&#39;]),
(&#39;folder/puntonet-5&#39;,[&#39;kw5&#39;,&#39;kw4&#39;]));
| path                  | arrKW       |
+---------------------+---------------+
| folder/puntonet     | [&#39;kw1&#39;,&#39;kw2&#39;] |
| folder/puntonet-2.0 | [&#39;kw2&#39;,&#39;kw3&#39;] |
| folder/puntonet-4   | [&#39;kw2&#39;,&#39;kw4&#39;] |
| folder/puntonet-5   | [&#39;kw5&#39;,&#39;kw4&#39;] |

Exaple Table2

create table test2 (path String, arrKW Array(String))Engine=Memory as
select * from values ((&#39;folder/otherpuntonet&#39;,[&#39;kw1&#39;,&#39;kw2&#39;]),
(&#39;folder/otherpuntonet-2.0&#39;,[&#39;kw2&#39;,&#39;kw77&#39;]),
(&#39;folder/otherpuntonet-4&#39;,[&#39;kw2&#39;,&#39;kw77&#39;]),
(&#39;folder/puntonet-5&#39;,[&#39;kw5&#39;,&#39;kw4&#39;]))
| path                    | arrKW           |
+--------------------------+----------------+
| folder/otherpuntonet     | [&#39;kw1&#39;,&#39;kw2&#39;]  |
| folder/otherpuntonet-2.0 | [&#39;kw2&#39;,&#39;kw77&#39;] |
| folder/otherpuntonet-4   | [&#39;kw2&#39;,&#39;kw77&#39;] |
| folder/puntonet-5        | [&#39;kw5&#39;,&#39;kw4&#39;]  |

Result desired

| path                 | arrKW         | arrResult (from table2)                                                                                        |
+----------------------+---------------+--------------------------------------------------------------------------------------------------+
| folder/otherpuntonet | [&#39;kw1&#39;,&#39;kw2&#39;] | [&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                                            |
| folder/puntonet-2.0  | [&#39;kw2&#39;,&#39;kw3&#39;] | [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                     |
| folder/puntonet-4    | [&#39;kw2&#39;,&#39;kw4&#39;] | [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;,&#39;folder/puntonet-5&#39;] |
| folder/puntonet-5    | [&#39;kw5&#39;,&#39;kw4&#39;] | [&#39;folder/otherpuntonet-4&#39;]                                                                       |

答案1

得分: 1

以下是翻译好的内容：

原始代码：

task is very unclear
create table test (path String, arrKW Array(String))Engine=Memory as
select * from values (('folder/puntonet',['kw1','kw2']),
('folder/puntonet-2.0',['kw2','kw3']),
('folder/puntonet-4',['kw2','kw4']),
('folder/puntonet-5',['kw5','kw4']));
create table test2 (path String, arrKW Array(String))Engine=Memory as
select * from values (('folder/otherpuntonet',['kw1','kw2']),
('folder/otherpuntonet-2.0',['kw2','kw77']),
('folder/otherpuntonet-4',['kw2','kw77']),
('folder/otherpuntonet-5',['kw5','kw4']))
SELECT
    (arrayJoin(a) AS t).2 AS path,
    t.1 AS KW,
    t.3 as tab,
    arraySort(groupUniqArrayArray(patha)) AS result
FROM
(
    SELECT
        groupArray(pathx) AS patha,
        groupArray(t) AS a,
        arrayJoin(arrKW) AS KW
    FROM (select 'test' tab, null pathx, arrKW, (arrKW, path, tab) t from test union all 
          select 'test2' tab, path pathx, arrKW, (arrKW, path, tab) t from test2)
    GROUP BY KW
)
GROUP BY t
having tab = 'test'
ORDER BY path ASC

改进后的代码：

SELECT
    (arrayJoin(a) AS t).2 AS path,
    t.1 AS KW,
    arraySort(groupUniqArrayArray(patha)) AS result
FROM
(
    SELECT
        groupArray(pathx) AS patha,
        groupArray(t) AS a,
        arrayJoin(arrKW) AS KW
    FROM (select null pathx, arrKW, (arrKW, path) t from test union all 
          select path pathx, arrKW, ([], null) t from test2)
    GROUP BY KW
)
WHERE path is not null
GROUP BY t
ORDER BY path ASC

希望这对你有帮助。

英文:

task is very unclear

create table test (path String, arrKW Array(String))Engine=Memory as
select * from values ((&#39;folder/puntonet&#39;,[&#39;kw1&#39;,&#39;kw2&#39;]),
(&#39;folder/puntonet-2.0&#39;,[&#39;kw2&#39;,&#39;kw3&#39;]),
(&#39;folder/puntonet-4&#39;,[&#39;kw2&#39;,&#39;kw4&#39;]),
(&#39;folder/puntonet-5&#39;,[&#39;kw5&#39;,&#39;kw4&#39;]));
create table test2 (path String, arrKW Array(String))Engine=Memory as
select * from values ((&#39;folder/otherpuntonet&#39;,[&#39;kw1&#39;,&#39;kw2&#39;]),
(&#39;folder/otherpuntonet-2.0&#39;,[&#39;kw2&#39;,&#39;kw77&#39;]),
(&#39;folder/otherpuntonet-4&#39;,[&#39;kw2&#39;,&#39;kw77&#39;]),
(&#39;folder/otherpuntonet-5&#39;,[&#39;kw5&#39;,&#39;kw4&#39;]))
SELECT
    (arrayJoin(a) AS t).2 AS path,
    t.1 AS KW,
    t.3 as tab,
    arraySort(groupUniqArrayArray(patha)) AS result
FROM
(
    SELECT
        groupArray(pathx) AS patha,
        groupArray(t) AS a,
        arrayJoin(arrKW) AS KW
    FROM (select &#39;test&#39; tab, null pathx, arrKW, (arrKW, path, tab) t from test union all 
          select &#39;test2&#39; tab, path pathx, arrKW, (arrKW, path, tab) t from test2)
    GROUP BY KW
)
GROUP BY t
having tab = &#39;test&#39;
ORDER BY path ASC
┌─path────────────────┬─KW────────────┬─tab──┬─result────────────────────────────────────────────────────────────────────────────────────────────────┐
│ folder/puntonet     │ [&#39;kw1&#39;,&#39;kw2&#39;] │ test │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                          │
│ folder/puntonet-2.0 │ [&#39;kw2&#39;,&#39;kw3&#39;] │ test │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                          │
│ folder/puntonet-4   │ [&#39;kw2&#39;,&#39;kw4&#39;] │ test │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;,&#39;folder/otherpuntonet-5&#39;] │
│ folder/puntonet-5   │ [&#39;kw5&#39;,&#39;kw4&#39;] │ test │ [&#39;folder/otherpuntonet-5&#39;]                                                                            │
└─────────────────────┴───────────────┴──────┴───────────────────────────────────────────────────────────────────────────────────────────────────────┘

probably it can be improved

SELECT
    (arrayJoin(a) AS t).2 AS path,
    t.1 AS KW,
    arraySort(groupUniqArrayArray(patha)) AS result
FROM
(
    SELECT
        groupArray(pathx) AS patha,
        groupArray(t) AS a,
        arrayJoin(arrKW) AS KW
    FROM (select null pathx, arrKW, (arrKW, path) t from test union all 
          select path pathx, arrKW, ([], null) t from test2)
    GROUP BY KW
)
WHERE path is not null
GROUP BY t
ORDER BY path ASC
┌─path────────────────┬─KW────────────┬─result────────────────────────────────────────────────────────────────────────────────────────────────┐
│ folder/puntonet     │ [&#39;kw1&#39;,&#39;kw2&#39;] │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                          │
│ folder/puntonet-2.0 │ [&#39;kw2&#39;,&#39;kw3&#39;] │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;]                          │
│ folder/puntonet-4   │ [&#39;kw2&#39;,&#39;kw4&#39;] │ [&#39;folder/otherpuntonet&#39;,&#39;folder/otherpuntonet-2.0&#39;,&#39;folder/otherpuntonet-4&#39;,&#39;folder/otherpuntonet-5&#39;] │
│ folder/puntonet-5   │ [&#39;kw5&#39;,&#39;kw4&#39;] │ [&#39;folder/otherpuntonet-5&#39;]                                                                            │
└─────────────────────┴───────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────┘

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Matching array elements from two tables and returning results in a new field with distinct values sharing at least one value using clickhouse

问题

答案1

返回由PostgreSQL中的集合组成的返回值。

SQL ORACLE – 查找连续的日期

如何正确执行此基于Promise的SQL查询？

如何总结一组行，并根据主键设置布尔值，并在SQL中获得结果

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。