《eradata培訓(xùn)》PPT課件.ppt

1、Teradata SQL Assistant,2 0 0 4 年 5 月,SQL Assistant,SQL Assistant,輸入SQL,結(jié)果,SQL歷史,數(shù)據(jù)庫(kù)，表結(jié)構(gòu),SQL Assistant,連接數(shù)據(jù)庫(kù),實(shí)行并行SQL,實(shí)行SQL,SQL Assistant,加數(shù)據(jù)庫(kù),SQL Assistant,將SQL結(jié)果輸出文檔,文檔數(shù)據(jù)插入數(shù)據(jù)庫(kù),Insert into tbl_a values (?,?,?),SQL Assistant Options,定義輸出/插入的文檔字段間隔符,SQL Assistant Options,如果有highlight， 只提交highlight 的SQL

2、,SQL Assistant Options,最高結(jié)果行數(shù),數(shù)字加千位逗號(hào),NULL用什么顯示,顯示字段標(biāo)題還是名字,SQL Assistant,從 command line 啟動(dòng) 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,包含SQL的輸入文檔,輸出文檔,可以用 windows scheduler 定時(shí)運(yùn)行,在線幫助, Help - Query Builder,

3、練習(xí),把prdview.Dim_brand 輸出文檔，用“，”作字段間隔符,Teradata SQL,2 0 0 4 年 5 月,SQL : current_date, current_timestamp(0),返回當(dāng)前系統(tǒng)日期 select current_date; Date - 04/05/18 返回當(dāng)前系統(tǒng)日期及時(shí)間 select current_timestamp(0); Current TimeStamp(0) - 2004-05-18 15:27:41+00:00 Current_timestamp(0) - CREATE SET TABLE test.demo ,NO FALL

4、BACK , 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 FORMAT YYYY-MM-DD, ts0 TIMESTAMP(0), ts6 TIMESTAMP(6), ts TIMESTAMP(6) PRIMARY INDEX ( i1 ) PARTITION BY RANGE_N(d

5、a1 BETWEEN DATE 2004-01-01 AND DATE 2004-12-31 EACH INTERVAL 1 DAY , NO RANGE OR UNKNOWN);,PI,分區(qū),MultiSet 可以插入完全相同的兩條記錄 會(huì)提高數(shù)據(jù)插入的速度 Set 不可以插入完全相同的兩條記錄 保證記錄的唯一性 由于需要查重處理，會(huì)降低數(shù)據(jù)插入的速度,練習(xí)（一）,找出以下表的PI和Partition prdmiddle.ft_mid_vcall_sum_daily prdmiddle.ft_mid_user_daily prdmiddle.ft_mid_user_daily,系統(tǒng)日歷：sy

6、s_calendar.calendar,時(shí)間范圍： 1900-01-01至2100-12-31 包括： 年/季/度/月/周/天的信息 day_of_week day_of_month day_of_year month_of_year quarter_of_year ,練習(xí)（二）,寫一句SQL找出今年是不是閏年 年數(shù)不能被4整除的是平年 年數(shù)能被4整除的“不逢百之年”是閏年 年數(shù)不能被400整除的“逢百之年”是平年 年數(shù)能被400整除的是閏年,SQL :Table Lock,以下的SQL會(huì)出現(xiàn)鎖表的情況 Insert into prdmart.dim_brand select * from A

7、; Select * from prdmart.dim_brand; 以下的SQL不會(huì)鎖表 locking table prdmart.dim_brand for access insert into prdmart.dim_brand; Select * from prdmart.dim_brand; 經(jīng)營(yíng)分析系統(tǒng)建立視圖庫(kù)Prdview來(lái)解決 REPLACE view prdview.Dim_Brand as locking table prdmart.Dim_Brand for access select all * from prdmart.Dim_Brand;,SQL : case

8、when,計(jì)算復(fù)雜條件運(yùn)算 格式一 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 ELSE C END ) FROM t1;,SQL : coalesce select extract(month from current_date); select extract(second from current_timestamp(0);,SQL : cast

9、,轉(zhuǎn)換數(shù)據(jù)類型 由于Teradata的日期型字段是以數(shù)字保存在相同中的，注意在將日期型的字段轉(zhuǎn)成數(shù)字型字段時(shí)，需要加上19000000，以轉(zhuǎn)換成正確的數(shù)字型日期； dt1 :數(shù)據(jù)類型是 date 或者是 timestamp(0),練習(xí)（三）,請(qǐng)寫出兩種將系統(tǒng)日期轉(zhuǎn)換成數(shù)字型格式的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 i

10、nt)+19000000 請(qǐng)寫出將系統(tǒng)日期轉(zhuǎn)換成字符串類型的SQL，格式：YYYY-MM-DD Select CAST(cast(current_date AS format YYYY-MM-DD ) AS VARCHAR(10) 請(qǐng)寫出將字符串2004-01-03 轉(zhuǎn)換成日期字段的SQL select cast(2004-01-03 as date format yyyy-mm-dd) Select date 2004-01-03,SQL : Rank (名次),SELECT sales_person, sales_region, sales_amount, RANK() OVER (PAR

11、TITION BY sales_region ORDER BY sales_amount DESC) as rank_sales FROM sales_table;,SQL : Rank (名次),SELECT sales_person, sales_region, sales_amount, RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC) as rank_sales FROM sales_table,QUALIFY rank_sales = 1;,練習(xí)（四）,請(qǐng)從客戶分析總表天表(Ft_mid_cus_vc

12、all_total_daily)中，統(tǒng)計(jì)按照歸屬縣(home_county)分類，得出不同品牌(brand_id)的總計(jì)費(fèi)時(shí)長(zhǎng)(total_call_dur60)排名（由大至小）的SQL：,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 :Jo

13、in,Inner join(等值關(guān)聯(lián)) 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 (左外關(guān)聯(lián)) Select * from A left outer join B On a.user_id=b.user_id 所有在表A中的記錄都會(huì)返回 Right outer join (右外關(guān)聯(lián)) Select * from A right outer join B On a.user_id=b.user_id 所有在表B中

14、的記錄都會(huì)返回 Full outer join (全外關(guān)聯(lián)) 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 code,Select a.staff_code , a.department, from table1 a left outer join table2 b on a.staff_code =

15、b.staff_code,SQL : Left Outer Join(自動(dòng)補(bǔ)空值),Join on clause 返回結(jié)果是： 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); 返回結(jié)果是： 原因：On的執(zhí)行順序在where之前；,Join Results: SQL返回的結(jié)果是：,表B,表A,結(jié)果1,

16、結(jié)果2,？,練習(xí)（五）,表一：ft_mid_user_daily,表二：ft_mid_vcall_detail_daily,問(wèn)題：請(qǐng)書寫SQL語(yǔ)句，查出話單加載日期為2004年1月1日，且計(jì)費(fèi)時(shí)長(zhǎng)之和大于20分鐘且話單數(shù)大于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=2004

17、0101 Where a.voc_call_dur6020 and a.voc_call_cnt10 and a.sum_date=20040101,其他常用函數(shù),其他運(yùn)算符,運(yùn)算符舉例,“/”除法運(yùn)算符號(hào) Integer/Integer返回字段類型：Integer； Decimal(m,n)/Integer返回字段類型：Decimal(m,n)； “intersect” 返回在兩個(gè)查詢中都存在的相同記錄（交集）； Select user_id from A intersect select user_id from B; “minus” 返回在第一查詢中出現(xiàn)，但沒(méi)有在第二個(gè)查詢的記錄（補(bǔ)集）

18、； Select user_id from A minus select user_id from B; “union/union all” 返回在兩個(gè)查詢中存在的所有記錄（全集）； Select user_id from A union select user_id from B; “intersect”/”minus”/”union”注意事項(xiàng) 兩個(gè)查詢選取的字段個(gè)數(shù)必需一樣 兩個(gè)查詢對(duì)應(yīng)的字段類型必需相同,練習(xí)（六）,請(qǐng)大家執(zhí)行以下SQL，并查看返回結(jié)果 select 100/24.00=? select 100/24=? select 100.00/24=? select cast(10

19、0 as dec(10,2)/24=?,數(shù)據(jù)類型：數(shù)字型字段,DECIMAL ( n , m ) ,數(shù)據(jù)類型：日期型字段,Teradata / Oracle 語(yǔ)法比較,dt1 :數(shù)據(jù)類型是 date 或者是 timestamp(0),Teradata / Oracle 數(shù)據(jù)類型比較,臨時(shí)表的創(chuàng)建,必需指定PI(要求：為表A指定col2為PI) Create table prdmart.A (col1 integer not null,col2 integer); 由于沒(méi)有顯式指定PI，系統(tǒng)會(huì)指定第一個(gè)字段為PI，這樣往往得不到我們想要設(shè)定的PI； 正確的是：Create prdmart.tab

20、le A(col1 inteer,col2 integer) primary index(col2); 通過(guò)Select創(chuàng)建臨時(shí)表 Create Table A as (select * from prdview.B) with data primary index(col2);會(huì)把表B中的記錄插入表A中 Create Table A as (select * from prdview.B) with no data primary index(col2);表A與表B結(jié)構(gòu)相同，但沒(méi)有數(shù)據(jù) Create Volatile Table Create Volatile A as(select * f

21、rom prdview.B) with data primary index(col2); 只會(huì)使用到用戶空間，不會(huì)占用其他系統(tǒng)空間 不需要手工Drop Table，在Session結(jié)束后自動(dòng)Drop；,導(dǎo)出表的應(yīng)用（derived table),SQL一：(沒(méi)有使用導(dǎo)出表) 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_dat

22、e=20040510 where a.sum_date=20040510 group by 1,2; SQL二：(使用導(dǎo)出表) 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

23、by 1,2; 對(duì)比： SQL一：在執(zhí)行的時(shí)候會(huì)把表a所有的字段都取道臨時(shí)空間中，再用sum_date=20040510的條件去過(guò)濾； SQL二：只會(huì)把必需用到的字段及滿足條件記錄取出，減少IO消耗； 使用時(shí)注意別名的使用 (select user_id as user_code from prdview.ft_mid_user_daily ) t(user_code),書寫SQL的要點(diǎn),對(duì)大表的關(guān)聯(lián)盡量使用PI； 對(duì)于大表的多表關(guān)聯(lián)盡量拆分成更細(xì)的步驟來(lái)完成； 在ON或WHERE子句中盡量避免過(guò)多的計(jì)算； 注意分區(qū)（Partition）的使用； 對(duì)于較大的SQL，請(qǐng)務(wù)必先使用Explain查

24、看資源消耗情況； 可以通過(guò)導(dǎo)出表將where提前，減少臨時(shí)空間的I/O操作； 盡量不要去UPDATE一張大表，改用數(shù)據(jù)遷移的方法；,INSERT操作（一）,插入空表比APPEND記錄要快很多； INSERT SELECT： 目標(biāo)表是空表； 源表和目標(biāo)表的PI是相同的； 目標(biāo)表的數(shù)據(jù)需盡量均勻分布在各個(gè)AMP上（傾斜度低）； 一個(gè)傾斜度高的目標(biāo)表可能造成性能上數(shù)百倍的差別； 對(duì)于STAR SCHEMA的PI應(yīng)該是所有維度的組合；,INSERT操作（二）,并行插入（ Multi-Statement Insert/Select）,INSERT into Summary_Table SELECT store, region,sum(sales),count