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 ProgramFiles NCR TeradataSQLAssistant6 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 Current timestamp 6 SQL showtable 返回表结构showtabletest demo CREATESETTABLEtest demo NOFALLBACK NOBEFOREJOURNAL NOAFTERJOURNAL i1INTEGER c1CHAR 10 CHARACTERSETLATINNOTCASESPECIFIC v1VARCHAR 10 CHARACTERSETLATINNOTCASESPECIFIC d1DECIMAL 4 2 da1DATEFORMAT YYYY MM DD ts0TIMESTAMP 0 ts6TIMESTAMP 6 tsTIMESTAMP 6 PRIMARYINDEX i1 PARTITIONBYRANGE N da1BETWEENDATE 2004 01 01 ANDDATE 2004 12 31 EACHINTERVAL 1 DAY 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 case when 计算复杂条件运算格式一SELECT CASEprodSTATUSWHEN1THEN A WHEN2THEN B ELSE C END FROMt1 格式二SELECT CASEWHENprodSTATUS 1THEN A WHENprodSTATUS 2THEN B ELSE C END FROMt1 SQL coalesce nullif Coalesce selectcoalesce f1 f2 f3 fromt1Nullif 遇空值 NULL 时 返回指定的内容Selectnullif brand id 0 selectcoalesce brand id 0 SQL extract 从时间中抽取year month day hour minute second例 selectextract dayfromcurrent date selectextract monthfromcurrent date selectextract secondfromcurrent timestamp 0 SQL cast 转换数据类型由于Teradata的日期型字段是以数字保存在相同中的 注意在将日期型的字段转成数字型字段时 需要加上19000000 以转换成正确的数字型日期 dt1 数据类型是date或者是timestamp 0 练习 三 请写出两种将系统日期转换成数字型格式的SQL 格式 YYYYMMDD Selectextract yearfromcurrent date 10000 extract monthfromcurrent date 100 extract dayfromcurrent date Selectcast current dateasint 19000000请写出将系统日期转换成字符串类型的SQL 格式 YYYY MM DD SelectCAST cast current dateASformat YYYY MM DD ASVARCHAR 10 请写出将字符串 2004 01 03 转换成日期字段的SQLselectcast 2004 01 03 asdateformat yyyy mm dd Selectdate 2004 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 b namefromtable1a table2bwherea staff code b staffcode Selecta staff code a department b namefromtable1aleftouterjointable2bona staff code b staff code SQL LeftOuterJoin 自动补空值 Joinonclause whereclause SQL1 SELECTOfferings CourseNo Enrollment EmpNoFROMOfferingsaLEFTJOINEnrollmentbONOfferings CourseNo Enrollment CourseNoWHEREb Location ElSegundo 返回结果是 SQL2 SELECTOfferings CourseNo Enrollment EmpNoFROMOfferingsaLEFTOUTERJOINEnrollmentbON b Location ElSegundo AND Offerings CourseNo Enrollment CourseNo 返回结果是 原因 On的执行顺序在where之前 Join groupby 有两张表数据如下 SQL Selecta user id sum b sum fee fromALeftjoinBona user id b user idgroupby1 Results 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 dur60 20anda voc call cnt 10anda 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 INSERT