GROUP BY in STRING_AGG

Question

我有一个postgresql查询，用于在按优先级分组的数据库中查找单词。它运行良好，看起来像这样：

select 
min(w.text) as text,
min(w.id) as w_id,
count(distinct w.text),
min(pwt.priority) as priority
from 
word as w
join topic_word tw ON tw.word_id = w.id
join profile_word_topic pwt on pwt.topic_id = tw.topic_id and pwt."level" = tw."level" 
group by w.text 
order by min(pwt.priority), min(w.id)

结果看起来像这样，并且单词数量正确(10(：

text|w_id|count|priority|-----+----+-----+--------+四|4|1|1|五|5|1|1|六|6|1|1|七|7|1|1|八|8|1|1|九|9|1|1|10|1|1|一个|1|1|2|两个|2|1|2|THREE|3|1|2|

现在我想把单词连接成一个字符串，如下所示：

select 
string_agg(distinct w.text, ','),
count(distinct w.text),
min(pwt.priority) as priority
from 
word as w
join topic_word tw ON tw.word_id = w.id
join profile_word_topic pwt on pwt.topic_id = tw.topic_id and pwt."level" = tw."level" 
group by pwt.priority 
order by min(pwt.priority), min(w.id)

但现在有些单词被复制了，单词总数是12:

字符串_标记|计数|优先级|----------------------------------+-----+--------+八、五、四、九、七、六、十|7|1|五、四、一、三、二|5|2|

我该如何去掉重复的单词？我该如何安排订单？

如果你能告诉我如何在SQLAlchemy中实现这一点，那将是加分项，但这是次要的，因为我认为移植这一点应该是直接的。

感谢您提前输入：-(

Answer 1

我现在可以处理这样的子查询了：

select 
string_agg(text, ','),
count(text),
min(priority)
from (
select 
min(w.text) as text,
min(w.id) as w_id,
count(distinct w.text),
min(pwt.priority) as priority
from 
word as w
join topic_word tw ON tw.word_id = w.id
join profile_word_topic pwt on pwt.topic_id = tw.topic_id and pwt."level" = tw."level" 
group by w.text 
order by min(pwt.priority), min(w.id)
) sub
group by priority

现在我有了正确的结果(正确的字数和排序是正确的(：

字符串_标记|计数|分钟|----------------------------------+-----+---+四、五、六、七、八、九、十|7|1|一、二、三|3|2|

所以理论上我的问题已经得到了回答。但我想知道这是否可以在没有子查询的情况下实现。