Oracle Analytic functions

First, through an example, the intuitive feeling of the analysis function is simple and convenient:

Create Table criss_sales (    dept_id    VARCHAR2(6),     -- Department number    Sale_ Date Date  ,            -- Sales Date    VARCHAR2(4),-     - type    of goods sale_cnt   number (ten)       - Sales quantity );

Insert test data:

Insert  intoCriss_salesValues('D01', To_date ('20140304','YYYYMMDD'),'G00', the);Insert  intoCriss_salesValues('D02', To_date ('20140306','YYYYMMDD'),'G00', -);Insert  intoCriss_salesValues('D01', To_date ('20140408','YYYYMMDD'),'G01', $);Insert  intoCriss_salesValues('D02', To_date ('20140427','YYYYMMDD'),'G01', -);Insert  intoCriss_salesValues('D01', To_date ('20140430','YYYYMMDD'),'G03', -);Insert  intoCriss_salesValues('D02', To_date ('20140502','YYYYMMDD'),'G03', the);Insert  intoCriss_salesValues('D01', To_date ('20140504','YYYYMMDD'),'G02', the);Insert  intoCriss_salesValues('D02', To_date ('20140408','YYYYMMDD'),'G02', -);

Requirements: To find out the total amount of sales per time division, using the Analysis function SQL as follows:

SELECT     t.dept_id,    t.sale_date,    t.goods_type,    t.sale_cnt,    SUMover (  ORDERbybetween and current ROW) all_cmp_cntfrom      criss_sales T

The results are as follows:

With this example, you can see that using analytic functions to solve problems in some scenarios is very intuitive and straightforward.

What is an analytic function?
Analytic functions are powerful functions that Oracle specifically uses to address the statistical requirements of complex reports, which can be grouped in data and then calculated based on some statistical values of groups, and each row of each group can return a statistic value.
What is the difference between an analytic function and an aggregate function?
The normal aggregation functions are grouped by group by, each grouping returns a statistic value, and the analysis functions are grouped by partition, and each group can return a statistic value.
The form of an analytic function?
The analysis function has an open window function over (), which contains three analysis clauses: grouping (partition by), sorting (Order by), window (rows), they are used in the following form: Over (partition by xxx order by yyy rows is Tween zzz and QQQ).

What scenarios do analytic functions apply to?
1. Aggregation of data in the table
2. In-table grouping accumulation
3. Group ranking
4. Scrolling statistics
5. Range Evaluation Sum MAX MIN
6. Comparison of adjacent rows
Wait a minute...

Oracle Analytic functions