SQL计算正则表达式匹配数量（PostgreSQL）

Question

我希望从表格中计算正则表达式实例的数量。例如：

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

我想要的结果如下：

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

有没有办法做到这一点？任何帮助都将不胜感激！

- Ramzendo

---

什么是“正则表达式实例”？ - Gordon Linoff

好的，我指的是更多的正则表达式“匹配”。谢谢！ - Ramzendo

这个怎么样？（http://community.sitepoint.com/t/counting-regexp-matches-with-mysql/4499） - SomeJavaGuy

SELECT COUNT(*) FROM table1 WHERE messageType ~ '[[]foo[]]'; - Wiktor Stribiżew

Answer 1

如果你只想计算消息中的第一个“单词”，那么请使用substring_index()函数：

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

编辑：

在Postgres中，您可以通过查找第一个空格来实现此操作：

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

- Gordon Linoff

---

谢谢您的回复！我认为这是一个好的提示，但我忘了提到我的数据库是PostgreSQL，substring_index不受支持。 - Ramzendo

Answer 2

与上面的响应类似，但涉及Postgres版本：

select regexp_matches(message, '\[.+\]') as messageType, count (*)
from table1
group by regexp_matches(message, '\[.+\]')
order by count (*) desc;

- Ramzendo