主页 / dba / 问题 / 239958
Accepted
Vérace
Asked: 2019-06-07 04:07:40 +0800 CST

字段中的字数（全部和唯一） - 有没有更优雅/最佳的方式？

  • 772

回答这个问题，

鉴于此表（根据问题构建）：

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

产生这个结果：

     id  | word_count | unique_word_count |  Description                        
---------+------------+-------------------+---------------
     1   |  4         | 4                 | What a great day
     2   |  7         | 6                 | This is a product. It is useful

我给出了（正确的）答案，你可以在这里找到。

然而，在评论中，OP 然后又问了一个问题 - 如果有问题的字符串是 ['a', ' ', ' ', 'b']并且我的解决方案完全崩溃了怎么办 - 对于初学者来说，字符串甚至不会INSERT进入表格。

所以，现在的问题是，如何处理这样的字符串 - 即撇号、方括号和 c。我将给出自己的答案，并为更优雅的解决方案提供奖金。

具有多种选择的解决方案将受到高度评价，那些显示出“跳出框框思考”证据的解决方案也将受到高度重视（对于陈词滥调感到抱歉 - 但它适合这里！:-)）。我还将详细解释我的推理 - 这也将获得荣誉！提及其他服务器的选项也将获得优势。显然，我只能将奖金奖励给一个人，但我会赞成所有体面的答案。

我只能在两天内提供奖金 - 所以我会发布我的答案并在允许时提供奖金（+100）。此外，任何处理我自己无法处理的字符串的解决方案 - 还没有经过详尽的测试。

postgresql query-performance

2 个回答

  1. 第一步显然是创建表和数据（根据提到的问题）：

    CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ($$What a great day$$);
INSERT INTO wordcount (description) VALUES ($$This is a product. It is useful$$);
INSERT INTO wordcount (description) VALUES ($$['a', ' ', ' ', 'b']$$);

    第一个“救命稻草”是美元报价 ( $$) - 一个非常简洁的 PostgreSQL 功能。在我遇到 这个之前我真的很挣扎- 甚至无法将数据放入表中（尝试反斜杠，双引号等。）

    我最终的 SQL 看起来像这样（在这里小提琴）：

    WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT id,
       COUNT(word),
       COUNT(DISTINCT(word)),
       description
FROM cte1
WHERE LENGTH(word) > 0
GROUP BY id, description
ORDER BY id;

    结果：

    id  Word_count  Distinct_count  description
 1           4               4    What a great day
 2           7               6    This is a product. It is useful
 3           2               2    ['a', ' ', ' ', 'b']


    逻辑解释：

    我决定不打扰大写 - 即“It”和“it”在这种情况下是不同的词 - 如果这是一个问题，简单添加一个UPPER()函数就可以解决这个问题 - 这不是问题的核心。

    步骤1：

    SELECT id, REGEXP_SPLIT_TO_TABLE(description, ',') FROM wordcount;
-- Keeping the id field helps clarity, even if superfluous.

    结果：

    id  regexp_split_to_table
1   What a great day
2   This is a product. It is useful
3   ['a'
3    ' '
3    ' '
3    'b']

    第 2 步（删除所有非空格、非 alpha）

    SELECT id, REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(description, ','), '[^a-zA-Z\s]', '', 'g')
FROM wordcount;

-- Remove all non-alpha, non-spaces. Otherwise the words "product" and "product." would
-- be counted as different! Again, keeping the id field makes things clearer, 
-- even if not strictly necessary for purists

    结果：

    id  regexp_replace
1   What a great day
2   This is a product It is useful
3   a
3     
3     
3    b

    第 3 步（将字符串放入数组中）：

    SELECT id, STRING_TO_ARRAY(REGEXP_REPLACE(
          REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')
FROM wordcount;  
--              id again - not strictly necessary at this step.

    结果：

    id  string_to_array
1   {What,a,great,day}
2   {This,is,a,product,It,is,useful}
3   {a}
3   {"","",""}
3   {"","",""}
3   {"",b}

    最后是答案本身——UNNEST然后选择那些LENGTH > 0按 id 和 description 分组的词。

    即SELECT来自以下cte（公用表表达式）的必要项-cte 不是绝对必要的-我本可以在UNNEST...整个最终查询中使用它，但这对于阅读和调试来说会很糟糕。这就是发明通用表表达式的原因！

    WITH cte1 AS
(
  SELECT id, 
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT blah... (see above)
    • 4
  2. Best Answer

    至于您的解决方案：聪明且有可靠的解释。但是这些情况呢：'', NULL, '"§$%', '-'? 没有言语。计数应该是0- 但您的解决方案完全删除了这些行。

    此外，任何解决方案首先取决于 "word" 的确切定义，这可能会有很大的不同......

    基于正则表达式的字符串处理

    与您的解决方案类似，但有一些替代建议：

    SELECT id
     , COALESCE(cardinality(arr), 0) AS word_count
     , unique_word_count
     , description
FROM  (
   SELECT *
        , string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ') AS arr
   FROM   wordcount
   ) a
LEFT   JOIN LATERAL (
   SELECT count(DISTINCT elem) AS unique_word_count
   FROM   unnest(arr) elem
   ) b ON true;

    db<>fiddle here（扩展测试用例）

    核心是regexp_replace(description, '\W+', ' ', 'g')将非单词字符的所有子字符串替换为单个空格。请参阅正则表达式类速记转义。这消除了游戏早期的所有噪音。

    随后以便宜trim()的方式删除前导/尾随空格，string_to_array()并将准备好的字符串转换为数组。

    word_count直接从数组中获取。再次：便宜。

    unique_word_count来自带有 的子LATERAL查询count(DISTINCT ...)。该部分可能会或可能不会比总的未嵌套/聚合慢。这有点简单。

    COALESCE外部SELECT处理输入（原始NULL问题未提及NOT NULL约束）。可选，以防您需要0而不是NULL.

    或者（在使用短字符串的快速测试中更快）：

    SELECT id
     , count(*) AS word_count
     , count(DISTINCT elem) AS unique_word_count
     , description
FROM  (
   SELECT id, description
        , unnest(string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ')) AS elem
   FROM   wordcount
   ) sub
GROUP  BY id, description;

    这会像您的答案一样删除0 个单词的行。

    (Ab-)使用文本搜索解析器

    使用文本搜索功能ts_parse()更简单。可能会或可能不会更快。但首先研究文本搜索解析器识别的各种标记，看看哪些符合您对“单词”的定义：

    SELECT * FROM ts_token_type('default')

    仅适用于“ASCII Words”：（与上面不同，下划线 ( _) 在这里不被视为单词字符）：

    SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', w.description) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;

    为了_避免分隔单词，replace()请先使用 simple：

    SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', replace(w.description, '_', 'x')) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;

    同样，要保留所有行：

    SELECT w.id
     , count(token) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w
LEFT   JOIN LATERAL (
   SELECT t.token
   FROM   ts_parse('default', w.description) t
   WHERE  t.tokid = 1 -- 'asciiword'
   ) t ON true
GROUP  BY w.id;

    db<>在这里摆弄

    有关的：

    • 2

相关问题