blocks|key|2301089|text|您可以通过以下两种方法之一完成此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2301090|2301091|使用array_distinct函数从结果数组中删除重复项：|ordered-list-item|offset|length|style|CODE|2301092|2301093|WITH+mytable(id,+message)+AS+(VALUES
++(1,+'foo'),+(1,+'foo'),+(1,+'bar'),+(1,+'bar'),+(1,+'baz'),+(1,+'baz'),
++(2,+'foo'),+(2,+'bar'),+(2,+'bar'),+(2,+'bar'),+(2,+'baz'),
++(3,+'foo'),+(3,+'foo'),+(3,+'foo'),+(3,+'bar'),+(3,+'bar'),+(3,+'baz')
)
SELECT+array_distinct(array_agg(message))+AS+sequence
FROM+mytable
GROUP+BY+id|code-block|syntax|javascript|2301094|2301095|在聚合中使用DISTINCT限定符在将重复值传递到array_agg之前将其删除。|2301096|2301097|WITH+mytable(id,+message)+AS+(VALUES
++(1,+'foo'),+(1,+'foo'),+(1,+'bar'),+(1,+'bar'),+(1,+'baz'),+(1,+'baz'),
++(2,+'foo'),+(2,+'bar'),+(2,+'bar'),+(2,+'bar'),+(2,+'baz'),+(3,+'foo'),
++(3,+'foo'),+(3,+'foo'),+(3,+'bar'),+(3,+'bar'),+(3,+'baz')
)
SELECT+array_agg(DISTINCT+message)+AS+sequence
FROM+mytable
GROUP+BY+id|2301098|这两种替代方法产生相同的结果：|2301099|++++sequence
-----------------
+[foo,+bar,+baz]
+[foo,+bar,+baz]
+[foo,+bar,+baz]
(3+rows)|2301100|更新：您可以使用最近引入的MATCH_RECOGNIZE功能删除重复的元素序列：|BOLD|2301101|WITH+mytable(id,+message)+AS+(VALUES
++(1,+'foo'),+(1,+'foo'),+(1,+'bar'),+(1,+'baz'),+(1,+'bar'),+(1,+'baz'),
++(2,+'foo'),+(2,+'bar'),+(2,+'bar'),+(2,+'bar'),+(2,+'baz'),
++(3,+'foo'),+(3,+'foo'),+(3,+'foo'),+(3,+'bar'),+(3,+'bar'),+(3,+'baz')
)
SELECT+array_agg(value)+AS+sequence
FROM+mytable
+MATCH_RECOGNIZE(
++++PARTITION+BY+id
++++MEASURES+A.message+AS+value
++++PATTERN+(A+B*)
++++DEFINE+B+AS+message+=+PREV(message)
)
GROUP+BY+id|2301102|entityMap|0|LINK|mutability|MUTABLE|url|https://trino.io/docs/current/functions/array.html#array_distinct|1|https://trino.io/docs/current/sql/match-recognize.html^0|0|0|2|E|2|E|0|0|0|0|0|6|8|0|0|0|0|0|0|2|D|F|D|F|1|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1G|8|@$F|1H|G|1I|H|I]]|9|@$F|1J|G|1K|1|1L]]|A|$]]|$1|J|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1N|8|@]|9|@]|A|$N|O]]|$1|P|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|Q|3|R|5|E|7|1P|8|@$F|1Q|G|1R|H|I]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|T|3|U|5|M|7|1T|8|@]|9|@]|A|$N|O]]|$1|V|3|W|5|6|7|1U|8|@]|9|@]|A|$]]|$1|X|3|Y|5|M|7|1V|8|@]|9|@]|A|$N|O]]|$1|Z|3|10|5|6|7|1W|8|@$F|1X|G|1Y|H|11]|$F|1Z|G|20|H|I]]|9|@$F|21|G|22|1|23]]|A|$]]|$1|12|3|13|5|M|7|24|8|@]|9|@]|A|$N|O]]|$1|14|3|-4|5|6|7|25|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B]]|1C|$5|17|18|19|A|$1A|1D]]]]

You can do this in one of two ways:
<ol>
<li>Remove duplicates from the resulting arrays using the <a href="https://trino.io/docs/current/functions/array.html#array_distinct" rel="nofollow noreferrer"><code>array_distinct</code></a> function:</li>
</ol>
<pre class="lang-sql prettyprint-override"><code>WITH mytable(id, message) AS (VALUES
 (1, 'foo'), (1, 'foo'), (1, 'bar'), (1, 'bar'), (1, 'baz'), (1, 'baz'),
 (2, 'foo'), (2, 'bar'), (2, 'bar'), (2, 'bar'), (2, 'baz'),
 (3, 'foo'), (3, 'foo'), (3, 'foo'), (3, 'bar'), (3, 'bar'), (3, 'baz')
)
SELECT array_distinct(array_agg(message)) AS sequence
FROM mytable
GROUP BY id
</code></pre>
<ol start="2">
<li>Use the <code>DISTINCT</code> qualifier in the aggregation to remove the duplicate values before they are passed into array_agg.</li>
</ol>
<pre class="lang-sql prettyprint-override"><code>WITH mytable(id, message) AS (VALUES
 (1, 'foo'), (1, 'foo'), (1, 'bar'), (1, 'bar'), (1, 'baz'), (1, 'baz'),
 (2, 'foo'), (2, 'bar'), (2, 'bar'), (2, 'bar'), (2, 'baz'), (3, 'foo'),
 (3, 'foo'), (3, 'foo'), (3, 'bar'), (3, 'bar'), (3, 'baz')
)
SELECT array_agg(DISTINCT message) AS sequence
FROM mytable
GROUP BY id
</code></pre>
Both alternatives produce the same result:
<pre><code> sequence
-----------------
 [foo, bar, baz]
 [foo, bar, baz]
 [foo, bar, baz]
(3 rows)
</code></pre>
UPDATE: You can remove repeated sequences of elements with the recently introduced <a href="https://trino.io/docs/current/sql/match-recognize.html" rel="nofollow noreferrer"><code>MATCH_RECOGNIZE</code></a> feature:
<pre class="lang-sql prettyprint-override"><code>WITH mytable(id, message) AS (VALUES
 (1, 'foo'), (1, 'foo'), (1, 'bar'), (1, 'baz'), (1, 'bar'), (1, 'baz'),
 (2, 'foo'), (2, 'bar'), (2, 'bar'), (2, 'bar'), (2, 'baz'),
 (3, 'foo'), (3, 'foo'), (3, 'foo'), (3, 'bar'), (3, 'bar'), (3, 'baz')
)
SELECT array_agg(value) AS sequence
FROM mytable
 MATCH_RECOGNIZE(
 PARTITION BY id
 MEASURES A.message AS value
 PATTERN (A B*)
 DEFINE B AS message = PREV(message)
)
GROUP BY id
</code></pre>

I have a query that produces strings of arrays using they <code>array_agg()</code> function

<pre><code>SELECT 
array_agg(message) as sequence
from mytable
group by id
</code></pre>

which produces a table that looks like this:

<pre><code> sequence
1 foo foo bar baz bar baz
2 foo bar bar bar baz
3 foo foo foo bar bar baz
</code></pre>

but I aim to condense the array of strings so that none can repeat more than once in a row, for example, the desired output would look like this:

<pre><code> sequence
1 foo bar baz bar baz
2 foo bar baz
3 foo bar baz
</code></pre>

How would one go about doing this with Presto SQL ?

Condensing arrays in Presto

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个使用array_agg()函数生成数组字符串的查询SELECT array_agg(message) as sequencefrom mytablegroup by id这将生成一个如下所示的表：                 sequence1 foo foo bar baz bar baz2     fo...

在Presto中压缩数组-腾讯云开发者社区-腾讯云

问在Presto中压缩数组
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Presto中压缩数组EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Presto中压缩数组
EN