2 回答
TA贡献1829条经验 获得超7个赞
用途RETURN QUERY:
CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
RETURNS TABLE (txt text -- also visible as OUT parameter inside function
, cnt bigint
, ratio bigint) AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt
, count(*) AS cnt -- column alias only visible inside
, (count(*) * 100) / _max_tokens -- I added brackets
FROM (
SELECT t.txt
FROM token t
WHERE t.chartype = 'ALPHABETIC'
LIMIT _max_tokens
) t
GROUP BY t.txt
ORDER BY cnt DESC; -- potential ambiguity
END
$func$ LANGUAGE plpgsql;
呼叫:
SELECT * FROM word_frequency(123);
说明:
这是很多更实用的明确定义的返回类型不是简单地声明为记录。这样,您不必在每次函数调用时都提供列定义列表。RETURNS TABLE是做到这一点的一种方法。还有其他 OUT参数的数据类型必须与查询返回的内容完全匹配。
OUT仔细选择参数名称。它们在功能主体中几乎任何位置都可见。对具有相同名称的列进行表限定,以避免冲突或意外结果。我在示例中对所有列进行了此操作。
但是请注意,参数和同名列别名之间可能存在命名冲突。在这种特殊情况下(),Postgres会以两种方式在参数上使用列别名。但是,在其他情况下,这可能是模棱两可的。有多种避免混淆的方法:OUTcntRETURN QUERY SELECT ...OUT
使用项目在SELECT列表中的顺序位置:ORDER BY 2 DESC。例:
在每个GROUP BY组中选择第一行?
重复表达式ORDER BY count(*)。
(此处不适用。)设置配置参数plpgsql.variable_conflict或#variable_conflict error | use_variable | use_column在功能中使用特殊命令。看到:
使用USING子句命名函数参数和JOIN结果之间的冲突
请勿使用“文本”或“计数”作为列名。两者在Postgres中都是合法使用的,但是“ count”是标准SQL中的保留字,并且是基本函数名称,而“ 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) AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt, t.cnt
, round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2) -- AS relative_share
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$ LANGUAGE plpgsql;
该表达式sum(t.cnt) OVER ()是一个窗口函数。您可以使用CTE代替子查询-漂亮,但是在这种简单情况下,子查询通常更便宜。
最后明确RETURN声明不要求有工作的时候(但允许)OUT参数或RETURNS TABLE(使隐式使用的OUT参数)。
round()具有两个参数的参数仅适用于numeric类型。count()子查询产生bigint的结果和sum()在此bigint产生numeric的结果,因此,我们对付numeric自动编号和一切都只是属于地方。
- 2 回答
- 0 关注
- 1837 浏览
添加回答
举报