Was this helpful?
Window Functions
Window function syntax is required with analytical functions and can also be used with aggregate functions.
A window function is defined “over” a group of rows (a window) from a query. A window function produces a result value for each input row, where the value is computed according to the records in the current window.
A window is defined with an optional partitioning definition and an ordering definition.
The partitioning defines sets of rows over which the function results are computed. Partitioning is similar to the grouping of a grouped query. Without the partitioning definition, the function will operate over the entire set of records returned by the query.
The ordering defines the order of rows within a partition, which determines the function values.
Note:  The ordering does not define the order of result rows from the query. If the results of a query need to be sorted, a separate ORDER BY clause is required on the result set.
Window Function Syntax
A window function has the following format:
window_function OVER window_specification
where:
window_function
Specifies an analytical function or an aggregate function.
A select list can have more than one function, each with a different OVER clause.
OVER window_specification
Specifies a partitioning definition (optional) and an ordering definition:
([PARTITION BY partitioning_list ] ORDER BY sort_specification_list )
PARTITION BY partitioning_list
Specifies a list of column names or expressions.
ORDER BY sort_specification_list
Specifies a list of column names or expressions optionally qualified by the ASC or DESC sort directions.
Note:  ORDER BY for an aggregate function is not supported.
An empty window specification, that is, OVER(), causes the function results to be computed across all rows in the table.
Windowing Aggregate Functions
Aggregate functions can be used as window functions; that is, you can use the OVER clause with aggregate functions.
Note:  The ORDER BY clause in the window specification is not supported for windowing aggregates.
Example:
This query computes, for each partition, the aggregate over the rows in that partition. 
SELECT OrderID, ProductID, OrderQty
     ,SUM(OrderQty) OVER(PARTITION BY OrderID) AS "Total"
     ,AVG(OrderQty) OVER(PARTITION BY OrderID) AS "Avg"
     ,COUNT(OrderQty) OVER(PARTITION BY OrderID) AS "Count"
     ,MIN(OrderQty) OVER(PARTITION BY OrderID) AS "Min"
     ,MAX(OrderQty) OVER(PARTITION BY OrderID) AS "Max"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553);
Results:
OrderID   ProductID   OrderQty   Total   Avg   Count   Min   Max
  32548         776          1      26     2      12     1     6
  32548         777          3      26     2      12     1     6
  32548         778          1      26     2      12     1     6
  32548         771          1      26     2      12     1     6
  32548         772          1      26     2      12     1     6
  32548         773          2      26     2      12     1     6
  32548         774          1      26     2      12     1     6
  32548         714          3      26     2      12     1     6
  32548         716          1      26     2      12     1     6
  32548         709          6      26     2      12     1     6
  32548         712          2      26     2      12     1     6
  32548         711          4      26     2      12     1     6
  32553         772          1      14     1       8     1     4
  32553         775          4      14     1       8     1     4
  32553         714          1      14     1       8     1     4
  32553         716          1      14     1       8     1     4
  32553         777          2      14     1       8     1     4
  32553         771          3      14     1       8     1     4
  32553         773          1      14     1       8     1     4
  32553         778          1      14     1       8     1     4
When the OVER clause is used without a window specification the function results are computed across all rows in the table. The following example returns the row count of the table for each row:
CREATE TABLE foobar(x INTEGER NOT NULL);
INSERT INTO foobar VALUES (1), (2), (2), (3), (3), (3);
SELECT x
,ROW_NUMBER() OVER(ORDER BY x) AS row_nbr
,COUNT(*) OVER() AS row_cnt
FROM foobar
 
x           row_nbr     row_cnt
-------------------------------
      1           1           6
      2           2           6
      2           3           6
      3           4           6
      3           5           6
      3           6           6
Example: Cumulative Column
Assuming a table called user_table with an ANSIDATE column signed_up, calculate the running total of the signed up per day. In this example, the weekly (or seven day) number is shown in the “Cumulative Total” column.
SELECT
  signed_up AS "Begin",
  COUNT(*) AS "Daily Signups",
SUM(COUNT(*)) OVER(PARTITION BY 1 ORDER BY year(signed_up) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Cumulative Total"
FROM user_table
WHERE signed_up BETWEEN ANSIDATE('2021-05-01')
                  AND TIMESTAMPADD(DAY, 6, ANSIDATE('2021-05-01'))
GROUP BY 1
ORDER BY 1;
Results—Example row set:
+----------+---------------+-----------------+
|Begin     |Daily Signups  |Cumulative Total |
+----------+---------------+-----------------+
|2021-05-01|             93|               93|
|2021-05-02|             92|              185|
|2021-05-03|             96|              281|
|2021-05-04|             95|              376|
|2021-05-05|             90|              466|
|2021-05-06|             93|              559|
|2021-05-07|             91|              650|
+----------+---------------+-----------------+
Analytical Functions
Analytical functions compute an aggregate value based on a group of rows, and can return multiple rows for each group. Analytical functions can be used to calculate percentages or top-N results in a group.
Analytical functions can appear only in the select list of a query or in the ORDER BY clause. They cannot appear in WHERE, ON, HAVING, or GROUP BY clauses. Analytical functions can appear in the select lists of views and derived tables.
Analytical functions are window functions (see Window Function Syntax), and thus require an OVER clause.
Note:  The ORDER BY clause within the OVER clause supports the NULLS FIRST / NULLS LAST syntax. The default is NULLS LAST.
DENSE_RANK
DENSE_RANK()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in no gaps in the list of ranks. For example: 1, 2, 2, 3, 4, 4, 4, 5.
The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is consecutive.
SELECT empno, deptno, sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank
FROM   emp;
 
     EMPNO     DEPTNO        SAL       RANK
---------- ---------- ---------- ----------
       839        100       4900          1
       782        100       2350          2
       934        100       1200          3
       788        200       2900          1
       902        200       2900          1
       566        200       2875          2
       876        200       1000          3
       369        200        700          4
       900        300       2750          1
       654        300       1500          2
       521        300       1400          3
       844        300       1150          4
       499        300       1150          4
       698        300        850          5
The following query uses the DENSE_RANK function and then gets only the rows ranked 1 from each group. You must provide a correlation name (in this case, x) for the subquery:
SELECT fname, address FROM (SELECT fname, address,
       DENSE_RANK() OVER (PARTITION BY fname ORDER BY timestamp DESC) AS rank
FROM tab1) x WHERE rank = 1;
 
     fname                address
---------- ---‑‑----------‑‑-----
      Mary   1600 Pennsylvania Av
LAG and LEAD
LAG | LEAD (scalar_expression [,offset] [,default]) [RESPECT NULLS | IGNORE NULLS]
Returns a scalar column value from a row before (LAG) or after (LEAD) the current row. Use LAG to compare values in the current row with values in a previous row. Use LEAD to compare values in the current row with values in a following row.
LAG and LEAD are useful for comparing values across multiple rows—for example, across time periods.
where:
scalar_expression
Is an expression that returns a single value. The expression can be a constant, row value, operator, scalar function, and scalar subquery.
offset
Specifies the number of rows of lag or lead from the current row. Must be an expression that resolves to a positive integer. Default is 1.
default
Returns the specified value if no lagging or leading row is present (for example, lag of the first value in an ordered set). Default is NULL.
RESPECT NULLS | IGNORE NULLS
Indicates whether to include null values in the selection of a lag or lead value. Default is RESPECT NULLS.
IGNORE NULLS and offset N returns the N-th previous/following not-NULL value.
RESPECT NULLS and offset N returns the N-th previous/following value, counting NULL values.
The following query shows the date, the weather, and the previous day's weather:
SELECT wdate, forecast, LAG(forecast) OVER(ORDER BY wdate) AS lag
FROM weather ORDER BY wdate;
 
wdate         forecast      lag
---------------------------------
2012-06-12    overcast      NULL
2012-06-13    sunny         overcast
2012-06-14    rain          sunny
The following query finds the salary of each employee in department 300 and lists the next higher salary:
SELECT deptno, empno, sal,
   LEAD(sal) OVER(ORDER BY sal) AS next_sal
FROM salary WHERE deptno=300;
 
deptno   empno      sal   next_sal
----------------------------------
   300     698     1150       1150
   300     844     1150       1450
   300     499     1450       1500
   300     521     1500       2750
   300     654     2750 
   300     900 
The following example demonstrates the use of IGNORE NULLS:
CREATE TABLE lead_table(c1 INT , c2 INT , c3 INT);
INSERT INTO lead_table VALUES (1,10,1), (2, 20,2), (NULL, 30,1), (NULL,40,2), (5,50,1);
 
   SELECT
       c1, c2, c3,
       LEAD(c1             , 1,100) OVER (ORDER BY c2) AS lead1,
       LEAD(c1 IGNORE NULLS, 1,200) OVER (ORDER BY c2) AS lead1ign,
       LAG (c1             , 2,300) OVER (ORDER BY c2) AS lag2,
       LAG (c1 IGNORE NULLS, 2,400) OVER (ORDER BY c2) AS lag2ign
   FROM lead_table;
 
   c1     c2    c3   lead1  lead1ign   lag2    lag2ign
------  ----    --   -----  --------   -----   --------
    1      10    1       2         2    300        400
    2      20    2    NULL         5    300        400 
 NULL      30    1    NULL         5      1          1 
 NULL      40    2       5         5      2          1 
    5      50    1     100       200   NULL          1 
Note the difference in results between the lag2 and lag2ign columns.
For the fourth row, for RESPECT NULLS the value two rows above is 2.
However, for IGNORE NULLS the second non-null value above is 1.
   SELECT
   c1, c2, c3,
   LEAD(c1             , 1,100) OVER (PARTITION BY c3 ORDER BY c2) AS lead1,
   LEAD(c1 IGNORE NULLS, 1,200) OVER (PARTITION BY c3 ORDER BY c2) AS lead1ign,
   LAG (c1             , 1,300) OVER (PARTITION BY c3 ORDER BY c2) AS lag1,
   LAG (c1 IGNORE NULLS, 1,400) OVER (PARTITION BY c3 ORDER BY c2) AS lag1ign
   FROM lead_table;
 
   c1   c2   c3   lead1   lead1ign   lag1    lag1ign 
 ----   ---  ---  -----  ---------   ----    -------
    1   10    1    NULL          5    300        400
 NULL   30    1       5          5      1          1
    5   50    1     100        200   NULL          1
    2   20    2    NULL        200    300        400
 NULL   40    2     100        200      2          2
NTILE
NTILE(n)
Divides the rows in an ordered partition into n groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
NTILE can be used, for example, to see what quartile, decile, or percentile a row is in.
The following query divides the employees in Department 400 into 4 groups by salary:
SELECT Department_ID, Employee_ID, Salary, NTILE(4)
OVER (PARTITION BY Department_ID ORDER BY Salary DESC) AS Quartile
FROM employees WHERE Department_ID = 400;
 
Department_ID    Employee_ID    Salary    Quartile
------------- -------------- --------- -----------
          400             50      9000           1
          400             48      8000           1
          400             51      7500           1
          400             54      7000           1
          400             53      6500           2
          400             47      6000           2
          400             44      5000           2
          400             46      4500           2
          400             52      4300           3
          400             45      4000           3
          400             43      3500           3
          400             42      3000           4
          400             41      3000           4
          400             49      2800           4
When the number of rows is not divisible by n, the later rows will have the smaller number of rows, so the first and second quartiles have 4 rows whereas the third and fourth have only 3.
PERCENT_RANK
PERCENT_RANK()
Calculates the relative rank of a row within a group of rows. PERCENT_RANK returns a number between 0 and 1, which represents the percentage of rows in the group that are less than the current row. If a partition has exactly one row, its percent_rank() is 0. Percent_rank() for the highest value in the group will always be 1.
Use this function to determine the relative standing of a value within a result set.
The following query calculates, for each employee, the percent rank of the employee's salary in the department:
SELECT deptno, empno, sal,
PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr
FROM emp
ORDER BY deptno, pr;
 
       DEPTNO         EMPNO       SAL         PR
------------- ------------- ------------ ----------
          100           840         4400      0.000
 
          300           900         2750      0.000
          300           654         1500      0.200
          300           521         1400      0.400
          300           844         1150      0.600
          300           499         1150      0.600
          300           698          850      1.000
 
          400           789         6500      0.000
 
          500           299         3900      0.000
          500           473         2200      0.333
          500           371         2200      0.333
          500           900         2100      1.000
 
          800             5        10500      0.000
          800           854         6200      1.000
RANK
RANK()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values of their sort specification have the same RANK() value and result in gaps in the list of ranks. For example: 1, 2, 2, 4, 5, 5, 5, 8.
The following query ranks each employee in a department based on his salary. When two employees have the same salary they are assigned the same rank. When multiple rows have the same rank, the next rank in the sequence is not consecutive.
SELECT empno, deptno, sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank
FROM   emp;
 
     EMPNO     DEPTNO        SAL       RANK
---------- ---------- ---------- ----------
       839        100       4900          1
       782        100       2350          2
       934        100       1200          3
       788        200       2900          1
       902        200       2900          1
       566        200       2875          3
       876        200       1000          4
       369        200        700          5
       698        300       2750          1
       499        300       1500          2
       844        300       1400          3
       521        300       1150          4
       654        300       1150          4
       900        300        850          6
ROW_NUMBER
ROW_NUMBER()
Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values in their sort specification are ordered arbitrarily. For example: 1, 2, 3, 4, 5.
The following query assigns a consecutive number to each row. Rows with matching numbers are ordered arbitrarily.
SELECT empno, deptno, sal,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) AS rownum 
FROM   emp;
 
     EMPNO     DEPTNO        SAL     ROWNUM
---------- ---------- ---------- ----------
       934        100       1200          1
       782        100       2350          2
       839        100       4900          3
       369        200        700          1
       876        200       1000          2
       566        200       2875          3
       788        200       2900          4
       902        200       2900          5
       900        300        850          1
       654        300       1150          2
       521        300       1150          3
       844        300       1400          4
       499        300       1500          5
       698        300       2750          6
Last modified date: 04/03/2024