中国移动培训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定义输定义输出出/插入插入的文档的文档字段间字段间隔符隔符 SQL Ass

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

3、dowsscheduler定时运行定时运行 在线帮助在线帮助 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_timestamp(0); Current TimeStamp(0)-2004-05-18 15:27:41+00

4、:00Current_timestamp(0)&Current_timestamp(6) SQL : show table返回表结构show table test.demo;-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,

5、2), da1 DATE 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和Partitionprdmiddle.ft_mid_vcall_sum_dailyprdmiddle.ft_mid_user_dailyprdmiddle.ft_mid_data_detail_daily 系统日历：系统日历：sys_calendar.calendar 时间范围：1900-01-01至2100-12-31 包括：年/季/度/月/周/天的信息day_of_weekday_of_monthday_of_yearmonth_of_yearquarter_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 accessinsert into prdmart.dim_brand;Select * from prdmart.dim_brand; 经营分析系统建立视图库Prdview来解决

8、 REPLACE view prdview.Dim_Brand as locking table prdmart.Dim_Brand for access select all * from prdmart.Dim_Brand; SQL : case when计算复杂条件运算格式一SELECT (CASE prodSTATUSWHEN 1 THEN AWHEN 2 THEN BELSE CEND )FROM t1;格式二SELECT (CASEWHEN prodSTATUS = 1 THEN AWHEN prodSTATUS = 2 THEN BELSE CEND )FROM t1; SQL

9、: coalesce & zeroifnullCoalesce:select coalesce(f1,f2,f3) from t1zeroifnull:遇空值（NULL）时，返回空值Select zeroifnull(brand_id)=select coalesce(brand_id,0)f1f2f3结果ABCABCBCC SQL : extract从时间中抽取 year, month, day, hour, minute, second例：select extract(day from current_date);select extract(month from current_

10、date);select extract(second from current_timestamp(0); SQL : cast转换数据类型由于Teradata的日期型字段是以数字保存在相同中的，注意在将日期型的字段转成数字型字段时，需要加上19000000，以转换成正确的数字型日期；dt1 :数据类型是 date 或者是 timestamp(0)date/timestamp(0) - charselect cast (cast(dt1 as format YYYYMMDD) as char(8)char - dateselect cast ( 20040815 as date format

11、 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:MI:SS 是预定格式，不用写)integer/decimal - charselect cast (1 as char(10)char - integer/de

12、cimal 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_date as int)+19000000请写出将系统日期转换成字符串类型的SQL，格式：YYYY-MM-DD Select CAST(cast(current_date

13、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_salesGarabaldiEast1001BakerEast992FineEast893AdamsEast754EdwardsWest1001ConnorsWest992DavisWest992SELE

14、CT sales_person, sales_region, sales_amount,RANK() OVER (PARTITION BY sales_regionORDER BY sales_amount DESC) as rank_salesFROM sales_table; sales_personsales_regionsales_amountrank_salesGarabaldiEast1001EdwardsWest1001sales_personsales_regionsales_amountrank_salesGarabaldiEast1001BakerEast992FineEa

15、st893AdamsEast754EdwardsWest1001ConnorsWest992DavisWest992SQL : Rank (名次名次)SELECT sales_person, sales_region, sales_amount,RANK() OVER (PARTITION BY sales_regionORDER BY sales_amount DESC) as rank_salesFROM sales_tableQUALIFY rank_sales = 1;sales_personsales_regionsales_amountrank_salesGarabaldiEast

16、1001BakerEast992FineEast893AdamsEast754EdwardsWest1001ConnorsWest992DavisWest992 练习（四）练习（四）请从客户分析总表天表(Ft_mid_cus_vcall_total_daily)中，统计按照归属县(home_county)分类，得出不同品牌(brand_id)的总计费时长(total_call_dur60)排名（由大至小）的SQL：归属县品牌总计费时长排名101 500000000 3942 1 101 40901100150 3794 2 101 32000000000 3190 3 102 35300000

17、000 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 total_call_dur60 desc) as bill_dur_rank from PRDVIEW.Ft_mid_cus_vcall_total_daily where sum_date=20040410 SQL :Join Inner join(等值关联

18、) 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.user_id 所有在表A中的记录都会返回Right outer join (右外关联) Select * from A right outer join B On a.user_id=b.user_id 所有在表B中的记录都会返回Full outer jo

19、in (全外关联) 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.staff codeSelect a.staff_code , a.department, from table1 a left outer join table2 b on a.staff_code = b.staff_codestaff_code

20、 departmentnameC100营销李安C200计费梁朝伟staff_code departmentnameC100营销李安C200计费梁朝伟C300营销SQL : Left Outer Join(自动补空值自动补空值)staff_code nameC100李安C200梁朝伟Table2staff_code departmentlocationC100营销福州C200计费厦门C300营销厦门Table1 Join on clause & where clauseSQL 1: SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offe

21、rings a LEFT JOIN Enrollment b ON Offerings.CourseNo =Enrollment.CourseNo WHERE b.Location = El Segundo; 返回结果是：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的执行顺

22、序在where之前； Join & group by 有两张表数据如下：SQL: Select a.user_id,sum(b.sum_fee) from A Left join B on a.user_id=b.user_id group by 1;Results:SQL返回的结果是：表表B表表A结果结果1结果结果2？ 练习（五）练习（五）user_idDec(15)用户编码PIBrand_idDec(15)用户品牌Home_countyInteger归属县Sum_dateInteger统计日期Partitionuser_idDec(15)用户编码PIvoc_call_dur60De

23、c(15)计费时长voc_call_CntDec(15)话单数Sum_dateInteger话单加载日期Partition表一：表一：ft_mid_user_daily 表二：表二：ft_mid_vcall_detail_daily 问题：请书写问题：请书写SQL语句，查出话单加载日期为语句，查出话单加载日期为2004年年1月月1日，且计日，且计费时长之和大于费时长之和大于20分钟且话单数大于分钟且话单数大于10条的用户信息，包括：用户条的用户信息，包括：用户编码、品牌及归属县。编码、品牌及归属县。 Select a.user_id,b.brand_id,b.home_countyFrom p

24、rdview.ft_mid_vcall_detail_daily aLeft join prdview.ft_mid_user_daily bOn a.user_id=b.user_id and b.sum_date=20040101Where a.voc_call_dur6020 and a.voc_call_cnt10 and a.sum_date=20040101 其他常用函数其他常用函数函数应用结果10 mod 2 0position (lo in hello)4lower(AbC)abcupper(AbC)ABCsubstring(abcdefg from 3 for 2)cdtri

25、m( abc )abc2 * 38 其他运算符其他运算符算子例子+ - * /ANSIbetweenANSI= =ANSIintersect unionANSInot and orANSIoverlapsselect overlaps!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)；“interse

26、ct” 返回在两个查询中都存在的相同记录（交集）； Select user_id from A intersect select user_id from B;“minus” 返回在第一查询中出现，但没有在第二个查询的记录（补集）； 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”注意事项 两个查询选取的字段个数必

27、需一样 两个查询对应的字段类型必需相同 练习（六）练习（六）请大家执行以下SQL，并查看返回结果select 100/24.00=?select 100/24=?select 100.00/24=?select cast(100 as dec(10,2)/24=? 数据类型：数字型字段数据类型：数字型字段SIGNBYTEINT (1 byte)-128 TO +127SIGNSMALLINT (2 bytes)-32,768 TO +32,767SIGNINTEGER (4 bytes)-2,147,483,648 TO +2,147,483,647DECIMAL ( n , m ) 1, 2

28、, 4, OR 8 BYTES 数据类型：日期型字段数据类型：日期型字段TIME WITH ZONE (8 bytes)hh:mm:ss.ssssss +/-hh.hTIME (6 bytes)hh:mm:ss.ssssssDATE (4 bytes)( ( year - 1900) * 10000 ) + (mm * 100) + (dd)DATE + TIMEDATE + TIME + ZONETIMESTAMP (10 bytes)TIMESTAMP WITH ZONE (12 bytes) Teradata / Oracle 语法比较语法比较dt1 :数据类型是 date 或者是 ti

29、mestamp(0)OracleTeradatanvl(f1,f2,)coalesce(f1,f2)decode(AA,V1,R1,V2,R2)case when AA=V1 then R1 when AA=V2 then R2else NULLendto_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

30、 as timestamp(0) format YYYYMMDDbHHMISS)nvl(f1,0)zeroifnull(f1)select from A, B where A.f1 = B.f2 (+)select from A left outer join B on A.f1 = B.f2 Teradata / Oracle 数据类型比较数据类型比较OracleTeradataINTEGERINTEGERCHAR CHARVARCHAR2 VARCHARNUMBER(M,N) DECIMAL(M+N,N)DATEDATE : 只存日子INTEGER : 用hhmmss形式存时间，e.g.2

31、00102TIMESTAMP(0) : 存日子，时间，秒数到整数 (不常用）TIMESTAMP(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 inte

32、er,col2 integer) primary index(col2);通过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) w

33、ith data primary index(col2); 只会使用到用户空间，不会占用其他系统空间 不需要手工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

34、 where a.sum_date=20040510 group by 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;对比：

35、SQL一：在执行的时候会把表a所有的字段都取道临时空间中，再用sum_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查看资源消耗情况；可以通过导

36、出表将where提前，减少临时空间的I/O操作；尽量不要去UPDATE一张大表，改用数据迁移的方法； INSERT操作（一）操作（一）插入空表比APPEND记录要快很多；INSERT SELECT： 目标表是空表； 源表和目标表的PI是相同的；目标表的数据需尽量均匀分布在各个AMP上（倾斜度低）；一个倾斜度高的目标表可能造成性能上数百倍的差别；对于STAR SCHEMA的PI应该是所有维度的组合； INSERT操作（二）操作（二）并行插入（ Multi-Statement Insert/Select）Region_1Region_2Region_NOptimizedEmpty Target T

37、ableSpoolINSERT into Summary_TableSELECT store, region,sum(sales),count(sale_item)FROM Region_1GROUP BY 1,2;INSERT intoSummary_TableSELECTregion2, sum (sales), count(sale_item)FROMRegion_2GROUP BY 1,2 ;INSERT intoSummary_TableSELECT region3, sum(sales), count(sale_item)FROM Region_NGROUP BY 1,2; INS

38、ERT小结小结避免从大表中插入数据至小表中；源表及目标表的PI尽量一致；如果源表和目标表的体积相当，建议使用Insert Select方法；注意Multi insert select的正确使用 目标表为空表 PI一致 不能把DDL及DML语句混用 没有必要把可以用一条SQL写完的Insert，拆分成几条来并行Teradata 资源 2 0 0 4 年 5 月 Teradata 资源资源 Teradata 的资源不是无尽的不合理的SQL会浪费资源 E.g. select * from prdview.tb_huge;(在没有过滤条件的情况下抽取巨大的表） select * from tbl_a,

39、tbl_b;（没有给表关联）一个滥用资源的SQL会拖慢其他的SQL SPOOL 空间空间 SPOOL 空间是Teradata内部的临时工作空间每一个SQL都会用SPOOL 每一个用户都有SPOOL上限SQL 超越了用户的SPOOL上限 - 马上停止运行 现在的SPOOL上限可以应付绝大部分的需求 SPOOL 空间空间如果你的SQL 超越了SPOOL上限 是否SQL有错？ 有没有遗漏过滤条件？ 有没有遗漏表关联？ 可不可以用Partition,PI? 是否用了正确的表？ 有没有汇总表可用？如果真的有需要大一点的SPOOL上限 联络数据仓库管理员 其他资源相关考虑其他资源相关考虑 非紧急的访问应该

40、放在非繁忙时间 访问不应超过每天23:00 ETL时段 系统会自动关掉 IDLE 超过24 小时的 session 如果系统管理员发现有session资源消耗过大而且不合理 记下用户名 手工 abort 表分区表分区 Explainselect * from devview.Tb_seu_first_bill_item where load_time is null 非表分区非表分区 Explainselect * from devview.Tb_seu_first_bill_item where load_time /100 is null PI Explainselect * from d

41、evview.Tb_seu_first_bill_item where cdr_id = 123 PI Explainselect * from devview.Tb_seu_first_bill_item where rate_id = 123 冠豸山实验室冠豸山实验室用explain看看以下SQL有没有用PI，分区select * from devview.tb_seu_gprs where cdr_id = 100select * from devview.tb_seu_gprs where call_type = 100select * from devview.tb_seu_gprs

42、 where tx_date = 20040428 PMON 资源管理资源管理 PMON 资源管理资源管理Teradata SQL 练 习 2 0 0 4 年 5 月 练习一练习一 要求： 请写出“查询归属地区为本公司，日期为2004年4月28日的在网用户到达数的SQL” 1、在网的条件业务口径是：入网时间小于等于统计日，并且用户状态小于30； 2、Devview.Dim_Home_Area可以查到各个归属地区及归属县的编码 3、ft_mid_user_daily 考察内容： Show table Cast用法 在网的技术口径 Partition的使用 结果结果591 福州1840061592

43、 厦门997018593 宁德423146594 莆田427133595 泉州1896575596 漳州757886597 龙岩479828598 三明402929599 南平436754 select home_area_group_code,home_area_group,count(*) from devview.ft_mid_user_daily aleft join devview.dim_home_area b on a.home_county=b.home_area_codewhere sum_date=20040428 and user_status30 and join_da

44、te=date 2004-04-28group by 1,2select home_area_group_code,home_area_group,count(*) fromdevview.ft_mid_user_daily aleft join devview.dim_home_area b on a.home_county=b.home_area_codewhere sum_date=20040428 and user_status30 and cast(join_date as int)+19000000=20040428group by 1,2 练习二练习二题目：请从话单类型表Ft_m

45、id_cdr_sum_daily统计个各个地市在2004年4月28日的语音主叫(call_type in (1,205)，分品牌（分全球通、神州行、动感地带）的话单数，计费时长，通话费，长途费。通过show table 查看表的结构和分区；过滤取得语音主叫话单；通过ft_mid_cdr_sum_daily的品牌字段(brand_id)和品牌表dim_brand(user_brand)维表关联得到按新品牌架构的分全球通、神州行、动感地带；统计话单数；Explain查看语句执行的情况，是否使用分区，运行时间考察；开始执行语句。 结果：结果：(地县市/100)Brand_DescSum(话单数)Su

46、m(计费时长)Sum(通话费(厘)Sum(长途费(厘)0神州行111145000591神州行11018242208332452603440890509134870591动感地带2819684446546844931725049256140591全球通6910731250227253182740125010600592全球通5529581048556275546380138946500592神州行5913280108985311267436830326320870592动感地带1256690189820920555107037954500593神州行33601716337973581726540

47、129397250593全球通1522702643834471124019557510593未知品牌333500593动感地带159293307279381649704227060594全球通1701572802995010149022832930594神州行33020486087972576803580117819420594动感地带368608608198556663708676980595全球通13508292265890534847770216407730595动感地带2049924352302328322268089849660595神州行1294569624567573242503

48、5740633999250596动感地带343213537771548414007850290596全球通2007483022065324956017506280596神州行604655596863321151242040240999700 SQL：Selecthome_area_group,brand_desc,sum(call_cnt),sum(call_dur60),sum(cfee),sum(lfee)Fromdevview.ft_mid_cdr_sum_daily aLeft Join devview.dim_brand bOna.brand_id=b.user_brandLeft Join devview.dim_h