Window Functions

Function definition:

Bigint count([distinct] expr) over(partition by [col1, col2…]
 [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• expr: any data type. When it is NULL, this row is not involved in count.  If the ‘distinct’ keyword is specified, it indicates taking the unique count value.
• partition by [col1, col2…]: Specify the columns to use window function.
• order by col1 [asc|desc], col2[asc|desc]: if ‘order by’ clause is not specified,  return the count vale of ‘expr’ in current window. If ‘order by’ clause is specified, the return result is ordered according to specified sequence and the value is a cumulative count value from start row to current row in current window.

Suppose that the table ‘test_src’ is existent and the column ‘user_id’ of bigint type exists in this table.

select user_id,
        count(user_id) over (partition by user_id) as count
    from test_src;
    
    | user_id | count |
    
    | 1 | 3 |
    | 1 | 3 |
    | 1 | 3 |
    | 2 | 1 |
    | 3 | 1 |
    
    -- the ‘order by’ clause is not specified, return the count value of user_id in current window.
    select user_id,
        count(user_id) over (partition by user_id order by user_id) as count
    from test_src;
    
    | user_id | count |
    
    | 1 | 1 | -- start row of the window
    | 1 | 2 | --two records exist from start row to current row. Return 2.
    | 1 | 3 |
    | 2 | 1 |
    | 3 | 1 |
    
    -- The ‘order by’ clause is specified and return a cumulative count value from start row to current row in current window.

Function definition:

avg([distinct] expr) over(partition by [col1, col2…]
 [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• distinct: if the keyword ‘distinct’ is specified, it indicates taking average of unique value.
• expr: Double type. 
  • If the input is ‘string’ type or ‘bigint’ type, it is converted to ‘double’ type by implicit conversion and involved in operation. If it is another data type, an exception indicated. 
  • If this value is NULL, this row is not involved in the calculation. 
  • If the data type is Boolean, it is not allowed to be involved in the calculation.
• partition by [col1, col2...]: specified columns to use window function.
• order by col1[asc|desc], col2[asc|desc]:  if ‘order by’ clause is not specified, return the average of all values in current window. If ‘order by’ clause is specified, the return result is ordered according to specified sequence and returns the cumulative average from the start row to current row in current window.

Function definition:

max([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• expr: Any types expect ‘Boolean’. If the value is NULL, this row is not involved in the calculation.  If the keyword ‘distinct’ is specified, it indicates taking the max value of unique value.
• partition by [col1, col2…]: specified columns to use window function.
• order by [col1[asc|desc], col2[asc|desc:  if ‘order by’ clause is not specified, return the maximum value in current window.  If ‘order by’ clause is specified, the return result is ordered according to specified sequence and return the maximum value from start row to current row in current window.

Function definition:

min([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• expr: Any types expect ‘Boolean’. If the value is NULL, this row is not involved in the calculation.  If the keyword ‘distinct’ is specified, it indicates taking the minimum value of a unique value.
• partition by [col1, col2…]: specified columns to use window function.
• order by [col1[asc|desc], col2[asc|desc:  if ‘order by’ clause is not specified, return the minimum value in current window.  If ‘order by’ clause is specified, the return result is ordered according to specified sequence and return the minimum value from start row to current row in current window.

Function definition:

Double median(Double number1,number2...) over(partition by [col1, col2…])
Decimal median(Decimal number1,number2...) over(partition by [col1,col2…])

Parameter description:

• number1,number1…: 1 to 255 digits of a Double or Decimal type.
  • When the input value is a String type or a Bigint type, the operation is performed after the implicit conversion to a Double type, and other types throw exceptions.
  • Return NULL when the input value is null.
  • When the input value is a Double type, it will be converted to the Array of Double by default .
• partition by [col1, col2…]: specified columns to use window function.

Function definition:

Double stddev([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev([distinct] expr) over(partition by [col1, col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• expr: Double type.
  •  If the input is ‘string’ or ‘bigint’ type, it is converted to ‘double’ type and involved in operation. If it is another data type, an exception is indicated. 
  • If the input value is ‘NULL’, this row is ignored. 
  • If the keyword ‘distinct’ is specified, it indicates calculating the population standard deviation of unique value.
• partition by [col1, col2..]: specified columns to use window function.
• order by col1[asc|desc], col2[asc|desc]:  if ‘order by’ clause is not specified, return the population standard deviation in current window.  If ‘order by’ clause is specified, the return result is ordered according to specified sequence and return the population standard deviation from start row to current row in current window.

Example:

select window, seq, stddev_pop('1\01') over (partition by window order by seq) from dual;

Function definition:

Double stddev_samp([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev_samp([distinct] expr) over((partition by [col1,col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• Expr: Double type. 
  • If the input is ‘string’ or ‘bigint’ type, it is converted to ‘double’ type and involved in operation. If it is another data type, an exception is indicated. 
  • If the input value is NULL, this row is ignored. 
  • If the keyword ‘distinct’ is specified, it indicates calculating the sample standard deviation of unique value.
• partition by [col1, col2..]: specified columns to use window function.
• Order by col1[asc|desc], col2[asc|desc]:  if ‘order by’ clause is not specified, return the sample standard deviation in current window.  If ‘order by’ clause is specified, the return result is ordered according to specified sequence and return the sample standard deviation from start row to current row in current window.

Function definition:

sum([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

Parameter description:

• Expr: Double type. 
  • If the input is ‘string’ or ‘bigint’ type, it is converted to ‘double’ type and involved in operation. If it is another data type, an exception is indicated. 
  • If the input value is NULL, ignore this row. 
  • If the keyword ‘distinct’ is specified, it indicates calculating the sum of unique value.
• Partition by [col1, col2..]: specified columns to use window function.
• Order by col1[asc|desc], col2[asc|desc]:  if ‘order by’ clause is not specified, return the sum in current window.  If ‘order by’ clause is specified, the return result is ordered according to specified sequence and return the sum from start row to current row in current window.

Return value:

• If the input parameter is ‘bigint’ type, return ‘bigint’ type. 
• If the input parameter is ‘Decimal’ type, return ‘Decimal’ type. 
• If the input parameter is ‘double’ type or ‘string’ type, return ‘double’ type.

Function definition:

Bigint dense_rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

Parameter description:

• partition by [col1, col2..]: specified columns to use window function.
• order by col1[asc|desc], col2[asc|desc]: specify the value which the rank is based on.

Suppose that data in table ‘emp’ is as follows:

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Now, all employees need to be grouped by department, and each group must be sorted in descending order according to SAL to obtain the serial number in own group.

SELECT deptno
        , ename
        , sal
        , DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--The result is as follows:

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 1 |
| 10 | CLARK | 2450.0 | 2 |
| 10 | WELAN | 2450.0 | 2 |
| 10 | TEBAGE | 1300.0 | 3 |
| 10 | MILLER | 1300.0 | 3 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 1 |
| 20 | JONES | 2975.0 | 2 |
| 20 | ADAMS | 1100.0 | 3 |
| 20 | SMITH | 800.0 | 4 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 4 |
| 30 | JAMES | 950.0 | 5 |

Function definition:

Bigint rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

Parameter description:

• Partition by [col1, col2..]: specify columns to use window function.
• Order by col1[asc|desc], col2[asc|desc]: specify the value which the rank is based on.

Suppose that data in table ‘emp’ is as follows:

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Now, all employees need to be grouped by department, and each group must be sorted in descending order according to SAL to obtain the serial number in own group.

SELECT deptno
        , ename
        , sal
        , RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--The result is as follows:

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 1 |
| 10 | CLARK | 2450.0 | 3 |
| 10 | WELAN | 2450.0 | 3 |
| 10 | TEBAGE | 1300.0 | 5 |
| 10 | MILLER | 1300.0 | 5 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 1 |
| 20 | JONES | 2975.0 | 3 |
| 20 | ADAMS | 1100.0 | 4 |
| 20 | SMITH | 800.0 | 5 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 4 |
| 30 | JAMES | 950.0 | 6 |

Function definition:

lag(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])

Parameter description:

• expr: any type.
• offset: a Bigint type constant.  If the input is String type or Double type, convert it to Bigint type by implicit conversion. Offset > 0;
• default: Define the default value while the specified range of ‘offset’ oversteps the boundary. It is a constant and default is null.
• partition by [col1, col2..]: specify columns to use window function.
• order by col1[asc|desc], col2[asc|desc]: specify the order method for return result.

The command format is as follows:

lead(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])

Parameter description:

• expr: any type.
• offset: a Bigint type constant.  If the input is String, Decimal or Double type, convert it to Bigint type by implicit conversion. Offset > 0.
• default: Define the default value while the specified range of offset oversteps the boundary. It is a constant.
• partition by [col1, col2..]: specify columns to use window function.
• order by col1[asc|desc], col2[asc|desc]: specify the order method for return result.

For example:

select c_Double_a,c_String_b,c_int_a,lead(c_int_a,1) over(partition by c_Double_a order by c_String_b) from dual;
select c_String_a,c_time_b,c_Double_a,lead(c_Double_a,1) over(partition by c_String_a order by c_time_b) from dual;
select c_String_in_fact_num,c_String_a,c_int_a,lead(c_int_a) over(partition by c_String_in_fact_num order by c_String_a) from dual;

The command format is as follows:

Percent_rank () over (partition by [col1, col2...]
order by [col1[asc|desc], col2[asc|desc]…])

The command format is as follows:

row_number() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

Parameter description:

• partition by [col1, col2..]: specify columns to use window function.
• order by col1[asc|desc], col2[asc|desc]: specify the order method for return result.

Suppose that data in table emp is as follows:

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566, Jones, Manager, fig-04-02 00:00:00, 2975, 20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788, Scott, analyst, fig-04-19 00:00:00, 3000, 20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956, tebage, clerk, maid-12-30 00:00:00, 1300, 10

Now, all employees need to be grouped by department, and each group must be sorted in descending order according to SAL to obtain the serial number in own group.

SELECT deptno
        , ename
        , Sal
        , Row_number () over (partition by deptno order by Sal DESC) as Nums  --Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--The result is as follows:

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 2 |
| 10 | CLARK | 2450.0 | 3 |
| 10 | WELAN | 2450.0 | 4 |
| 10 | TEBAGE | 1300.0 | 5 |
| 10 | MILLER | 1300.0 | 6 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 2 |
| 20 | JONES | 2975.0 | 3 |
| 20 | ADAMS | 1100.0 | 4 |
| 20 | SMITH | 800.0 | 5 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 5 |
| 30 | JAMES | 950.0 | 6 |

The command format is as follows:

boolean cluster_sample([Bigint x, Bigint y])
over(partition by [col1, col2..])

Parameter description:

• x: a Bigint type constant, x>=1.  If you specify the parameter y, x indicates dividing a window into x parts.  Otherwise x indicates selecting x rows records in a window (if x rows are in this window, return value is true).  If x is NULL, return NULL.
• y: a Bigint type constant, y>=1, y<=x.  It indicates selecting y parts records from x parts in a window (that is to say, if y parts records exist, return value is true).  If y is NULL, return NULL.
• partition by [col1, col2]: specify columns to use window function.

If two columns key and value are in the table test_tbl, key is grouping field. The corresponding values of key have groupa and groupb, the field value indicates value of key. As follows:

    | key | value |
    
    | groupa | -1.34764165478145 |
    | groupa | 0.740212609046718 |
    | groupa | 0.167537127858695 |
    | groupa | 0.630314566185241 |
    | GroupA | 0.0112401388646925 |
    | groupa | 0.199165745875297 |
    | groupa | -0.320543343353587 |
    | groupa | -0.273930924365012 |
    | groupa | 0.386177958942063 |
    | groupa | -1.09209976687047 |
    | groupb | -1.10847690938643 |
    | groupb | -0.725703978381499 |
    | groupb | 1.05064697475759 |
    | groupb | 0.135751224393789 |
    | groupb | 2.13313102040396 |
    | groupb | -1.11828960785008 |
    | groupb | -0.849235511508911 |
    | groupb | 1.27913806620453 |
    | groupb | -0.330817716670401 |
    | groupb | -0.300156896191195 |
    | groupb | 2.4704244205196 |
    | groupb | -1.28051882084434 |
    

To select 10% values from each group, the following MaxCompute  SQL is suggested:

Select key, Value
    from (
        Select key, value, cluster_sample (10, 1) over (partition by key) as flag
        from tbl
        ) sub
    where flag = true;

| Key | value |

| groupa | 0.167537127858695 |
| groupb | 0.135751224393789 |

The command format is as follows:

BIGINT ntile(BIGINT n) over(partition by [col1, col2…]  
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]))

Assume the data in the table EMP is as follows:

| Empno | ename | job | Mgr | hiredate | Sal | REM | deptno |
7369, Smith, clerk, maid-12-17 00:00:00, 800, 20
7499, Allen, salesman, maid-02-20 00:00:00, 1600,300, 30
7521, Ward, salesman, maid-02-22 00:00:00, 1250,500, 30
7566, Jones, Manager, fig-04-02 00:00:00, 2975, 20
7654 Martin, salesman, fig-09-28 00:00:00, fig, 30
7698, Blake, Manager, fig-05-01 00:00:00, 2850, 30
7782, Clark, Manager, fig-06-09 00:00:00, 2450, 10
7788, Scott, analyst, fig-04-19 00:00:00, 3000, 20
00:00:00, King, President, 1991-11-17 5000, 7839, 10
7844, Turner, salesman, fig-09-08 00:00:00, 1500,0, 30
7876, Adams, clerk, maid-05-23 00:00:00, 1100, 20
7900 James, clerk, maid-12-03 00:00:00, 950, 30
7902 Ford, analyst, fig-12-03 00:00:00, 3000, 20
7934 Miller, clerk, fig-01-23 00:00:00, 1300, 10
7948, jaccka, clerk, fig-04-12 00:00:00, 5000, 10
7956, welan, clerk, fig-07-20 00:00:00, 2450, 10
7956, tebage, clerk, maid-12-30 00:00:00, 1300, 10

All employees now need to be divided into three groups according to Sal high to low cut, and get the serial number of the employee's own group.

Select deptno, ename, Sal, ntile (3) over (partition by depno order by Sal DESC) as nt3 from EMP;
-- Execution results as follows

| Deptno | ename | Sal | nt3 |

| 10 | jaccka | 5000.0 | 1 |
| 10 | King | 5000.0 | 1 |
| 10 | welan | 2450.0 | 2 |
| 10 | Clark | 2450.0 | 2 |
| 10 | tebage | 1300.0 | 3 |
10 | Miller | 1300.0 | 3 |
| 20 | Scott | 3000.0 | 1 |
| 20 | Ford | 3000.0 | 1 |
| 20 | Jones | 2975.0 | 2 |
| 20 | Adams | 1100.0 | 2 |
| 20 | Smith | 800.0 | 3 |
| 30 | Blake | 2850.0 | 1 |
| 30 | Allen | 1600.0 | 1 |
| 30 | Turner | 1500.0 | 2 |
| 30 | Martin | 1250.0 | 2 |
| 30 | ward | 1250.0 | 3 |
| 30 | James | 950.0 | 3 |