文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 函数和操作符/ QUALIFY
更新时间：2024-11-15 GMT+08:00
查看PDF
分享

QUALIFY

通常，QUALIFY子句和CSUM()、MDIFF()、ROW_NUMBER()、RANK()等分析函数（窗口函数）一同使用。子查询中会包含QUALIFY子句指定的窗口函数。迁移工具支持QUALIFY使用MDIFF()、RANK()和ROW_NUMBER()函数。QUALIFY是Teradata扩展项，不是标准ANSI语法。QUALIFY在WHERE和GROUP BY子句后执行，必须单独成行。

只有当SELECT语句显式包含列名和/或表达式时，DSC才允许在ORDER BY子句中指定该列名和/或表达式。

输入：QUALIFY

1
2
3
4
5
6
 
SELECT
        CUSTOMER_ID
       ,CUSTOMER_NAME
  FROM
       CUSTOMER_T QUALIFY row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) = 1
;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
          CUSTOMER_ID
          ,CUSTOMER_NAME
     FROM
          (
               SELECT
                         CUSTOMER_ID
                         ,CUSTOMER_NAME
                         ,row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) AS ROW_NUM1
                    FROM
                         CUSTOMER_T
          ) Q1
     WHERE
          Q1.ROW_NUM1 = 1
;

输入：QUALIFY，使用MDIFF和RANK

1
2
3
4
5
6
7
8
9
 
SELECT
           material_name
          ,unit_of_measure * standard_cost AS tot_cost
     FROM
          raw_material_t m LEFT JOIN supplies_t s
               ON s.material_id = m.material_id 
          QUALIFY rank ( ) over( ORDER BY tot_cost DESC ) IN '5'
                  OR mdiff( tot_cost ,3 ,material_name ) IS NULL
;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
SELECT
          material_name
          ,tot_cost
     FROM
          (
               SELECT
                         material_name
                         ,unit_of_measure * standard_cost AS tot_cost
                         ,rank ( ) over( ORDER BY unit_of_measure * standard_cost DESC ) AS ROW_NUM1
                         ,unit_of_measure * standard_cost - (LAG( unit_of_measure * standard_cost ,3 ,NULL ) over( ORDER BY material_name )) AS ROW_NUM2
                    FROM
                         raw_material_t m LEFT JOIN supplies_t s
                              ON s.material_id = m.material_id
          ) Q1
     WHERE
          Q1.ROW_NUM1 = '5'
          OR Q1.ROW_NUM2 IS NULL
;

输入：QUALIFY，使用ORDER BY且ORDER BY中包含不存在于SELECT列表的列

1
2
3
4
5
6
 
SELECT Postal_Code
   FROM db_pvfc9_std.Customer_t t1
   GROUP BY Customer_Name ,Postal_Code
   QUALIFY ---comments
 (  Rank ( CHAR(Customer_Address) DESC  )  ) = 1
  ORDER BY t1.Customer_Name;

输出：

1
2
3
4
5
6
7
8
 
SELECT Postal_Code FROM
           ( SELECT Customer_Name, Postal_Code
    , Rank () over( PARTITION BY Customer_Name, Postal_Code ORDER BY LENGTH(Customer_Address) DESC ) AS Rank_col
               FROM db_pvfc9_std.Customer_t t1
           ) Q1
      WHERE /*comments*/
    Q1.Rank_col = 1
  ORDER BY Q1.Customer_Name;

输入：QUALIFY，使用列别名且不应在SELECT列表中再次添加相应的列表达式

1
2
3
4
5
 
SELECT material_name, unit_of_measure * standard_cost as tot_cost,
        RANK() over(order by tot_cost desc) vendor_cnt
 FROM raw_material_t m left join supplies_t s
 ON s.material_id = m.material_id
 QUALIFY vendor_cnt < 5 or MDIFF(tot_cost, 3, material_name) IS NULL;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT material_name, tot_cost, vendor_cnt
   FROM  ( SELECT material_name
                       , unit_of_measure * standard_cost AS tot_cost
                      , rank () over (ORDER BY tot_cost DESC) vendor_cnt
                      , tot_cost - ( LAG(tot_cost ,3 ,NULL) over (ORDER BY material_name) ) AS anltfn
               FROM raw_material_t m LEFT JOIN supplies_t s
                  ON s.material_id = m.material_id
           ) Q1
      WHERE  Q1.vendor_cnt < 5 OR Q1.anltfn IS NULL
 ;

TITLE和QUALIFY

输入：

REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc}
(
  CLICLINBR
, CLICHNNAM
, CLICHNSHO
, CLICLIMNE
, CLIBNKCOD
)
AS
LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS
SELECT
  CLICLINBR (title '    VARCHAR(20)')
, CLICHNNAM (title '        VARCHAR(200)')
, CLICHNSHO (title '        VARCHAR(20)')
, CLICLIMNE (title '      VARCHAR(10)')
, CLIBNKCOD (title '       VARCHAR(11)')
FROM
  ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1
QUALIFY
    ROW_NUMBER() OVER(PARTITION BY  CLICLINBR ORDER BY CLICLINBR  ) = 1
;

输出：

CREATE OR REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc}
(
  CLICLINBR
, CLICHNNAM
, CLICHNSHO
, CLICLIMNE
, CLIBNKCOD
)
AS
/* LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS */
SELECT CLICLINBR
        , CLICHNNAM
       , CLICHNSHO
       , CLICLIMNE
      , CLIBNKCOD
 FROM (
           SELECT
                          CLICLINBR /* (title '    VARCHAR(20)') */
                        , CLICHNNAM /* (title '        VARCHAR(200)') */
                       , CLICHNSHO /* (title '        VARCHAR(20)') */
                       , CLICLIMNE /* (title '      VARCHAR(10)') */
                       , CLIBNKCOD /* (title '       VARCHAR(11)') */
                       , ROW_NUMBER() OVER(PARTITION BY  CLICLINBR ORDER BY CLICLINBR  ) AS ROWNUM1
FROM
  ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1 ) Q1
WHERE Q1.ROWNUM1 = 1
;
父主题： 函数和操作符