Shulou(Shulou.com)06/01 Report--

Aggregate functions can be operated in analytical mode or non-analytical mode. The aggregate function in non-analysis mode reduces the result set to fewer rows of data. However, in parse mode, the aggregate function does not reduce the number of rows of output results. Also, the aggregate function can get both aggregated and non-aggregated columns in a row. The aggregate function of the analysis pattern provides the ability to aggregate different levels of data without any self-join.

1. Sum

You can use sum as an aggregate function or as an analysis function

Unlike the previous sql, there is a change in the window. In this example, the starting line is taken to the current line, that is, the sum of the rows before the current line (including the current row)

It is easy to understand the sum analysis function from the results.

2. Avg

It's about the same as sum, so I won't say much.

The above two statements are equivalent. Look at the result.

The following statement averages every three lines (front, middle, and back)

3. Lag

Lag provides a way to access the front line of the current location. The default offset is 1, which means to access the previous line.

4. Lead

Using lead, you can access the lines behind the current location.

5. First_value and last_value

First_value takes the first value in the window and last_value takes the last one.

6. Rank

Rank, which means ranking or ranking.

The rank function returns the ranking of a row. Rows with equal values rank the same, but the rankings are discontiguous. The rank function is useful for operations such as top-N and bottom-N. Rank returns the ranking of a given value as an aggregate function and the ranking of each row as an analysis function.

7. Row_number

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

This function can implement operations such as top-N,bottom-N,inner-N.

Note that rownum pseudo columns are also distinguished, and rownum returns a number for each row to indicate the order in which it is selected, such as 1, 2...

8. Listagg

The listagg function is used for column wrapping

As a single-row aggregate 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 analysis function, listagg partitions data based on query partition words and returns the same result for each row in each partition

Measure_expr, which specifies the column or expression to operate on

Delimiter_expr, specifying the delimiter

Order_by_clause, which determines the order of the results

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.