Oracle database management system is one of the most popular representatives of such software. It attracts many users due to its wide functionality and significant capabilities. And this is not surprising, because how much time it is already being developed! So what is interesting about Oracle? The analytical functions of this DBMS are an important component of the opportunities provided. And we will consider them in the framework of this article.
general information
Initially, it should be noted that in Oracle analytic functions have specific capabilities, but use a common syntax. To understand them, you need to understand how the data:
- Section.
- Sort.
- Set windows.
The book of Tom Kite was taken as theoretical support. The material from it will be presented briefly, and only the most important points will be considered. If you need details, you can advise you to contact the source. But believe me, all the necessary information will be provided as part of the article.
About features
How do they look? In Oracle, analytic functions are built on this principle: Name (argument, ...) OVER (fragmentation, ordering, window). How to understand that this is exactly what you need? Thanks to the word OVER. It allows you to identify the input function as analytical. Everything that comes after OVER is a description of the data slice, by which it will be calculated. And now a few general words about classification. Four types are conditionally distinguished (depending on the capabilities provided):
- Ranking functions. They allow you to build queries "first N".
- Window functions. Needed to calculate a variety of aggregates (group operations).
- Summary functions. Similar to item 2, but can work with all lines of a group or section. The key difference is the lack of ORDER BY. This construction is not used in the OVER statement. If it is not, then this function is used in relation to each line of a section or group. If available, it is applied to the window, which implies a transition to another type (see clause 2).
- Statistical Functions. Examples include STDEVJPOP, VAR_SAMP, and others. Allow to calculate values of statistical indicators for any unordered section.
In general, we can say that the analytical functions of SQL Oracle have already been considered. But only in general terms. There is no desire to drop the case halfway, so let's detail. And the analytical functions of Oracle will help in this, examples of which will now be considered.
Section Design
To do this, use a query of the form PARTITION BY expression [, ...]. Such a construction carries out a logical breakdown of the resulting set into certain groups, according to the criteria that were specified by section expressions. At the same time, the analytical functions of Oracle are applied independently in relation to each subject of interaction. That is, after the processing has been completed, they are reset for the new group. But what if the partition design is not specified? In this case, the resulting set will be considered as one group.
Ordering construct
In this case, a query of the form ORDER BY expression is used. Additionally, DESC, ASC and NULLS FIRST / LAST can be applied. This construction is used to define the criteria for sorting data in a section or group. Using ORDER BY can significantly affect the result that Oracle will introduce. The analytical functions of FIRST and LAST, for example, focus on the first and last elements. That is, they are opposite in meaning! But what do FIRST and LAST tell us? They indicate where the NULL value should be when ordering, at the beginning of a section or the end. If ORDER BY is not foreseen, then the average value for the whole group will be calculated. And it will be issued for each line. Why? The fact is that in this case the function is considered as the final one. It should be noted that ordering is carried out exclusively within groups or sections.
Window construction
Its construction is very complicated. After all, with its help, a rigidly attached or moving window (interval, set) of data is set within the section (group) with which the analytical function will interact. You can set the lower and upper boundaries of the aggregation. In other words, in such cases, we decide to apply an analytic function to each row of the section (group) from numbers X to Y. When using windows, ORDER BY plays an important role. For tightly tied intervals, the standard is considered to be the choice from the first to the current row of the group.
Why analytic functions are needed
The technical goal is to provide a concise wording and to facilitate the speed of execution of queries to the database. They have the following advantages over the usual SQL statements:
- Reduced network load. What previously required a series of queries could now be retrieved from the database in one.
- Concise and simple wording. Thanks to this, debugging and comprehension capabilities have improved compared to traditional tools.
- Processing efficiency. Analytical functions have computational algorithms that are optimized for faster results.
- Transfer of work to the server. All calculations are carried out on a server, the resources of which, as a rule, are more suitable for this task.
The introduction of such functions also made it possible to achieve such a strategic goal as providing a basic tool for building so-called analytical data warehouses.
Conclusion
What can be said at the end of the article? Analytical functions are a very interesting solution. To better understand their specifics, you can practice their use on complex and large databases. As an example, for comparison, you can choose such traditional functions as SUM, MAX and the like. But keep in mind that they act as an intermediate result of calculations. Therefore, they can only be used with SELECT or ORDER BY. It should be recognized that this topic is very interesting. There are a large number of variations, and theoretically describing them is all problematic. You need to try and work with queries.