OLAP specification

On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing aggregate function information as a scalar value in a query result.

OLAP-specification
Read syntax diagramSkip visual syntax diagramordered-OLAP-specificationnumbering-specificationaggregation-specification
ordered-OLAP-specification
Read syntax diagramSkip visual syntax diagramCUME_DIST ()PERCENT_RANK ()RANK ()DENSE_RANK ()NTILE(‚ÄČnum-tiles)lag-functionlead-functionOVER( window-partition-clause window-order-clause)
lag-function
Read syntax diagramSkip visual syntax diagramLAG(expression ,offset,default-value,'RESPECT NULLS','IGNORE NULLS' )
lead-function
Read syntax diagramSkip visual syntax diagramLEAD(expression ,offset,default-value,'RESPECT NULLS','IGNORE NULLS' )
window-partition-clause
Read syntax diagramSkip visual syntax diagramPARTITION BY ,partitioning-expression
window-order-clause
Read syntax diagramSkip visual syntax diagramORDER BY,sort-key-expressionasc-optiondesc-optionORDER OFtable-designator
asc-option
Read syntax diagramSkip visual syntax diagramASC NULLS LASTNULLS FIRST
desc-option
Read syntax diagramSkip visual syntax diagramDESC NULLS FIRSTNULLS LAST
numbering-specification
Read syntax diagramSkip visual syntax diagramROW_NUMBER ()OVER( window-partition-clause window-order-clause )
aggregation-specification
Read syntax diagramSkip visual syntax diagram column-function1OLAP-column-function OVER(window-partition-clause RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGwindow-order-clauseRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWwindow-aggregation-group-clause)
OLAP-column-function
Read syntax diagramSkip visual syntax diagram first-value-functionlast-value-functionnth-value-functionratio-to-report-function
first-value-function
Read syntax diagramSkip visual syntax diagram FIRST_VALUE ( expression ,'RESPECT NULLS','IGNORE NULLS' )
last-value-function
Read syntax diagramSkip visual syntax diagram LAST_VALUE ( expression ,'RESPECT NULLS','IGNORE NULLS' )
nth-value-function
Read syntax diagramSkip visual syntax diagram NTH_VALUE ( expression , nth-row ) RESPECT NULLSIGNORE NULLS FROM FIRSTFROM LAST
ratio-to-report-function
Read syntax diagramSkip visual syntax diagramRATIO_TO_REPORT(expression )
window-aggregation-group-clause
Read syntax diagramSkip visual syntax diagramROWSRANGEgroup-startgroup-betweengroup-end
group-start
Read syntax diagramSkip visual syntax diagramUNBOUNDED PRECEDINGunsigned-constantPRECEDINGCURRENT ROW
group-between
Read syntax diagramSkip visual syntax diagramBETWEENgroup-bound1AND group-bound2
group-bound1
Read syntax diagramSkip visual syntax diagramUNBOUNDED PRECEDINGunsigned-constantPRECEDINGunsigned-constantFOLLOWINGCURRENT ROW
group-bound2
Read syntax diagramSkip visual syntax diagramUNBOUNDED FOLLOWINGunsigned-constantPRECEDINGunsigned-constantFOLLOWINGCURRENT ROW
group-end
Read syntax diagramSkip visual syntax diagramUNBOUNDED FOLLOWINGunsigned-constantFOLLOWING
Notes:
  • 1 ARRAY_AGG, CUME_DIST, and PERCENT_RANK are not supported as an aggregate function in aggregation-specification (SQLSTATE 42887).

An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used within an argument to an XMLQUERY or XMLEXISTS expression (SQLSTATE 42903). An OLAP function cannot be used as an argument of an aggregate function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function.

When specifying an OLAP function, a window is specified that defines the rows over which the function is applied, and in what order. When used with an aggregate function, the applicable rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a partition by month, an average can be calculated over the previous three month period.

The CUME_DIST function is a distribution function that returns a cumulative distribution of a row within an OLAP window, expressed as a value between 0.0 - 1.0. The result is computed as follows:
  • The number of rows preceding or peer with the current row in the OLAP window, divided by the number of rows in the OLAP window.

The data type of the result is DECFLOAT(34). The result cannot be NULL.

The PERCENT_RANK function is a distribution function that returns a relative percentile rank of a row within an OLAP window, expressed as a value between 0.0 - 1.0. When the number of rows in the OLAP window is greater than 1, the result is computed as follows:
  • The RANK of the current row in the OLAP window minus 1 divided by the number of rows in the OLAP window minus 1.
Otherwise, the result is 0.0.

The data type of the result is DECFLOAT(34). The result cannot be NULL.

The ranking function computes the ordinal rank of a row within the window. Rows that are not distinct with respect to the ordering within their window are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.

If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus, if two or more rows are not distinct with respect to the ordering, then there will be one or more gaps in the sequential rank numbering.

If DENSE_RANK (or DENSERANK) is specified, the rank of a row is defined as 1 plus the number of preceding rows that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.

The ROW_NUMBER (or ROWNUMBER) function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order, as returned by the subselect (not according to any ORDER BY clause in the select-statement).

If the fetch-clause is used along with the ROW_NUMBER function, the row numbers might not be displayed in order. The fetch-clause is applied after the result set (including any ROW_NUMBER assignments) is generated; therefore, if the row number order is not the same as the order of the result set, some assigned numbers might be missing from the sequence.

The data type of the result of RANK, DENSE_RANK or ROW_NUMBER is BIGINT. The result cannot be null.

The NTILE function returns the quantile rank of a row.
num-tiles
An expression that specifies the number of quantiles. The expression must return a value that is a built-in numeric data type, CHAR, or VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting. If the expression is not a SMALLINT, INTEGER, or BIGINT, it is cast to BIGINT before the function is evaluated. The value must be greater than 0 (SQLSTATE 22014). The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 42601).

The data type of the result of NTILE is the same data type as the data type of num-tiles after any implicit casting. If the argument can be null, the result can be null. If the argument is null, the result is the null value.

The NTILE function computes the quantile rank of a row by dividing the ordered rows within the OLAP window into num-tiles quantiles and returns a value between 1 and MIN(n, num-tiles), where n is the number of rows within the OLAP window. If n is evenly divisible by num-tiles, the rows in the OLAP window are grouped into num-tiles quantiles, each containing (n / num-tiles) rows. Otherwise, each of the quantiles 1 through MOD(n, num-tiles) is assigned (n / num-tiles + 1) rows while each of the quantiles (MOD(n, num-tiles) + 1) through num-tiles is assigned (n / num-tiles) rows. The result is the quantile rank which is associated with the current row.

Equivalent sort keys are not considered when rows are divided into quantiles. Rows with equivalent sort keys can be assigned to different quantiles based on the non-deterministic order of these sort keys. Therefore, NTILE is a non-deterministic function.

The LAG function returns the expression value for the row at offset rows before the current row. The offset must be a positive integer constant (SQLSTATE 42815). An offset value of 0 means the current row. If a window-partition-clause is specified, offset means offset rows before the current row and within the current partition. If offset is not specified, the value 1 is used. If default-value (which can be an expression) is specified, it will be returned if the offset goes beyond the scope of the current partition. Otherwise, the null value is returned. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all rows are null, default-value (or the null value if default-value was not specified) is returned.

The LEAD function returns the expression value for the row at offset rows after the current row. The offset must be a positive integer constant (SQLSTATE 42815). An offset value of 0 means the current row. If a window-partition-clause is specified, offset means offset rows after the current row and within the current partition. If offset is not specified, the value 1 is used. If default-value (which can be an expression) is specified, it will be returned if the offset goes beyond the scope of the current partition. Otherwise, the null value is returned. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all rows are null, default-value (or the null value if default-value was not specified) is returned.

The FIRST_VALUE function returns the expression value for the first row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, FIRST_VALUE returns the null value.

The LAST_VALUE function returns the expression value for the last row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, LAST_VALUE returns the null value.

The data type of the result of FIRST_VALUE, LAG, LAST_VALUE, and LEAD is the data type of the expression. The result can be null.

The NTH_VALUE function returns the expression value for the nth-row row in an OLAP window.
expression
An expression that specifies the current row in an OLAP window. The expression must return a value that is a built-in data type.(SQLSTATE 42884).
nth-row
An expression that specifies which row of the OLAP window to return. The expression must return a value that is a built-in numeric data type, a CHAR, or a VARCHAR data type. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported using implicit casting. If the expression is not a SMALLINT, INTEGER, or BIGINT, it is cast to BIGINT before the function is evaluated. The value must be greater than 0 (SQLSTATE 22016). The expression must be a constant, a variable, or a cast of a constant or variable (SQLSTATE 428I9).
FROM FIRST or FROM LAST
Specifies how nth-row is applied. If FROM FIRST is specified, nth-row is treated as counting forward from the first row in the OLAP window. If FROM LAST is specified, nth-row is treated as counting backward from the last row in the OLAP window.
RESPECT NULLS or IGNORE NULLS
Specifies how NULL values in the OLAP window are handled. If RESPECT NULLS is specified, all rows where the expression value for the row is the null value are considered in the calculation. If IGNORE NULLS is specified, all rows where the expression value for the row is the null value are not considered in the calculation.

The data type of the result of NTH_VALUE is the same as the data type of expression.

The result can be null. If nth-row is null, the result is the null value. If the number of rows in the OLAP window (including null values if RESPECT NULLS is specified or excluding null values if IGNORE NULLS is specified) is less than the value of nth-row, the result is the null value.

The NTH_VALUE function is a non-deterministic function because the window-order-clause is not required and when window-order-clause is specified, rows with equivalent sort keys have a non-deterministic order.

The RATIO_TO_REPORT function returns the ratio of an argument to the sum of the arguments in an OLAP partition. For example, the following functions are equivalent:
   RATIO_TO_REPORT(expression) OVER (...)
   CAST(expression AS DECFLOAT(34)) / SUM(expression) OVER(...) 
The division is always performed using DECFLOAT(34). The result data type is DECFLOAT(34). If the argument can be null, the result can be null; if the argument is null, the result is the null value.
PARTITION BY (partitioning-expression,...)
Defines the partition within which the function is applied. A partitioning-expression is an expression that is used in defining the partitioning of the result set. Each column-name that is referenced in a partitioning-expression must unambiguously reference a column of the result table of the subselect that contains the OLAP specification (SQLSTATE 42702 or 42703). A partitioning-expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822), or any function or query that is not deterministic or that has an external action (SQLSTATE 42845).
window-order-clause
ORDER BY (sort-key-expression,...)
Defines the ordering of rows within a partition that determines the value of the OLAP function or the meaning of the ROW values in the window-aggregation-group-clause (it does not define the ordering of the query result set).
sort-key-expression
An expression used in defining the ordering of the rows within a window partition. Each column name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect, including the OLAP function (SQLSTATE 42702 or 42703). A sort-key-expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822), or any function or query that is not deterministic or that has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSE_RANK functions (SQLSTATE 42601).
ASC
Uses the values of the sort-key-expression in ascending order.
DESC
Uses the values of the sort-key-expression in descending order.
NULLS FIRST
The window ordering considers null values before all non-null values in the sort order.
NULLS LAST
The window ordering considers null values after all non-null values in the sort order.
ORDER OF table-designator
Specifies that the same ordering used in table-designator should be applied to the result table of the subselect. There must be a table reference matching table-designator in the FROM clause of the subselect that specifies this clause (SQLSTATE 42703). The ordering that is applied is the same as if the columns of the ORDER BY clause in the nested subselect (or fullselect) were included in the outer subselect (or fullselect), and these columns were specified in place of the ORDER OF clause.
window-aggregation-group-clause
The aggregation group of a row R is a set of rows defined in relation to R (in the ordering of the rows of R's partition). This clause specifies the aggregation group. If this clause is not specified and a window-order-clause is also not specified, the aggregation group consists of all the rows of the window partition. This default can be specified explicitly using RANGE (as shown) or ROWS.

If window-order-clause is specified, the default behavior is different when window-aggregation-group-clause is not specified. The window aggregation group consists of all rows of the partition of R that precede R and that are peers of R in the window ordering of the window partition defined by the window-order-clause.

ROWS
Indicates the aggregation group is defined by counting rows.
RANGE
Indicates the aggregation group is defined by an offset from a sort key.
group-start
Specifies the starting point for the aggregation group. The aggregation group end is the current row. Specification of the group-start clause is equivalent to a group-between clause of the form "BETWEEN group-start AND CURRENT ROW".
group-between
Specifies the aggregation group start and end based on either ROWS or RANGE.
group-end
Specifies the ending point for the aggregation group. The aggregation group start is the current row. Specification of the group-end clause is equivalent to a group-between clause of the form "BETWEEN CURRENT ROW AND group-end".
UNBOUNDED PRECEDING
Includes the entire partition preceding the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
UNBOUNDED FOLLOWING
Includes the entire partition following the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.
CURRENT ROW
Specifies the start or end of the aggregation group based on the current row. If ROWS is specified, the current row is the aggregation group boundary. If RANGE is specified, the aggregation group boundary includes the set of rows with the same values for the sort-key-expressions as the current row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.
unsigned-constant PRECEDING
Specifies either the range or number of rows preceding the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or unsigned-constant FOLLOWING.
unsigned-constant FOLLOWING
Specifies either the range or number of rows following the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

Examples

  1. Display the ranking of employees, in order by surname, according to their total salary (based on salary plus bonus) that have a total salary more than $30,000.
       SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
           RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
         FROM EMPLOYEE WHERE SALARY+BONUS > 30000
         ORDER BY LASTNAME
    Note that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:
       ORDER BY RANK_SALARY
    or
       ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)
  2. Rank the departments according to their average total salary.
       SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
           RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
         FROM EMPLOYEE 
         GROUP BY WORKDEPT
         ORDER BY RANK_AVG_SAL
  3. Rank the employees within a department according to their education level. Having multiple employees with the same rank in the department should not increase the next ranking value.
       SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,
           DENSE_RANK() OVER
             (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
         FROM EMPLOYEE
         ORDER BY WORKDEPT, LASTNAME
  4. Provide row numbers in the result of a query.
       SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
           LASTNAME, SALARY
         FROM EMPLOYEE
         ORDER BY WORKDEPT, LASTNAME
  5. List the top five wage earners.
       SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY 
         FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
           RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
           FROM EMPLOYEE) AS RANKED_EMPLOYEE 
         WHERE RANK_SALARY < 6 
         ORDER BY RANK_SALARY
    Note that a nested table expression was used to first compute the result, including the rankings, before the rank could be used in the WHERE clause. A common table expression could also have been used.
  6. For each department, list employee salaries and show how much less each person makes compared to the employee in that department with the next highest salary.
       SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY,
           LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT
             ORDER BY SALARY) - SALARY AS DELTA_SALARY
         FROM EMPLOYEE
         ORDER BY WORKDEPT, SALARY
    
  7. Calculate an employee's salary relative to the salary of the employee who was first hired for the same type of job.
       SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY,
           FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
             ORDER BY HIREDATE) AS FIRST_SALARY,
           SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
             ORDER BY HIREDATE) AS DELTA_SALARY
         FROM EMPLOYEE
         ORDER BY JOB, HIREDATE
  8. Calculate the average close price for stock XYZ during the month of January, 2006. If a stock doesn't trade on a given day, its close price in the DAILYSTOCKDATA table is the null value. Instead of returning the null value for days that a stock doesn't trade, use the COALESCE function and LAG function to return the close price for the most recent day the stock was traded. Limit the search for a previous not-null close value to one month before January 1st, 2006.
       WITH V1(SYMBOL, TRADINGDATE, CLOSEPRICE) AS
       (
       SELECT SYMBOL, TRADINGDATE,
         COALESCE(CLOSEPRICE,
           LAG(CLOSEPRICE,
             1,
             CAST(NULL AS DECIMAL(8,2)),
             'IGNORE NULLS')
           OVER (PARTITION BY SYMBOL
           ORDER BY TRADINGDATE)
       )
       FROM DAILYSTOCKDATA
       WHERE SYMBOL = 'XYZ' AND
         TRADINGDATE BETWEEN '2005-12-01' AND '2006-01-31'
       )
       SELECT SYMBOL, AVG(CLOSEPRICE) AS AVG
         FROM V1
         WHERE TRADINGDATE BETWEEN '2006-01-01' AND '2006-01-31'
         GROUP BY SYMBOL
    
  9. Calculate the 30-day moving average for stocks ABC and XYZ during the year 2005.
       WITH V1(SYMBOL, TRADINGDATE, MOVINGAVG30DAY) AS
       (
       SELECT SYMBOL, TRADINGDATE,
         AVG(CLOSEPRICE) OVER (PARTITION BY SYMBOL
           ORDER BY TRADINGDATE
         ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
       FROM DAILYSTOCKDATA
       WHERE SYMBOL IN ('ABC', 'XYZ')
         AND TRADINGDATE BETWEEN DATE('2005-01-01') - 2 MONTHS
           AND '2005-12-31'
       )
       SELECT SYMBOL, TRADINGDATE, MOVINGAVG30DAY
         FROM V1
         WHERE TRADINGDATE BETWEEN '2005-01-01' AND '2005-12-31'
         ORDER BY SYMBOL, TRADINGDATE
  10. Use an expression to define the cursor position and query a sliding window of 50 rows before that position.
       SELECT DATE, FIRST_VALUE(CLOSEPRICE + 100) OVER
           (PARTITION BY SYMBOL
           ORDER BY DATE
           ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING) AS FV
         FROM DAILYSTOCKDATA
         ORDER BY DATE
  11. For each employee, calculate the average salary for the set of employees that includes those employees in the same department who have an education level 1 lower and 1 higher than the employee.
       SELECT WORKDEPT, EDLEVEL, SALARY, AVG(SALARY)
             OVER (PARTITION BY WORKDEPT 
             ORDER BY EDLEVEL
             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
          FROM EMPLOYEE 
          ORDER BY WORKDEPT, EDLEVEL
  12. Calculate which quartile (4-quantiles) each employee's salary is in.
       SELECT EMPNO, SALARY, NTILE(4) OVER 
             (ORDER BY SALARY) AS QUARTILE
          FROM EMPLOYEE 
          ORDER BY SALARY
    The result set is:
    EMPNO  SALARY      QUARTILE
    ------ ----------- -----------
    200340    31840.00           1
    000290    35340.00           1
    200330    35370.00           1
    000310    35900.00           1
    200310    35900.00           1
    000280    36250.00           1
    000270    37380.00           1
    000300    37750.00           1
    200240    37760.00           1
    200120    39250.00           1
    000320    39950.00           1
    000230    42180.00           2
    000340    43840.00           2
    000170    44680.00           2
    000330    45370.00           2
    200280    46250.00           2
    200010    46500.00           2
    000260    47250.00           2
    000240    48760.00           2
    000250    49180.00           2
    000120    49250.00           2
    000220    49840.00           2
    000190    50450.00           3
    000180    51340.00           3
    000150    55280.00           3
    000200    57740.00           3
    000160    62250.00           3
    200170    64680.00           3
    000110    66500.00           3
    000210    68270.00           3
    000140    68420.00           3
    200140    68420.00           3
    200220    69840.00           4
    000060    72250.00           4
    000130    73800.00           4
    000050    80175.00           4
    000100    86150.00           4
    000090    89750.00           4
    000020    94250.00           4
    000070    96170.00           4
    000030    98250.00           4
    000010   152750.00           4
    
      42 record(s) selected.   
  13. The query in the following example divides the rows into 3 buckets, grouping them by maximum salary. The maximum salary is included to show what values go into each bucket:
       SELECT NTILE(3) OVER (ORDER BY MAX_SALARY) AS Bucket, 
    MAX_SALARY FROM GOSALESDW.EMP_POSITION_DIM;
    A portion of the output from the query is in the following table:
    Table 1. Example output
    BUCKET MAX_SALARY
    1 0.00
    ... ...
    1 35000.00
    2 5000.00
    ... ...
    2 12000.00
    3 13000.00
    ... ...
    3 301500.00
  14. Find the cumulative distribution and the relative percentile rank of each employee's salary within their department.
       SELECT EMPNO, WORKDEPT, SALARY, 
          CAST(CUME_DIST() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS DECIMAL(4,3))
             AS CUME_DIST, 
          CAST(PERCENT_RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) 
             AS DECIMAL(4,3)) 
             AS PERCENT_RANK FROM EMP 
          ORDER BY WORKDEPT, SALARY
    The result set is:
    EMPNO  WORKDEPT SALARY      CUME_DIST PERCENT_RANK
    ------ -------- ----------- --------- ------------
    200120 A00         39250.00     0.200        0.000
    200010 A00         46500.00     0.400        0.250
    000120 A00         49250.00     0.600        0.500
    000110 A00         66500.00     0.800        0.750
    000010 A00        152750.00     1.000        1.000
    000020 B01         94250.00     1.000        0.000
    000140 C01         68420.00     0.500        0.000
    200140 C01         68420.00     0.500        0.000
    000130 C01         73800.00     0.750        0.666
    000030 C01         98250.00     1.000        1.000
    000170 D11         44680.00     0.090        0.000
    000220 D11         49840.00     0.181        0.100
    000190 D11         50450.00     0.272        0.200
    000180 D11         51340.00     0.363        0.300
    000150 D11         55280.00     0.454        0.400
    000200 D11         57740.00     0.545        0.500
    000160 D11         62250.00     0.636        0.600
    200170 D11         64680.00     0.727        0.700
    000210 D11         68270.00     0.818        0.800
    200220 D11         69840.00     0.909        0.900
    000060 D11         72250.00     1.000        1.000
    000270 D21         37380.00     0.142        0.000
    200240 D21         37760.00     0.285        0.166
    000230 D21         42180.00     0.428        0.333
    000260 D21         47250.00     0.571        0.500
    000240 D21         48760.00     0.714        0.666
    000250 D21         49180.00     0.857        0.833
    000070 D21         96170.00     1.000        1.000
    000050 E01         80175.00     1.000        0.000
    000290 E11         35340.00     0.142        0.000
    000310 E11         35900.00     0.428        0.166
    200310 E11         35900.00     0.428        0.166
    000280 E11         36250.00     0.571        0.500
    000300 E11         37750.00     0.714        0.666
    200280 E11         46250.00     0.857        0.833
    000090 E11         89750.00     1.000        1.000
    200340 E21         31840.00     0.166        0.000
    200330 E21         35370.00     0.333        0.200
    000320 E21         39950.00     0.500        0.400
    000340 E21         43840.00     0.666        0.600
    000330 E21         45370.00     0.833        0.800
    000100 E21         86150.00     1.000        1.000
    
      42 record(s) selected.
  15. Compare each employee's salary to the highest salary and second highest salary in the department.
          SELECT WORKDEPT, SALARY, FIRST_VALUE(SALARY) 
          OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS FIRST, 
          NTH_VALUE(SALARY, 2) OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS SECOND 
             FROM EMP 
             ORDER BY WORKDEPT, SALARY
    The result set is:
    WORKDEPT SALARY      FIRST       SECOND  
    -------- ----------- ----------- --------
    A00         39250.00   152750.00 66500.00
    A00         46500.00   152750.00 66500.00
    A00         49250.00   152750.00 66500.00
    A00         66500.00   152750.00 66500.00
    A00        152750.00   152750.00 66500.00
    B01         94250.00    94250.00 -
    C01         68420.00    98250.00 73800.00
    C01         68420.00    98250.00 73800.00
    C01         73800.00    98250.00 73800.00
    C01         98250.00    98250.00 73800.00
    D11         44680.00    72250.00 69840.00
    D11         49840.00    72250.00 69840.00
    D11         50450.00    72250.00 69840.00
    D11         51340.00    72250.00 69840.00
    D11         55280.00    72250.00 69840.00
    D11         57740.00    72250.00 69840.00
    D11         62250.00    72250.00 69840.00
    D11         64680.00    72250.00 69840.00
    D11         68270.00    72250.00 69840.00
    D11         69840.00    72250.00 69840.00
    D11         72250.00    72250.00 69840.00
    D21         37380.00    96170.00 49180.00
    D21         37760.00    96170.00 49180.00
    D21         42180.00    96170.00 49180.00
    D21         47250.00    96170.00 49180.00
    D21         48760.00    96170.00 49180.00
    D21         49180.00    96170.00 49180.00
    D21         96170.00    96170.00 49180.00
    E01         80175.00    80175.00 -
    E11         35340.00    89750.00 46250.00
    E11         35900.00    89750.00 46250.00
    E11         35900.00    89750.00 46250.00
    E11         36250.00    89750.00 46250.00
    E11         37750.00    89750.00 46250.00
    E11         46250.00    89750.00 46250.00
    E11         89750.00    89750.00 46250.00
    E21         31840.00    86150.00 45370.00
    E21         35370.00    86150.00 45370.00
    E21         39950.00    86150.00 45370.00
    E21         43840.00    86150.00 45370.00
    E21         45370.00    86150.00 45370.00
    E21         86150.00    86150.00 45370.00
    
      42 record(s) selected.