您可以使用筛选聚合：

select count(*) filter (where the_column like '%timeout%') as timeout_count, 
       count(*) filter (where the_column like '%error%') as error_count, 
       count(*) filter (where the_column like '%warning%') as warning_count
from the_table;

这将返回三列中的计数，而不是所示的三行。
如果您确实需要在单独的行中使用它，您可以使用 regexp_replace() 要清除字符串，请按以下方式分组：

select regexp_replace(the_column, '(.*)(warning|error|timeout)(.*)', '\2') as what,
       count(*) 
from the_table
group by what;

请使用下面的查询，没有硬编码的值使用 STRPOS ```
select val, count(1) from
(select substring(column_name ,position(' ' in (column_name))+1,
length(column_name) - position(reverse(' ') in reverse(column_name)) -
position(' ' in (column_name))) as val from matching) qry
group by val; -- Provide the proper column name

演示：
![](https://i.stack.imgur.com/CABlG.png)

如果要在单独的行上执行此操作，也可以使用横向联接：

select which, count(*)
from t cross join lateral
     (values (case when col like '%error%' then 'error' end),
             (case when col like '%warning%' then 'warning' end),
             (case when col like '%timeout%' then 'timeout' end)
     ) v(which)
where which is not null
group by which;

另一方面，如果您只需要第二个单词，但不想硬编码值，则可以使用：

select split_part(col, ' ', 2) as which, count(*)
from t
group by which;

这是一把小提琴。