时间戳

首页 > 解决方案 > 从 reg_ex_split_table 输出中删除空白结果

sql - 从 reg_ex_split_table 输出中删除空白结果

问题描述

我有一个查询，它计算一列中的所有单词，并给我单词的频率和频率等级作为结果。出于某种原因，我不断得到一个没有字的行。我该如何摆脱它？

桌子：

CREATE TABLE dummy (
created_at TIMESTAMPTZ,
tweet TEXT);

插入：

INSERT INTO dummy VALUES ('2020-12-18 00:00:00+00', 'foo squared');
INSERT INTO dummy VALUES ('2020-12-18 00:00:00+00', 'foo foo');
INSERT INTO dummy VALUES ('2020-12-18 00:00:00+00', 'foo foo');
INSERT INTO dummy VALUES ('2020-12-18 00:00:00+00', 'foo bar');

询问：

select *
from (
    select date_trunc('day', created_at) as created_day, word, count(*) as cnt,
        rank() over(partition by date_trunc('day', created_at) order by count(*) desc) rn
    from dummy d
    cross join lateral regexp_split_to_table(
        regexp_replace(tweet, '\y(rt|co|https|bar|none)\y', '', 'g'),
        '\s+'
    ) w(word)
    group by created_day, word
) d
where created_day = CURRENT_DATE and word IS NOT NULL
order by rn
LIMIT 10;

回报：

      created_day       |  word   | cnt | rn
------------------------+---------+-----+----
 2020-12-18 00:00:00+00 | foo     |   4 |  1
 2020-12-18 00:00:00+00 |         |   2 |  2
 2020-12-18 00:00:00+00 | arm     |   1 |  3
 2020-12-18 00:00:00+00 | squared |   1 |  3

我想摆脱空白词：

      created_day       |  word   | cnt | rn
------------------------+---------+-----+----
 2020-12-18 00:00:00+00 | foo     |   4 |  1
 2020-12-18 00:00:00+00 | arm     |   1 |  2
 2020-12-18 00:00:00+00 | squared |   1 |  3

标签： sqlpostgresqlcountgreatest-n-per-grouplateral-join

解决方案

问题出在内部regexp_replace()；当匹配部分位于字符串的末尾时，您最终会在字符串的末尾有一个尾随空格。基本上，当应用于 时'foo bar'，它会生成'foo '.

然后在解析时，会生成一个最终单词，其值为空字符串 ( '')。

一个简单的解决方法是trim()输出regexp_replace()，所以基本上替换：

cross join lateral regexp_split_to_table(
    regexp_replace(tweet, '\y(rt|co|https|bar|none)\y', '', 'g'),
    '\s+'
) w(word)

和：

cross join lateral regexp_split_to_table(
    trim(regexp_replace(tweet, '\y(rt|co|https|bar|none)\y', '', 'g')),
    '\s+'
) w(word)

DB Fiddle 上的演示

推荐阅读