oracle 中的分析函数
Analytical Functions in ORACLE 8iEdward Kosciuzko, Sequel Consulting, Inc.IntroductionThe purpose of this article is to introduce some of the new analytical functions that were introduced in ORACLE 8i. After reading Oracle’s documentation on the functions, I feel certain that most users will, or did have, trouble understanding exactly what some of the options are. The windowing clause options, in particular, were poorly documented and required a lot of testing to determine exactly what the options were and even more importantly, when they were permitted. Numerous examples are contained in this article to explain the various options.All the functions are not covered here due to time. The regression analysis functions should be self-explanatory after understanding the functions covered in this article. Not being a statistician, some of the statistical functions were avoided like the plague.With my special interest in SQL, these new functions also provided a far superior way of specifying complex queries, plus listing aggregates with the details used to compute the aggregates. Included below are numerous examples, and in certain cases, execution statistics are listed to illustrate the significant performance improvements that can be attained with the new functions.Objective of FunctionsWhile these functions can be implemented by utilizing standard SQL, the benefits are:simplicity of specification• reducing network traffic• moving processing to server• provide superior performance over previous SQL functionsSimplicityIn the early days of Oracle Corporation I would demo ORACLE to prospective clients and tell them that the beauty of the relational approach is any query could be formulated with SQL. Fortunately only one client ever asked me (using ORACLE’s demo database) to list the sum of salaries by department and compare that against all other departments (i.e. what percentage of the total company salaries, each department’s sum represented). Having demonstrated ORACLE for years, I immediately hedged by saying you must first create a view. The following view was required for the solution: CREATE VIEW co_tot_sal (total_sal) ASSELECT SUM(sal) FROM empThe final SQL would then be:SELECT deptno, (SUM(sal)/total_sal)*100FROM emp e, co_tot_sal cGROUP BY deptno, total_salSQL 1The problem here was having the appropriate views created in advance.Another problem encountered years ago was trying to phrase a query to find the top 10 salesmen. To illustrate let’s look at the top 2 salaries in the EMP table.SELECT * FROM emp e1WHERE EXISTS(SELECT null FROM emp e2WHERE e2.sal > e1.salAND e1.rowid != e2.rowidHAVING COUNT(*) <2)SQL 2Specifying SQL like this is beyond the average user, and it’s inefficient because there is no way to inform ORACLE what we are trying to achieve.Reducing Network TrafficMany of the data analysis tools consume large amounts of data that must be transmitted over the network to the client for analysis. The specialized tool then produces the summaries requested. Now the server can be produce and only transmit the summaries.Moving Processing to ServerHow long ago were we thrilled to have PC’s to offload the processing from the server? Now we’re moving it back. Consider the reports we could generate easily using SQL*Plus and the BREAK and COMPUTE commands. Of course you were forced to display the details, but the totaling and subtotaling was performed at the client. This processing was simply rescanning and resorting the results of the query. Now ORACLE performs those functions.Analytical Function vs Standard AggregatesDifferentiating the analytical functions from the standard aggregate functions, such as AVG, SUM, etc, is really based on a similarity. Both type of functions work on sets of values. The way in which the sets are defined is the difference. The standard aggregates would produce a value for each set of rows defined by the GROUP BY function. The analytical functions allow you to also group rows defined by the query, and the value of the analytical function is based on the group of rows. The difference is that the GROUP BY compresses detail rows into a single row, whereas the analytical functions produce a value for each detail row comprising a group. The groups defined by analytical functions are called partitions. The following query uses a standard aggregate with a GROUP BY to produce the sum of salaries per group defined by the same job and deptno.SELECT deptno, job, SUM(sal)FROM empGROUP BY deptno, jobSQL 3DEPTONO JOB10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 600020 CLERK 190020 MANAGER 297530 CLERK 95030 MANAGER 285030 SALESMAN 5600Table 1The following illustrates an analytical function that produces the same sum but lists it with all the details.SELECT empno, deptno, job,SUM(sal) OVER (PARTITION BY deptno, job) sum_salFROM empSQL 4EMPNO DEPTNO JOB SUM_SAL7934 10 CLERK 13007782 10 MANAGER 24507839 10 PRESIDENT 50007788 20 ANALYST 60007902 20 ANALYST 60007369 20 CLERK 19007876 20 CLERK 19007566 20 MANAGER 29757900 30 CLERK 9507698 30 MANAGER 28507499 30 SALESMAN 56007654 30 SALESMAN 56007844 30 SALESMAN 56007521 30 SALESMAN 5600Table 2The main difference at this point to recognize is that the analytical aggregates do not compress the groups of rows into a single row as does the standard aggregate. That means the analytical functions can also be applied to a SQL module containing a GROUP BY. However, when the SQL module does have a GROUP BY the only columns or expressions that can be referenced by the analytical functions are the columns/expressions that are being grouped, plus the other aggregates.PartitionsThe analytical functions operate on groups of rows called partitions. The syntax for the SUM analytical function is as follows:SUM (column/expression) OVER ( [PARTITION BY col/express, [col/express, …] ] )The PARTITION clause is optional. If the PARTITION clause is not used the set of rows operated on by the analytical function is the entire result set. This is analogous to the standard aggregate when there is no GROUP BY clause. For example, the following uses the SUM analytical function to retrieve the total salaries for all EMP rows, and lists it with each individual EMP row, allowing us to determine what percentage of the total salaries an EMP’s salary is.SELECT empno, (sal/SUM(sal) OVER () ) AS percentFROM empSQL 5EMPNO PERCENT7369 .0275624467499 .0551248927521 .0430663227566 .1024978477654 .0430663227698 .0981912147782 .0844099917788 .1033591737839 .1722652897844 .0516795877876 .0378983637900 .0327304057902 .1033591737934 .044788975Table 3Compare this solution with the solution used in SQL 1.Execution PlanSo what’s really happening within ORACLE? The execution plan for SQL 4 in figure 1 shows the sorting used to produce the output of the analytical function in step 2. After the normal criteria and grouping (if a GROUP BY is part of the syntax), a scan and sort is performed on the result set to produce the analytical function output.Figure 1Top or Bottom N ValuesThe top or bottom refers to the rows in a result set that either have the largest (top) or smallest (bottom) values. For instance, in sales it’s important to be able to identify things such as:• top n selling products• top n selling regions• top n salesmen• bottom n selling products• etcSQL 2 above illustrates retrieving the top 2 highest paid employees in the EMP table. ORACLE now provides two analytical functions that ranks the rows in the result set based on a set of columns. There are two functions because ranking semantics has two categories: one where rank values are skipped due to ties and one that doesn’t skip values. The functions are RANK and DENSE_RANK. DENSE_RANK is the one that doesn’t skip values.To illustrate the idea of skipping values, the following SQL ranks the EMP rows by SAL using both functions.SELECT empno, sal, RANK() OVER ( ORDER BY sal) Rank_Values,DENSE_RANK () OVER (ORDER BY sal) Dense_Rank_ValuesFROM empSQL 6The results are displayed in Table 4. Check where the SAL values are the same. The first location is highlighted in yellow. Both EMPNO = 7521 and 7654 have a SAL of 1250. Both the RANK and the DENSE_RANK give the SAL values the same rank; but it’s the subsequent SAL values where the ranking is different. With RANK, since two rows ties for a rank of 4, the rank of 5 is skipped, making 6 the next rank value, whereas with DENSE_RANK rank values are not skipped. The row is highlighted in green (dark shading).EMPNO SAL RANK DENSE_RANK7369 800 1 17900 950 2 27876 1100 3 37521 1250 4 47654 1250 4 47934 1300 6 57844 1500 7 67499 1600 8 77782 2450 9 87698 2850 10 97566 2975 11 107788 3000 12 117902 3000 12 117839 5000 14 12Table 4It should be noted that providing ties with the same rank is important, since they both have the same value.The syntax for the RANK and DENSE_RANK functions are the same. The syntax follows:RANK () OVER ([PARTITION BY col/express [,col/express, …] ]ORDER BY col/express [,…] [ASC|DESC] [NULLS FIRST|NULLS LAST]The RANK function itself does not take an argument. As always, the PARTITION clause, which groups rows of the result set for the input to the analytical function, is optional. If omitted the entire result set is the partition. The RANK and DENSE_RANK require specifying the ORDER BY, since the rows must be sorted by the columns the ranking is applied to. As with the standard ORDER BY, the collation order can be specified with ASC or DESC for each ORDER BY column/expression. And also like the standard ORDER BY, nulls can appear last or first for each order by item. The default depends on whether you are ordering by ASC or DESC. If ordering by ASC, by default nulls will appear last, and the reverse for DESC.Note that the Data Warehousing Guide shows the “[collate clause]”. Who knows what they were thinking, but just disregard it.From QueryEver wonder why ORACLE introduced the ability to place a SQL statement in the FROM clause of a SQL module? Initially it provided a means of sidestepping the creation of a view. The real significance is the ability to filter the results of a SQL statement relative to the selected items. This becomes especially important with analytical functions, since they cannot appear in the WHERE clause. The work-around is to embed the SQL in the FROM clause of another SQL module and then reference the result set in the WHERE clause. For instance, the analogous SQL to produce the results of SQL 2 appears in SQL 7.SELECT empno, sal, rank_valueFROM (SELECT empno, sal,RANK() OVER ( ORDER BY sal DESC) AS rank_valueFROM emp)WHERE rank_value <=2SQL 7The main query, whose results are ranked, is highlighted in bold. In order to return only the top 2, the query must be embedded in a FROM clause and have the WHERE clause filter the rows.First note that the intention is to return the top 2 paid employees. That means we must sort by SAL, and the sort must be in descending order since the first sort row will get the rank of 1. If the rows are sorted in ascending order the rank of 1 identifies the bottom paid employees.EMPNO SAL RANK_VALUE7839 5000 17788 3000 27902 3000 2Table 5To highlight the difference between RANK and DENSE_RANK, consider what SQL 7 would have produced if 2 employees tied for 1st place. Those 2 employees would both have a RANK value of 1, and EMPNO=7788 and 7902 would have a RANK value of 3. But if we used the DESNSE_RANK function both EMPNO = 7788 and 7902 would have a DENSE_RANK of 2. So the criterion “rank_value <=2” works for the RANK function, but would have produced the wrong answer if DENSE_RANK was used.Certain types of top-bottom queries are more complex when the top or bottom members are based on an aggregate. For example, the TIME_SHEETS table lists the hours worked per project per employee. To list the top 5 employees who worked the most hours would require the following solution:SELECT *FROM(SELECT emp_seq , SUM (hours ) AS sum_hrsFROM time_sheetsGROUP BY emp_seq )WHERE 5 >=(SELECT count (count (* ) )FROM time_sheetsGROUP BY emp_seqHAVING SUM (hours ) > sum_hrs )SQL 8Unfortunately SQL 8’s execution would not finish in “your lifetime”. ( I executed the SQL for over 24 hours and then cancelled.) SQL 8 requires grouping the entire table for each employee and then for each employee, the correlated subquery would have to recompute the total hours per employee and filter out those that did not work as many hours. The count is then compared against 5, since we want to list only the top 5 workers. To make this work you have no choice but to embed the initial GROUP BY in the FROM clause of the main SQL module, otherwise there is no way to reference the sum of hours for an employee in the correlated subquery.A more efficient solution follows:SELECT *FROM (SELECT emp_seq, SUM(hours),RANK () OVER (ORDER BY SUM(hours) DESC) AS rnkFROM time_sheetsGROUP BY emp_seq)WHERE rnk <= 5SQL 9Only one grouping of the data is necessary. And performance is reasonable for a TIME_SHEETS table with 13,939,925 rows. The execution statistics are listed in figure 2.Figure 2Note that SQL 8 and 9 did not account for NULLs. In both cases you can simply eliminate the NULLs with a WHERE clause, or in SQL 8, you can order the results and request NULLs to appear first or last. The ORDER BY clause in SQL 9 allows the same type of NULL handling.One final example ranks the employees by their hiredate and birthdate in descending order enabling us to obtain the last 10 employees hired, and if there is a tie, the youngest employee is ranked lower. SQL 10 below uses standard SQL. SELECT emp_seq, hiredate, birthdateFROM employees e1WHERE 10 > (SELECT count(*) FROM employees e2WHERE e2.hiredate > e1.hiredateOR (e2.hiredate = e1.hiredate AND e2.birthdate <= e1.birthdate))SQL 10The complexity of specifying SQL 10 is not intuitive, though it does make sense if you consider the request carefully. It’s basically the subquery that’s difficult. As with the RANK function, if the primary columns, HIREDATE is equal, then the tie breaker is the BIRTHDATE column. So we OR a criterion stating that if the HIREDATE’s are equal, then the BIRTHDATE of the subquery must be less than that of the outer query. For example, the subquery returns, per each employee in the outer query, the number of employees that have a more recent hiredate, plus, when the hiredate is the same, the employee with the lesser birthdate. The complexity only increases as the number of columns involved in the ranking increases. But not so with the RANK function. SQL 11 accomplishes the same task and is trivial compared to SQL 10. Adding more columns for the ranking only means adding the column to the ORDER BY clause of the RANK. SELECT /*+ ALL_ROWS */ *FROM (SELECT emp_seq, hiredate, birthdate,RANK() OVER (ORDER BY hiredate DESC, birthdate ASC) rnkFROM employees)WHERE rnk <= 10SQL 11The execution of SQL 10 was over 30 minutes while using the RANK function in SQL 11 took a fraction of a second. (The EMPLOYEES table contains 15,000 rows.)Ranking SubtotalsWhen performing data analysis using the CUBE or ROLLUP functions, often it’s the subtotals and totals that need to be ranked. The key to specifying the ranking involves the GROUPING function which allows us to determine when the row contains a subtotal or total. GROUPING of a column that is part of the ORDER BY clause of the RANK function returns 1 when the NULL is due to a subtotal or total.Using the EMP and DEPT tables the listing of the average salary by department, all departments, job and all jobs is simple. To filter out the details, use the HAVING clause.SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname,DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal",RANK() OVER (PARTITION BY GROUPING(dname), GROUPING(job)ORDER BY COUNT(*) DESC) AS rnkFROM emp, deptWHERE dept.deptno = emp.deptnoGROUP BY CUBE (dname, job)HAVING GROUPING(dname) = 1 OR GROUPING(job) = 1SQL 12DNAME JOB Total Empl Average Sal RNKSALES All Jobs 6 18800 1RESEARCH All Jobs 5 26100 2ACCOUNTING All Jobs 3 35000 3All Departments CLERK 4 12450 1All Departments SALESMAN 4 16800 1All Departments MANAGER 3 33100 3All Departments ANALYST 2 36000 4All Departments PRESIDENT 1 60000 5All Departments All Jobs 14 24878.5714 1Table 6Windowing FunctionsCertain analytical functions operate on a subset of rows within a partition. These subsets are referred to as windows. There are two types of windows that can be specified; a physical or logical window. Physical means a specific number of rows, whereas logical means the window is based on the ORDER BY value (only one column/expression can occur in the ORDER BY in certain circumstances). The syntax to specify a window follows the ORDER BY syntax (the ORDER BY is mandatory):ROWS | RANGE {{UNBOUNDED PRECEDING | <value expression4> PRECEDING}| BETWEEN {UNBOUNDED PRECEDING | <value expression4> PRECEDING}AND{CURRENT ROW | <value expression4> FOLLOWING}}The ROWS keyword refers to physical window and RANGE, the logical window. The other keywords are relative to the current row. But it’s the current row that has different meanings for physical and logical windows.Logical WindowsTo better understand the difference between physical and logical windows, let’s start with the logical window, since physical windows should be simple enough to understand.The following query uses the EMP table to list the sum of salaries for employees with a lower or equal salary. The logical window only specifies an upper limit.SELECT empno, sal,SUM(sal) OVER (ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_salFROM empSQL 13The results are in table 7 below:EMPNO SAL SUM_SAL7369 800 8007900 950 17507876 1100 28507521 1250 53507654 1250 53507934 1300 66507844 1500 81507499 1600 97507782 2450 122007698 2850 150507566 2975 180257788 3000 240257902 3000 240257839 5000 29025Table 7The rows in yellow (shading) both have the same SUM_SAL value. This is the key to understanding logical windows. The point here is that CURRENT ROW refers to all rows have the same value of the ORDER BY column. Since both highlighted employees have the same SAL, both values are added to the sum for EMPNO=7521.To further illustrate the point, the following query computes the sum of the DEPTNO values (forget the query makes no sense).SELECT empno, sal,SUM(deptno) OVER (ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_deptnoFROM empSQL 14The results follow:EMPNO DEPTNO SAL SUM_DEPTNO7369 20 800 207900 30 950 507876 20 1100 707521 30 1250 1307654 30 1250 1307934 10 1300 1407844 30 1500 1707499 30 1600 2007782 10 2450 2107698 30 2850 2407566 20 2975 2607788 20 3000 3007902 20 3000 3007839 10 5000 310Table 8The yellow (light shaded) highlighted row in table 8 has other DEPTNO values of 30, but the window is based on equal orless values of SAL, since the ORDER BY is on SAL. The red (dark shaded) rows have the same SAL value, so theSUM_DEPTNO value is the same for both rows.Date IntervalsIf the ORDER BY is over a date column, it would helpful to specify an interval without having to consider the actual physical values. When using a logical window (only with logical windows) specification and the ORDER BYcolumn/expression is a date, you can easily specify date intervals in terms of days, months or years. This feature gives you the ability to specify sliding date windows for requests, such as summarizing outstanding invoices. Combine this with the CASE function and you can easily request invoices “30 days outstanding”, “60 days…”, etc.To illustrate some of the interval syntax, I downloaded historical stock pricing for ORCL from ’01-Dec-00’ to ’14-Dec-01’. The moving average for 30 days is returned in SQL 15, along with the average for the next 30 days from the current date.SELECT quote_date, close,AVG(close) OVER (ORDER BY quote_dateRANGE INTERVAL '30' DAY PRECEDING) AS prv_30,AVG(close) OVER (ORDER BY quote_dateRANGE BETWEEN CURRENT ROWAND INTERVAL '30' DAY FOLLOWING) AS fol_30FROM stock_quotesSQL 15When BETWEEN is not used, the value supplied is considered the start-point by ORACLE and the end-point if the current row. So PRV_30 averages the stock prices from 30 days preceding the current row. FOL_30 averages the price from the current row till 30 days following.If you want to compare PRV_30 and FOL_30, embed the SQL in a FROM clause. For example if SQL 15 was embedded in a FROM clause, a criterion could be applied to the outer query to return only those rows where the difference between PRV_30 and FOL_30 is more than 25% of PRV_30. Other types of analysis can easily be performed to compare an increase in the moving average with the change in volume.The Data Warehousing Guide illustrates the INTERVAL syntax using DAYS/MONTHS/YEARS. Drop the S in the time categories to compile without error. I couldn’t find anything in the SQL Reference Manual.ORACLE provides two other functions to assist in the specification of a time interval; NUMTODSINTERVAL and NUMTOYMINTERVAL. The syntax is as follows:NUMTODSINTERVAL (n, ‘DAY|HOUR|MINUTE|SECOND’)NUMTOYMINTERVAL (n, ‘YEAR|MONTH’)The DS in NUMTODSINTERVAL stands for Day or Second. The YM stands for Year and Month. So if you want to use another numeric column as the first parameter of the NUMTO_DS_INTERVAL, you can. Using the STOCK_QUOTES table, you can specify a logical window as:RANGE NUMTODSINTERVAL (open, 'DAY') PRECEDINGThe Unwritten DocumentationI only hope the folks that write the instructions for nuclear power plants are better than Oracle’s documentation crew. The following query drove me crazy trying to figure out what in the world was happening. It deals with a logical window defined by ‘n’ PRECEDING or FOLLOWING. SQL 16 below was initially used to test the features.SELECT emp_seq, effective_date, sal,MAX(sal) OVER (ORDER BY effective_date DESCRANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS Max_SalFROM sal_historySQL 16So in the logical world, what does “1 PRECEDING” mean? Using the previous knowledge that was also not documented well, the CURRENT ROW should refer to the group of rows having the same EFFECTIVE_DATE since that’s what we ordered by. Does ‘1 PRECEDING’ mean the previous logical group? The results of the query are displayed in table 9.EMP_SEQ EFFECTIVE_DATE SAL MAX_SAL1015 11-JAN-01 500 5001001 06-JAN-01 300 3001003 06-JAN-01 200 3001015 06-JAN-01 300 3001001 01-JAN-01 200 2001003 01-JAN-01 100 2001002 01-JAN-01 150 2001015 01-JAN-01 200 2001001 22-DEC-00 100 10001007 22-DEC-00 400 10001009 22-DEC-00 1000 1000Table 9The rows in the same logical group are highlighted with the same color. If ‘1 PRECEDING’ actually meant one logical row preceding the current row, then MAX_SAL for 1001 should be 500, but instead it’s 300 which is the maximum SAL for that logical group. The same goes all the other logical groups.So to make sense out of this, you first have to consider what the rows in the partition are ordered by; a date column. It turns out that since the sort column is a date column ‘1 PRECEDING’ means ‘1 DAY PRECEDING’. To check this out, change the 1 to a 5 since ’11-JAN-01’ is 5 days after ’06-JAN-01’.SELECT emp_seq, effective_date, sal,MAX(sal) OVER (ORDER BY effective_date DESCRANGE BETWEEN 5 PRECEDING AND CURRENT ROW) AS Max_SalFROM sal_historySQL 17EMP_SEQ EFFECTIVE_DATE SAL MAX_SAL1015 11-JAN-01 500 5001001 06-JAN-01 300 5001003 06-JAN-01 200 5001015 06-JAN-01 300 5001001 01-JAN-01 200 3001003 01-JAN-01 100 3001002 01-JAN-01 150 3001015 01-JAN-01 200 3001001 22-DEC-00 100 10001007 22-DEC-00 400 10001009 22-DEC-00 1000 1000Table 10Now what happens when the ORDER BY column is a numeric? The following is similar to SQL 17 except the ORDER BY is by SAL.SELECT emp_seq, effective_date, sal,MAX(sal) OVER (ORDER BY sal DESCRANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS Max_SalFROM sal_historySQL 18If you look at the results it’s clear that ‘1 PRECEDING doesn’t mean 1 logical row. Just like the date field, it means units of SAL. SQL 19 uses a value of 100.SELECT emp_seq, effective_date, sal,MAX(sal) OVER (ORDER BY sal DESCRANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS Max_SalFROM sal_historySQL 19The results are:EMP_SEQ EFFECTIVE_DATE SAL MAX_SAL1009 22-DEC-00 1000 10001015 11-JAN-01 500 5001007 22-DEC-00 400 5001001 06-JAN-01 300 4001015 06-JAN-01 300 4001003 06-JAN-01 200 3001015 01-JAN-01 200 3001001 01-JAN-01 200 3001002 01-JAN-01 150 2001003 01-JAN-01 100 2001001 22-DEC-00 100 200Table 11Now the results make sense. Logical appears to always refer to the value of the ORDER BY. That might explain why logical windows are limited to one ORDER BY column/expression when a specific numeric value is given for the PRECEDING keyword. The next logical question is what about sorting by a character column. This is something else that is never mentioned in the manuals. I tried the following SQL to see what it would generate.SELECT empno, job, MAX(sal) OVER (ORDER BY jobRANGE 1 PRECEDING) max_jobFROM empSQL 20And all it generated was error “ORA-00902: invalid datatype”. So I guess we should assume that you just can’t do that; but as you’ll see you can sort by character columns when the window is a physical window.Physical WindowsPhysical windows are pretty straightforward, except for when the window is limited by the number of rows. For instance,you can specify the end-points as either the boundaries of the partition, or a specified number of rows. Just use ROWS instead of RANGE to indicate a physical window. SQL 20 is rewritten below as a physical window instead.:SELECT empno, job, MAX(sal) OVER (ORDER BY jobROWS 1 PRECEDING) max_jobFROM empSQL 21The results are as you would expect. So where would you use a physical window? A good example is historical data. For example, the SAL_HISTORY table contains a history of all salaries per employee. To determine the amount of each raise requires sorting the rows per employee in descending order and then comparing the current row with the next row. Since the last row in each partition (by EMP_SEQ) is the first salary assigned the employee, there was no raise, thus returning zero. We must eliminate the last row of each partition.The LAST_VALUE function allows us to select the last row in the window. FIRST_VALUE selects the first row. SELECT emp_seq, sal, effective_date, sal - LAST_VALUE(sal) OVER(PARTITION BY emp_seq ORDER BY effective_date DESCROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS raise,MIN(effective_date) OVER (PARTITION BY emp_seq ORDER BY effective_date)AS first_salFROM sal_historySQL 22The MIN function is included to get the date per employee when the employee was first given a salary. We can use that to compare with the EFFFECTIVE_DATE. If they are equal then we don’t return the row. The results in table 12 illustrates the data from SQL 22.EMP_SEQ SAL EFFECTIVE_DATE RAISE FIRST_SAL1001 300 06-JAN-01 100 22-DEC-001001 200 01-JAN-01 100 22-DEC-001001 100 22-DEC-00 0 22-DEC-001002 150 01-JAN-01 0 01-JAN-011003 200 06-JAN-01 100 01-JAN-011003 100 01-JAN-01 0 01-JAN-011007 400 22-DEC-00 0 22-DEC-001009 1000 22-DEC-00 0 22-DEC-001015 500 11-JAN-01 200 01-JAN-011015 300 06-JAN-01 100 01-JAN-011015 200 01-JAN-01 0 01-JAN-01Table 12Each partition is shaded in a different color. The first SAL_HISTORY row for each employee has theEFFECTIVE_DATE and FIRST_SAL in bold making it easy to see which row to exclude.Recall that in order to compare the aggregate with the column we need to embed the query in a FROM clause and then use a WHERE clause to filter out the first SAL_HISTORY row per employee. The final solution is SQL 23.SELECT *FROM (SELECT emp_seq, sal, effective_date, sal - LAST_VALUE(sal) OVER(PARTITION BY emp_seq ORDER BY effective_date DESCROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS raise,。
ORACLE_分析函数大全
ORACLE_分析函数大全Oracle分析函数是一种高级SQL函数，它可以在查询中实现一系列复杂的分析操作。
这些函数可以帮助我们在数据库中执行各种数据分析和报表生成任务。
本文将介绍Oracle数据库中的一些常用分析函数。
1.ROW_NUMBER函数：该函数为查询结果中的每一行分配一个唯一的数字。
可以用它对结果进行排序或分组。
例如，可以使用ROW_NUMBER函数在结果集中为每个员工计算唯一的编号。
2.RANK和DENSE_RANK函数：这两个函数用于计算结果集中每个行的排名。
RANK函数返回相同值的行具有相同的排名，并且下一个排名值将被跳过。
DENSE_RANK函数类似，但是下一个排名值不会被跳过。
G和LEAD函数：LAG函数返回结果集中指定列的前一个（上一个）行的值，而LEAD函数返回后一个（下一个）行的值。
这些函数通常用于计算增长率或发现趋势。
4.FIRST和LAST函数：这两个函数用于返回结果集中分组的第一个和最后一个行的值。
可以与GROUPBY子句一起使用。
5.CUME_DIST函数：该函数用于计算给定值的累积分布。
它返回值的累积分布在结果集中的位置（百分比）。
6.PERCENT_RANK函数：该函数用于计算结果集中每个行的百分位数排名。
它返回值的百分位数排名（0到1之间的小数）。
7. NTILE函数：该函数用于将结果集分成指定数量的桶（Bucket），并为每个行分配一个桶号。
通常用于将数据分组为更小的块。
8.LISTAGG函数：该函数将指定列的值连接成一个字符串，并使用指定的分隔符分隔每个值。
可以用它将多个值合并在一起形成一个字符串。
9.AVG、SUM、COUNT和MAX/MIN函数：这些是常见的聚合函数，可以在分析函数中使用。
它们用于计算结果集中的平均值、总和、计数和最大/最小值。
以上只是Oracle数据库中的一些常用分析函数。
还有其他一些分析函数，如PERCENTILE_CONT、PERCENTILE_DISC等可以用于更高级的分析计算。
Oracle分析函数用法详解
Oracle分析函数Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
Oracle从8.1.6开始提供分析函数。
一、基本语法oracle分析函数的语法：function_name(arg1,arg2,...)over(<partition-clause> <order-by-clause ><windowing clause>)说明：1.partition-clause 数据记录集分组2.order-by-clause 数据记录集排序3.windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。
有三种开窗方式: range、row、specifying。
二、常用分析函数1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t;DEPTNO EMPNO SAL AVG_SAL---------- ---------- ---------- ----------10 7782 2450 2916.666677839 5000 2916.666677934 1300 2916.6666720 7566 2975 21757902 3000 21757876 1100 21757369 800 21757788 3000 217530 7521 1250 1566.666677844 1500 1566.666677499 1600 1566.666677900 950 1566.666677698 2850 1566.666677654 1250 1566.666672.count(<distinct><*><expression>) 对组内数据进行计数3．rank() 和dense_rank()dense_rank()根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。
Oracle 分析函数的使用
Oracle 分析函数的使用Oracle 分析函数使用介绍分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1. 自动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. lag,lead函数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据Code: [Copy to clipboard]06:34:23 SQL> select * from t;BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- --------------200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.Elapsed: 00:00:00.001. 使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:50 2 from t06:43:51 3 group by area_code06:43:57 4 union all06:44:00 5 select '合计' area_code,sum(local_fare) local_fare06:44:06 6 from t06:44:08 7 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计 333157065.316 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=24884)1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)4 1 SORT (AGGREGATE)5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=17017)Statistics----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size561 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed下面是使用分析函数rollup得出的汇总数据的例子06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合计'))06:45:50 4 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19333157065.316 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309Bytes=24871)1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size557 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.1. 使用cube函数的介绍Quote:为了介绍cube函数我们再来看看另外一个使用rollup的例子06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare06:53:37 2 from t06:53:38 3 group by rollup(area_code,bill_month)06:53:49 4 /---------- --------------- --------------5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19333157065.3126 rows selected.Elapsed: 00:00:00.00系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.下面,让我们看看使用cube函数的结果06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:58:30 2 from t06:58:32 3 group by cube(area_code,bill_month)06:58:42 4 order by area_code,bill_month nulls last06:58:57 5 /---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72200405 79899.53200406 82588.15200407 84168.03200408 86501.34333157.0530 rows selected.Elapsed: 00:00:00.01可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果1 rollup 和cube函数的再深入Quote:从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,3 sum(local_fare) local_fare4 from t5 group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL> /AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.2. rank函数的介绍介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.问题2.我想查出这几个月份中各个地区的总话费的排名.Quote:为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.1 update t t1 set local_fare = (2 select local_fare from t t23 where t1.bill_month = t2.bill_month4 and _type = _type5 and t2.area_code = '5761'6* ) where area_code = '5763'07:19:18 SQL> /8 rows updated.Elapsed: 00:00:00.01我们先使用rank函数来计算各个地区的话费排名.07:34:19 SQL> select area_code,sum(local_fare) local_fare,07:35:25 2 rank() over (order by sum(local_fare) desc) fare_rank07:35:44 3 from t07:35:45 4 group by area_codee07:35:50 507:35:52 SQL> select area_code,sum(local_fare) local_fare,07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank07:36:20 3 from t07:36:21 4 group by area_code07:36:25 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 45762 52039.62 5Elapsed: 00:00:00.01我们可以看到红色标注的地方出现了,跳位,排名3没有出现下面我们再看看dense_rank查询的结果.07:36:26 SQL> select area_code,sum(local_fare) local_fare,07:39:16 2 dense_rank() over (order by sum(local_fare) desc ) fare_rank 07:39:39 3 from t07:39:42 4 group by area_code07:39:46 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4Elapsed: 00:00:00.00在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处1 select area_code,sum(local_fare) local_fare,2 row_number() over (order by sum(local_fare) desc ) fare_rank3 from t4* group by area_code07:44:50 SQL> /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.2. rank函数的介绍a. 取出数据库中最后入网的n个用户select user_id,tele_num,user_name,user_status,create_datefrom (select user_id,tele_num,user_name,user_status,create_date,rank() over (order by create_date desc) add_rankfrom user_info)where add_rank <= :n;b.根据object_name删除数据库中的重复记录create table t as select obj#,name from sys.obj$;再insert into t1 select * from t1 数次.delete from t1 where rowid in (select row_id from (select rowid row_id,row_number() over (partition by obj# order by rowid ) rn ) where rn <> 1);c. 取出各地区的话费收入在各个月份排名.SQL> select bill_month,area_code,sum(local_fare) local_fare,2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank3 from t4 group by bill_month,area_code5 /BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK--------------- --------------- -------------- ----------200405 5765 25057.74 1200405 5761 13060.43 2200405 5763 13060.43 2200405 5762 12643.79 4200405 5764 12487.79 5200406 5765 26058.46 1200406 5761 13318.93 2200406 5763 13318.93 2200406 5764 13295.19 4200406 5762 12795.06 5200407 5765 26301.88 1200407 5761 13710.27 2200407 5763 13710.27 2200407 5764 13444.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2200408 5764 13929.69 4200408 5762 13376.47 520 rows selected.SQL>3. lag和lead函数介绍取出每个月的上个月和下个月的话费总额1 select area_code,bill_month, local_fare cur_local_fare,2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,4 lead(local_fare,1,0) over (partition by area_code order by bill_month )next_local_fare,5 lead(local_fare,2,0) over (partition by area_code order by bill_month )post_local_fare6 from (7 select area_code,bill_month,sum(local_fare) local_fare8 from t9 group by area_code,bill_month10* )SQL> /AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARELAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE--------- ---------- -------------- -------------- --------------- --------------- ---------------5761 200405 13060.433 0 0 13318.93 13710 .2655761 200406 13318.93 0 13060.433 13710.265 14 135.7815761 200407 13710.265 13060.433 13318.93 14135.7815761 200408 14135.781 13318.93 13710.265 05762 200405 12643.791 0 0 12795.06 13224 .2975762 200406 12795.06 0 12643.791 13224.297 13 376.4685762 200407 13224.297 12643.791 12795.06 13376.4685762 200408 13376.468 12795.06 13224.297 05763 200405 13060.433 0 0 13318.93 13710 .2655763 200406 13318.93 0 13060.433 13710.265 14 135.7815763 200407 13710.265 13060.433 13318.93 14135.7815763 200408 14135.781 13318.93 13710.265 05764 200405 12487.791 0 0 13295.187 1344 4.0935764 200406 13295.187 0 12487.791 13444.093 1 3929.6945764 200407 13444.093 12487.791 13295.187 13929.6945764 200408 13929.694 13295.187 13444.093 05765 200405 25057.736 0 0 26058.46 26301 .8815765 200406 26058.46 0 25057.736 26301.881 27 130.6385765 200407 26301.881 25057.736 26058.46 27130.6385765 200408 27130.638 26058.46 26301.881 020 rows selected.利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.4. sum,avg,max,min移动计算数据介绍计算出各个连续3个月的通话费用的平均数1 select area_code,bill_month, local_fare,2 sum(local_fare)3 over ( partition by area_code4 order by to_number(bill_month)5 range between 1 preceding and 1 following ) "3month_sum",6 avg(local_fare)7 over ( partition by area_code8 order by to_number(bill_month)9 range between 1 preceding and 1 following ) "3month_avg",10 max(local_fare)11 over ( partition by area_code12 order by to_number(bill_month)13 range between 1 preceding and 1 following ) "3month_max",14 min(local_fare)15 over ( partition by area_code16 order by to_number(bill_month)17 range between 1 preceding and 1 following ) "3month_min"18 from (19 select area_code,bill_month,sum(local_fare) local_fare20 from t21 group by area_code,bill_month22* )SQL> /AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg3month_max 3month_min--------- ---------- ---------------- ---------- ---------- ---------- ----------5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5761 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5761 200407 13710.265 41164.976 13721.6587 14135.781 13318.93 40089.628 = 13060.433 + 13318.930 + 13710.26513363.2093 = (13060.433 + 13318.930 + 13710.265) / 313710.265 = max(13060.433 + 13318.930 + 13710.265)13060.433 = min(13060.433 + 13318.930 + 13710.265)5761 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5762 200405 12643.791 25438.851 12719.4255 12795.06 12643.791 5762 200406 12795.060 38663.148 12887.716 13224.297 12643.791 5762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06 5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.297 5763 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5763 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5763 200407 13710.265 41164.976 13721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5764 200405 12487.791 25782.978 12891.489 13295.187 12487.791 5764 200406 13295.187 39227.071 13075.6903 13444.093 12487.791 5764 200407 13444.093 40668.974 13556.3247 13929.694 13295.187 5764 200408 13929.694 27373.787 13686.8935 13929.694 13444.093 5765 200405 25057.736 51116.196 25558.098 26058.46 25057.736 5765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736 5765 200407 26301.881 79490.979 26496.993 27130.638 26058.46 5765 200408 27130.638 53432.519 26716.2595 27130.638 26301.88120 rows selected.5. ratio_to_report函数的介绍Quote:1 select bill_month,area_code,sum(local_fare) local_fare,2 ratio_to_report(sum(local_fare)) over3 ( partition by bill_month ) area_pct4 from t5* group by bill_month,area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on bill_monthSQL> compute sum of area_pct on bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT---------- --------- ---------------- ----------200405 5761 13060.433 .1711492795762 12643.791 .1656894315763 13060.433 .1711492795764 12487.791 .1636451435765 25057.736 .328366866********** ---------------- ----------sum 76310.184 1200406 5761 13318.930 .1690507725762 12795.060 .1624015425763 13318.930 .1690507725764 13295.187 .1687494145765 26058.460 .330747499********** ---------------- ----------sum 78786.567 1200407 5761 13710.265 .1705451975762 13224.297 .1645001275763 13710.265 .1705451975764 13444.093 .1672342215765 26301.881 .327175257********** ---------------- ----------sum 80390.801 1200408 5761 14135.781 .1709111475762 13376.468 .1617305395763 14135.781 .1709111475764 13929.694 .1684194165765 27130.638 .328027751********** ---------------- ----------sum 82708.362 120 rows selected.6 first,last函数使用介绍Quote:取出每月通话费最高和最低的两个用户.1 select bill_month,area_code,sum(local_fare) local_fare,2 first_value(area_code)3 over (order by sum(local_fare) desc4 rows unbounded preceding) firstval,5 first_value(area_code)6 over (order by sum(local_fare) asc7 rows unbounded preceding) lastval8 from t9 group by bill_month,area_code10* order by bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL ---------- --------- ---------------- --------------- ---------------200405 5764 12487.791 5765 5764200405 5762 12643.791 5765 5764200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 **** ****200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 576420 rows selected.。
oralce函数
oralce函数Oracle是一种关系数据库管理系统，它使用了一种名为Oracle数据库的数据库管理系统。
Oracle是一种强大的工具，提供了许多内置函数，可以用于在数据库中进行各种操作。
以下是一些常用的Oracle函数。
1.聚合函数-AVG：计算指定列的平均值。
-COUNT：计算指定列中非空数据的数量。
-SUM：计算指定列的总和。
-MAX：找到指定列的最大值。
-MIN：找到指定列的最小值。
2.字符串函数-CONCAT：将两个字符串连接成一个字符串。
-LOWER：将字符串转换为小写。
-UPPER：将字符串转换为大写。
-LENGTH：计算字符串的长度。
-SUBSTR：返回一个字符串的子字符串。
3.数值函数-ROUND：将一个数值四舍五入到指定的小数位数。
-CEIL：向上取整，返回不小于指定数值的最小整数。
-FLOOR：向下取整，返回不大于指定数值的最大整数。
-ABS：返回指定数值的绝对值。
-MOD：返回两个数值的余数。
4.日期和时间函数-SYSDATE：返回当前日期和时间。
-ADD_MONTHS：在指定日期上增加指定的月份。
-TRUNC：截断日期或时间到指定的精度。
-MONTHS_BETWEEN：计算两个日期之间的月数差。
-TO_CHAR：将日期转换为指定格式的字符串。
5.条件函数-DECODE：根据条件返回不同的值。
-CASE：根据条件执行不同的操作。
-NVL：如果给定的表达式为NULL，则将其替换为指定的值。
-NULLIF：如果两个表达式的值相等，则返回NULL。
6.分析函数-ROW_NUMBER：为每一行分配一个唯一的数字。
-RANK：为每一行分配一个排名，如果有并列的值，则排名相同。
-DENSE_RANK：为每一行分配一个排名，如果有并列的值，则排名可以重复。
-LEAD：返回指定行后的值。
-LAG：返回指定行前的值。
上述函数只是Oracle提供的一小部分功能，Oracle还提供了许多其他有用的函数。
ORACLE中的ROW_NUMBEROVER分析函数的用法
ORACLE中的ROW_NUMBEROVER分析函数的用法ROW_NUMBER(OVER(是ORACLE数据库中的一个分析函数，用来为结果集中的每一行分配一个唯一的序号。
ROW_NUMBER(OVER(的语法是：ROW_NUMBER( OVER ( [ PARTITION BY expr1 [, expr2, ...] ]ORDER BY clause )其中，PARTITIONBY子句可选，用来指定分区依据的列或表达式；ORDERBY子句用来指定排序的列或表达式。
ROW_NUMBER(OVER(常用在查询结果需要进行分页或者进行排序后获取前几行的场景中。
以下是ROW_NUMBER(OVER(的用法示例：示例1：查询员工表中每个部门的员工数，并按照员工数降序排序。
SELECT department_id, count(*) as employee_count,ROW_NUMBER( OVER (ORDER BY count(*) DESC) as rankFROM employeesGROUP BY department_idORDER BY count(*) DESC;在这个示例中，ROW_NUMBER(OVER(函数根据部门中的员工数进行降序排序，并为每个部门分配一个唯一的序号。
示例2：查询员工表中每个部门的员工数，并按照员工数降序排序，并且只返回前三名。
SELECT department_id, count(*) as employee_count,ROW_NUMBER( OVER (ORDER BY count(*) DESC) as rankFROM employeesGROUP BY department_idWHERE rank <= 3ORDER BY count(*) DESC;在这个示例中，ROW_NUMBER(OVER(函数的结果用于限制查询结果只返回前三名。
示例3：查询员工表中每个部门的员工信息，并按照部门和薪水进行排序。
Oracle分析函数row_number（）over（partitionbyorderby）
Oracle分析函数row_number（）over（partitionbyorderby）1、格式row_number() over(partition by 列名1 order by 列名2 desc)2、解析表⽰根据列名1 分组，然后在分组内部根据列名2 排序，⽽此函数计算的值就表⽰每组内部排序后的顺序编号,可以⽤于去重复值与rownum的区别在于：使⽤rownum进⾏排序的时候是先对结果集加⼊伪列rownum然后再进⾏排序，⽽此函数在包含排序从句后是先排序再计算⾏号码．3、实例--分析函数SELECT USER_NAME,SCHOOL,DEPART,ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY SCHOOL, DEPART DESC)FROM USER_M;结果--分析函数SELECT *FROM (SELECT USER_NAME,SCHOOL,DEPART,ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY SCHOOL, DEPART DESC) RNFROM USER_M)WHERE RN = 1;结果--结合分页SELECT *FROM (SELECT ER_NAME,A.SCHOOL,A.DEPART,ROW_NUMBER() OVER(PARTITION BY SCHOOL ORDER BY USER_NAME, DEPART DESC) RNFROM (SELECT * FROM USER_M) AWHERE ROWNUM <= 10)WHERE RN >= 1;结果。
oracle常用的分析函数
oracle常⽤的分析函数常⽤的分析函数如下所列:row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order by ...)lead() over(partition by ... order by ...)⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）(1)rank函数返回⼀个唯⼀的值，除⾮遇到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间空出排名。
(2)dense_rank函数返回⼀个唯⼀的值，除⾮当碰到相同数据时，此时所有相同数据的排名都是⼀样的。
(3)row_number函数返回⼀个唯⼀的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
(4)ntile是要把查询得到的结果平均分为⼏组，如果不平均则分给第⼀组。
例如：create table s_score( s_id number(6),score number(4,2));insert into s_score values(001,98);insert into s_score values(002,66.5);insert into s_score values(003,99);insert into s_score values(004,98);insert into s_score values(005,98);insert into s_score values(006,80);selects_id,score,rank() over(order by score desc) rank --按照成绩排名，纯排名,dense_rank() over(order by score desc) dense_rank --按照成绩排名，相同成绩排名⼀致,row_number() over(order by score desc) row_number --按照成绩依次排名,ntile(3) over (order by score desc) group_s --按照分数划分成绩梯队from s_score;排名/排序的时候，有时候，我们会想到利⽤伪列row_num，利⽤row_num确实可以解决某些场景下的问题(但是相对也⽐较复杂)，⽽且有些场景下的问题却很难解决。
Oracle之分析函数
Oracle之分析函数⼀、分析函数 1、分析函数 分析函数是Oracle专门⽤于解决复杂报表统计需求的功能强⼤的函数，它可以在数据中进⾏分组然后计算基于组的某种统计值，并且每⼀组的每⼀⾏都可以返回⼀个统计值。
2、分析函数和聚合函数的区别 普通的聚合函数⽤group by分组，每个分组返回⼀个统计值，⽽分析函数采⽤partition by分组，并且每组每⾏都可以返回⼀个统计值。
3、分析函数的形式 分析函数带有⼀个开窗函数over()，包含分析⼦句。
分析⼦句⼜由下⾯三部分组成： partition by ：分组⼦句，表⽰分析函数的计算范围，不同的组互不相⼲； ORDER BY：排序⼦句，表⽰分组后，组内的排序⽅式； ROWS/RANGE：窗⼝⼦句，是在分组(PARTITION BY)后，组内的⼦分组（也称窗⼝），此时分析函数的计算范围窗⼝，⽽不是PARTITON。
窗⼝有两种，ROWS和RANGE； 使⽤形式如下：OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd) 注：窗⼝⼦句在这⾥我只说rows⽅式的窗⼝,range⽅式和滑动窗⼝也不提。
⼆、OVER() 函数 1、sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执⾏顺序 分析函数是在整个sql查询结束后(sql语句中的order by的执⾏⽐较特殊)再进⾏的操作, 也就是说sql语句中的order by也会影响分析函数的执⾏结果： [1] 两者⼀致：如果sql语句中的order by满⾜分析函数分析时要求的排序，那么sql语句中的排序将先执⾏,分析函数在分析时就不必再排序； [2] 两者不⼀致：如果sql语句中的order by不满⾜分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执⾏排序。
2、分析函数中的分组/排序/窗⼝分析函数包含三个分析⼦句：分组(partition by)，排序(order by)，窗⼝(rows/range)窗⼝就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗⼝中的记录⽽不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗⼝指定到该分组中的第⼀⾏数据到当前⾏, 如果你指定该窗⼝从该分组中的第⼀⾏到最后⼀⾏,那么该组中的每⼀个sum值都会⼀样，即整个组的总和。
Oracle数据库分析函数用法
Oracle数据库分析函数⽤法⽬录1、什么是窗⼝函数？2、窗⼝函数——开窗3、⼀些分析函数的使⽤⽅法4、OVER()参数——分组函数5、OVER()参数——排序函数1、什么是窗⼝函数？窗⼝函数也属于分析函数。
Oracle从8.1.6开始提供窗⼝函数，窗⼝函数⽤于计算基于组的某种聚合值，窗⼝函数指定了分析函数⼯作的数据窗⼝⼤⼩，这个数据窗⼝⼤⼩可能会随着⾏的变化⽽变化。
与聚合函数的不同之处是：对于每个组返回多⾏，⽽聚合函数对于每个组只返回⼀⾏基本语法: ‹分析函数› over (partition by ‹⽤于分组的列名› order by ‹⽤于排序的列名›)。
语法中的‹分析函数›主要由序列函数（rank、dense_rank和row_number等组成）与聚合函数（sum、avg、count、max和min等）作为窗⼝函数组成。
从窗⼝函数组成上看，它是group by 和 order by的功能组合，group by分组汇总后改变了表的⾏数，⼀⾏只有⼀个类别，⽽partiition by则不会减少原表中的⾏数。
恰如窗⼝函数的组成，它同时具有分组和排序的功能，且不减少原表的⾏数。
OVER 关键字表⽰把函数当成窗⼝函数⽽不是聚合函数。
SQL 标准允许将所有聚合函数⽤做窗⼝函数，使⽤ OVER 关键字来区分这两种⽤法。
2、窗⼝函数——开窗OVER 关键字后的括号中经常添加选项⽤以改变进⾏聚合运算的窗⼝范围。
如果 OVER 关键字后的括号中的选项为空，则窗⼝函数会对结果集中的所有⾏进⾏聚合运算。
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)为什么叫开窗呢？因为在over()括号中的，partition() 函数可以将查询到的数据进⾏单独开⼀个窗⼝处理。
譬如，查询每个班级的学⽣的排名情况，查询每个国家的历年⼈⼝等，诸如此类，都是在查询到的每⼀个班级、每⼀个国家中都开⼀个窗⼝，单独去执⾏命令。