TD笔记

teradata 使用手册

基本数据类型

-- 基本数据类型
-- 数学类型

date 
decimal
numeric
byteint
smallint
integer
real

-- 字符串类型

char
varchar

-- 二进制

byte

-- 其他

graphic
vargraphic
long

-- 大对象

blob
clob 

sql分类

-- DDL(data definition language)
-- 创建表语句

create
drop
alter

-- DML(data manipulate language)
-- 操作语句

select 
update
delete
insert

-- DCL(data control language)
-- 操作语句

grant
revoke
give

会话建立

-- 访问之前需要进行会话连接,建立session
-- ODBC 数据库业内遵循的标准,绝大多数数据库支持
-- CLI(call level interface),是TD的一个专门编程接口,不能用于其他数据库

-- tips:
-- CLI比ODBC快
-- 客户端查询工具使用ODBC,Teradata Administrator ,Teradata SQL Assitant

帮助命令

-- help
-- show
-- explain

-- help 用于查看表的字段结构
-- show 用于查看建表语句
-- explain 用于查看sql执行过程,是否全表查询

-- tips ;
-- 我们可以用odc来查看表是哪个创建的

show table xxx;
show database xxx;

show table xxx;
show database xxx;
help user;
help database xxx;-- 横强大,可以tables 里面所有的东西

help database dbc;--查看根数据库

help sql insert ;--查看insert 命令使用

-- 没有权限去看view
show view dbc.userdb;-- 查看view
-- 查看所有的列
help column ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL.*;

sel top 10 * from DBC.Tables where TableName like '%ARTB_A_STS_QP_F3EACDTAP_DEL%';

逻辑与条件表达式子

-- = <> > < >= <= between and
-- between and 最后会转换成 >= <=
-- not in 和 in
-- like 符号
-- _站位字符

-- 注意不要留下空格

select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT     >=  'cust_1000000652' and a.EACOWNCLT <= 'cust_1000000662' order by a. EACOWNCLT desc;

explain select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT    between  'cust_1000000652' and  'cust_1000000662' order by a. EACOWNCLT desc;

-- 使用like
-- '_' 是用来占位置的

select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT  like 'cust_1000%';
explain select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT  like 'cust_1000%';

-- 使用 _

select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like '_ust%' ;

-- 使用站位字符

select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like '__st%' ;

-- 不进行转义

select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like 'cust<escpe char>_%' ;

数学运算

--- 数学运算
-- 取模
`sel 9 mod 2;`
-- 取幂
`sel 8 ** 2;`
-- 开方
`sel SQRT(9);`

系统变量

-- 系统变量

sel DATE;
sel time;
sel user;
sel DATABASE;

抽取操作

-- 数据抽取操作
-- 抽取月份,在日期基础上+30(比如现在2018-07-09 + 30 = 2018-08-08)

sel EXTRACT(month from date + 30);--基准是天(day)
sel EXTRACT(hour from time);
sel EXTRACT(second from time + 30);-- 基准是秒(second)
-- 减去一个月
sel ADD_MONTHS(date,-1);--2018-07-09--> 2018-06-09
-- 加上一个月
sel ADD_MONTHS(date , 1);--2018-07-09--> 208-08-09

cast 转换

-- cast使用
sel CAST ('200.123' as integer);
sel CAST ('400112' as integer);--会自动带三位小数
--- 默认日期格式是yyyy/mm/dd
sel CAST (current_date  as date format 'yyyy-mm-dd');

字符串操作

-- 字符串操作

sel TRIM ('         8  ');-- 结果:8
-- 去右边的空格:
sel TRIM (trailing from '   p    ');
sel length(TRIM (trailing from '   p    '));
-- 去吃左边的空格
sel TRIM (leading from '   p    ');
sel length(trim (leading from '   p    '));

-- 字符串截取
sel SUBSTRING('hello' from 3 for 2);
-- 简写成
sel substr ('hello',3,2);

-- 常用的写法

sel SUBSTRING('hello' from 0);-- 从头开始
sel SUBSTRING('hello' from 3);-- 从3个开始

-- 字符串拼接

-- index

select index ('abc','b');
select index ('abc','c')

-- 连接没有条件,叫做交叉连接(cross join)也叫乘积连接 (product join)
-- 笛卡尔积会很大的,建议不要这么搞

-- 进行hello world拼接
-- || 拼接

select 'hello'||'world';

-- case when

--coalesce

sel COALESCE(null,1,null,2);--返回1
sel coalesce(null,null,1,2);--返回1

label

goto

.if

定义变量

select cast((SELECT cast('${v_dt}' as date format 'YYYY-MM-DD') - INTERVAL '1' DAY) as date format 'YYYY-MM-DD') as v_dt; -- 日期减去1,cast 日期一定要加上''
select cast('${v_end_dt}' as date format 'YYYY-MM-DD') as v_end_dt;
select (cast('${v_dt}' as date format 'YYYY-MM-DD') - cast('${v_end_dt}' as date format 'YYYY-MM-DD')) as gap; 
.if gap > 0  then .goto LB_LOOPSTR;  -- 判断取模时候,使用mod 
.if ERRORCODE <> 0 then .quit 12;

union 和 union all 性能差别

使用 union all 会返回所有的结果,性能好一些
使用 union 会对结果去重,性能不是很好

-- uninon hui shiyong  distinct 去重

select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000650' union   
select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000655' ;    

-- uninon all 不会去重,性能不好
select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000650' 
union   all
select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000655' ;    

JOIN TIPS

1. JOIN ON 1=1 会变成 cross join 不管使用left inner full
2. full join很多事情可以用union 来替换
3. left join 中on 条件越多,右表的数据会越来越少,但是左边的不变;(只是作用于左边,不会作用于右边)

4. 输入'a,b'--->'''a'||','||'b''' 2个' ('') 表示一个'

   
   

特殊语法

replace 用的是oreplace

替换双引号,用' " ' (单引号包双引号)

select oreplace('1233','2','ttttttttt')

-- "qqqq"@@"qqqq"

sel oreplace('"qqqq"@@"qqqq"','"','------')

partition by 用法

常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

SELECT * FROM ARTB_data_D.ARTB_I_STS_QP_LOST_WARN_ID
qualify row_number() over (partition by DOC_NBR order by SEQ_NBR desc) = 1

-- oracle 可以单独使用
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1；

-- td 查询结果

SELECT DOC_NBR,rank() over (partition by DOC_NBR order by SEQ_NBR desc)  FROM ARTB_data_D.ARTB_I_STS_QP_LOST_WARN_ID 

证件号码    Rank(序号)

1 362202100033331111 1
2 362202100033331111 2
3 362202100033331111 3
4 362202100033331112 1

SELECT DOC_NBR , oreplace(tdstats.udfconcat(CUST_NM),',','@@') FROM ARTB_data_D.ARTB_I_STS_QP_LOST_WARN_ID GROUP BY DOC_NBR

-- 拼接字符串

证件号码    oreplace(udfconcat(姓名),',','@@')

1 362202100033331111 "aaa"@@"bbb"@@"ccc"
2 362202100033331112 "ddd"

参考链接

https://www.w3cschool.cn/architectroad/architectroad-single-point-system.html