如果我在這樣的表中一行的列中有一個字元串

1 2 2 2 2 2 2

我將如何計算字元串中子字元串的出現次數2。假設除了" ".

為此，讓我們將數字視為子字元串

樣本數據

CREATE TABLE foo
 AS
   SELECT 1 AS id, '1 2 2 2 2 2 2'::text AS data;

TABLE foo
id |     data      
----+---------------
 1 | 1 2 2 2 2 2 2

你可以解決這個問題

1. FASTEST是我在此列表中最後放置的 pl/perl 方法，因為它需要 pl/perl，並且對於大多數工作負載可能不需要。
2. FAST一個字元串函式，例如下面解釋的模式之一

length(str) - length(*replace(str, replaceStr))
 / length(replaceStr)

3. 從字元串轉換為數組的東西。
4. SLOW從字元串轉換為表格的東西。

---

可能的解決方案

細繩

使用length和regexp_replace

大多數 RDBMS 提供了一些方法來計算這樣的子字元串出現，

SELECT length(data) - length(replace(data, '2', ''))
 / length('2')
FROM foo;

這個方法在這裡不適用，因為沒有錨，我們不能確定我們是否替換了某物空間分隔的子字元串。例如，上面替換了2in 329。我們可以通過使用regexp_replace來錨定子字元串來解決這個問題。

SELECT length(data) - length(regexp_replace(data, '\m2\M', '', 'g'))
 / length('2')
FROM foo;

因為雖然我們可以更複雜地分割簡單的空格（’’），所以我們可能還想容納不同長度的子字元串，就像在這個問題中一樣。這就是我們明確包含/ length('2'). 這簡化為無操作，但如果我們要搜尋的內容超過一個字元，則它是必需的。

SELECT length(data) - length(regexp_replace(data, '\m42\M', '', 'g'))
 / length('42')
FROM foo;

使用數組$$ $$

分裂成一個ARRAY[]

在這裡，我們必須減去一個匹配，將一個字元串拆分為兩個片段，因此出現次數比片段計數少一：這個xyx拆分在y, 產生{'x', 'x'}並且我們希望長度1對應於 的出現y。

SELECT array_length(x, 1) - 1
FROM foo
CROSS JOIN LATERAL regexp_split_to_array(data, '\m2\M') AS t(x);
-- un-anchored version for reference.
-- CROSS JOIN LATERAL string_to_array(data, '2') AS t(x);

或者，我們可以string_to_array用來分隔以空格分隔的內容，然後計算匹配項，

SELECT id, array_length(array_positions(x, '2'), 1)
FROM foo
CROSS JOIN LATERAL string_to_array(data, ' ') AS t(x);

使用表

拆分成一個表regexp_split_to_table

在這裡，我們將正則表達式拆分為一個表。在這種方法中，您使用的是GROUP BYand count()。

SELECT id, x
FROM foo
CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
 AS t(x);

id | x 
----+---
 1 | 1
 1 | 2
 1 | 2
 1 | 2
 1 | 2
 1 | 2
 1 | 2
(7 rows)

而且，您可以從那裡執行正常 SQL。

SELECT id, x, count(*)
FROM (
   SELECT id, x
   FROM foo
   CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
     AS t(x)
) AS t(id,x)
GROUP BY id, x;

id | x | count 
----+---+-------
 1 | 1 |     1
 1 | 2 |     6

使用regex_matches

在這裡，我們擺脫了拆分，而是使用\m, 和\M錨點作為單詞邊界。

SELECT count(*)
FROM foo
CROSS JOIN LATERAL regexp_matches(data, '\m2\M', 'g');

程序語言

Perl

事實證明，這種方法總體上是最快的，

CREATE LANGUAGE plperl

CREATE FUNCTION count_occurances(inputStr text, regex text)
RETURNS smallint
AS $BODY$
 scalar @{[ $_[0] =~ m/$_[1]/g ]} 
$BODY$
LANGUAGE plperl
IMMUTABLE;

總結和性能影響

遵循相同格式的數據，可以通過以下方式獲得性能影響

CREATE TABLE foo
AS
 SELECT
   1 AS id,
   array_to_string(
     ARRAY(SELECT trunc(random()*100+1)::int % 100 FROM generate_series(1,5000) AS t(x)),
     ' '
   ) AS data
;

在這些限制下，我發現使用 plperl 的程序方法是最快的。接下來我發現以下是最快的本機方法，

length(str) - regexp_replace(str, replacement, g)
 / length(replacement)

請記住，除了需要錨定字元串之外，經過驗證的字元串替換方法仍然是最快和最有效的本地方法，儘管它可能很笨重，

length(str) - replace(str, replacement)
 / length(replacement)

也就是說，該ARRAY[]方法比拆分到表格要快得多。

引用自：https://dba.stackexchange.com/questions/166762