teradata培训文档.ppt

TeradataSQLAssistant,2004年5月,SQLAssistant,SQLAssistant,输入SQL,结果,SQL历史,数据库，表结构,SQLAssistant,连接数据库,实行并行SQL,实行SQL,SQLAssistant,加数据库,SQLAssistant,将SQL结果输出文档,文档数据插入数据库,Insertintotbl_avalues(?,?,?),SQLAssistantOptions,定义输出/插入的文档字段间隔符,SQLAssistantOptions,如果有highlight，只提交highlight的SQL,SQLAssistantOptions,最高结果行数,数字加千位逗号,NULL用什么显示,显示字段标题还是名字,SQLAssistant,从commandline启动SQLAssistantqueryman.exe-cfjbi-devwp-fc:test.sql-ec:test.txt(queryman.exe在C:ProgramFilesNCRTeradataSQLAssistant6.2）,ODBCDSN,包含SQL的输入文档,输出文档,可以用windowsscheduler定时运行,在线帮助,Help-QueryBuilder,练习,把prdview.Dim_brand输出文档，用“，”作字段间隔符,TeradataSQL,2004年5月,SQL:current_date,current_timestamp(0),返回当前系统日期selectcurrent_date;Date-04/05/18返回当前系统日期及时间selectcurrent_timestamp(0);CurrentTimeStamp(0)-2004-05-1815:27:41+00:00Current_timestamp(0)-CREATESETTABLEtest.demo,NOFALLBACK,NOBEFOREJOURNAL,NOAFTERJOURNAL(i1INTEGER,c1CHAR(10)CHARACTERSETLATINNOTCASESPECIFIC,v1VARCHAR(10)CHARACTERSETLATINNOTCASESPECIFIC,d1DECIMAL(4,2),da1DATEFORMATYYYY-MM-DD,ts0TIMESTAMP(0),ts6TIMESTAMP(6),tsTIMESTAMP(6)PRIMARYINDEX(i1)PARTITIONBYRANGE_N(da1BETWEENDATE2004-01-01ANDDATE2004-12-31EACHINTERVAL1DAY,NORANGEORUNKNOWN);,PI,分区,MultiSet可以插入完全相同的两条记录会提高数据插入的速度Set不可以插入完全相同的两条记录保证记录的唯一性由于需要查重处理，会降低数据插入的速度,练习（一）,找出以下表的PI和Partitionprdmiddle.ft_mid_vcall_sum_dailyprdmiddle.ft_mid_user_dailyprdmiddle.ft_mid_user_daily,系统日历：sys_calendar.calendar,时间范围：1900-01-01至2100-12-31包括：年/季/度/月/周/天的信息day_of_weekday_of_monthday_of_yearmonth_of_yearquarter_of_year,练习（二）,写一句SQL找出今年是不是闰年年数不能被4整除的是平年年数能被4整除的“不逢百之年”是闰年年数不能被400整除的“逢百之年”是平年年数能被400整除的是闰年,SQL:TableLock,以下的SQL会出现锁表的情况Insertintoprdmart.dim_brandselect*fromA;Select*fromprdmart.dim_brand;以下的SQL不会锁表lockingtableprdmart.dim_brandforaccessinsertintoprdmart.dim_brand;Select*fromprdmart.dim_brand;经营分析系统建立视图库Prdview来解决REPLACEviewprdview.Dim_Brandaslockingtableprdmart.Dim_Brandforaccessselectall*fromprdmart.Dim_Brand;,SQL:casewhen,计算复杂条件运算格式一SELECT(CASEprodSTATUSWHEN1THENAWHEN2THENBELSECEND)FROMt1;格式二SELECT(CASEWHENprodSTATUS=1THENAWHENprodSTATUS=2THENBELSECEND)FROMt1;,SQL:coalesceselectextract(monthfromcurrent_date);selectextract(secondfromcurrent_timestamp(0);,SQL:cast,转换数据类型由于Teradata的日期型字段是以数字保存在相同中的，注意在将日期型的字段转成数字型字段时，需要加上19000000，以转换成正确的数字型日期；dt1:数据类型是date或者是timestamp(0),练习（三）,请写出两种将系统日期转换成数字型格式的SQL：格式：YYYYMMDDSelectextract(yearfromcurrent_date)*10000+extract(monthfromcurrent_date)*100+extract(dayfromcurrent_date)Selectcast(current_dateasint)+19000000请写出将系统日期转换成字符串类型的SQL，格式：YYYY-MM-DDSelectCAST(cast(current_dateASformatYYYY-MM-DD)ASVARCHAR(10)请写出将字符串2004-01-03转换成日期字段的SQLselectcast(2004-01-03asdateformatyyyy-mm-dd)Selectdate2004-01-03,SQL:Rank(名次),SELECTsales_person,sales_region,sales_amount,RANK()OVER(PARTITIONBYsales_regionORDERBYsales_amountDESC)asrank_salesFROMsales_table;,SQL:Rank(名次),SELECTsales_person,sales_region,sales_amount,RANK()OVER(PARTITIONBYsales_regionORDERBYsales_amountDESC)asrank_salesFROMsales_table,QUALIFYrank_sales=1;,练习（四）,请从客户分析总表天表(Ft_mid_cus_vcall_total_daily)中，统计按照归属县(home_county)分类，得出不同品牌(brand_id)的总计费时长(total_call_dur60)排名（由大至小）的SQL：,selecthome_county,brand_id,total_call_dur60,rank()over(partitionbyhome_countyorderbytotal_call_dur60desc)asbill_dur_rankfromPRDVIEW.Ft_mid_cus_vcall_total_dailywheresum_date=20040410,SQL:Join,Innerjoin(等值关联)Select*fromAinnerjoinBOna.user_id=b.user_id;Select*fromA,Bwherea.user_id=b.user_id;Leftouterjoin(左外关联)Select*fromAleftouterjoinBOna.user_id=b.user_id所有在表A中的记录都会返回Rightouterjoin(右外关联)Select*fromArightouterjoinBOna.user_id=b.user_id所有在表B中的记录都会返回Fullouterjoin(全外关联)Select*fromAfullouterjoinBOna.user_id=b.user_id返回表A及表B的并集,Selecta.staff_code,a.department,fromtable1a,table2bwherea.staff_code=b.staffcode,Selecta.staff_code,a.department,fromtable1aleftouterjointable2bona.staff_code=b.staff_code,SQL:LeftOuterJoin(自动补空值),Joinonclause返回结果是：SQL2:SELECTOfferings.CourseNo,Enrollment.EmpNoFROMOfferingsaLEFTOUTERJOINEnrollmentbON(b.Location=ElSegundo)AND(Offerings.CourseNo=Enrollment.CourseNo);返回结果是：原因：On的执行顺序在where之前；,JoinResults:SQL返回的结果是：,表B,表A,结果1,结果2,？,练习（五）,表一：ft_mid_user_daily,表二：ft_mid_vcall_detail_daily,问题：请书写SQL语句，查出话单加载日期为2004年1月1日，且计费时长之和大于20分钟且话单数大于10条的用户信息，包括：用户编码、品牌及归属县。,Selecta.user_id,b.brand_id,b.home_countyFromprdview.ft_mid_vcall_detail_dailyaLeftjoinprdview.ft_mid_user_dailybOna.user_id=b.user_idandb.sum_date=20040101Wherea.voc_call_dur6020anda.voc_call_cnt10anda.sum_date=20040101,其他常用函数,其他运算符,运算符举例,“/”除法运算符号Integer/Integer返回字段类型：Integer；Decimal(m,n)/Integer返回字段类型：Decimal(m,n)；“intersect”返回在两个查询中都存在的相同记录（交集）；Selectuser_idfromAintersectselectuser_idfromB;“minus”返回在第一查询中出现，但没有在第二个查询的记录（补集）；Selectuser_idfromAminusselectuser_idfromB;“union/unionall”返回在两个查询中存在的所有记录（全集）；Selectuser_idfromAunionselectuser_idfromB;“intersect”/”minus”/”union”注意事项两个查询选取的字段个数必需一样两个查询对应的字段类型必需相同,练习（六）,请大家执行以下SQL，并查看返回结果select100/24.00=?select100/24=?select100.00/24=?selectcast(100asdec(10,2)/24=?,数据类型：数字型字段,DECIMAL(n,m),数据类型：日期型字段,Teradata/Oracle语法比较,dt1:数据类型是date或者是timestamp(0),Teradata/Oracle数据类型比较,临时表的创建,必需指定PI(要求：为表A指定col2为PI)Createtableprdmart.A(col1integernotnull,col2integer);由于没有显式指定PI，系统会指定第一个字段为PI，这样往往得不到我们想要设定的PI；正确的是：Createprdmart.tableA(col1inteer,col2integer)primaryindex(col2);通过Select创建临时表CreateTableAas(select*fromprdview.B)withdataprimaryindex(col2);会把表B中的记录插入表A中CreateTableAas(select*fromprdview.B)withnodataprimaryindex(col2);表A与表B结构相同，但没有数据CreateVolatileTableCreateVolatileAas(select*fromprdview.B)withdataprimaryindex(col2);只会使用到用户空间，不会占用其他系统空间不需要手工DropTable，在Session结束后自动Drop；,导出表的应用（derivedtable),SQL一：(没有使用导出表)Selecta.user_id,a.user_status,sum(b.sum_fee)fromprdview.ft_mid_user_dailyaleftjoinprdview.ft_mid_subtotalbill_dailybona.user_id=b.user_idandb.sum_date=20040510wherea.sum_date=20040510groupby1,2;SQL二：(使用导出表)Selecta.user_id,a.user_status,sum(b.sum_fee)from(selectuser_id,user_statusfromprdview.ft_mid_user_dailywheresum_date=20040510)aleftjoinprdview.ft_mid_subtotalbill_dailybona.user_id=b.user_idandb.sum_date=20040510groupby1,2;对比：SQL一：在执行的时候会把表a所有的字段都取道临时空间中，再用sum_date=20040510的条件去过滤；SQL二：只会把必需用到的字段及满足条件记录取出，减少IO消耗；使用时注意别名的使用(selectuser_idasuser_codefromprdview.ft_mid_user_daily)t(user_code),书写SQL的要点,对大表的关联尽量使用PI；对于大表的多表关联尽量拆分成更细的步骤来完成；在ON或WHERE子句中尽量避免过多的计算；注意分区（Partition）的使用；对于较大的SQL，请务必先使用Explain查看资源消耗情况；可以通过导出表将where提前，减少临时空间的I/O操作；尽量不要去UPDATE一张大表，改用数据迁移的方法；,INSERT操作（一）,插入空表比APPEND记录要快很多；INSERTSELECT：目标表是空表；源表和目标表的PI是相同的；目标表的数据需尽量均匀分布在各个AMP上（倾斜度低）；一个倾斜度高的目标表可能造成性能上数百倍的差别；对于STARSCHEMA的PI应该是所有维度的组合；,INSERT操作（二）,并行插入（Multi-StatementInsert/Select）,INSERTintoSummary_TableSELECTstore,region,sum(sales),count(