文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Teradata语法迁移> 系统函数和操作符

系统函数和操作符

分享
更新时间:2020/08/05 GMT+08:00

本节主要介绍Teradata系统函数和运算符的迁移语法。迁移语法决定了关键字/特性的迁移方式。

模式

带有模式名的数据库更改为“SET SESSION CURRENT_SCHEMA”。

Oracle 语法

迁移后语法

DATABASE SCHTERA 
SET SESSION CURRENT_SCHEMA TO SCHTERA

分析函数

在Teradata中,分析函数统称为有序分析函数,它们为数据挖掘、分析和商业智能提供了强大的分析能力。

  1. ORDER BY中的分析函数

    输入:ORDER BY子句中的分析函数

    1
    2
    3
    4
    SELECT customer_id, customer_name, RANK(customer_id, customer_address DESC) 
       FROM customer_t
     WHERE  customer_state = 'CA'
     ORDER BY RANK(customer_id, customer_address DESC);
    

    输出

    1
    2
    3
    4
    SELECT customer_id, customer_name, RANK() over(order by customer_id, customer_address DESC) 
       FROM customer_t
     WHERE  customer_state = 'CA'
     ORDER BY RANK() over(order by customer_id DESC, customer_address DESC) ;
    

    输入:GROUP BY子句中的分析函数

    1
    2
    3
    4
    5
    6
    7
    SELECT customer_city, customer_state, postal_code
         , rank(postal_code)
         , rank() over(partition by customer_state order by postal_code)
        , rank() over(order by postal_code)
      FROM Customer_T 
      GROUP BY customer_state
      ORDER BY customer_state;
    

    输出

    1
    2
    3
    4
    5
    6
    SELECT customer_city, customer_state, postal_code
         , rank() over(PARTITION BY customer_state ORDER BY postal_code DESC) 
         , rank() over(partition by customer_state order by postal_code)
         , rank() over(order by postal_code)
      FROM Customer_T 
      ORDER BY customer_state;
    
  2. PARTITION BY中的分析函数

    当输入脚本的PARTITION BY子句中包含数值时,迁移脚本将原样保留该数值。

    输入:PARTITION BY子句中的分析函数(包含数值)

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT
              Customer_id
              ,customer_name
              ,rank (
              ) over( partition BY 1 ORDER BY Customer_id )
              ,rank (customer_name)
         FROM
              Customer_t
         GROUP BY
              1
    ;
    

    输出

    SELECT
              Customer_id
              ,customer_name
              ,rank (
              ) over( partition BY 1 ORDER BY Customer_id )
              ,rank (
              ) over( PARTITION BY Customer_id ORDER BY customer_name DESC )
         FROM
              Customer_t
    ;
  3. 窗口函

    窗口函数在查询结果中执行跨行计算。DSC支持以下Teradata窗口函数:

    DSC仅支持QUALIFY子句使用一个窗口函数。如果QUALIFY使用多个窗口函数,可能会导致迁移失败。

  4. CSUM

    累计函数(CSUM)为一列数值计算运行或累计总数,建议在QUALIFY语句中使用ALIAS。

    输入:CSUM,使用GROUP_ID
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    INSERT INTO GSIS_SUM.DW_DAT71 (
       col1
      ,PROD_GROUP
    )
       SELECT 
          CSUM(1, T1.col1)
          ,T1.PROD_GROUP
         FROM tab1  T1
        WHERE T1.col1 = 'ABC'
    ;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    INSERT
         INTO
              GSIS_SUM.DW_DAT71 (
                   col1
                   ,PROD_GROUP
              ) SELECT
                        SUM (1) over( ORDER BY T1.col1 ROWS UNBOUNDED PRECEDING )
                        ,T1.PROD_GROUP
                   FROM
                        tab1 T1
                   WHERE
                        T1.col1 = 'ABC'
    ;
    
    输入:CSUM,使用GROUP_ID
    1
    2
    3
    4
    5
    SELECT  top 10
          CSUM(1, T1.Test_GROUP)
          ,T1.col1
      FROM  $[schema}.  T1
     WHERE T1.Test_GROUP = 'Test_group' group by Test_group order by Test_Group;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    SELECT
           SUM (1) over( partition BY Test_group ORDER BY T1.Test_GROUP ROWS UNBOUNDED PRECEDING )
           ,T1.col1
      FROM
           $[schema}. T1
     WHERE
           T1.Test_GROUP = 'Test_group'
     ORDER BY
           Test_Group LIMIT 10
    ;
    
    输入:CSUM,使用GROUP BY和QUALIFY
    1
    2
    3
    4
    SELECT c1, c2, c3, CSUM(c4, c3) 
      FROM tab1 
    QUALIFY ROW_NUMBER(c4) = 1  
    GROUP BY 1, 2;
    

    输出

    1
    2
    3
    4
    5
    6
    7
    SELECT c1, c2, c3, ColumnAlias1
      FROM ( SELECT c1, c2, c3
                  , SUM (c4) OVER(PARTITION BY 1 ,2 ORDER BY c3 ROWS UNBOUNDED PRECEDING) AS ColumnAlias1
                  , ROW_NUMBER( ) OVER(PARTITION BY 1, 2 ORDER BY c4) AS ROW_NUM1
               FROM tab1
           ) Q1
       WHERE Q1.ROW_NUM1 = 1;
    
  5. MDIFF

    MDIFF函数基于预定的查询宽度计算一列的移动差分值。查询宽度即所指定的行数。建议在QUALIFY语句中使用ALIAS。

    输入:MDIFF,使用QUALIFY

    1
    2
    3
    4
    5
    6
    SELECT DT_A.Acct_ID, DT_A.Trade_Date, DT_A.Stat_PBU_ID
          , CAST( MDIFF( Stat_PBU_ID_3, 1, DT_A.Trade_No ASC ) AS DECIMAL(20,0) ) AS MDIFF_Stat_PBU_ID
       FROM Trade_His DT_A
      WHERE Trade_Date >= CAST( '20170101' AS DATE FORMAT 'YYYYMMDD' ) 
      GROUP BY DT_A.Acct_ID, DT_A.Trade_Date
     QUALIFY MDIFF_Stat_PBU_ID <> 0 OR MDIFF_Stat_PBU_ID IS NULL;
    

    输出

    1
    2
    3
    4
    5
    6
    7
    SELECT Acct_ID, Trade_Date, Stat_PBU_ID, MDIFF_Stat_PBU_ID
       FROM (SELECT DT_A.Acct_ID, DT_A.Trade_Date, DT_A.Stat_PBU_ID
             , CAST( (Stat_PBU_ID_3 - (LAG(Stat_PBU_ID_3, 1, NULL) OVER (PARTITION BY DT_A.Acct_ID, DT_A.Trade_Date ORDER BY DT_A.Trade_No ASC)))  AS MDIFF_Stat_PBU_ID
               FROM Trade_His DT_A
              WHERE Trade_Date >= CAST( '20170101' AS DATE)
                    )
     WHERE MDIFF_Stat_PBU_ID <> 0 OR MDIFF_Stat_PBU_ID IS NULL;
    
  6. RANK

    RANK(col1, col2...)

    输入:RANK,使用GROUP BY

    1
    2
    3
    4
    SELECT  c1, c2, c3, RANK(c4, c1 DESC, c3) AS Rank1 
      FROM  tab1 
     WHERE  ... 
     GROUP BY c1;
    

    输出

    1
    2
    3
    SELECT c1, c2, c3, RANK() OVER (PARTITION BY c1 ORDER BY c4, c1 DESC ,c3) AS Rank1
      FROM tab1
     WHERE ...;
    
  7. ROW_NUMBER

    ROW_NUMBER(col1, col2...)

    输入:ROW NUMBER,使用GROUP BY和QUALIFY

    1
    2
    3
    4
    SELECT c1, c2, c3, ROW_NUMBER(c4, c3) 
       FROM tab1 
    QUALIFY RANK(c4) = 1  
      GROUP BY 1, 2;
    

    输出

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    SELECT
          c1
         ,c2
         ,c3
         ,ColumnAlias1
      FROM
          (
            SELECT
                    c1
                   ,c2
                   ,c3
                   ,ROW_NUMBER( ) over( PARTITION BY 1 ,2 ORDER BY c4 ,c3 ) AS ColumnAlias1
                   ,RANK (
                   ) over( PARTITION BY 1 ,2 ORDER BY c4 ) AS ROW_NUM1
              FROM
                  tab1
          ) Q1
     WHERE
          Q1.ROW_NUM1 = 1
    ;
    
  8. COMPRESS(使用*****)

    输入

    ORDCADBRN VARCHAR(6) CHARACTER SET LATIN CASESPECIFIC TITLE '    ' COMPRESS '******'

    输出

    ORDCADBRN VARCHAR( 6 ) /* CHARACTER SET LATIN*/ /* CASESPECIFIC*/ /*TITLE '    '*/ /* COMPRESS  '******' */

比较和列表操作符

比较操作符LT、LE、GT、GE、EQ和NE不得用作表别名或列别名。

以下内容介绍了支持的比较和列表操作符。

  1. ^=和GT
    输入:比较操作(^=和GT)
    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1, tab2 t2 
     WHERE t1.c3 ^= t1.c3
       AND t2.c4 GT 100;
    

    输出

    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1, tab2 t2 
     WHERE t1.c3 <> t1.c3
       AND t2.c4 > 100;
    
  2. EQ和NE
    输入:比较操作(EQ和NE)
    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1 INNER JOIN tab2 t2 
        ON t1.c2 EQ t2.c2
     WHERE t1.c6 NE 1000;
    

    输出

    1
    2
    3
    4
    5
     SELECT t1.c1, t2.c2 
      FROM tab1 t1 INNER JOIN tab2 t2 
        ON t1.c2 = t2.c2
     WHERE
            t1.c6 <> 1000;
    
  3. LE和GE
    入:比较操作(LE和GE)
    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1, tab2 t2 
     WHERE t1.c3 LE 200
       AND t2.c4 GE 100;
    

    输出

    1
    2
    3
    4
     SELECT t1.c1, t2.c2 
       FROM tab1 t1, tab2 t2 
      WHERE t1.c3 <= 200
        AND t2.c4 >= 100;
    
  4. NOT=和LT
    输入:比较操作(NOT=和LT)
    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1, tab2 t2 
     WHERE t1.c3 NOT= t1.c3
       AND t2.c4 LT 100;
    

    输出

    1
    2
    3
    4
    SELECT t1.c1, t2.c2 
      FROM tab1 t1, tab2 t2 
     WHERE t1.c3 <> t1.c3
       AND t2.c4 < 100;
    
  5. IN 和 NOT IN

    详情请参见IN/NOT IN转换

    输入:IN和NOT IN
    1
    2
    3
     SELECT c1, c2
       FROM tab1
      WHERE c1 IN 'XY';
    

    输出

    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c1 = 'XY';
    

    GaussDB A 和 DWS支持IN和NOT IN操作符,特殊场景除外。

  6. IS NOT IN
    输入:IS NOT IN
    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c1 IS NOT IN (subquery);
    

    输出

    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c1 NOT IN (subquery);
    
  7. LIKE ALL 和 NOT LIKE ALL
    输入:LIKE ALL/NOT LIKE ALL
    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c3 NOT LIKE ALL ('%STR1%', '%STR2%', '%STR3%');
    

    输出

    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c3 NOT LIKE ALL (ARRAY[ '%STR1%', '%STR2%', '%STR3%' ]);
    
  8. LIKE ANY 和 NOT LIKE ANY
    输入:LIKE ANY/NOT LIKE ANY
    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c3 LIKE ANY ('STR1%', 'STR2%', 'STR3%');
    

    输出

    1
    2
    3
    SELECT c1, c2
      FROM tab1
     WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]);
    

表操作符

可以在查询的FROM子句中调用函数,该函数包含在表操作符内部。

输入:表操作符,使用RETURNS
1
2
SELECT * 
  FROM TABLE( sales_retrieve (9005) RETURNS ( store INTEGER, item CLOB, quantity BYTEINT) ) AS ret;

输出

1
2
SELECT *
  FROM sales_retrieve(9005) AS ret (store, item, quantity);
分享:

    相关文档

    相关产品

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

提交成功!

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

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

*必选

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

字符长度不能超过200

提交反馈 取消

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

智能客服提问云社区提问