使用RETURN QUERY
语句:
CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
RETURNS TABLE (txt text
, cnt bigint
, ratio bigint)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt
, count(*) AS cnt
, (count(*) * 100) / _max_tokens
FROM (
SELECT t.txt
FROM token t
WHERE t.chartype = 'ALPHABETIC'
LIMIT _max_tokens
) t
GROUP BY t.txt
ORDER BY cnt DESC;
END
$func$;
呼叫:
SELECT * FROM word_frequency(123);
明确定义返回类型比返回通用的“记录(record)”更实用。这样,您就不必在每个函数调用中提供列定义列表。使用
RETURNS TABLE
是一种方法。还有其他方法可以实现此目的。
OUT
参数的数据类型必须与查询返回的完全匹配。
谨慎选择
OUT
参数的名称。它们在函数体中几乎可以任何地方看到。对相同名称的列进行表格限定,以避免冲突或意外结果。在我的示例中,我为所有列都做了这样的处理。
但请注意,
OUT
参数
cnt
和相同名称的列别名之间可能存在
名称冲突(naming conflict)。在这种特定情况下(
RETURN QUERY SELECT ...
),Postgres无论如何都会使用列别名覆盖
OUT
参数。不过,在其他情况下,这可能会产生歧义。有各种方法可以避免任何混淆:
- 使用SELECT列表中项的序数位置:
ORDER BY 2 DESC
。例如:
- 重复表达式
ORDER BY count(*)
。
- (在此处不需要.)设置配置参数
plpgsql.variable_conflict
或在函数中使用特殊命令#variable_conflict error|use_variable|use_column
。请参见:
不要使用“text”或“count”作为列名称。Postgres都允许使用这两个名称,但是“count”是标准SQL的一个
保留字(reserved word)和一个基本函数名称,“text”则是一种基本数据类型。这可能会导致混淆错误。在我的示例中,我使用了
txt
和
cnt
,您可以使用更明确的名称。
在标题中添加了一个缺少的分号并更正了语法错误。
(_max_tokens int)
而不是
(int maxTokens)
- 名称后面的数据类型。
在进行整数除法时,最好先乘后除,以尽量减小舍入误差。或者使用
numeric
或浮点类型。请参见下文。
备选方案
这是我认为你的查询实际上应该像这样(计算每个标记的相对份额):
CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
RETURNS TABLE (txt text
, abs_cnt bigint
, relative_share numeric)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt, t.cnt
, round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2)
FROM (
SELECT t.txt, count(*) AS cnt
FROM token t
WHERE t.chartype = 'ALPHABETIC'
GROUP BY t.txt
ORDER BY cnt DESC
LIMIT _max_tokens
) t
ORDER BY t.cnt DESC;
END
$func$;
表达式
sum(t.cnt) OVER ()
是一个
窗口函数。在简单的情况下(主要是在Postgres 12之前),子查询通常比公共表达式更便宜,尽管公共表达式更加美观。
当使用
OUT
参数或
RETURNS TABLE
(隐式使用
OUT
参数)时,
没有必要使用最终的
RETURN
语句(但是允许使用)。
round()
带有两个参数的
round()函数仅适用于
numeric
类型。子查询中的
count()
产生一个
bigint
结果,而对这个
bigint
执行
sum()
会生成一个
numeric
结果,因此我们自动处理了一个
numeric
数字,一切顺理成章。
LANGUAGE SQL
。 - jpmc26