文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Teradata语法迁移> 查询迁移操作符> QUALIFY

QUALIFY

分享
更新时间: 2019/08/09 GMT+08:00

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

说明:

只有当SELECT语句显性包含列名和/或表达式时,Migration Tool才支持ORDER BY子句中的列名和/或表达式。

输入:QUALIFY

SELECT
        CUSTOMER_ID
       ,CUSTOMER_NAME
  FROM
       CUSTOMER_T QUALIFY row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) = 1
;

输出

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和多个ANALYTIC函数及动态输入参数

SELECT
           Customer_Name
          ,Postal_Code
     FROM
          Customer_t 
               QUALIFY (
                         Rank ( ) over( partition BY Customer_Name ORDER BY Customer_City DESC ) IN( '5' ,'1' )
                         AND( ROW_NUMBER( ) over( partition BY Customer_Name ORDER BY Customer_City ) = 1 OR ROW_NUMBER( ) over( partition BY Customer_Name ORDER BY Customer_City ) = :Customer_value_1 )
                       )
;

输出

SELECT
           Customer_Name
          ,Postal_Code
     FROM
          (
               SELECT
                         Customer_Name
                         ,Postal_Code
                         ,Rank ( ) over( partition BY Customer_Name ORDER BY Customer_City DESC ) AS ROW_NUM1
                         ,ROW_NUMBER( ) over( partition BY Customer_Name ORDER BY Customer_City ) AS ROW_NUM2
                         ,ROW_NUMBER( ) over( partition BY Customer_Name ORDER BY Customer_City ) AS ROW_NUM3
                    FROM
                         Customer_t
          ) Q1
     WHERE
          (
               Q1.ROW_NUM1 IN( '5' ,'1' )
               AND( Q1.ROW_NUM2 = 1 OR Q1.ROW_NUM3 = :Customer_value_1 )
          )
;

输入:使用QUALIFY,并指定DISTINCT

SELECT
          *
     FROM
          Customer_t
     WHERE
          Customer_Name NOT IN (
               SELECT
                         DISTINCT Customer_Name
                    FROM
                         Customer_t
                    GROUP BY
                         1
          )
;

输出

SELECT
          *
     FROM
          Customer_t MigTblAlias1
     WHERE
          NOT EXISTS (
               SELECT
                         DISTINCT Customer_Name
                    FROM
                         Customer_t
                    WHERE
                         Customer_Name = MigTblAlias1.Customer_Name
                    GROUP BY
                         1
          )
;

输入:使用QUALIFY,并指定MDIFF和RANK

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
;

输出

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列表的列

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;

输出

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和COLUMN ALIAS,不应在SELECT列表中再次添加相应的列表达式

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;

输出

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
 ;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区