ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Automatically Calculating Percentages in Queries[akadia]

Automatically Calculating Percentages in Queries[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-03-30 14:30:05 0 删除 编辑

Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task.

Example: Show percentage of salaries for each department

Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.

column percentage format 99.9

select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;

DEPTNO SUM(SAL) PERCENTAGE
---------- ---------- ----------
10 8750 30.1
20 10875 37.5
30 9400 32.4

With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:

column percentage format 99.9

select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;

DEPTNO SUM(SAL) PERCENTAGE
---------- ---------- ----------
10 8750 30.1
20 10875 37.5
30 9400 32.4

The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60574/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    107
  • 访问量
    78187