标签
PostgreSQL , Oracle , RATIO_TO_REPORT , 分析函数
背景
Oracle的分析函数RATIO_TO_REPORT()是用于计算当前值在分组内的占比的
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.
PostgreSQL也支持窗口查询，但是没有提供这个分析函数，不过我们知道它是干什么的，当然就知道如何写SQL来实现同样的目的了。
Oracle 例子
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME SALARY RR
------------------------- ---------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
PostgreSQL 例子
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 ║
╚═════╩══════╩═════════════════════╝
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 ║
╚═════╩══════╩═══════════╩═════════════════╝
参考
http://stackoverflow.com/questions/35976390/postgres-ratio-to-report-function
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions124.htm
相关资源：python入门教程(PDF版)
