创建SplitWords SQL函数遇PARSE_SYNTAX_ERROR语法错误求助

问题描述 #

刚接触CREATE OR REPLACE FUNCTION，编写了拆分文本为单词的SplitWords函数及后续查询，想要统计每日聊天术语的使用频率变化，但运行时触发语法错误：

[PARSE_SYNTAX_ERROR] Syntax error at or near 'LANGUAGE'.line 4,pos 7

原代码如下：

-- Function to split text into words
CREATE OR REPLACE FUNCTION SplitWords(input_text TEXT) RETURNS TABLE (word TEXT) AS $$
    SELECT regexp_split_to_table(input_text, '\s') AS word
$$ LANGUAGE SQL;

-- Query to count term occurrences per conversation
SELECT
    ChatID,
    Disposition,
    word,
    COUNT(*) AS TermCount
FROM
    chat_log_10_14_10_16,
    LATERAL SplitWords(ConversationText) AS words
GROUP BY
    ChatID,
    Disposition,
    word
ORDER BY
    ChatID,
    TermCount DESC;

-- Query to analyze changes in term frequency over multiple days
SELECT
    word,
    ConversationDate,
    COUNT(*) AS TermCount
FROM
    chat_log_10_14_10_16,
    LATERAL SplitWords(ConversationText) AS words
GROUP BY
    word,
    ConversationDate
ORDER BY
    word,
    ConversationDate;

期望得到的每日单词使用次数统计示例：

10/14
The 1000
Buy 500
Want 400
Can 2000
I 50000

10/15
The 3000
Buy 300
Want 100
Can 20000
I 300000

错误修正与函数优化 #

语法错误修复 #

报错原因是部分数据库环境下，SQL表函数不需要显式声明LANGUAGE SQL，直接移除该语句即可解决语法问题。

功能优化 #

原函数用\s拆分仅匹配单个空白符，会产生空单词（比如文本中有连续空格时），建议改为\s+匹配一个或多个空白符，同时过滤空值。

修正后的函数代码：

-- 修正后的拆分单词函数
CREATE OR REPLACE FUNCTION SplitWords(input_text TEXT) RETURNS TABLE (word TEXT) AS $$
    SELECT regexp_split_to_table(input_text, '\s+') AS word
    WHERE regexp_split_to_table(input_text, '\s+') <> '';
$$;

优化后的每日频率统计查询 #

使用修正后的函数，调整查询以匹配你期望的按日期分组、频次排序的结果，同时过滤空单词：

-- 每日聊天术语使用频率统计
SELECT
    TO_CHAR(ConversationDate, 'MM/DD') AS chat_date,
    word,
    COUNT(*) AS term_count
FROM
    chat_log_10_14_10_16,
    LATERAL SplitWords(ConversationText) AS words
WHERE word <> ''
GROUP BY
    chat_date,
    word
ORDER BY
    chat_date,
    term_count DESC;

示例输出 #

运行上述查询后，会得到类似以下的结构化结果（可根据需求调整展示格式）：

10/14
I 50000
Can 2000
The 1000
Buy 500
Want 400

10/15
I 300000
Can 20000
The 3000
Buy 300
Want 100

内容的提问来源于stack exchange，提问作者Audry Rimland