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):
- . " N".
- . ( ).
- . .2, . – ORDER BY. OVER . , . , ( .2).
- . STDEVJPOP, VAR_SAMP . .
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
ORDER BY . DESC, ASC NULLS FIRST/LAST. . ORDER BY , Oracle. FIRST LAST, , . ! FIRST LAST? , NULL , . ORDER BY, . . ? , . , .
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 analytical 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:
- . , , .
- . .
- . , .
- . , , , .
, .
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.