teradata基础培训

1、Teradata SQL Assistant 2 0 0 4 年 5 月 SQL Assistant SQL Assistant 输入输入SQL 结果结果 SQL历史历史 数据数据 库，库， 表结表结 构构 SQL Assistant 连接数据库连接数据库 实行并行实行并行 SQL 实行实行SQL SQL Assistant 加数据库加数据库 SQL Assistant 将将SQL结果输出结果输出 文档文档 文档数据插入数文档数据插入数 据库据库 Insert into tbl_a values (?,?,?) SQL Assistant Options 定义输定义输 出出/插入插入 的文档的

2、文档 字段间字段间 隔符隔符 SQL Assistant Options 如果有highlight， 只提交highlight 的SQL SQL Assistant Options 最高结果行数 数字加千位逗 号 NULL用什么显 示 显示字段标题 还是名字 SQL Assistant 从 command line 启动 SQL Assistant queryman.exe -c fjbi-devwp -f c:test.sql -e c:test.txt (queryman.exe 在C:Program FilesNCRTeradata SQL Assistant 6.2） ODBC DSN

3、 包含包含SQL的输入的输入 文档文档 输出文档输出文档 可以用可以用 windows scheduler 定时运行定时运行 在线帮助在线帮助 Help - Query Builder 练习练习 把prdview.Dim_brand 输出文档，用“，”作字段间隔符 Teradata SQL 2 0 0 4 年 5 月 SQL : current_date, current_timestamp(0) 返回当前系统日期返回当前系统日期 select current_date; Date - 04/05/18 返回当前系统日期及时间返回当前系统日期及时间 select current_timesta

4、mp(0); Current TimeStamp(0) - 2004-05-18 15:27:41+00:00 Current_timestamp(0) - CREATE SET TABLE test.demo ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( i1 INTEGER, c1 CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, v1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, d1 DECIMAL(4,2), da1 DATE

5、FORMAT YYYY-MM-DD, ts0 TIMESTAMP(0), ts6 TIMESTAMP(6), ts TIMESTAMP(6) PRIMARY INDEX ( i1 ) PARTITION BY RANGE_N(da1 BETWEEN DATE 2004-01-01 AND DATE 2004-12-31 EACH INTERVAL 1 DAY , NO RANGE OR UNKNOWN); PI 分区分区 MultiSet 可以插入完全相同 的两条记录 会提高数据插入的 速度 Set 不可以插入完全相 同的两条记录 保证记录的唯一性 由于需要查重处理， 会降低数据插入的 速度

6、练习（一）练习（一） 找出以下表的PI和Partition prdmiddle.ft_mid_vcall_sum_daily prdmiddle.ft_mid_user_daily prdmiddle.ft_mid_user_daily 系统日历：系统日历：sys_calendar.calendar 时间范围： 1900-01-01至2100-12-31 包括： 年/季/度/月/周/天的信息 day_of_week day_of_month day_of_year month_of_year quarter_of_year 练习（二）练习（二） 写一句SQL找出今年是不是闰年 年数不能被4整除

7、的是平年 年数能被4整除的“不逢百之年”是闰年 年数不能被400整除的“逢百之年”是平年 年数能被400整除的是闰年 SQL :Table Lock 以下的SQL会出现锁表的情况 Insert into prdmart.dim_brand select * from A; Select * from prdmart.dim_brand; 以下的SQL不会锁表 locking table prdmart.dim_brand for access insert into prdmart.dim_brand; Select * from prdmart.dim_brand; 经营分析系统建立视图库P

8、rdview来解决 REPLACE view prdview.Dim_Brand as locking table prdmart.Dim_Brand for access select all * from prdmart.Dim_Brand; SQL : case when 计算复杂条件运算 格式一 SELECT (CASE prodSTATUS WHEN 1 THEN A WHEN 2 THEN B ELSE C END ) FROM t1; 格式二 SELECT (CASE WHEN prodSTATUS = 1 THEN A WHEN prodSTATUS = 2 THEN B EL

9、SE C END ) FROM t1; SQL : coalesce select extract(month from current_date); select extract(second from current_timestamp(0); SQL : cast 转换数据类型 由于Teradata的日期型字段是以数字保存在相同中的，注意在将日期型的字段转成数字型字 段时，需要加上19000000，以转换成正确的数字型日期； dt1 :数据类型是 date 或者是 timestamp(0) date/timestamp(0) - charselect cast (cast(dt1 as

10、format YYYYMMDD) as char(8) char - date select cast ( 20040815 as date format YYYYMMDD) select cast ( 2004-08-15 as date) (YYYY-MM-DD 是预定格式，不用写) char - timestmap(0) select cast ( 20040815 212301 as timestamp(0) format YYYYMMDDbHHMISS) select cast ( 2004-08-15 21:23:01 as timestamp(0) (YYYY-MM-DDbHH:

11、MI:SS 是预定格式，不用写) integer/decimal - charselect cast (1 as char(10) char - integer/decimal select cast (11 as integer) 练习（三）练习（三） 请写出两种将系统日期转换成数字型格式的SQL：格 式：YYYYMMDD Select extract(year from current_date)*10000+ extract(month from current_date)*100 +extract(day from current_date) Select cast(current_d

12、ate as int)+19000000 请写出将系统日期转换成字符串类型的SQL，格式：YYYY-MM-DD Select CAST(cast(current_date AS format YYYY-MM-DD ) AS VARCHAR(10) 请写出将字符串2004-01-03 转换成日期字段的SQL select cast(2004-01-03 as date format yyyy-mm-dd) Select date 2004-01-03 SQL : Rank (名次名次) sales_personsales_regionsales_amountrank_sales Garabald

13、iEast1001 BakerEast992 FineEast893 AdamsEast754 EdwardsWest1001 ConnorsWest992 DavisWest992 SELECT sales_person, sales_region, sales_amount, RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC) as rank_sales FROM sales_table; sales_personsales_regionsales_amountrank_sales GarabaldiEast

14、1001 EdwardsWest1001 sales_personsales_regionsales_amountrank_sales GarabaldiEast1001 BakerEast992 FineEast893 AdamsEast754 EdwardsWest1001 ConnorsWest992 DavisWest992 SQL : Rank (名次名次) SELECT sales_person, sales_region, sales_amount, RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC

15、) as rank_sales FROM sales_tableQUALIFY rank_sales = 1; sales_personsales_regionsales_amountrank_sales GarabaldiEast1001 BakerEast992 FineEast893 AdamsEast754 EdwardsWest1001 ConnorsWest992 DavisWest992 练习（四）练习（四） 请从客户分析总表天表(Ft_mid_cus_vcall_total_daily)中，统计按 照归属县(home_county)分类，得出不同品牌(brand_id)的总计费

16、 时长(total_call_dur60)排名（由大至小）的SQL： 归属县品牌总计费时长排名 101 500000000 3942 1 101 40901100150 3794 2 101 32000000000 3190 3 102 35300000000 1843 1 102 4201100116 1828 2 102 4201100116 1739 3 102 40201100116 1736 4 select home_county,brand_id,total_call_dur60 ,rank() over(partition by home_county order by tot

17、al_call_dur60 desc) as bill_dur_rank from PRDVIEW.Ft_mid_cus_vcall_total_daily where sum_date=20040410 SQL :Join Inner join(等值关联) Select * from A inner join B On a.user_id=b.user_id; Select * from A,B where a.user_id=b.user_id; Left outer join (左外关联) Select * from A left outer join B On a.user_id=b.

18、user_id 所有在表A中的记录都会返回 Right outer join (右外关联) Select * from A right outer join B On a.user_id=b.user_id 所有在表B中的记录都会返回 Full outer join (全外关联) Select * from A full outer join B On a.user_id=b.user_id 返回表A及表B的并集 Select a.staff_code , a.department, from table1 a, table2 b where a.staff_code = b.s

19、taff code Select a.staff_code , a.department, from table1 a left outer join table2 b on a.staff_code = b.staff_code staff_code departmentname C100营销李安 C200计费梁朝伟 staff_code departmentname C100营销李安 C200计费梁朝伟 C300营销 SQL : Left Outer Join(自动补空值自动补空值) staff_code name C100李安 C200梁朝伟 Table2 staff_co

20、de departmentlocation C100营销福州 C200计费厦门 C300营销厦门 Table1 Join on clause 返回结果是： SQL 2: SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings a LEFT OUTER JOIN Enrollment b ON (b.Location = El Segundo) AND (Offerings.CourseNo = Enrollment.CourseNo); 返回结果是： 原因：On的执行顺序在where之前； Join Results: SQL返回的结

21、果是： 表表B表表A 结果结果1结果结果2 ？ 练习（五）练习（五） user_idDec(15)用户编码PI Brand_idDec(15)用户品牌 Home_countyInteger归属县 Sum_dateInteger统计日期Partition user_idDec(15)用户编码PI voc_call_dur60Dec(15)计费时长 voc_call_CntDec(15)话单数 Sum_dateInteger话单加载日期Partition 表一：表一：ft_mid_user_daily 表二：表二：ft_mid_vcall_detail_daily 问题：请书写问题：请书写SQL语

22、句，查出话单加载日期为语句，查出话单加载日期为2004年年1月月1日，且计日，且计 费时长之和大于费时长之和大于20分钟且话单数大于分钟且话单数大于10条的用户信息，包括：用户条的用户信息，包括：用户 编码、品牌及归属县。编码、品牌及归属县。 Select a.user_id,b.brand_id,b.home_county From prdview.ft_mid_vcall_detail_daily a Left join prdview.ft_mid_user_daily b On a.user_id=b.user_id and b.sum_date=20040101 Where a.vo

23、c_call_dur6020 and a.voc_call_cnt10 and a.sum_date=20040101 其他常用函数其他常用函数 函数应用结果 10 mod 2 0 position (lo in hello)4 lower(AbC)abc upper(AbC)ABC substring(abcdefg from 3 for 2)cd trim( abc )abc 2 * 38 其他运算符其他运算符 算子例子 + - * /ANSI betweenANSI = =ANSI intersect unionANSI not and orANSI overlaps select ov

24、erlaps! where (current_date , current_date + 1) overlaps (date 2004-06-01,date 2004-06-01) 运算符举例运算符举例 “/”除法运算符号 Integer/Integer返回字段类型：Integer； Decimal(m,n)/Integer返回字段类型：Decimal(m,n)； “intersect” 返回在两个查询中都存在的相同记录（交集）； Select user_id from A intersect select user_id from B; “minus” 返回在第一查询中出现，但没有在第二个查

25、询的记录（补集）； Select user_id from A minus select user_id from B; “union/union all” 返回在两个查询中存在的所有记录（全集）； Select user_id from A union select user_id from B; “intersect”/”minus”/”union”注意事项 两个查询选取的字段个数必需一样 两个查询对应的字段类型必需相同 练习（六）练习（六） 请大家执行以下SQL，并查看返回结果 select 100/24.00=? select 100/24=? select 100.00/24=? s

26、elect cast(100 as dec(10,2)/24=? 数据类型：数字型字段数据类型：数字型字段 S I G N BYTEINT (1 byte) -128 TO +127 S I G N SMALLINT (2 bytes) -32,768 TO +32,767 S I G N INTEGER (4 bytes) -2,147,483,648 TO +2,147,483,647 DECIMAL ( n , m ) 1, 2, 4, OR 8 BYTES 数据类型：日期型字段数据类型：日期型字段 TIME WITH ZONE (8 bytes) hh:mm:ss.ssssss +/-

27、hh.h TIME (6 bytes) hh:mm:ss.ssssss DATE (4 bytes) ( ( year - 1900) * 10000 ) + (mm * 100) + (dd) DATE + TIME DATE + TIME + ZONE TIMESTAMP (10 bytes) TIMESTAMP WITH ZONE (12 bytes) Teradata / Oracle 语法比较语法比较 dt1 :数据类型是 date 或者是 timestamp(0) OracleTeradata nvl(f1,f2,)coalesce(f1,f2) decode(AA,V1,R1,V

28、2,R2) case when AA=V1 then R1 when AA=V2 then R2 else NULL end to_char(d1,YYYYMMDD) select cast (cast(dt1 as format YYYYMMDD) as char(8) to_date(20040815,YYYYMMDD) select cast ( 20040815 as date format YYYYMMDD) select cast ( 20040815 212301 as timestamp(0) format YYYYMMDDbHHMISS) nvl(f1,0)zeroifnul

29、l(f1) select from A, B where A.f1 = B.f2 (+) select from A left outer join B on A.f1 = B.f2 Teradata / Oracle 数据类型比较数据类型比较 OracleTeradata INTEGERINTEGER CHAR CHAR VARCHAR2 VARCHAR NUMBER(M,N) DECIMAL(M+N,N) DATE DATE : 只存日子 INTEGER : 用hhmmss形式存时间，e.g.200102 TIMESTAMP(0) : 存日子，时间，秒数到整数 (不常用） TIMESTAM

30、P(6) : 存日子，时间，秒数到小数后六个位（极不常用，不推荐使 用。） TIMESTAMP : TIMESTAMP(6) (如果不给小数位，系统会自动给六个位) 临时表的创建临时表的创建 必需指定PI(要求：为表A指定col2为PI) Create table prdmart.A (col1 integer not null,col2 integer); 由于没有显式指定PI，系统会指定第一个字段为PI，这样往往得不到我们想 要设定的PI； 正确的是：Create prdmart.table A(col1 inteer,col2 integer) primary index(col2);

31、通过Select创建临时表 Create Table A as (select * from prdview.B) with data primary index(col2);会把表B中的记录插入表A中 Create Table A as (select * from prdview.B) with no data primary index(col2);表A与表B结构相同，但没有数据 Create Volatile Table Create Volatile A as(select * from prdview.B) with data primary index(col2); 只会使用到用

32、户空间，不会占用其他系统空间 不需要手工Drop Table，在Session结束后自动Drop； 导出表的应用（导出表的应用（derived table) SQL一：(没有使用导出表) Select a.user_id,a.user_status,sum(b.sum_fee) from prdview.ft_mid_user_daily a left join prdview.ft_mid_subtotalbill_daily b on a.user_id=b.user_id and b.sum_date=20040510 where a.sum_date=20040510 group by

33、 1,2; SQL二：(使用导出表) Select a.user_id,a.user_status,sum(b.sum_fee) from (select user_id,user_status from prdview.ft_mid_user_daily where sum_date=20040510) a left join prdview.ft_mid_subtotalbill_daily b on a.user_id=b.user_id and b.sum_date=20040510 group by 1,2; 对比： SQL一：在执行的时候会把表a所有的字段都取道临时空间中，再用 s

34、um_date=20040510的条件去过滤； SQL二：只会把必需用到的字段及满足条件记录取出，减少IO消耗； 使用时注意别名的使用 (select user_id as user_code from prdview.ft_mid_user_daily ) t(user_code) 书写书写SQL的要点的要点 对大表的关联尽量使用PI； 对于大表的多表关联尽量拆分成更细的步骤来完成； 在ON或WHERE子句中尽量避免过多的计算； 注意分区（Partition）的使用； 对于较大的SQL，请务必先使用Explain查看资源消耗情况； 可以通过导出表将where提前，减少临时空间的I/O操作； 尽量不要去UPDATE一张大表，改用数据迁移的方法； INSERT操作（一