PostgreSQL Oracle 兼容性之 - RATIO_TO_REPORT 分析函数

标签

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