Oracle provides an analysis function from 8.1.6, and the analysis function is used to calculate a group-based polymeric value. Its and aggregate functions are multi-row, and the aggregate function returns only to each group. . The table used in the following example is from the table under the HR user comes with the Oracle. If the user is not installed, you can run $ ORACLE_HOME / SCHEMA / HUMAN_RESOURES / HR_MAIN.SQL under SYS users to create. A few examples require access to the table under the SH user. If the user is not installed, you can run $ ORACLE_HOME / DEMO / Schema / Sales_history / sh_main.sql under SYS users to create. If it is not specified, the default is an example of running under HR users. The understanding of the window function: the window function specifies the size of the data window for the analysis function. This data window size may vary with the change of the row. For example, Over (Order by Salry) is sorted by Salarit, Order BY is a default window function over (Partition by deptno) Follow the departmental partition over (Order By Salary Range Between 50 Preceding and 150 FOLLOWING) The previous row range is not more than 50, and the line is not exceeded. 150Over (Order by Salary Rows Between 50 Preceding and 150 Following) The corresponding data window is 50 lines, and then 150 line OVER (Order by Salary Rows Between Unbooked Preceding and UNBounded Following) The corresponding data window of each row is from the first To the last line, equivalent: over (Order by Salary Range Between Unbooking) Major Reference: "Expert One-on-One" Tom Kyte "Oracle9i SQL Reference" Chapter 6 AVG Function Description: Used to calculate A average of the expression in a group and data window.
Sample: The following example is the average salary report of each employee in the employee table in the example below. The average is from the current employee and the previous and last three of the three and the last three of the three people; select manager_id, last_name , hire_date, salary, AVG (salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; MANAGER_ID LAST_NAME HIRE_DATE SALARY c_MAVG ---------- ----- ---------------------------------- 100 Kochhar 21-Sep-89 17000 17000 100 de Haan 13-Jan-93 17000 15000 100 Raphaly 07-Dec-94 11000 11966.6667 100 Kaufling 01-May-95 7900 10633.3333 100 Hartstein 17-Feb-96 13000 9633.33333 100 Weiss 18-JUL -96 8000 11666.667 100 RUSSELL 01-OCT-96 14000 11833.3333 ... CORR Function Description: Returns a pair of expressions related to the following abbreviation: COVAR_POP (expr1, expr2) / stddev_pop (expr1) * stddev_pop (expr2 )) From statistics, correlation is the strong associated between variables Degree, the association between variables means that the value of a variable in some extent can be predicted by other values. By returning a number between one -1 to 1, the correlation coefficient gives an associated strength, 0 means it is not related.
Sample: The following example returns the cumulative factor of the relationship between sales revenue and monthly sales in 1998 (this example runs under the SH user) Select T.calendar_Month_Number, Cor (Sum (S.Amount_sold), Sum (S.quantity_Sold) over (ORDER BY t.calendar_month_number) as CUM_CORR FROM sales s, times tWHERE s.time_id = t.time_id AND calendar_year = 1998GROUP BY t.calendar_month_numberORDER BY t.calendar_month_number; CALENDAR_MONTH_NUMBER CUM_CORR ------------- ------------------ 1 2 1 3. 903542662covar_pop function Description: Returns a pair of expressions of the overall covariance.
SAMPLE: The following example CUM_COVP return pricing and minimum prices cumulative population covariance SELECT product_id, supplier_id, COVAR_POP (list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVP, COVAR_SAMP (list_price, min_price) OVER (ORDER BY product_id, SUPPLIER_ID) AS CUM_COVS from Product_Information Pwhere Category_ID = 29Order by Product_ID, Supplier_ID; Product_ID Supplier_id Cum_COVP CUM_COVS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- 1774 103088 0 1775 103087 1473.25 2946.5 1794 103096 1702.77778 2554.16667 1825 103093 1926.25 2568.33333 2004 103086 1591.4 1989.25 2005 103086 1512.5 1815 2416 103088 1475.97959 1721.97619..COVAR_SAMP function description: returns the expression of one pair of samples covariance sAMPLE: the following example CUM_COVS minimum pricing and price return cumulative sample covariance SELECT product_id, supplier_id, COVAR_POP (list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVP, COVAR_SAMP (list_price, min_price) OVER ( ORDER BY product_id, supplier_id) AS CUM_COVS FROM product_information pWHERE category_id = 29ORDER BY product_id, supplier_id; PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS ---------- ----------- ------ ---- ---------- 1774 103088 0 1775 103087 1473.25 2946.5 1794 103096 1702.77778 2554.16667 1825 103093 1926.25 2568.33333 2004 103086 1591.4 1989.25 2005 103086 1512.5 1815 2416 103088 1475.97959 1721.97619..COUNT description: a group of What happens in the incident, if you specify * or some non-empty constants, Count will count all lines, if you specify an expression, count returns the count of non-empty assignments, when there is the same value, these equal The value is incorporated into the calculated value; you can use distinct to record the number of rows that appear after the identical data is removed.
Sample: In the following example, each employee calculates each employee's number of rows between [N-50, N 150] in the current line in salary, and n means that the salary of the current row, for example, the PHILTANKER's salary 2200, ranking in him There is a line of salary than or equal to 2200-50, and the salary is less than or equal to 2200 150 after the row, so the count count value CNT3 is 2 (including its current line); CNT2 value is equal to less than equal All rows of Salary values of the current line Select last_name, Salarit Last_name, Salary, Count (*) over () AS CNT1, Count (*) over (Order by Saliff) AS CNT2, Count (*) over (Order By Salary Range Between 50 Preceding AND 150 FOLLOWING) AS CNT3 from Employees; Last_name Salary CNT1 CNT2 CNT3 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- Olson 2100 107 1 3markle 2200 107 3 2Philtanker 2200 107 3 2LANDRY 2400 107 5 8Gee 2400 107 5 8colmenares 2500 107 11 10patel 2500 107 11 10..cume_dist function description: Calculate the relative position in the group, Cume_Dist always returns a number of more than 0, less than or equal to 1, indicating the position of the row in the N row.
For example, in a 3-line group, the returned cumulative distribution value is 1/3, 2/3, 3 / 3Sample: The following example calculates the employee of each type of employee to sequentially accumulate the distribution percentage of SELECT JOB_ID, LAST_NAME , Salary, Cume_Dist () OVER (Partition By Job_ID ORDER BY SALY) AS CUME_DIST FROM EMPLOYEES Where Job_id Like 'Pu%'; Job_ID Last_name Salry Cume_Dist ---------- --------- -------------------------- ---------- Pu_Clerk colmenrees 2500 .2pu_clerk himuro 2600 .4pu_clerk tobias 2800 .6pu_clerk baida 2900 .8pu_clerk khoo 3100 1PU_man Raphaly 11000 1DENSE_RANK function Description: According to the value of the expression in the ORDER BY clause, the relative positions of the other rows are calculated from each row returned from the query. The data in the group is sorted by the Order By clause, and then assigns a number to each line to form a sequence, the sequence starts from 1, and it is accumulated. This sequence also increases when the value of the ORDER BY expression changes. Rows with the same value get the same digital sequence number (considered null).
The number of occurrences of the intensive sequence returned to the number: The following example calculates that each employee is sorted by department partition, sequentially sequentially, sequentially appears (Note to the difference with the RANK function) Select D.Department_ID, E.last_name, E .salary, DENSE_RANK () OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank FROM employees e, departments dWHERE e.department_id = d.department_id AND d.department_id IN ( '60', '90'); DEPARTMENT_ID Last_name Salary Drank --------------------------------- - ---------- 60 Lorentz 4200 1 60 Austin 4800 2 60 Patabase 4800 2 60 Ernst 6000 3 60 Hunold 9000 4 90 Kochhar 17000 1 90 de Haan 17000 1 90 KING 24000 2First Function Description: Return from Dense_Rank The line is taken out in the collections (possibly multi-line, because the value may be equal), so the complete syntax needs to add a collection function to the start. Record Sample: In the following example, Dense_rank is partitioned in the department, then press the commission commission_pct sort, first take the lowest possible row of the commission, then the front MAX function takes the lowest value of the salary from this collection; Last takes the highest corresponding to the highest OK, then the previous min function removes the highest salarage in this collection. SELECT LAST_NAME, Department_ID, SALARY, MIN (SALARY) Keep (Partition By Department_ID) "WORST", Max (Salary) Keep (Dense_rank Last Order By Commission_PCT) OVER (Partition By Department_ID) "Best" from Employees Where Department_ID in (20, 80) Order by Department_ID, SALARY DIALY; DEPARTMENT_ID, SALARY;
Last_name department --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- Fay 20 6000 6000 13000kumar 80 6000 6100 14000Banda 80 6200 6100 14000Johnson 80 6200 6100 14000ande 80 6400 6100 14000lee 80 6800 6100 14000TUVAULT 80 7000 6100 14000Sewall 80 7000 6100 14000Marvins 80 7200 6100 14000 ... first_value function description: Returns the first value of the data window in the group.
Sample: The following example calculates the name corresponding to the first value of the data window sorted by the department partition, if the first value of the salary has multiple, from multiple corresponding names to the first one name SELECT department_id, last_name, salary, FIRST_VALUE (last_name) OVER (PARTITION BY department_id ORDER BY salary ASC) AS lowest_sal FROM employees WHERE department_id in (20,30); dEPARTMENT_ID lAST_NAME sALARY LOWEST_SAL ----------- - ----------------------------------- - 20 Fay 6000 Fay 20 Hartstein 13000 Fay 30 Colmenares 2500 Colmenares 30 Himuro 2600 Colmenares 30 Tobias 2800 Colmenares 30 Baida 2900 Colmenares 30 Khoo 3100 Colmenares 30 Raphaely 11000 ColmenaresLAG function description: You can access other rows in the result set without self-connection. It allows you to deal with the cursor, as if the cursor is an array. In a given group, you can refer to the row before the current row, so you can select the previous row with the current row from the group.
Offset is a positive integer, its default value is 1, if the index exceeds the window, return to the default value (the default returned is the first line in the group), the opposite function is LeadSample: The following example is column prev_sal Returns Press 1 salary front row ordered value hire_date SELECT last_name, hire_date, salary, LAG (salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employeesWHERE job_id = 'PU_CLERK'; lAST_NAME hIRE_DATE sALARY PREV_SAL ------ ---------------------------------- Khoo 18-May - 95 3100 0Tobias 24-July -97 2800 3100Baida 24-December -97 2900 2800Himuro 15- November -98 2600 2900Colmenares 10-August-99 2500 2600LAST function description: Remove from Dense_Rank returned The row of a value of the last side (possibly, because the value may be equal), the complete syntax need to add a collection function to the start to add a recorded Sample: The following example is divided into department partition, and then press commission Commission_pct sorting, first remarks all the rows of the lowest commission, then the front MAX function takes the lowest value from this collection; Last takes out all the rows of the highest corresponding to the commission, then the previous MIN function is highest from this collection. Value Select Last_name, Department_ID, SALARY, MIN (SALARY) Keep (Dense_Rank First Order By Commission_PCT) OVER (Partitio N by department_id) "Worst", Max (Salary) Keep (Dense_Rank Last Order By Commission_PCT) over (Partition By Department_ID) "Best" from Employees Where Department_ID in (20, 8) Order by Department_ID, SALARY;
Last_name department --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- Fay 20 6000 6000 13000kumar 80 6000 6100 14000Banda 80 6200 6100 14000Johnson 80 6200 6100 14000ande 80 6400 6100 14000lee 80 6800 6100 14000Tuvault 80 7000 6100 14000Sewall 80 7000 6100 6100 14000Bates 80 7300 6100 14000 ... Last_Value function description: Returns the last value of the data window in the group.
SAMPLE: The following example calculates the name corresponding to the last value of the data window sorted by the department partition, if there is a plurality of salary, then the last name of the default name from multiple corresponding names SELECT Department_ID , last_name, salary, LAST_VALUE (last_name) OVER (PARTITION BY department_id ORDER BY salary) AS highest_sal FROM employees WHERE department_id in (20,30); DEPARTMENT_ID lAST_NAME SALARY hIGHEST_SAL ------------- - -------------------------------------------- 20 Fay 6000 Fay 20 Hartstein 13000 Hartstein 30 Colmenares 2500 Colmenares 30 Himuro 2600 Himuro 30 Tobias 2800 Tobias 30 Baida 2900 Baida 30 khoo 3100 khoo 30 Raphaly 11000 Raphalylead Function Description: LEAD is in contrast to the LAG, the Lead can access the rows after the current line in the group. Offset is a positive integer, its default value is 1, if the index exceeds the window, return to the default value (default returned is the first line in the group) Sample: Next example, "NextTHIRED" in each row returns to press HIRE_DATE Sort by Hire_Date the value of the SELECT last_name hire_date next row, hire_date, LEAD (hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" FROM employees WHERE department_id = 30; lAST_NAME hIRE_DATE NextHired ------------- ------------ --------- --------- Raphaly 07-Dec-94 18-May-95khoo 18-May-95 24-jul- 97TOBIAS 24-JUL-97 24-DEC-97BAIDA 24-DEC-97 15-NOV-98HIMURO 15-NOV-98 10-AUG-99COLMENARES 10-AUG-99MAX function description: Find expressions in the data window in a group The maximum value.
SAMPLE: The following example returns the current maximum salary in dept_max sector where the value of the row SELECT department_id, last_name, salary, MAX (salary) OVER (PARTITION BY department_id) AS dept_max FROM employees WHERE department_id in (10,20,30); DEPARTMENT_ID LAST_NAME SALARY DEPT_MAX ------------------------------------------------ ---------- 10 Whalen 4400 4400 20 Hartstein 13000 13000 20 Fay 6000 13000 30 Raphaly 11000 11000 30 khoo 3100 11000 30 Baida 2900 11000 30 Tobias 2800 11000 30 Himuro 2600 11000 30 Colmenares 2500 11000min function description: in one The minimum value of the expression is found in the data window in the group.
SAMPLE: The following example returns the current minimum salary in dept_min sector where the value of the row SELECT department_id, last_name, salary, MIN (salary) OVER (PARTITION BY department_id) AS dept_min FROM employees WHERE department_id in (10,20,30); DEPARTMENT_ID LAST_NAME SALARY DEPT_MIN ------------------------------------- ---------- 10 Whalen 4400 4400 20 Hartstein 13000 6000 20 May 6000 6000 30 Khoo 3100 2500 30 Baida 2900 2500 30 Tobias 2800 2500 30 Himuro 2600 2500 30 Colmenares 2500 2500 NTILE function description: Will The burst is represented by "expressions", for example, if expression = 4, then assign a number (from 1 to 4) in each row in the group, if there is 20 lines in the group, give the top 5 lines allocation 1, give the next 5 lines to assign 2, etc.. If the group's base cannot be separated from the expression value, the number of rows of percentiles do not have any percentiles in the group more than one line in the group, and the lowest Percentile is those who have additional lines. For example, if the expression = 4, the number of rows = 21, then PERCENTILE = 1 has 5 lines, and there is 5 lines of percentile = 2.
Sample: The six-line data is divided into 4 serving last_name, Salary, NTILEESWHERE DEPARTILLE from Employeeswhere Department_ID = 100; Last_Name Salary Quartile ---------------- ------------------------ ---------- Greenberg 12000 1FAVIET 9000 1CHEN 8200 2URMAN 7800 2SCIARRA 7700 3POPP 6900 4PERCENT_RANK function description : Similar to the cume_dist (cumulative assignment) function, for a set of rows in a group, when the serial number of the line is calculated, then 1, then divide by N-1 (n is all the number of rows in the group). This function always returns the number between 0 to 1 (including 1).
Sample: If the Khoo's Salry is 2900, the PR value is 0.6, because the Rank function is the same as the equivalent return sequence value is the same select department_id, last_name, salance, percent_rank () over (partition by department_id order by sales) AS Pr From Employeeswhere Department_ID <50 ORDER BY Department_ID, SALARY; Department_ID Last_name Salary PR -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- ---------- 10 Whalen 4400 0 20 May 6000 0 20 20 Hartstein 13000 1 30 Colmenares 2500 0 30 Himuro 2600 0.2 30 Tobias 2800 0.4 30 Baida 2900 0.6 30 khoo 3100 0.8 30 Raphaly 11000 1 40 MAVRIS 6500 0Percentile_ CONT function Description: Returns a data value corresponding to the input distribution percentage value, a calculation method of distribution percentage, see the function percent_rank, if there is no corresponding data value, the value is obtained by the following algorithm: RN = 1 (p * (N-1)) where P is the input distribution percentage value, n is the number of rows in the group CRN = CEIL (RN) FRN = floor (rn) IF (crn = fn = rn) THEN (Value of Expression from Row At) RN) ELSE (CRN - RN) * (Value Of Expression for Row AT FRN) (Value of Expression for Row AT CRN) Note: This function is not found to find the corresponding distribution value in this function and PERCENTILE_DISC The calculation method of the alternative value returned, different SAMPLE: In the following example, the percentile_cont value of the department 60 is calculated as follows: p = 0.7 n = 5 rn = 1 (p * (n-1) = 1 (0.7 * (5) -1)) = 3.8 cRN = CEIL (3.8) = 4 frn = floor (3.8) =
3 (4 - 3.8) * 4800 (3.8 - 3) * 6000 = 5760SELECT last_name, salary, department_id, PERCENTILE_CONT (0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) "Percentile_Cont", PERCENT_RANK () OVER ( Partition by department_id order by sales) "percent_rank" from Employees Where Department_ID IN (30, 60); Last_name Salary Department_Id Percentile_Cont Percent_rank ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - - ---------- -------------------------- -Colmenares 2500 30 3000 0Himuro 2600 30 3000 0.4Baida 2900 30 3000 0.6khoo 3100 30 3000 0.8ralinely 11000 30 3000 1Lorentz 4200 60 5760 0.25PATABALLA 4800 60 5760 0.75HUNOLD 9000 60 5760 0.75HUNOLD 9000 60 5760 1percentile_DISC function description: Returns a data value corresponding to the input distribution percentage value, the calculation method of distribution is described in the function cume_dist, if There is no justified data value, takes a lower value greater than the distribution value.
Note: This function is different from Percentile_Cont's calculation method that the alternative value returned when the corresponding distribution value is not found, the SAMPLE: The distribution value of 0.7 in the following example does not have a corresponding cume_dist value in the department 30, so take the next distribution. value 0.83333333 corresponding SALARY replaced SELECT last_name, salary, department_id, PERCENTILE_DISC (0.7) WITHIN GROUP (ORDER bY salary) OVER (PARTITION bY department_id) "Percentile_Disc", CUME_DIST () OVER (PARTITION bY department_id ORDER bY salary) "Cume_Dist "From Employees Where Department_ID in (30, 60); Last_name Salary Department_ID Percentile_Disc Cume_Dist -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - - ------------------------------------ Colmenares 2500 30 3100 .16666667Himuro 2600 30 3100. 333333333Tobias 2800 30 3100 .5baida 2900 30 3100.66666667khoo 3100 30 3100.833333333Raphaly 11000 30 3100 1Lorentz 4200 60 6000 60 6000 .6pataballa 4800 60 6000.6rnuLD 9000 60 6000 1rank Features Description: According to the value of the expression in the order of the order, each line returned from the query, calculate them The relative position with other rows. The data in the group is sorted by the Order By clause, and then assigns a number to each line to form a sequence, the sequence starts from 1, and it is accumulated. This sequence also increases when the value of the ORDER BY expression changes. Rows with the same value get the same digital sequence number (considered null). However, if the two lines do have the same sort, the number will then jump.
If the number of two lines is 1, there is no order 2, the sequence will assign the value of the next row in the group 3, and Dense_Rank does not jump. Sample: In the following example, each employee is calculated according to the department partition and then sequentially, the serial number appears in turn (note that the difference with the Dense_Rank function) Select D.Department_ID, E.last_name, E.SAlary, Rank () over (Partition By e.department_id ORDER BY e.salary) as drank FROM employees e, departments dWHERE e.department_id = d.department_id AND d.department_id IN ( '60', '90'); DEPARTMENT_ID LAST_NAME SALARY DRANK ------- ------ ------------------------------- - 60 Lorentz 4200 1 60 Austin 4800 2 60 Ernst 6000 4 60 Hunold 9000 5 90 Kochhar 17000 1 90 De Haan 17000 1 90 KING 24000 3RATIO_TO_REPORT Function Description: This function calculates the value of Expression / (SUM (Expression)) It gives the percentage of the total number, that is, the current line of contribution to SUM (Expression). Sample: The following example calculates the salary of each employee accounts for the percentage of the total salary of the employee, SALECT LAST_NAME, SALARY, RATIO_TO_REPORT (SALARY) OVER () AS RR FROM EMPLOYESWHERE JOB_ID = 'PU_Clerk'; Last_Name Salary RR ------- ------------------ ---------- ---------- khoo 3100 .223021583baida 2900.208633094tobias 2800.201438849Himuro 2600. 18705036Colmenares 2500.179856115REGR_ (Linear Regression) Functions Functions Features Description: These linear regression functions are suitable for minimum multiplied regression lines, and 9 different regression functions can be used.
REGR_SLOPE: Returns slope equal COVAR_POP (expr1, expr2) / VAR_POP (expr2) REGR_INTERCEPT: Returns the y-intercept of the regression line, equal AVG (expr1) - REGR_SLOPE (expr1, expr2) * AVG (expr2) REGR_COUNT: Returns the regression line for filling Non-air digital pair REGR_R2: Returns the decision coefficient of the return line, the calculation form is: if var_pop (expr2) = 0 Then return null if var_pop (expr1) = 0 and var_pop (expr2)! = 0 THEN RETURN 1 if var_pop EXPR1)> 0 and var_pop (expr2! = 0 THEN RETURN POWER (Corr (expr1, expr), 2) REGR_AVGX: calculates the average of the argument (expr2) of the returns line, removes the empty pair (expr1, expr2), equal to AVG (Expr2) REGR_AVGY: The average value of the strain (EXPR1) of the returns line is calculated, after removing empty pair (expr1, expr2), equal to AVG (expr1) REGR_SXX: return value equal to REGR_COUNT (expr1, expr2) * var_pop (expr2) REGR_SYY: Return value equal to REGR_COUNT (expr1, expr2) * var_pop (expr1) REGR_SXY: Return value equal to REGR_COUNT (Expr1, Expr2) * Covar_POP (expr1, expr2) (below the following example is completed under the SH user) Sample 1: The following example calculates two products (260 and 270) in the last three weeks in 1998, in the weekend, the number of invoices and total number of cumulative slope and returns lines Select T.fiscal_Month_Number "Month", T.day_Number_i n_month "Day", REGR_SLOPE (s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE, REGR_INTERCEPT (s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_month_desc, t. day_number_in_month) AS CUM_ICPT FROM sales s, times tWHERE s.time_id = t.time_id AND s.prod_id IN (270, 260) AND t.fiscal_year = 1998 AND t.fiscal_week_number IN (50, 51, 52) AND t.day_number_in_week IN (6, 7) Order by t.fiscal_month_desc, t.day_number_in_month;
Month day cum_slope cum_icpt ---------------------------- ---------- 12 12 -68 1872 12 12-6818721213 1254.36735 -20.244898 12 13 12 19 1254.36735 -20.244898 -18.826087 12871220 12 20 62.4561404 62.4561404 125.28655 125.28655 125.28655 12 20 12 20 62.4561404 62.4561404 67.2658228 58.9712313 125.28655 1226 1227 1226 67.2658228 58.9712313 37.5245541 37.5245541 284.958221 1227 284.958221 12 27 37.5245541 284.958221SAMPLE 2: calculation April 1998 the cumulative number of transactions per day under the SELECT UNIQUE t.day_number_in_month embodiment, REGR_COUNT (s.amount_sold, s.quantity_sold) OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month) " REGR_COUNT "from sales s, Times Twhere s.time_id = t.time_id and t.fiscal_year = 1998 and t.fiscal_month_number = 4; DA Y_number_in_month regr_count ---------------------------- 1 825 2 1650 3 2475 4 3300 ... 26 21450 30 22200sample 3: The following example Calculate the number of invoiced returns to the number of votes and total quantities in 1998 SELECT T.FISCAL_MONTH_NUMBER, REGR_R2 (SUM (S.Amount_soLD), SUM (S.Quantity_Sold) over (order by t.fiscal_month_number " REGR_R2 "From Sales S, Times T where s.time_id = t.time_id and t.fiscal_year = 1998 group by t.fiscal_month_number order by t.fiscal_month_number;
FISCAL_MONTH_NUMBER REGR_R2 ---------------------------- 1 2 1 3.927372984 4.932745567 6.932745567 6.94682861 7.965342011 8 .959542618 11.938618575 11.8827691415 12.882769189Sample 4: The following example calculates the number of invoiced tickets and total number of accumulated averages in the last two weeks of product 260 in December 1998, SELECT T. DAY_NUMBER_IN_MONTH, REGR_AVGY ( s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) "Regr_AvgY", REGR_AVGX (s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) "Regr_AvgX" FROM sales s, times t WHERE s.time_id = t.time_id AND s.prod_id = 260 AND t.fiscal_month_desc = '1998-12' AND t.fiscal_week_number IN (51, 52) ORDER BY t.day_number_in_month; DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX-- ----------------- -------- ---------- 14 882 24.5 14 882 24.5 15 801 22.25 15 801 22.25 16 21.6 18 777.6 18 642.857143 642.857143 17.8571429 17.8571429 15.1111111 20 22 21 544 589.5 16.375 22 592.363636 592.363636 16.4545455 16.4545455 24 553.846154 15.3846154 24 553.846154 15.3846154 26 522 14.5 27 578.4 16.0666667sample 5:
The following example calculates the number of tickets and total quantities in the number of tickets and total quantities in the weekend of January 1998, and the total number of accumulated regR_sxy, regr_sxx, and regr_syy statistics SELECT T.DAY_NUMBER_IN_MONTH, REGR_SXY (S.Amount_Sold, S.quantity_Sold) over ( ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxy", REGR_SYY (s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_syy", REGR_SXX (s.amount_sold, s.quantity_sold) OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxx" FROM sales s, times tWHERE s.time_id = t.time_id AND prod_id IN (270, 260) AND t.fiscal_month_desc = '1998-02' AND t.day_number_in_week IN (6,7) Order by t.day_number_in_month; day_number_in_month regr_sxy rar_syy rar_sxx --------------------------------- ----- - -------- 1 18870.4 2116198.4 258.4 1 18870.4 2116198.4 258.4 1 18870.4 2116198.4 258.4 21198.4 7 18870.4 2116198.4 258.4 8 18870.4 2116198.4 258.4 14 18870.4 2116198.4 258.4 15 18870.4 2116198.4 258.4 21 18870.4 2116198.4 258.4 22 18870.4 2116198.4 258.4ROW_NUMBER Function Description: Returns the ordered set the offset line so that the line number can be used for sorting according to specific standards.
SAMPLE: The following example returns the sequence number of each employee and then press the SELECT department_id sorted employee number, last_name, employee_id, ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id FROM employeesWHERE department_id in each sector <50; DEPARTMENT_ID Last_name employee_id Emp_id --------------------------------- - ---------- 10 Whalen 200 1 20 Hartstein 201 20 Fay 202 2 30 Raphaly 114 1 30 khoo 115 2 30 Baida 116 3 30 Tobias 117 4 30 Himuro 118 5 30 Colmenares 119 6 40 MAVRIS 203 1stDDEV Function Description: Calculate the current line of standard deviation from the group.
(Standard Deviation) SAMPLE: The following example returns sector of the cumulative standard salary values sorted by hire date 30 deviates SELECT last_name, hire_date, salary, STDDEV (salary) OVER (ORDER BY hire_date) "StdDev" FROM employees WHERE department_id = 30; LAST_NAME Hire_date Salary Stddev ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- Raphaly 07-2 month -94 11000 0khoo 18-May-95 3100 5586.14357Tobias 24-July-97 2800 4650.0896Baida 24-December -97 2900 4035.26125Himuro 15-November -98 2600 3649.2465 Colmenares 10-August-99 2500 3362.58829StDDEV_POP Function Description: This function calculates the overall standard deviation, and returns the square root of the overall variable, its return value is the same as the square root of the var_pop function.
(Standard Deviation-Population) SAMPLE: The following example returns salary population standard deviation value sectors 20,30,60 SELECT department_id, last_name, salary, STDDEV_POP (salary) OVER (PARTITION BY department_id) AS pop_std FROM employeesWHERE department_id in (20, 30, 60); department_id last_name salary pop_std --------------------------------------- ---------------- 20 Hartstein 13000 3500 20 May 6000 3500 30 Raphaly 11000 3069.6091 30 Khoo 3100 3069.6091 30 Baida 2900 3069.6091 30 Colmenares 2500 3069.6091 30 Tobias 2800 3069.6091 60 Hunold 9000 1722.32401 60 Ernst 6000 1722.32401 60 AUSTIN 4800 1722.32401 60 PATABALLA 4800 1722.32401 60 Lorentz 4200 1722.32401stddev_samp function description: This function calculates the cumulative sample standard deviation, and returns the square root of the overall variable, and its return value is the same as the square root of the var_pop function.
(Standard Deviation-Sample) SAMPLE: The following example returns the value of the sample standard salary department 20,30,60 deviation SELECT department_id, last_name, hire_date, salary, STDDEV_SAMP (salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND Current Row) As cum_sdev from Employeeswhere Department_id in (20, 30, 60); Department_ID Last_name Hire_Date Salary Cum_SDEV --------------------------- ---------- ---------------------------- 20 Hartstein 17-February -96 13000 20 Fay 17-August-97 6000 4949.74747 30 Raphaly 07-December -94 11000 30 khoo 18-May-95 3100 5586.14357 30 Tobias 24-July - 97 2800 4650.0896 30 Baida 24-December -97 2900 4035.26125 30 Himuro 15-November -98 2600 3649.2465 30 Colmenares 10-August -99 2500 3362.58829 60 HUNOLD 03- January -90 9000 60 Ernst 21- May - 91 6000 2121.32034 60 Austin 25-June-97 4800 2163.33077 60 Patabase 05-201-98 4800 1982.42276 60 Lorentz 07- February -99 4200 1925.61678 SUM Function Description: The accumulation of the expression in this function calculation group.
SAMPLE: calculated by the following embodiments the same employee's manager salaries cumulative value SELECT manager_id, last_name, salary, SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees WHERE manager_id in (101,103,108); MANAGER_ID LAST_NAME SALARY L_CSUM ---------- --------------------------- ----- 101 Whalen 4400 4400 101 Mavris 6500 10900 101 Baer 10000 20900 101 Greenberg 12000 44900 101 Higgins 12000 44900 103 Lorentz 4200 4200 103 Austin 4800 13800 103 Pataballa 4800 13800 103 Ernst 6000 19800 108 Popp 6900 6900 108 Sciarra 7700 14600 108 Urman 7800 22400 108 CHEN 8200 30600 108 VAR_POP Function Description: (Variance Population) This function returns the overall variable (ignore null) of non-empty set, VAR_POP performs as follows: (SUM (Expr2) - SUM (Expr) 2 / Count 2 / Count (expr)) / count (expr) Sample: The following example calculates the accumulated overall and sample variables of monthly monthly sales in 1998 (this example runs under the SH user) SELECT T.CALENDAR_MONTH_DESC, VAR_POP (SUM (S.Amount_sold) over ORDER BY T.CALENDAR_MONTH_DESC) "var_pop", var_samp (SUM (SUM (S.AMOUNT_SOLD)) OVER (ORDER BY T.CALENDAR_MONTH_DESC) "VAR_SAMP" from sales s, Times Twhere s.time_id = t.time_id and t.calendar_Year =
1998Group by t.calendar_month_desc; calendar var_pop var_samp -------- ------------------ 1998-01 01998-02 6.1321E 11 1.2264E 121998-03 4.705E 11 7.0587E 111998-04 4.6929E 11 6.2572E 111998-05 1.5524E 12 1.9405E 121998-06 2.3711E 12 2.8453E 121998-07 3.7464E 12 4.3708E 121998-08 3.7852E 12 4.3260E 121998-09 3.5753E 12 4.0222E 121998-10 3.4343E 12 3.8159E 121998-11 3.424E 12 3.7669E 121998-12 4.8937E 12 5.3386E 12var_samp function description: (Variance Sample) This function returns a sample variable (ignore null) of non-empty set, VAR_POP performs as follows: (SUM (Expr * EXPR) -Sum (expr) * SUM (Expr) / Count (expr)) / (Count (expr) -1) Sample: The following example calculates the accumulated overall and sample variables of 1998 monthly sales, var_pop (SUM (SUM (SUM (SUM (SUM (S.AMOUNT_SOLD)) OVER (ORDER BY T.CALENDAR_MONTH_DESC) "Var_POP" , VAR_SAMP (SUM (s.amount_sold)) OVER (ORDER BY t.calendar_month_desc) "Var_Samp" FROM sales s, times tWHERE s.time_id = t.time_id AND t.calendar_year = 1998GROUP BY t.calendar_month_desc; CALENDAR Var_Pop Var_Samp-- --------------------- --- 1998-01 01998-02 6.1321E 11 1.2264E 121998-03 4.7058E 11 7.0587E 111998-04 4.6929E 11 6.2572E 111998-05 1.5524E 12 1.9405E 121998-06 2.3711 E 12 2.8453E 121998-07 3.7464E 12 4.3708E 121998-08 3.7852E 12 4.3260E 121998-09 3.5753E 12 4.022E 121998-10 3.4343E 12 3.8159E 121998-11 3.4245 E 12 3.7669E 121998-12 4.8937E 12 5.3386E 12Variance Function Description: This function returns the variable of the expression, Oracle calculates that the variable is as follows: If the number of banks in the expression is 1, return 0 If the number of banks Greater than 1,
