Treadata学习笔记

 

1. 当用户从前端访问Teradata时，首先要建立连接，即所谓的会话层(Session)，
这可以通过两种方式来建立。一种是标准的ODBC连接，另一种是调用层接口CLI
(Call Level Interface).

2. 工具：CLI的速度比ODBC快许多，Teradata中很多工具都是基于CLI开发的，如最常
用的查询工具BTEQ(详细说明参见附录一)、数据加载工具FastLoad和MultiLoad、
数据备份与恢复工具ASF2、数据输出工具FastExport等

3. 指令
  3.1 。与Teradata会话有关的命令是以SET SESSION开头的几个，即SET SESSION CHARSET、SET
SESSION TRANSACTION、SET SESSION SQLFLAG和SET SESSIONS。
     3.1.1 改变交易处理模式的命令如下： .SET SESSION TRANSACTION [ANSI|BTET]
     3.1.2 SQL语法的强制级别：.SET SESSION SQLFLAG [NONE|ENTRY|INTERMEDIATE]
  3.2 SHOW,EXPLAIN,HELP命令
   HELP: 显示数据库对象的信息
   SHOW: 显示数据库对象的DDL语句
   EXPLAIN: 描述执行一个交易请求的详细过程和计划，用法（ explain SQL
  3.3 宏
   3.2.1 定义宏
    CREATE MACRO macroname AS ( . . . );
   3.2.2 执行宏
    EXECUTE macroname;
   3.2.3 显示宏
    EXECUTE macroname;
   3.2.4 改变宏 REPLACE MACRO macroname AS (. . . );
   3.2.5 删除宏 DROP MACRO macroname;
   3.2.6 显示宏执行的解释 DROP MACRO macroname;
   
 
4. SQL语法的不同之处
 4.1 Like 语句 与 ALL,SOME,ANY配对
 4.2 模式匹配中使用ESCAPE字符表示_和%,如WHERE tvmname LIKE ''_Z_%'' ESCAPE ''Z'
 4.3  TITLE , FORMAT
  4.3.1 TITLE 标题 SELECT last_name
   ,first_name,salary_amount / 12 (TITLE 'MONTHLY // SALARY')
  4.3.2 FORMAT

5. 函数
   5.1 CHARACTERs 用于计算VARCHAR型数据字段的实际字符串长度
   5.2 trim
   5.3 format 如SELECT salary_amount (FORMAT ''$$$,$$9.99'');
    ! $ 美元标识符
 ! 9 数字位
 ! Z 将数字中的前缀零去除
 ! , 在指定位置插入逗号
 ! . 指定小数点位置
 ! - 在指定位置插入连字号
 ! / 在指定位置插入斜线
 ! % 在指定位置插入百分号
 ! X 字符数据，每个X代表一个字符
 ! G 图形数据.一个G代表一个逻辑字符(双字节)
 ! B 在指定位置插入空格
   5.4 属性函数 (可查询字段的元数据定义信息
    TYPE 数据类型
 TITLE 标题短语
 FORMAT 格式短语
 NAMED NAMED子句
 CHARACTERS 字符个数
   5.5 SUBSTRING (<字符串表达式> FROM <开始位置> [ FOR <长度> ])
   5.6 INDEX(母串，子串） (字符串定位函数)
   5.7 NULLIF
   5.8 COALESCE
    CASE
 WHEN <expression1> IS NOT NULL THEN <expression1>
 WHEN <expression2> IS NOT NULL THEN <expression2 >
 ...
 WHEN <expressionX> IS NOT NULL THEN <expressionX>
 ELSE NULL
 END
   
     
6. 系统日历功能
calendar_date DATE UNIQUE (标准Teradata日期)
day_of_week BYTEINT, (1-7，星期几，1代表星期天)
day_of_month BYTEINT, (1-31，本月中的第几号)
day_of_year SMALLINT, (1-366，本年中的第几天)
day_of_calendar INTEGER, (从01/01/1900开始的天, 本日历中第几天)
weekday_of_month BYTEINT, (本月中该星期几出现的次数)
week_of_month BYTEINT, (本月中第几周，以星期天到星期六为一周。0，表
示月的第一个不完整的周；1表示月的第一个完整的周)
week_of_year BYTEINT, (0-53) (本年中第几周，0表示第一个不完整的周)
week_of_calendar INTEGER, (0-n) (本日历中的第几周，0表示第一个不完整的周)
month_of_quarter BYTEINT, (1-3，本季度中第几月)
month_of_year BYTEINT, (1-12，本年中第几月)
month_of_calendar INTEGER, (1-n，本日历中第几月，从1900年1月起)
quarter_of_year BYTEINT, (1-4，本年中第几季度)
quarter_of_calendar INTEGER, (本日历中第几季度，从1900年1月起)
year_of_calendar SMALLINT, (年份，从1900起)
系统日历有一组高性能的视图，包含日历中每一天的详细信息.

7. 参数宏，以:var 表示宏变量

8. 利用WITH BY进行数据小计,它是Teradata的一个扩展特性。

9. 相交操作与合并操作 UNION , EXCEPT

10. OLAP简介
 OLAP函数与聚合函数有类似的地方：
 ! 对数据进行分组操作 (类似于GROUP BY 子句)
 ! 能够使用QUALIFY子句过滤组 (类似于HAVING 子句)
 OLAP函数又与 聚合函数不同，因为：
 ! 返回满足条件的每行的数据值，而不是组的值
 ! 不能在子查询内使用
 OLAP函数可以对下面的数据库对象或动作使用：
 ! Tables (Perm, Temp, Derived)
 ! Views
 ! INSERT/SELECT

11. OLAP 标准函数
 10.1 csum 累计加总
 10.2 MAVG(colname, n, sortlist) 移动平均数
  colname = 计算移动平均值的列
  n = 行数(< 4096)，计算时将使用，包括当前行('n' 也称为平均宽度)
  sortlist = 确定行顺序的列
 10.3 MSUM(colname, n, sortlist) 移动汇总函数
 10.4 MDIFF(colname, n, sortlist) 移动差分
  宽度n<=4096
 10.5 RANK(colname) 排队函数,即排名次
 使用排队函数的规则包括：
 ! WHERE子句限定参与排队的记录。
 ! 应用排队函数时，缺省最大的数名次最低。
 ! 缺省顺序是按排队列的降序。
 10.6 QUANTILE (quantile_constant,sortlist ASC/DESC) 分位函数 
 quantile_constant 分数
 sortlist 计分依据
 10.7 MLINREG (y, n, x) 移动线性回归函数
 10.8 SAMPLE n 简单采样
  n 是整数，表示采样n记录；n是小数，表示采样比率为n

12. OLAP 统计函数
 12.1 STDDEV_SAMP ({DISTINCT} value_expression) 样本标准偏差
 12.2 STDDEV_POP ({DISTINCT} value_expression) 全体标准偏差
 12.3 VARSAMP ({DISTINCT} value_expression) 样本变异函数
 12.4 VAR_POP ({DISTINCT} value_expression) 全体变异函数
 12.5 SKEW ({DISTINCT} value_expression) 分布函数
 12.6 KURTOSIS ({DISTINCT} value_expression) 分布峰态函数
 12.7 COVAR_POP (value_expression, value_expression) 协方差函数
 12.8 CORR (value_expression, value_expression) 相关性函数
 12.9 REG_SLOPE (value_expression1, value_expression2) 回归倾斜函数
 12.10 REG_INTERCEPT (value_expression1, value_expression2) 回归截取函数

13。 Group <index> 表示按select中的第<index>个字段分组

14. 触发器
14.1触发器动作时间 - 触发器激活的时间，在触发事件之前(BEFORE)、之后
(AFTER)，或代替(INSTEAD OF)触发事件。
参照子句 - 定义表中旧值和新值怎样命名。
触发器类型 - 触发器分行(Row)类型或语句(Statement)类型。
REFERENCING 参照规则，INSERTS不需要旧参照，DELETES不需要新参照
------------------------------------------------
行触发器  语句触发器
REFERENCING  REFERENCING
OLD AS oldrow  OLD_TABLE AS oldtable
NEW AS newrow  NEW_TABLE AS newtable
FOR EACH ROW  FOR EACH STATEMENT
14.2 启用触发器
ALTER TRIGGER [triggername] [ENABLED or DISABLED];
14.3 给触发器增加注释
COMMENT ON TRIGGER StageUpd 'After Hours Updates';

15. 临时表
15.1 分类
Teradata中有3类临时表：
! 全局临时表(Global Temporay Table) 每个session都有自己的实例
 CREATE GLOBAL TEMPORARY命令创建
 ! 基础定义是永久的，保存在数据字典中。
 ! 要物化表，要有相应SQL的权限。
 ! 空间要占用用户的“临时空间(temporary space)”。
 ! 每个会话最多可以物化32个全局临时表。
 ! 系统重启动后，还存在。
! 可变临时表(Volatile Temporay Table) 存在于一个session内
 使用CREATE VOLATILE TABLE语句创建。
 ON COMMIT PRESERVE ROWS，允许会话中的其他查询使用这个可变临时表。缺省是ON COMMIT DELETE ROWS，意味着查询提交后，数据被删除。
 LOG指示维护交易日志，NO LOG的性能更好。缺省是LOG。
 ! 一个会话中，最多有64个可变临时表。
 ! 每个可变临时表必须有唯一的名称。
 ! 可变临时表必须被会话的用户名限定。
! 导出表(Derived Table) 查询期间存在

16. 索引
 16.1 创建一个连接索引，索引将提高它覆盖的连接的性能
 CREATE JOIN INDEX cust_ord_ix
  AS SELECT (c.cust_id, cust_name),(order_id, order_status, order_date)
  FROM customer c INNER JOIN orders o
  ON c.cust_id = o.cust_id
  PRIMARY INDEX (cust_id);
  连接索引包括两部分：固定部分(第一个括号内) 和可重复部分 (第二个括号内)。类似于表达一对多关系
 16.2 简单索引
  Create Index(no) on Job
 16.3 聚合索引 ：是为了提高聚合查询的性能
  CREATE JOIN INDEX monthly_sales AS
   SELECT itemid AS Item
   ,EXTRACT(YEAR FROM salesdate) AS Yr
   ,EXTRACT(MONTH FROM salesdate) AS Mon
   ,SUM(sales) AS SumSales
   FROM daily_sales
   GROUP BY 1,2,3;
 16.4 索引覆盖查询
  创建了连接索引，由优化器来选择是否使用。如果优化器使用索引产生结果，我们称索引覆盖了查询。
  
17. 从己有表创建新表
 17.1 基于已经存在的表定义，创建空的新表。
 大部分标准的列属性都可以复制：
 ! 列名
 ! 数据类型
 ! 缺省值
 ! NOT NULL约束
 ! CHECK 约束
 ! UNIQUE 约束
 ! PRIMARY KEY约束
 ! Fallback选项(仅永久表)
 ! Journal选项(仅永久表)
 ! 所有索引(除了连接索引)
 不能复制的属性：
 ! 参照约束
 ! 触发器
 ! 统计(Statistics)
 例：CREATE TABLE dept1, FALLBACK AS department WITH NO DATA UNIQUE INDEX (department_name);
 17.2 使用子查询创建表，并选择所需的列。
 CREATE TABLE emp1 AS
  (SELECT employee_number
  ,department_number
  ,salary_amount
  FROM employee) WITH NO DATA;
 17.3 使用已有的表创建有数据的表
 CREATE TABLE dept1 AS department WITH DATA;
 记住：可变临时表和全局临时表不能使用WITH DATA选项来装载数据，必须
  使用INSERT 或INSERT SELECT来装载数据。
  
18. 存储过程
 SPL/SQL 语句
 18.1 使用LOOP语句
 ! 可以使用LOOP语句，来定义重复执行的顺序。
 ! 可以选择定义一个标签。
 ! LEAVE语句允许退出循环。
 ! 没有LEAVE语句，循环是无限的循环。
 ! 如果在循环中出现例外条件，并且没有定义例外处理，则循环和存储过程
 被终止。
 ! DECLARE语句用于说明局部变量及其数据类型和可选的缺省值。
 18.2 游标
 游标的规则：
  ! 声明游标，需要使用FOR语句。
  ! 要赋予游标一个名字，例子中的名字为cur1。
  ! 赋予循环一个名字loopvar。
  ! SELECT语句返回多行。
  ! DO语句定义对返回的每行进行的操作。
  ! PRINT语句表明打印到数据库窗口中。主要用于调式存储过程。
  ! 列名必须以循环名为限定词。(如loopvar.employee_number)
  ! END FOR说明FOR循环的边界。
  FOR loopvar AS cur1 CURSOR FOR
  SELECT employee_number, department_number FROM employee
  DO PRINT 'EmpNo:', loopvar.employee_number;
  END FOR;
 18.3 ACTIVITY_COUNT。这是一个系统变量，表示任何时刻SQL语句在该时刻处理的记录数。
 18.4 编译存储过程 
  存储在服务器上 .COMPILE FILE = sp_source;
  如果不要求将源代码存储在服务器上，则使用下列编译命令：.COMPILE FILE = sp_source WITH NOSPL;
  当创建使用ODBC或JDBC的存储过程时，编译在创建过程时自动进行。用户只提交CREATE PROCEDURE语句，如果创建成功，存储过程就可以执行了。在
  BTEQ中，用户必须显式提交一个编译步骤。
 18.5 异常处理
  处理器有两种形式：
   ! EXIT处理 - 在执行了异常处理的动作后，过程终止。
   ! CONTINUE处理 - 在执行了异常处理的动作后，过程从异常语句的下一条语句继续执行。
  处理器可以选择使用两个保留字：
  ! SQLSTATE - 返回特定的错误码。
  ! SQLEXCEPTION - 告诉处理程序处理所有的异常，除指定为SQLSTATE的异常外。
 18.6 可写游标
  可更新游标的下列规则：
  ! 通过SELECT语句检索到所有记录。
  ! 每条记录单独测试。
  ! 对测试的记录可以直接修改，不需重新定位。
  ! SELECT语句申请了读级锁。
  ! UPDATE语句申请了行哈稀(row-hash)写级锁。
  ! 创建者对employee表有读和写的权限。
  可更新游标符合下列规则：
  ! 能够在ANSI模式下创建和执行。
  ! 允许对当前位置进行多次修改。
  ! 在修改后，还允许对当前位置执行删除操作。
  ! 定义触发器的表不能使用游标。
  游标中CURRENT OF <cursor name> 表示游标中的当前行
 18.7 系统变量,使用:变量名 引用
  SQL_STATE 是ANSI标准的异常错误码，类型为CHAR(5)
  SQL_CODE 保留字SQLCODE包含与异常相关的Teradata的错误码(7627)
  ACTIVITY_COUNT 表示任何时刻SQL语句在该时刻处理的记录数。
 18.9 ANSI交易模式
 ANSI模式允许存储过程包括下列SQL语句：
 ! COMMIT
 ! ABORT
 ! ROLLBACK
 Teradata模式允许存储过程包括下列SQL语句：
 ! BEGIN TRANSACTION 或BT
 ! END TRANSACTION 或ET
 ! ABORT
 ! ROLLBACK
 在ANSI模式下，一条SQL语句有4种可能的结果：
 成功条件：
 
SQL成功完成
 
SQLSTATE = '00000'
 
ACTIVITY_COUNT = # rows affected
 
交易继续
 警告条件：
 
SQL完成，显示警告信息
 
SQLSTATE > '00000'
 
ACTIVITY_COUNT = # rows affected
 
交易继续
 错误条件：
 
SQL遇到错误条件
 
SQLSTATE > '00000'
 
请求被回滚
 
交易继续
 失败条件：
 
发生下列一个条件：
 . 检测到死锁
 . DDL语句中断了
 . 遇到一条ROLLBACK或ABORT语句
 
SQLSTATE > '00000'
 
交易被回滚并结束

19. BTEQ Basic Teradata Query
 19.1 登陆 .logon systemname/username
 19.2 登出 .logoff , .quit
 19.3 显示所有的BTEQ命令 .help bteq
 19.4 要将BTEQ的输出保存到一个文件中 .export file = output_file
 19.5 将BTEQ的输出恢复到标准输出 .export reset
 19.6 BTEQ中的注释和C语言中的注释一样，以"/*"开头，以" */"结尾，可以跨行