文档首页> 数据仓库服务 GaussDB(DWS)> 工具指南（阿布扎比区域）> DSC SQL语法迁移工具> Teradata语法迁移> 系统函数和操作符
更新时间：2023-03-17 GMT+08:00
查看PDF

系统函数和操作符

本节主要介绍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(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);
父主题： Teradata语法迁移