TERADATA SQL 重要章节总结__By Ryan

Teradata

1、CASE语句

1.1 基于值的CASE语句

select cast(sum(case department_number when 401 then salary_amount when 501 then salary_amount 
                else 0 end) as numeric(9,2)) as total_sal_401_501 
from employee;

1.2 基于搜索的CASE语句

select last_name case when salary_amount < 30000 then 'Under $30K'
                      when salary_amount < 40000 then 'Under $40K'
                      when salary_amount < 50000 then 'Under $50K'
                 else 'Over $50K' end 
from employee order by salary_amount;

• 基于值的CASE关键词后面一定紧着一个字段；
• 基于搜索的CASE语句，更像是编程语言中的if()；

1.3 NULLIF

nullif(<表达式1>，<表达式2>）；
# 如果表达式1  = 表达式2，返回NULL；
# 如果表达式1 != 表达式2，返回表达式1的值。

select description,hourly_billing_rate/nullif(hourly_cost_rate,0) (title 'Billing to Cost Ratio')
from job
where description like '%analyst%';

1.4 COALESCE（接合）表达式

该表达式旨在返回第一个非NULL表达式的值。

select name,coalesce(office_phone,home_phone)
from phone_table;

select course_name,coalesce(num_students,0) (title '#Students')
from class_schedule;
# 此处的coalesce表达式，是为了将可能的NULL值转换为零。

2、OLAP

2.1 OLAP与聚合函数的不同

• OLAP返回满足条件的每行的数据值，而不是组的值；
• OLAP不能在子查询中使用；

2.2 TERADATA中的OLAP函数

2.2.1 CSUM

基本语法：CSUM(colname,sort list)

select salesdate,sales,csum(sales,salesdate)
from daily_sales
where salesdate between 980101 and 980301 and itemid = 10;

2.2.2 MAVG

MAVG（移动平均函数），基于预定的行数（查询宽度）计算一列的移动平均值。如果行数小于查询宽度，则基于前面已有的行来计算当前平均值。

select salesdate,itemid,sales,mavg(sales,7,salesdate) 
from daily_sales;

2.2.3 MSUM

MSUM（移动汇总平均），基于预定的查询宽度计算一列的移动汇总值。如果前面的行数小n，则仅使用前面的所有行。

select salesdate,itemid,sales,msum(sales,3,salesdate)
from daily_sales;

2.2.4 MDIFF

MDIFF（移动差分函数），基于预定的查询宽度计算一列的移动差分值。如果前面的行数小于n，则产生一个NULL代替差值。

select salesdate,itemid,sales,midiff(sales,3,salesdate)
from daily_sales;

2.2.5 RANK

简单排队：

select storeid,prodid,sales,rank(sales)
from salestbl
where storeid = 1001;
# 用where子句限定参与排队的记录。

用qualify子句，使输出限制在一定范围内：

select storeid,prodid,sales,rank(sales)
from salestbl
group by storeid    # 此处的group by子句不是做聚合，它在此的作用是控制查询范围，将rank排序限定在商店内。
qualify rank(sales) <3;

使用导出表，进行带聚合的排队：

select t.prodid,t.sumsales.rank(t.sumsales) 
from (select a.prodid,sum(a.sales) 
      from salestbl a
      group by 1) as t(prodid,sumsales)
qualify rank(sumsales ASC) <3;

2.2.6 QUANTILE

QUANTILE（分位数函数），用于将一组记录分成大致相等的部分。（默认是已将对应字段的值按升序排列好了。）

select sum(sals) 
from (select salary_amount from employee qualify quantile(100,salary_amount) >=75)  temp(sals);
# 借用导出表，计算公司前25%的薪水总和。

select employ_number,salary_amount,quantile(100,salary_amount,employee_number) 
from employee
qualify quantile(100,salary_amount) < 25;
# 显示薪水最低的25%的所有员工。

2.2.7 SAMPLE

• 基于实际的行数抽样；
• 基于行数的百分比抽样；

select department_number 
from employee 
sample .25,.50,.75/sample 10,24,35;

3、临时表

3.1 为什么要使用临时表

• 临时表是一种辅助工具，能够提高SQL操作的性能。特别是针对以下SQL操作：
  
  • 不能使用规范化的表，比如汇总表、重复分组；
  • 要求多条SQL语句完成；（不太理解是哪种情况。@_@）
  • 个人觉得临时表最大的用处是在于：它在取数中作为过渡表，以避免多次重复地访问同一张原始表；

3.2 只举一个例子

create volatile table pmarttemp.#fp_fs_base_usr as
(
sel
  I_TM_INTRVL_CD int_bill_month --月
  ,VC_USR_NBR c_usr_nbr  --用户号码
  ,BI_BRND_CD brand     --品牌
  ,BI_INNET_MO_CNT innet_dur --在网月数
from PU_MART.TB_M_BI_USR_MO 
where int_bill_month=201502 --选15年2月
      and substr(C_CMCC_BRANCH_CD,1,2)='FS' --选佛山
      and C_INNET_USR_IND='1' --在网
      and VC_USR_STS_CD<>'US30'--剔除停机用户，先留着，感觉挺有用
)with no data  --子查询创建表
primary index(c_usr_nbr)
index(int_bill_month);

用create volatile table 创建可变临时表，在session结束时，会自动丢掉，不使用数据字典。

4、分组与聚合

4.1 聚合函数有哪些

• MIN：求最小值，忽略空值；
• MAX：求最大值，忽略空值；
• SUM：求合计，忽略空值；
• COUNT：返回个数，包括空值；
• AVG：求平均数，忽略空值。

4.2 WHERE子句 和 GROUP BY子句

二者各司其职，WHERE子句将不符合条件的record剔除，GROUP BY只对符合WHERE限制的record进行分组聚合计算。

select department_number,
       sum(salary_amount)
from employee
where department_number in (401,403)
group by department_number;

4.3 GROUP BY 和 ORDER BY

在GROUP BY后添加ORDER BY，可以使得分组统计按照指定的顺序来显示。

select department_number,
       sum(salary_amount),
       avg(salary_amount)
from employee
group by department_number
order by department_number
;

4.4 GROUP BY 和 HAVING

HAVING子句用来对分组的统计的结果进行限定，只返回满足其条件的统计结果。

select department_number,
       sum(salary_amount),
       avg(salary_amount)
from employee
group by department_number
having avg(salary_amount) < 36000;

5、总计与小计

5.1 WITH BY

WITH BY有哪些优势：

• 保留明细记录，并且按照分类要求增加小计行；
• 允许小计当中嵌套小计；
• BY的作用是用于标识根据哪些字段进行分层统计；
• 输出结果根据BY后面的所有字段自动进行排序；

select last_name AS NAME,
       salary_amount AS SALARY,
       department_number AS DEPT
from employee
with sum (salary) (title `Dept Total'),
     avg (salary) (title `Dept Avg ') 
by DEPT;

5.2 用WITH BY 进行多层小计

利用WITH BY 进行多层小计时，越在后面的字段，表明它的小计和排序层次越高。

select department_number AS Dept,
       job_code AS Job,
       employee_number AS Emp,
       salary_amount AS Sal
from employee
where department_number in (401, 501)
with sum(salary_amount) (title 'Job Total') 
BY Job
with sum(salary_amount) (title 'DEPT TOTAL') 
BY Dept;

5.3 WITH语句产生最后的统计

由于没有BY，故不会进行分层统计，而只会产生最后的统计。

select employee_number,
       salary_amount
from employee
where department_number = 301
with sum(salary_amount) (title 'GRAND TOTAL')
order by employee_number;

5.4 WITH和GROUP BY

select department_number (title 'dept_no'),
       sum (salary_amount),
       avg (salary_amount)
from employee
group by department_number
with sum (salary_amount) (title 'GRAND TOTAL'),
     avg (salary_amount) (title '')
order by department_number;

6、索引

6.1 非唯一次索引（INDEX）

非唯一次索引（NUSI）是teradata的一种索引，索引的列值允许不唯一。典型地，在WHERE子句中使用索引的列，将提高查询性能。

6.1.1 普通的“非唯一次索引”

创建普通的“非唯一次索引”，即在每个AMP上都建立了一个子表。子表中包含每个索引值和基础表记录的记录号(row-id)，子表中记录按照索引值的哈稀值排序存储。这样，按照索引值进行精确查找（注意，是按照索引值进行查找！）会非常高效，但是进行范围搜索，该索引就排不上用场了。

# 在创建表时一并创建NUSI
create multiset table employee,fallback,
(
employee_number INTEGER,
job_code INTEGER,
...
)unique primary index (employee_number)
index (job_code);

# 用语法直接创建NUSI
create index (job_code) on employee;

6.1.2 适用于范围搜索的“非唯一次索引”

当NUSI是按值排序(Value Ordered NUSI)时，索引子表按数据值存储记录，而不是哈稀值。这样在进行范围查询时，这种索引非常高效。

# 在创建表时一并创建按值排序的NUSI
create multiset table employee ,fallback,
(
employee_number INTEGER,
job_code INTEGER,
...
)unique primary index ( employee_number )
index (job_code) order by values (job_code);

# 用语法直接创建按值排序的NUSI
create index (job_code) order by values (job_code) on employee;

按值排序的NUSI必须满足下列条件：

• 单一的列（一个索引对应一个字段，可以“多表连接索引”进行对比）；
• 属于索引定义中的列；
• 必须是数字列；
• 长度不能大于4个字节 – INT, SMALLINT, BYTEINT, DATE,DEC是有效的。

6.1.3 索引覆盖查询

如果一个查询只引用了索引中的列，优化器使用索引产生结果，则称这个索引“覆盖”了查询。
此时，优化器会选择访问索引子表，不会访问基础表。查询中引用列的地方包括：
(1)、SELECT 列表；
(2)、WHERE 子句；
(3)、聚合函数；
(4)、GROUP BY；
(5)、表达式；

6.2 连接索引

连接索引是一种能够提高某些类型查询的性能的索引技术，可以包含一个或多个表中的列。连接索引被创建后，由优化器决定是否使用，用户不能直接访问。连接索引的作用，是从索引子表提供数据，避免访问基础表。

连接索引有三类：

• 多表连接索引 – 预先连接多个表；
• 单表连接索引 – 按照外部键的哈稀值分布单个表的记录；
• 聚合连接索引 – 聚合一个或多个表中的列，形成汇总表；

6.2.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 key (cust_id);

整一张表都是JOIN INDEX “cust_ord_ix”，只要查询中涉及到的列在该表中，该索引就覆盖了此次查询。

select count(c.cust_id) from customer c inner join orders o
on c.cust_id = o.cust_id
where o.order_date between 990101 and 990131;

同样是上面这条SQL，在建立了JOIN INDEX 后执行，只需要0.17s（查询中涉及到的cust_id，order_date都在JOIN INDEX中，优化器会选择使用该索引）。在建立JOIN INDEX 前执行，则需要0.40s。

如果还想提高范围查询的效率呢？？？

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
order by order_date
primary key (cust_id);

由于让JOIN INDEX按照order_data的顺序排列，故再次执行上面的SQL，范围查询的效率会更高。

如果索引的记录已经按照其他列排序了，如cust_id，而连接索引只能使用一列排序。此时要怎么提高对其它字段的范围查询效率？

解决办法是：给连接索引增加非唯一次索引（NUSI）

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
order by c.cust_id 
# This ORDER BY controls how the rows of the Join Index will be sorted on the AMPs. 
primary key (cust_id)
index (order_date) order by (order_date)
# This ORDER BY controls how the rows of the NUSI will be sorted on the AMPs.
;

6.2.2 单表连接索引

在构造两表的连接计划时，优化器首先要保证连接的记录都分布在同一个AMP上。如果两表按照主索引连接，连接的记录已经分布在同一个AMP上，不需要重新分布数据。如果有一个表不使用主索引连接，就会重新分布数据。此时，创建单表连接索引，按照连接的索引值预先分布记录，避免在连接时重新分布，提高查询效率。

create multiset table employee, fallback,
(employee_number INTEGER,
department_number INTEGER,
...
)
unique primary index (employee_number);

create table department, fallback,
(department_number SMALLINT
...
)
unique primary index (department_number);

做连接查询：

select e.employee_number,
       d.department_number,
       d.department_name
from employee e inner join department d
on e.department_number = d.department_number;

基于department_number列连接两个表，会引起employee表重新分布数据。
Department表的主索引是department_number，数据是按照department_number分布的；
但employee表是按照其主索引employee_number分布的，故需要重新分布。

应该怎么优化？

在employee表上创建单表连接索引。

create join index emp_deptno
as select employee_number, department_number
from employee
primary key (department_number)
;

此时再执行上面的SQL，效率会大大提升。

6.2.3 聚合索引

查询计数、合计或平均值，需要执行聚合。如果表非常大，聚合的开销就很大，查询受影响。聚合索引类似于前面的连接索引，不同之处在于使用了合计、计数和数据抽取。内部创建了一个非规范化的汇总表，当优化器决定使用时，不必在访问基础表，查询效率大大提高。

# 执行EXPLAIN 语句
explain select extract(year from salesdate) as yr,extract(month from salesdate) as mon,sum(sales)
from daily_sales
where itemid = 10 and yr in ('1997','1998')
group by 1,2
order by 1,2;

# 创建聚合索引：
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;

# 再次执行EXPLAIN 语句
explain select extract(year from salesdate) as yr,extract(month from salesdate) as mon,sum(sales)
from daily_sales
where itemid = 10 and yr in ('1997','1998')
group by 1,2
order by 1,2;

由于聚合的结果是在该查询之前就已经生成好的，且聚合索引覆盖了该查询，优化器选择了它，查询效率提高。

7、字符串函数

7.1 SUBSTRING

基本语法为：

SUBSTRING (<字符串表达式> FROM <开始位置> [ FOR <长度> ])

select SUBSTRING (first_name FROM 1 FOR 1) (TITLE 'FI'),last_name
from employee
where department_number = 403
order by last_name;

7.2 字符串合并

字符串合并的符号是"||"，它把两个字符串串联成一个字符串。其基本语法为：

<字符串1> || <字符串2>

select TRIM (last_name) || ', '|| first_name (TITLE 'EMPLOYEE')
from employee
where department_number = 403;  
# TRIM()用于去掉'last_name'后端的'空格'；
# 若要去除前后两端的'空格'，在teradata中必须"TRIM (BOTH FROM <expression>)"；
# 若要去除前端的'空格'，则是"TRIM (LEADING FROM <expression>)"；

7.3 INDEX（字符串定位函数）

# 为了返回所有部门名称中包含"SUPPORT"的部门，并且返回出"SUPPORT"位于该部门名的起始位置。
select department_name,INDEX(department_name,'SUPPORT')
from department
where INDEX (department_name,'SUPPORT') > 0   # INDEX函数返回的是逻辑表达式，1表示真，0表示假。
order by department_number;

# 我更倾向用LIKE来做匹配：
select department_name
from department
where department_name like '%SUPPORT%';       # 在like前加上关键字'CASESPECIFIC'就可以区分大小写。

8、查询优化

8.1 数据类型

• 大于“10位”，用“varchar”；
• 小于“10位”，用“char”；

8.2 SQL编写规范

• 嵌套时，注意层次感；
• 有多条语句时，上一句的“;”放在下一句的开头。

8.3 关键的statistics

collect statistics 主要用在“关联”、“where”和“group by”上。

# 用下面的方法，可以知道当前应该收集哪里的统计信息？
DIAGNOSTIC HELPSTATS ON FOR SESSION;
Explain SELECT * FROM syscrmadm.lab_employee;    
     Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct syscrmadm."pseudo table" for read on a RowHash to prevent global deadlock for syscrmadm.lab_employee.
  2) Next, we lock syscrmadm.lab_employee for read.
  3) We do an all-AMPs RETRIEVE step from syscrmadm.lab_employee by way of an all-rows scan with no residual conditions into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of Spool 1 is estimated with low confidence to be 16 rows.  The
     estimated time for this step is 0.06 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of statement 1.  The total estimated time is 0.06 seconds.
     BEGIN RECOMMENDED STATS ->
  5) "COLLECT STATISTICS syscrmadm.lab_employee INDEX (EMPLOYEE_ID)".   (HighConf)
     <- END RECOMMENDED STATS

8.4 left join技巧

• 左表限制只能写在“where”中；
• 右表限制只能写在“on”中；

8.5 个人经验

• 尽量将临时表设定为“Multiset”（允许记录重复）；
• 尽量只访问一次原始表，用临时表做过渡；
• 去重用“group by”效率更高；
• 尽量索引（普通index）覆盖查询；
• 少用unique primary key这么强限制条件，很容易出错；