Oracle Analytic functions

Aggregation functions can be performed in either analytic or non-analytic mode. Aggregate functions in non-analytic mode reduce the result set to fewer rows of data. However, in analytic mode, the aggregate function does not reduce the number of rows of output results. Also, an aggregate function can get both aggregated and non-aggregated columns in a row. The aggregation function of the analytic pattern provides the ability to aggregate different levels of data without any self-connection.

1. Sum

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CB/wKioL1WoulTwzTSLAABYZS7pd3c124.jpg "title=" sum_0. PNG "alt=" wkiol1woultwztslaabyzs7pd3c124.jpg "/>

Sum can be used as an aggregation function or as an analytic function.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CE/wKiom1WouTaT6utjAAEXwzYiB8A137.jpg "title=" sum_3. PNG "alt=" wkiom1woutat6utjaaexwzyib8a137.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CB/wKioL1Wou2Pgm-fQAAPd_tG9srs387.jpg "title=" sum_1. PNG "alt=" wkiol1wou2pgm-fqaapd_tg9srs387.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CE/wKiom1WoubPRuQ7XAAEWEjShQuk163.jpg "title=" sum_4. PNG "alt=" wkiom1woubpruq7xaaewejshquk163.jpg "/> is different from the previous SQL is the window changes, this example takes the start line to the current line, that is, the line before the current row (including the current row) sum

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CB/wKioL1Wou_yAFpxOAAPPy11v-j0966.jpg "title=" sum_2. PNG "alt=" wkiol1wou_yafpxoaappy11v-j0966.jpg "/> Easy to understand the sum analysis function from the results

2. Avg

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CE/wKiom1WourCA7TXtAABYgONnD0M552.jpg "title=" avg_0. PNG "alt=" wkiom1wourca7txtaabygonnd0m552.jpg "/>

Like sum, it's not much to say.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CB/wKioL1WovXyShWbiAAEJsfzgS8A448.jpg "style=" float: none; "title=" Avg_3.png "alt=" Wkiol1wovxyshwbiaaejsfzgs8a448.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CE/wKiom1Wou6CgYxf9AAEwbi-ZWaM733.jpg "style=" float: none; "title=" Avg_4.png "alt=" Wkiom1wou6cgyxf9aaewbi-zwam733.jpg "/>

The above two statements are equivalent and look at the results

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CB/wKioL1Wovdqw6xAaAASU6Bfyf0M251.jpg "title=" avg_1. PNG "alt=" wkiol1wovdqw6xaaaasu6bfyf0m251.jpg "/>

In the following statement, every three lines are averaged (before, during, and after)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CE/wKiom1WovX3SL-iWAADzLeA6B9I731.jpg "title=" Avg_ 10.png "alt=" Wkiom1wovx3sl-iwaadzlea6b9i731.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CC/wKioL1Wov6Lw6FvVAASs3uFO7Is631.jpg "title=" avg_2. PNG "alt=" wkiol1wov6lw6fvvaass3ufo7is631.jpg "/>

3. Lag

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CE/wKiom1WovsmwAs5EAAB7nkADg48680.jpg "title=" lag_0. PNG "alt=" wkiom1wovsmwas5eaab7nkadg48680.jpg "/>

Lag provides a way to access the front row of the current position, with the default offset of 1, which indicates access to the previous row

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CE/wKiom1WowLKQM80fAAIMF0eBq3A535.jpg "title=" lag_1. PNG "alt=" wkiom1wowlkqm80faaimf0ebq3a535.jpg "/>

4. Lead

Leads can be used to access rows following the current position

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CC/wKioL1Wow1ijUQ2lAAB5mrx6Hik786.jpg "style=" float: none; "title=" Lead_0.png "alt=" Wkiol1wow1ijuq2laab5mrx6hik786.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CE/wKiom1WowXzyTe7rAAICVM8_R48609.jpg "style=" float: none; "title=" Lead_1.png "alt=" Wkiom1wowxzyte7raaicvm8_r48609.jpg "/>

5. First_value and Last_value

First_value takes the first value in the window, Last_value takes the last

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CC/wKioL1WoyVjyUjbwAAUP0i4Znlk570.jpg "title=" Fist_ Last.png "alt=" Wkiol1woyvjyujbwaaup0i4znlk570.jpg "/>

6. Rank

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CC/wKioL1Wo1F3SEMpvAACugnJTLRw698.jpg "style=" float: none; "title=" Rank_1.png "alt=" Wkiol1wo1f3sempvaacugnjtlrw698.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/CF/wKiom1Wo0oDRbXhoAABV_ep6SzI608.jpg "style=" float: none; "title=" Rank_2.png "alt=" Wkiom1wo0odrbxhoaabv_ep6szi608.jpg "/>

Rank, meaning rank, rank.

The Rank function returns the rank of a row, with the same number of rows with equal values, and the rank is discontinuous. The rank function is useful for operations such as Top-n and Bottom-n. Rank returns the rank of an assumed value as a clustered function, and returns the rank of each row as an analytic function.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CC/wKioL1Wo1i7QhpXTAADOtQTuj44566.jpg "style=" float: none; "title=" Rank_agg.png "alt=" Wkiol1wo1i7qhpxtaadotqtuj44566.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CC/wKioL1Wo1i7BSSLvAAOca1w2328594.jpg "style=" float: none; "title=" Rank_any.png "alt=" Wkiol1wo1i7bsslvaaoca1w2328594.jpg "/>

7. Row_number

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CF/wKiom1Wo1PnCqhLIAABQ68QqRu4894.jpg "title=" Row_ Number.png "alt=" Wkiom1wo1pncqhliaabq68qqru4894.jpg "/>

Row_number is an analytic function that assigns a number to each line, starting with the number 1.

This function can implement TOP-N,BOTTOM-N,INNER-N operations.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CF/wKiom1Wo1efgF7n0AAMjuL9gkHs406.jpg "title=" Row_ Number_.png "alt=" wkiom1wo1efgf7n0aamjul9gkhs406.jpg "/>650" this.width=650; "src=" http://s3.51cto.com/wyfs02/ M01/6f/cf/wkiom1wo1quxcdfsaadkihl27ym290.jpg "title=" Row_num_1.png "alt=" wkiom1wo1quxcdfsaadkihl27ym290.jpg "/ >

Note also rownum the pseudo-column area, ROWNUM returns a number for each row to indicate the order of the selection, such as 1, 2 ...

8. Listagg

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6F/CF/wKiom1Wo2GvRXosrAACLbVU8M4U725.jpg "title=" Lisgagg_0.png "alt=" Wkiom1wo2gvrxosraaclbvu8m4u725.jpg "/>

Listagg function for column changing

As a single-line aggregation function, Listagg acts on all rows and returns a single row

As a grouping aggregation function, Listagg acts on each group and returns a row for each group

As an analytic function, Listagg partitions the data based on the query partition and returns the same result for each row in each partition

MEASURE_EXPR, specifying the column or expression to manipulate

Delimiter_expr, specifying delimiters

Order_by_clause, determine the order of the results

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CF/wKiom1Wo3dHjazL9AAF9fn1BdG4179.jpg "style=" float: none; "title=" Listagg_1.png "alt=" Wkiom1wo3dhjazl9aaf9fn1bdg4179.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/CF/wKiom1Wo3dGjy2FYAAOq9Pqu3cI984.jpg "style=" float: none; "title=" Listagg_2.png "alt=" Wkiom1wo3dgjy2fyaaoq9pqu3ci984.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/CC/wKioL1Wo366T_p3dAAS0UHB1-kA102.jpg "style=" float: none; "title=" Listagg_3.png "alt=" Wkiol1wo366t_p3daas0uhb1-ka102.jpg "/>

This article is from the "ten-li Rice flower" blog, please be sure to keep this source http://5880861.blog.51cto.com/5870861/1675769

Oracle Analytic functions