SQL计数正则表达式匹配（PostgreSQL） [英] SQL count regex matches (PostgreSQL)

问题描述

我想从表中计算正则表达式实例。例如：

I would like to count the regex instances from a table. For example:

    message                    state
    ================================
    [foo] aaaa                 active
    [bar] aaaa                 idle
    [foo] bbbb                 idle
    [foo] cccc                 active
    [bar] dddd                 active
    [tar] eeee                 idle

我想要的是以下内容： p>

What I would like to have is following:

    messageType               ocurrences
    ====================================
    [foo]                             3
    [bar]                             2
    [tar]                             1

有什么办法吗？
任何帮助将非常感谢！

Is there any way to do that? Any help would be greatly appreciated!

推荐答案

如果你只是想计算消息中的第一个 ，然后使用 substring_index（）：

If you just want to count the first "word" in the message, then use substring_index():

select substring_index(message, ' ', 1) as messageType, count(*)
from table t
group by substring_index(message, ' ', 1)
order by count(*) desc;

编辑：

在Postgres中通过寻找第一个空格：

You can do this in Postgres by looking for the first space:

select left(message, position(' ' in message) as messageType, count(*)
from table t
group by messageType
order by count(*) desc;

这篇关于SQL计数正则表达式匹配（PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！