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