PostgreSQL中的条件分组依据（分组相似项） [英] Conditional group by (group similar items) in PostgreSQL

问题描述

我有以下查询：

SELECT s.id,
       s.name,
       s.prom,
       s.some_other_field,
       ... many more fields also
FROM mytable s
INNER JOIN (on some other tables etc.)

如果有6条以上具有相同 name （s.name）名称的记录，我想将这些项目分组为一行（我不在乎将哪些其他数据分组到该行中，即该组的第一条记录就可以了。）

In the case where there are more than 6 records with the same name (s.name), I want to group these items together as one row (I don't care which other data is grouped into that row, i.e. the first record of that group would be fine).

在Postgres中有可能吗？从某种意义上说，我想这是一个有条件的分组，我想对同一名称出现超过6次的记录进行分组，同时将所有其他记录都照常返回。

Is this possible in Postgres? I guess it is a conditional group by in a sense that I want to group the records where the same name appears more than 6 times, whilst returning all the other records as normal.

任何帮助都非常感谢-谢谢！

Any help is much appreciated - thanks!

推荐答案

您可以执行以下操作：

select * from (

    SELECT s.id,
           s.name,
           s.prom,
           s.some_other_field,
           ... many more fields also,
           row_number() over (partition by s.name order by s.id) as rnk,
           count(*) over (partition by s.name) cnt
    FROM mytable s
    INNER JOIN (on some other tables etc.)

) a
where cnt < 6 or (cnt >=6 and rnk = 1)

这篇关于PostgreSQL中的条件分组依据（分组相似项）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！