问题描述
有人可以指示我如何在Postgres数据库中安装分析功能,尤其是 ratio_to_report 功能吗?
Can someone indicate me how to install analytic functions and especially the ratio_to_report function in a Postgres database ?
我尝试在其中搜索postgres提供了模块,但没有看到包含该功能的模块。
I have tried to search in the postgres supplied modules but I didn't see the module that contains the function.
推荐答案
RATIO_TO_REPORT是一个分析函数。 它计算一个值与一组值之和的比率。如果expr评估为空,则报告比率也将评估为空。
RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.
完全需要导入特定功能。使用窗口 SUM
的Postgresql等效项:
You don't need importing specific function at all. Postgresql equivalent using windowed SUM
:
SELECT ID, val, 1.0 * val / NULLIF(SUM(val) OVER(),0) AS ratio_to_report
FROM tab
输出:
╔═════╦══════╦═════════════════════╗
║ id ║ val ║ ratio_to_report ║
╠═════╬══════╬═════════════════════╣
║ 1 ║ 10 ║ 0.16666666666666666 ║
║ 2 ║ 10 ║ 0.16666666666666666 ║
║ 3 ║ 20 ║ 0.3333333333333333 ║
║ 4 ║ 20 ║ 0.3333333333333333 ║
╚═════╩══════╩═════════════════════╝
要模拟 PARTITION BY
,您可以使用:
SELECT ID, val, category,
1.0 * val / NULLIF(SUM(val) OVER(PARTITION BY category),0) AS ratio_to_report
FROM tab
输出:
╔═════╦══════╦═══════════╦═════════════════╗
║ id ║ val ║ category ║ ratio_to_report ║
╠═════╬══════╬═══════════╬═════════════════╣
║ 1 ║ 10 ║ a ║ 0.25 ║
║ 2 ║ 10 ║ a ║ 0.25 ║
║ 3 ║ 20 ║ a ║ 0.5 ║
║ 4 ║ 20 ║ b ║ 1 ║
╚═════╩══════╩═══════════╩═════════════════╝
这篇关于Postgres ratio_to_report函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!