teradata培訓文檔.pptVIP

TeradataSQLAssistant 2004年5月 SQLAssistant SQLAssistant 輸入SQL 結(jié)果 SQL歷史 數(shù)據(jù)庫 表結(jié)構(gòu) SQLAssistant 連接數(shù)據(jù)庫 實行并行SQL 實行SQL SQLAssistant 加數(shù)據(jù)庫 SQLAssistant 將SQL結(jié)果輸出文檔 文檔數(shù)據(jù)插入數(shù)據(jù)庫 Insertintotbl avalues SQLAssistantOptions 定義輸出 插入的文檔字段間隔符 SQLAssistantOptions 如果有highlight 只提交highlight的SQL SQLAssistantOptions 最高結(jié)果行數(shù) 數(shù)字加千位逗號 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 返回當前系統(tǒng)日期selectcurrent date Date 04 05 18返回當前系統(tǒng)日期及時間selectcurrent timestamp 0 CurrentTimeStamp 0 2004 05 1815 27 41 00 00Current timestamp 0 Current timestamp 6 SQL showtable 返回表結(jié)構(gòu)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 分區(qū) MultiSet可以插入完全相同的兩條記錄會提高數(shù)據(jù)插入的速度Set不可以插入完全相同的兩條記錄保證記錄的唯一性由于需要查重處理 會降低數(shù)據(jù)插入的速度 練習 一 找出以下表的PI和Partitionprdmiddle ft mid vcall sum dailyprdmiddle ft mid user dailyprdmiddle ft mid user daily 系統(tǒng)日歷 sys calendar calendar 時間范圍 1900 01 01至2100 12 31包括 年 季 度 月 周 天的信息day of weekday of monthday of yearmonth of yearquarter of year 練習 二 寫一句SQL找出今年是不是閏年年數(shù)不能被4整除的是平年年數(shù)能被4整除的 不逢百之年 是閏年年數(shù)不能被400整除的 逢百之年 是平年年數(shù)能被400整除的是閏年 SQL TableLock 以下的SQL會出現(xiàn)鎖表的情況Insertintoprdmart dim brandselect fromA Select fromprdmart dim brand 以下的SQL不會鎖表lockingtableprdmart dim brandforaccessinsertintoprdmart dim brand Select fromprdmart dim brand 經(jīng)營分析系統(tǒng)建立視圖庫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 時 返回指定的內(nèi)容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 轉(zhuǎn)換數(shù)據(jù)類型由于Teradata的日期型字段是以數(shù)字保存在相同中的 注意在將日期型的字段轉(zhuǎn)成數(shù)字型字段時 需要加上19000000 以轉(zhuǎn)換成正確的數(shù)字型日期 dt1 數(shù)據(jù)類型是date或者是timestamp 0 練習 三 請寫出兩種將系統(tǒng)日期轉(zhuǎn)換成數(shù)字型格式的SQL 格式 YYYYMMDD Selectextract yearfromcurrent date 10000 extract monthfromcurrent date 100 extract dayfromcurrent date Selectcast current dateasint 19000000請寫出將系統(tǒng)日期轉(zhuǎn)換成字符串類型的SQL 格式 YYYY MM DD SelectCAST cast current dateASformat YYYY MM DD ASVARCHAR 10 請寫出將字符串 2004 01 03 轉(zhuǎn)換成日期字段的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 中 統(tǒng)計按照歸屬縣 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 等值關聯(lián) Select fromAinnerjoinBOna user id b user id Select fromA Bwherea user id b user id Leftouterjoin 左外關聯(lián) Select fromAleftouterjoinBOna user id b user id所有在表A中的記錄都會返回Rightouterjoin 右外關聯(lián) Select fromArightouterjoinBOna user id b user id所有在表B中的記錄都會返回Fullouterjoin 全外關聯(lián) 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 返回結(jié)果是 SQL2 SELECTOfferings CourseNo Enrollment EmpNoFROMOfferingsaLEFTOUTERJOINEnrollmentbON b Location ElSegundo AND Offerings CourseNo Enrollment CourseNo 返回結(jié)果是 原因 On的執(zhí)行順序在where之前 Join groupby 有兩張表數(shù)據(jù)如下 SQL Selecta user id sum b sum fee fromALeftjoinBona user id b user idgroupby1 Results SQL返回的結(jié)果是 表B 表A 結(jié)果1 結(jié)果2 練習 五 表一 ft mid user daily 表二 ft mid vcall detail daily 問題 請書寫SQL語句 查出話單加載日期為2004年1月1日 且計費時長之和大于20分鐘且話單數(shù)大于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 其他常用函數(shù) 其他運算符 運算符舉例 除法運算符號Integer Integer 返回字段類型 Integer Decimal m n Integer 返回字段類型 Decimal m n intersect 返回在兩個查詢中都存在的相同記錄 交集 Selectuser idfromAintersectselectuser idfromB minus 返回在第一查詢中出現(xiàn) 但沒有在第二個查詢的記錄 補集 Selectuser idfromAminusselectuser idfromB union unionall 返回在兩個查詢中存在的所有記錄 全集 Selectuser idfromAunionselectuser idfromB intersect minus union 注意事項兩個查詢選取的字段個數(shù)必需一樣兩個查詢對應的字段類型必需相同 練習 六 請大家執(zhí)行以下SQL 并查看返回結(jié)果select100 24 00 select100 24 select100 00 24 selectcast 100asdec 10 2 24 數(shù)據(jù)類型 數(shù)字型字段 DECIMAL n m 數(shù)據(jù)類型 日期型字段 Teradata Oracle語法比較 dt1 數(shù)據(jù)類型是date或者是timestamp 0 Teradata Oracle數(shù)據(jù)類型比較 臨時表的創(chuàng)建 必需指定PI 要求 為表A指定col2為PI Createtableprdmart A col1integernotnull col2integer 由于沒有顯式指定PI 系統(tǒng)會指定第一個字段為PI 這樣往往得不到我們想要設定的PI 正確的是 Createprdmart tableA col1inteer col2integer primaryindex col2 通過Select創(chuàng)建臨時表CreateTableAas select fromprdview B withdataprimaryindex col2 會把表B中的記錄插入表A中CreateTableAas select fromprdview B withnodataprimaryindex col2 表A與表B結(jié)構(gòu)相同 但沒有數(shù)據(jù)CreateVolatileTableCreateVolatileAas select fromprdview B withdataprimaryindex col2 只會使用到用戶空間 不會占用其他系統(tǒng)空間不需要手工DropTable 在Session結(jié)束后自動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一 在執(zhí)行的時候會把表a所有的字段都取道臨時空間中 再用sum date 20040510的條件去過濾 SQL二 只會把必需用到的字段及滿足條件記錄取出 減少IO消耗 使用時注意別名的使用 selectuser idasuser codefromprdview ft mid user daily t user code 書寫SQL的要點 對大表的關聯(lián)盡量使用PI 對于大表的多表關聯(lián)盡量拆分成更細的步驟來完成 在ON或WHERE子句中盡量避免過多的計算 注意分區(qū) Partition 的使用 對于較大的SQL 請務必先使用Explain查看資源消耗情況 可以通過導出表將where提前 減少臨時空間的I O操作 盡量不要去UPDATE一張大表 改用數(shù)據(jù)遷移的方法 INSERT操作 一 插入空表比APPEND記錄要快很多 INSERTSELECT 目標表是空表 源表和目標表的PI是相同的 目標表的數(shù)據(jù)需盡量均勻分布在各個AMP上 傾斜度低 一個傾斜度高的目標表可能造成性能上數(shù)百倍的差別 對于STARSCHEMA的PI應該是所有維度的組合 INSERT操作 二 并行插入 Multi StatementInsert Select INSERT