PostgreSQL - string_agg 元素数量有限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27405150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - string_agg with limited number of elements
提问by nickbusted
Is it possible to limit the number of elements in the following string_aggfunction?
是否可以限制以下string_agg函数中的元素数量？
string_agg(distinct(tag),', ')
采纳答案by Gordon Linoff
I am not aware that you can limit it in the string_agg()function. You can limit it in other ways:
我不知道你可以在string_agg()函数中限制它。您可以通过其他方式限制它：
select postid, string_agg(distinct(tag), ', ')
from table t
group by postid
Then you can do:
然后你可以这样做：
select postid, string_agg(distinct (case when seqnum <= 10 then tag end), ', ')
from (select t.*, dense_rank() over (partition by postid order by tag) as seqnum
from table t
) t
group by postid
回答by Erwin Brandstetter
To limit the number of elements in the following string_agg(), use LIMITin a subquery:
要limit the number of elements in the following string_agg()，使用LIMIT子查询：
SELECT string_agg(tag, ', ') AS tags
FROM (
SELECT DISTINCT tag
FROM tbl
-- ORDER BY tag -- optionally order to get deterministic result
LIMIT 123 -- add your limit here
) sub;Note that the subquery is not a problem for performance at all. On the contrary, this is typically faster, even if you don't impose a maximum number with LIMIT, because the groupwise DISTINCTin the aggregate function is more expensive than doing it in a subquery for all rows at once.
请注意，子查询根本不是性能问题。相反，这通常更快，即使您不使用 强加最大数量LIMIT，因为DISTINCT在聚合函数中分组比在子查询中同时对所有行执行分组更昂贵。
Or, to get the "100 most common tags":
或者，要获得“ 100 个最常见的标签”：
SELECT string_agg(tag, ', ') AS tags
FROM (
SELECT tag
FROM tbl
GROUP BY tag
ORDER BY count(*) DESC
LIMIT 100
) sub;回答by alexkovelsky
There are two more ways.
还有两种方法。
1) make an array from rows, limit it, and then concatenate into string:
1）从行创建一个数组，限制它，然后连接成字符串：
SELECT array_to_string((array_agg(DISTINCT tag))[1:3], ', ') FROM tbl
("array[1:3]" means: take items from 1 to 3 from array)
("array[1:3]" 表示：从数组中取出 1 到 3 的项)
2) concatenate rows into string without limit, then use "substring" to trim it:
2) 无限制地将行连接成字符串，然后使用“子字符串”对其进行修剪：
string_agg(distinct(tag),',')
If you know that your "tag" field cannot contain ,character then you can select all text before nth occurence of your ,
如果您知道您的“标签”字段不能包含,字符，那么您可以在第 n 次出现之前选择所有文本,
SELECT substring(
string_agg(DISTINCT tag, ',')
from '(?:[^,]+,){1,3}')
FROM tbl
This substring will select 3 or less strings divided by ,
此子字符串将选择 3 个或更少的字符串除以 ,
回答by user2220029
Use IN to filter
使用 IN 过滤
Like This:
像这样：
Select
primaryID,
String_Agg(email, '|') As Email
From
contacts
Where
contactID In (Select filter.contactID
From contacts filter
Where filter.primaryID = contacts.primaryID
Order By filter.contactID)
Group By
primaryID;
