共用方式為

QUALIFY 子句

适用于：勾选“是” Databricks SQL 勾选“是” Databricks Runtime 10.4 LTS 及更高版本。

筛选窗口函数的结果。 要使用 QUALIFY，SELECT 列表或 QUALIFY 子句中必须至少有一个窗口函数。

语法

QUALIFY boolean_expression

参数

  • boolean_expression

    计算得出 boolean 结果类型的任何表达式。 使用逻辑运算符（AND、OR）可以将两个或多个表达式组合在一起。

    QUALIFY 子句中指定的表达式不能包含聚合函数。

示例

CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer VALUES
    (100, 'Fremont', 'Honda Civic', 10),
    (100, 'Fremont', 'Honda Accord', 15),
    (100, 'Fremont', 'Honda CRV', 7),
    (200, 'Dublin', 'Honda Civic', 20),
    (200, 'Dublin', 'Honda Accord', 10),
    (200, 'Dublin', 'Honda CRV', 3),
    (300, 'San Jose', 'Honda Civic', 5),
    (300, 'San Jose', 'Honda Accord', 8);

-- QUALIFY with window functions in the SELECT list.
> SELECT
    city,
    car_model,
    RANK() OVER (PARTITION BY car_model ORDER BY quantity) AS rank
  FROM dealer
  QUALIFY rank = 1;
 city     car_model    rank
 -------- ------------ ----
 San Jose Honda Accord 1
 Dublin   Honda CRV    1
 San Jose Honda Civic  1

-- QUALIFY with window functions in the QUALIFY clause.
SELECT city, car_model
FROM dealer
QUALIFY RANK() OVER (PARTITION BY car_model ORDER BY quantity) = 1;
 city     car_model
 -------- ------------
 San Jose Honda Accord
 Dublin   Honda CRV
 San Jose Honda Civic