Definisikan jendela bernama dengan kata kunci WINDOW - MaxCompute

Kata kunci WINDOW memungkinkan Anda mendefinisikan jendela bernama yang dapat digunakan ulang dalam fungsi jendela.

Sintaksis

WINDOW <window_name> AS (<window_definition>)
    [, <window_name> AS (<window_definition>)]
    ...

window_name: Wajib. Nama kustom untuk jendela tersebut.
window_definition: Wajib. Spesifikasi jendela tersebut. Untuk informasi lebih lanjut, lihat Window functions.

Batasan

Anda dapat mendefinisikan jendela bernama baru berdasarkan jendela yang sudah ada, sehingga keduanya menjadi ekuivalen. Namun, Anda tidak dapat mereferensikan jendela bernama yang belum didefinisikan.
Anda tidak dapat mendefinisikan jendela bernama yang sama lebih dari sekali dalam cakupan yang sama.

Data sampel

Contoh berikut menggunakan data sampel ini. Buat dan isi tabel emp:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

File emp.txt berisi data berikut:

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

Kueri data sebagai berikut:

SELECT * FROM emp;
-- The command returns the following result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| empno      | ename      | job        | mgr        | hiredate   | sal        | comm       | deptno     |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 7369       | SMITH      | CLERK      | 7902       | 1980-12-17 00:00:00 | 800        | NULL       | 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       | NULL       | 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       | NULL       | 30|
| 7782       | CLARK      | MANAGER    | 7839       | 1981-06-09 00:00:00 | 2450       | NULL       | 10|
| 7788       | SCOTT      | ANALYST    | 7566       | 1987-04-19 00:00:00 | 3000       | NULL       | 20|
| 7839       | KING       | PRESIDENT  | NULL       | 1981-11-17 00:00:00 | 5000       | NULL       | 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       | NULL       | 20|
| 7900       | JAMES      | CLERK      | 7698       | 1981-12-03 00:00:00 | 950        | NULL       | 30|
| 7902       | FORD       | ANALYST    | 7566       | 1981-12-03 00:00:00 | 3000       | NULL       | 20|
| 7934       | MILLER     | CLERK      | 7782       | 1982-01-23 00:00:00 | 1300       | NULL       | 10|
| 7948       | JACCKA     | CLERK      | 7782       | 1981-04-12 00:00:00 | 5000       | NULL       | 10|
| 7956       | WELAN      | CLERK      | 7649       | 1982-07-20 00:00:00 | 2450       | NULL       | 10|
| 7956       | TEBAGE     | CLERK      | 7748       | 1982-12-30 00:00:00 | 1300       | NULL       | 10|
+------------+------------+------------+------------+------------+------------+------------+------------+

Contoh

Contoh 1: Mendefinisikan jendela bernama.

-- Query without the WINDOW keyword
SELECT deptno,
       ename,
       sal,
       row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
FROM emp;
-- The command returns the following result:
+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

-- Query with the WINDOW keyword
SELECT deptno,
       ename,
       sal,
       row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);

-- The queries return the same result:
+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

Contoh 2: Mendefinisikan beberapa jendela bernama dalam satu pernyataan.

SELECT deptno, ename,
    max(sal) OVER w2 AS max_sal,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w2 AS (PARTITION BY deptno);

Contoh 3: Menggunakan klausa WINDOW dalam pernyataan INSERT.

INSERT OVERWRITE TABLE table1 SELECT row_number() OVER w1 AS nums FROM table2 WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);
INSERT OVERWRITE TABLE table3 SELECT max(column1) OVER w1 AS nums FROM table4 WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);

Contoh 4: Mendefinisikan jendela bernama berdasarkan jendela bernama lainnya.

SELECT deptno, ename,
    max(sal) OVER w2 AS max_sal,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w2 AS w1;

Catatan

Definisi ini menjadikan kedua jendela bernama tersebut ekuivalen.

Contoh 5 (contoh error): Anda tidak dapat mendefinisikan jendela bernama dengan mereferensikan jendela yang belum didefinisikan. Pada contoh salah berikut, w1 didefinisikan berdasarkan w2, tetapi w2 tidak didefinisikan.
```
SELECT deptno, ename,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS w2;
```
Contoh 6 (contoh error): Anda tidak dapat mendefinisikan jendela bernama yang sama lebih dari sekali dalam cakupan yang sama. Pada contoh salah berikut, w1 didefinisikan dua kali.
```
SELECT deptno, ename,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w1 AS (PARTITION BY ename ORDER BY sal DESC);
```