Oracle analysis functions (notes)

Oracle analysis functions (notes)

Analytics functions are new types of functions introduced by Oracle Database in 9i and are continuously enhanced in later versions. The analysis function provides the ability to aggregate reference values across rows and layers. The results presented by analysis functions can also be achieved using traditional SQL statements, but the implementation method is complicated and inefficient.

Analytics functions have three basic components: partition clauses, sorting clauses, and window clauses. The basic syntax is

function1 (argument1,argument2…argumentN)     over([partition- by -clause] [ order - by -clause] [windowing-clause]) -- Description -- Function1 is the called analysis function that accepts 0 or more parameters. -- The partition clause groups data based on the value of the partition column. Columns with the same values in all partition columns are placed in the same partition. -- The sorting can be ascending or descending, or the nllls first and nulls last clauses can be used to place null values at the beginning or end of the partition. -- The window clause specifies the data subset of the analysis function operation. The specific syntax is as follows:     [ ROWS | RANGE] BETWEEN <Start expr> AND < End expr> -- Where     <Start expr> is [NUBOUNDED PRECEDING | CURRENT ROW | n PRECEDING | n PRECEDING]       < End expr> is [NUBOUNDED FOLLOWING| CURRENT ROW | n PRECEDING | n FOLLOWING]  -- The keyword preceding specifies the upper boundary of the window opening statement. The following or current row clause specifies the lower boundary.  -- Note that analysis functions cannot be nested. However, you can place the SQL statements in the nested statements for nesting.

List of common functions

Take the lag function as an example.

-- No Default Value select zgqk flag,              kind,         sid,         lag(sid, 1) over(partition by flag, kindorder by flag, kind) sid_lag_1   from hidden_danger_ybyhxx   where userid= 717   order by flag, kind; -- Result        FLAG KIND                SID  SID_LAG_1 ---------- ------------- ---------- ----------           1 A01                2295           1 A02                3414           1 A02                20785      3414           1 A02                2328      20785           1 A04                3412           1 A07                2297           1 A08                2332           1 A10                3420           1 A10                2298      3420           1 A10                2329      2298           1 A10                2327      2329           1 A11                2299           1 A11                3416      2299 -- Default value available select zgqk flag,              kind,         sid,         lag(sid, 1，sid) over(partition by flag, kindorder by flag, kind) sid_lag_1   from hidden_danger_ybyhxx   where userid= 717   order by flag, kind; -- Result       FLAG KIND                SID  SID_LAG_1 ---------- ------------- ---------- ----------           1 A01                2295      2295           1 A02                3414      3414           1 A02                20785      3414           1 A02                2328      20785           1 A04                3412      3412           1 A07                2297      2297           1 A08                2332      2332           1 A10                3420      3420           1 A10                2298      3420           1 A10                2329      2298           1 A10                2327      2329           1 A11                2299      2299           1 A11                3416      2299           1 A12                2333      2333           1 B07                7800      7800           1 B07                12595      7800

Specifically, the listparts function does not support the window opening clause. The example is as follows:

select listagg(sid, ',' ) within group ( order by sid desc ) sid   from   (   select sid from user   where userid = 76298 order by sid   ); -- Result:  SID ------------------------------------- 31827,11199,9303,7901,5629,3254  -- Is it similar to the wmsys. WM_CONCAT function?

Detailed examples of Oracle function GREATEST Functions

Single-row conversion functions of Oracle Functions

Oracle functions that generate dynamic prefixes and auto-increment numbers

Replace () for Oracle Functions ()

Oracle Functions

Differences in case and decode usage and Performance Comparison of Oracle Functions