《eradata培訓》PPT課件.ppt

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

2、,SQL Assistant Options,最高結(jié)果行數(shù),數(shù)字加千位逗號,NULL用什么顯示,顯示字段標題還是名字,SQL Assistant,從 command line 啟動 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 定時運行,在線幫助, Help - Query Builder,

3、練習,把prdview.Dim_brand 輸出文檔，用“，”作字段間隔符,Teradata SQL,2 0 0 4 年 5 月,SQL : current_date, current_timestamp(0),返回當前系統(tǒng)日期 select current_date; Date - 04/05/18 返回當前系統(tǒng)日期及時間 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 可以插入完全相同的兩條記錄 會提高數(shù)據(jù)插入的速度 Set 不可以插入完全相同的兩條記錄 保證記錄的唯一性 由于需要查重處理，會降低數(shù)據(jù)插入的速度,練習（一）,找出以下表的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,時間范圍： 1900-01-01至2100-12-31 包括： 年/季/度/月/周/天的信息 day_of_week day_of_month day_of_year month_of_year quarter_of_year ,練習（二）,寫一句SQL找出今年是不是閏年 年數(shù)不能被4整除的是平年 年數(shù)能被4整除的“不逢百之年”是閏年 年數(shù)不能被400整除的“逢百之年”是平年 年數(shù)能被400整除的是閏年,SQL :Table Lock,以下的SQL會出現(xiàn)鎖表的情況 Insert into prdmart.dim_brand select * from A

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

8、when,計算復雜條件運算 格式一 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ù)字型字段時，需要加上19000000，以轉(zhuǎn)換成正確的數(shù)字型日期； dt1 :數(shù)據(jù)類型是 date 或者是 timestamp(0),練習（三）,請寫出兩種將系統(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 請寫出將系統(tǒng)日期轉(zhuǎn)換成字符串類型的SQL，格式：YYYY-MM-DD Select CAST(cast(current_date AS format YYYY-MM-DD ) AS VARCHAR(10) 請寫出將字符串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;,練習（四）,請從客戶分析總表天表(Ft_mid_cus_vc

12、all_total_daily)中，統(tǒng)計按照歸屬縣(home_county)分類，得出不同品牌(brand_id)的總計費時長(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中的記錄都會返回 Right outer join (右外關(guān)聯(lián)) Select * from A right outer join B On a.user_id=b.user_id 所有在表B中

14、的記錄都會返回 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(自動補空值),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,？,練習（五）,表一：ft_mid_user_daily,表二：ft_mid_vcall_detail_daily,問題：請書寫SQL語句，查出話單加載日期為2004年1月1日，且計費時長之和大于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ù),其他運算符,運算符舉例,“/”除法運算符號 Integer/Integer返回字段類型：Integer； Decimal(m,n)/Integer返回字段類型：Decimal(m,n)； “intersect” 返回在兩個查詢中都存在的相同記錄（交集）； Select user_id from A intersect select user_id from B; “minus” 返回在第一查詢中出現(xiàn)，但沒有在第二個查詢的記錄（補集）

18、； 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”注意事項 兩個查詢選取的字段個數(shù)必需一樣 兩個查詢對應(yī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 語法比較,dt1 :數(shù)據(jù)類型是 date 或者是 timestamp(0),Teradata / Oracle 數(shù)據(jù)類型比較,臨時表的創(chuàng)建,必需指定PI(要求：為表A指定col2為PI) Create table prdmart.A (col1 integer not null,col2 integer); 由于沒有顯式指定PI，系統(tǒng)會指定第一個字段為PI，這樣往往得不到我們想要設(shè)定的PI； 正確的是：Create prdmart.tab

20、le A(col1 inteer,col2 integer) primary index(col2); 通過Select創(chuàng)建臨時表 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結(jié)構(gòu)相同，但沒有數(shù)據(jù) Create Volatile Table Create Volatile A as(select * f

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

22、e=20040510 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

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

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