《eradata培训》PPT课件.ppt

1、Teradata SQL Assistant,2 0 0 4 年 5 月,SQL Assistant,SQL Assistant,输入SQL,结果,SQL历史,数据库，表结构,SQL Assistant,连接数据库,实行并行SQL,实行SQL,SQL Assistant,加数据库,SQL Assistant,将SQL结果输出文档,文档数据插入数据库,Insert into tbl_a values (?,?,?),SQL Assistant Options,定义输出/插入的文档字段间隔符,SQL Assistant Options,如果有highlight， 只提交highlight 的SQL

2、,SQL Assistant Options,最高结果行数,数字加千位逗号,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),返回当前系统日期 select current_date; Date - 04/05/18 返回当前系统日期及时间 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,分区,MultiSet 可以插入完全相同的两条记录 会提高数据插入的速度 Set 不可以插入完全相同的两条记录 保证记录的唯一性 由于需要查重处理，会降低数据插入的速度,练习（一）,找出以下表的PI和Partition prdmiddle.ft_mid_vcall_sum_daily prdmiddle.ft_mid_user_daily prdmiddle.ft_mid_user_daily,系统日历：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找出今年是不是闰年 年数不能被4整除的是平年 年数能被4整除的“不逢百之年”是闰年 年数不能被400整除的“逢百之年”是平年 年数能被400整除的是闰年,SQL :Table Lock,以下的SQL会出现锁表的情况 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; 经营分析系统建立视图库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、,转换数据类型 由于Teradata的日期型字段是以数字保存在相同中的，注意在将日期型的字段转成数字型字段时，需要加上19000000，以转换成正确的数字型日期； dt1 :数据类型是 date 或者是 timestamp(0),练习（三）,请写出两种将系统日期转换成数字型格式的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 请写出将系统日期转换成字符串类型的SQL，格式：YYYY-MM-DD Select CAST(cast(current_date AS format YYYY-MM-DD ) AS VARCHAR(10) 请写出将字符串2004-01-03 转换成日期字段的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)中，统计按照归属县(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(等值关联) 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 (左外关联) Select * from A left outer join B On a.user_id=b.user_id 所有在表A中的记录都会返回 Right outer join (右外关联) Select * from A right outer join B On a.user_id=b.user_id 所有在表B中

14、的记录都会返回 Full outer join (全外关联) 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 返回结果是： 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); 返回结果是： 原因：On的执行顺序在where之前；,Join Results: SQL返回的结果是：,表B,表A,结果1,

16、结果2,？,练习（五）,表一：ft_mid_user_daily,表二：ft_mid_vcall_detail_daily,问题：请书写SQL语句，查出话单加载日期为2004年1月1日，且计费时长之和大于20分钟且话单数大于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,其他常用函数,其他运算符,运算符举例,“/”除法运算符号 Integer/Integer返回字段类型：Integer； Decimal(m,n)/Integer返回字段类型：Decimal(m,n)； “intersect” 返回在两个查询中都存在的相同记录（交集）； Select user_id from A intersect select user_id from B; “minus” 返回在第一查询中出现，但没有在第二个查询的记录（补集）

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”注意事项 两个查询选取的字段个数必需一样 两个查询对应的字段类型必需相同,练习（六）,请大家执行以下SQL，并查看返回结果 select 100/24.00=? select 100/24=? select 100.00/24=? select cast(10

19、0 as dec(10,2)/24=?,数据类型：数字型字段,DECIMAL ( n , m ) ,数据类型：日期型字段,Teradata / Oracle 语法比较,dt1 :数据类型是 date 或者是 timestamp(0),Teradata / Oracle 数据类型比较,临时表的创建,必需指定PI(要求：为表A指定col2为PI) Create table prdmart.A (col1 integer not null,col2 integer); 由于没有显式指定PI，系统会指定第一个字段为PI，这样往往得不到我们想要设定的PI； 正确的是：Create prdmart.tab

20、le A(col1 inteer,col2 integer) primary index(col2); 通过Select创建临时表 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结构相同，但没有数据 Create Volatile Table Create Volatile A as(select * f

21、rom prdview.B) with data primary index(col2); 只会使用到用户空间，不会占用其他系统空间 不需要手工Drop Table，在Session结束后自动Drop；,导出表的应用（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一：在执行的时候会把表a所有的字段都取道临时空间中，再用sum_date=20040510的条件去过滤； SQL二：只会把必需用到的字段及满足条件记录取出，减少IO消耗； 使用时注意别名的使用 (select user_id as user_code from prdview.ft_mid_user_daily ) t(user_code),书写SQL的要点,对大表的关联尽量使用PI； 对于大表的多表关联尽量拆分成更细的步骤来完成； 在ON或WHERE子句中尽量避免过多的计算； 注意分区（Partition）的使用； 对于较大的SQL，请务必先使用Explain查

24、看资源消耗情况； 可以通过导出表将where提前，减少临时空间的I/O操作； 尽量不要去UPDATE一张大表，改用数据迁移的方法；,INSERT操作（一）,插入空表比APPEND记录要快很多； INSERT SELECT： 目标表是空表； 源表和目标表的PI是相同的； 目标表的数据需尽量均匀分布在各个AMP上（倾斜度低）； 一个倾斜度高的目标表可能造成性能上数百倍的差别； 对于STAR SCHEMA的PI应该是所有维度的组合；,INSERT操作（二）,并行插入（ Multi-Statement Insert/Select）,INSERT into Summary_Table SELECT store, region,sum(sales),count