Oracle analysis function over and Window Function

Oracle's analysis function over and window function eg: Related parsing: Table t_pi_part field id code namevalue 1 222 avalue 2 222 bvalue 3 333 c add Row labels to the same part code, sort www.2cto.com select p. *, row_number () over (partition by p. code order by. id desc) as row_index from t_pi_part p; 1: The analysis function overOracle provides the analysis function from 8.1.6. The analysis function is used to calculate a group-based aggregate value, unlike Aggregate functions, Aggregate functions return multiple rows for each group, while aggregate functions return only one row for each group. The following examples illustrate the application. Www.2cto.com 1: Count the turnover of a store. Date sale 1 20 2 15 3 14 4 5 30 rule: daily statistics: the total amount of the previous days is calculated every day: date sale sum ----- -------- 1 20 20 -- 1 day 2 15 35 -- 1 day + 2 days 3 14 49 -- 1 day + 2 days + 3 days 4 18 67. 5 30 97. 2: count the information of the first student in each CLASS. name class s ----- -------------------- fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 pass: -- select * from (select name, class, s, rank () over (partition by class order by s desc) mm from t2) where mm = 1 -- The result is as follows: name class s mm ----- ---------------------- dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 Note: 1. row_number () is not allowed when the first score is obtained, because if there are two parallel numbers in the same class, row_number () returns only one result 2. the difference between rank () and dense_rank () is that -- rank () is a skip sorting, and there are two second names followed by the fourth name -- dense_rank () l is a continuous sorting, when there are two second names, they still follow the third name. classification statistics (and display information) a B c -- ---------------------- m a 2 n a 3 m a 2 n B 2 n B 1 x B 3 x B 2 x B 4 h B 3 select a, c, sum (c) over (partition by a) from t2: a B C SUM (C) OVER (PARTITIONBYA) -- ------- ------------------------ h B 3 3 m a 2 4 m a 2 4 n a 3 6 n B 2 6 n B 1 6 x B 3 9 x B 2 9 x B 4 9 if sum is used, group by can only get a sum (C) -- ---------------------- h 3 m 4 n 6x9 cannot get B column value ===== select * from test data: a B c 1 1 1 1 2 2 3 3 2 5 3 4 6 --- add the corresponding C column values with the same B column values to the total select a, B, c, SUM (C) OVER (partition by B) c_Sumfrom test a B C C_SUM 1 1 1 1 1 2 2 2 7 2 2 5 7 1 3 3 3 4 6 6 --- if you do not need to split the values of A column, use null eg: Put the column value summary of C after each row select a, B, c, SUM (C) OVER (PARTITION BY null) c_Sumfrom test a B c C_SUM 1 1 1 17 1 2 2 2 17 1 3 3 3 17 2 2 5 17 3 4 6 17 www.2cto.com calculate the percentage of personal salary to department salary SQL> select * from salary; name dept sal ---------- ---- a 10 2000b 10 3000c 10 5000d 20 4000 SQL> select name, dept, sal, sal * 100/sum (sal) over (partition by dept) percent from salary; name dept sal percent ---------- ---- ---------- a 10 2000 20b 10 3000 30c 10 5000 50d 20 4000 2: the Window Function specifies the size of the data window used by the analysis function. The size of the data window may change with the change of rows. For example: 1: over (order by salary) accumulate by salary. order by is a default window function over (partition by deptno) by Department partition 2: over (order by salary range between 5 preceding and 5 following) the data window corresponding to each row is that the range value of the previous row cannot exceed 5, and the range value of the subsequent row cannot exceed 5. For example: for the following columns aa 1 2 2 3 4 5 6 7 9 sum (aa) over (order by aa range between 2 preceding and 2 following) the result is aa sum ------------------------ --------------------------------------------------------- 1 10 2 14 2 14 2 14 3 18 4 5 22 6 7 22 9 9, for a line with aa = 5, sum is 5-1 <= aa <= 5 + 2 and for aa = 2, sum = 1 + 2 + 2 + 2 + 3 + 4 = 14; for example, for aa = 9, 9-1 <= aa <= 9 + 2 only has 9 numbers, so sum = 9; www.2cto.com 3: Others: over (order by salary rows between 2 preceding and 4 following) the data window corresponding to each row is the first two rows, followed by four rows 4: The following three statements are equivalent: over (order by salary rows between unbounded preceding and unbounded following) the data window corresponding to each row is from the first row to the last row, which is equivalent to over (order by salary range between unbounded preceding) equivalent over (partition by null)