Views can build on each other. You can easily join views to other views and tables to produce additional views to simplify the tasks of querying and reporting.
As your groupings grow more complex, you will find that views are invaluable to your coding efforts; they simplify the representation of data at different grouping levels within your application. They also make it easier to use the more advanced analytic functions available.
Consider the CATEGORY_COUNT view:
Let’s order the results by their COUNTER column values, with the highest first:
The output shows the ranking of the categories; the ADULTNF category ranks first in terms of the number of books. Without displaying this list, you could determine where a different COUNTER value would be in the rankings. To do this, we’ll use the RANK built-in function. As shown in the following listing, the RANK function takes a value as its input and has additional clauses—the WITHIN GROUP and ORDER BY clauses—that tell Oracle how to do the ranking. Where would a COUNTER value of 3 rank?
A COUNTER value of 3 would be the fifth-highest COUNTER value. How about a COUNTER value of 8?
Adding those five books to the category would move it up to second place. From a percentile perspective, what would the ranking be for that category?
As expected, it would be in the top one-sixth of the categories.
With this technique of using both summary views and analytic functions, you can create views and reports that include weighted average, effective yield, percentage of total, percentage of subtotal, and many similar calculations. There is no effective limit to how many views can be built on top of each other, although even the most complex calculations seldom require more than three or four levels of views built on views. Note that you can also create inline views in the FROM clause.
Leave a Reply