使用QUALIFY過濾Window函數的結果-雲原生MaxCompute MaxCompute-阿里雲

MaxCompute支援使用QUALIFY文法根據您指定的過濾條件過濾窗（Window）函數的結果，QUALIFY文法過濾Window函數資料類似於HAVING文法處理經過彙總函式和GROUP BY後的資料。本文為您介紹QUALIFY文法和使用樣本。

功能簡介

在一個查詢語句中QUALIFY文法的執行順序在WINDOW函數之後，用於對視窗函數處理後的資料進行篩選。通常，SELECT語句的子句按如下所示的順序執行：

FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

注意事項

QUALIFY文法需要查詢語句裡面至少包含一個Window函數，在沒有Window函數的情況下使用QUALIFY文法會報錯：FAILED: ODPS-0130071:[3,1] Semantic analysis exception - use QUALIFY clause without window function。錯誤樣本如下。
```
SELECT * 
FROM values (1, 2) t(a, b) 
QUALIFY a > 1;
```
QUALIFY文法中允許使用者使用SELECT中列的別名作為過濾條件的一部分，樣本如下。
```
SELECT 
sum(t.a) over (partition by t.b) as c1 
FROM values (1, 2) t(a, b) 
QUALIFY c1 > 1;
```

命令格式

QUALIFY <expression>

其中expression為過濾條件運算式。

使用樣本

通過使用QUALIFY文法可以將原有的文法簡化。

改造前SQL命令及結果：

SELECT col1, col2
FROM
(
SELECT
t.a as col1,
sum(t.a) over (partition by t.b) as col2
FROM values (1, 2),(2,3) t(a, b)
)
WHERE col2 > 1;

--返回結果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

改寫後SQL命令及結果：

SELECT 
t.a as col1, 
sum(t.a) over (partition by t.b) as col2 
FROM values (1, 2),(2,3) t(a, b) 
QUALIFY col2 > 1;

--返回結果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

也可以不使用別名，直接對Window函數進行過濾，改寫如下。

SELECT t.a as col1,
sum(t.a) over (partition by t.b) as col2
FROM values (1, 2),(2,3) t(a, b)
QUALIFY sum(t.a) over (partition by t.b)  > 1;

--返回結果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

QUALIFY和WHERE、HAVING的使用方法相同，只是執行順序不同，所以QUALIFY文法允許您寫一些複雜的條件，樣本如下。
```
SELECT * 
FROM values (1, 2),(2,3) t(a, b) 
QUALIFY sum(t.a) over (partition by t.b)  IN (SELECT a FROM <table_name>);
```

QUALIFY執行於視窗函數生效後，如下較複雜的樣本可以直觀感受QUALIFY文法的執行順序。

SELECT a, b, max(c) 
FROM values (1, 2, 3),(1, 2, 4),(1, 3, 5),(2, 3, 6),(2, 4, 7),(3, 4, 8) t(a, b, c) 
WHERE a < 3 
GROUP BY a, b 
HAVING max(c) > 5 
QUALIFY sum(b) over (partition by a) > 3;

--返回結果
+------+------+------+
| a    | b    | _c2  |
+------+------+------+
| 2    | 3    | 6    |
| 2    | 4    | 7    |
+------+------+------+