《teradata培训文档ppt课件》由会员分享，可在线阅读，更多相关《teradata培训文档ppt课件（51页珍藏版）》请在金锄头文库上搜索。
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 Ass
2、istant Options如果有highlight，只提交highlight 的SQL SQL Assistant Options最高结果行数数字加千位逗号NULL用什么显示显示字段标题还是名字 SQL Assistant从 command line 启动 SQL Assistantqueryman.exe -c fjbi-devwp -f c:test.sql -e c:test.txt(queryman.exe 在C:Program FilesNCRTeradata SQL Assistant 6.2）ODBC DSN包含包含SQL的输入的输入文档文档输出文档输出文档可以用可以用 win
3、dowsscheduler定时运行定时运行 在线帮助在线帮助 Help - Query Builder 练习练习把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)-2019-05-18 15:27:41+00
4、:00Current_timestamp(0)&Current_timestamp(6) SQL : show table返回表结构show table test.demo;-CREATE SET TABLE test.demo ,NO FALLBACK , 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),
5、da1 DATE FORMAT YYYY-MM-DD, ts0 TIMESTAMP(0), ts6 TIMESTAMP(6), ts TIMESTAMP(6)PRIMARY INDEX ( i1 )PARTITION BY RANGE_N(da1 BETWEEN DATE 2019-01-01 AND DATE 2019-12-31 EACH INTERVAL 1 DAY , NO RANGE OR UNKNOWN);PI分区分区MultiSet可以插入完全相同的两条记录会提高数据插入的速度Set不可以插入完全相同的两条记录保证记录的唯一性由于需要查重处理，会降低数据插入的速度 练习一）练习一
6、）找出以下表的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整除的“不逢百之年是闰年年数不能被
7、400整除的“逢百之年是平年年数能被400整除的是闰年 SQL :Table Lock以下的SQL会出现锁表的情况Insert into prdmart.dim_brand select * from A;Select * from prdmart.dim_brand;以下的SQL不会锁表locking table prdmart.dim_brand for accessinsert into prdmart.dim_brand;Select * from prdmart.dim_brand;经营分析系统建立视图库Prdview来解决REPLACE view prdview.Dim_Brand
8、 as locking table prdmart.Dim_Brand for access select all * from prdmart.Dim_Brand; SQL : case when计算复杂条件运算格式一SELECT (CASE prodSTATUSWHEN 1 THEN AWHEN 2 THEN BELSE CEND )FROM t1;格式二SELECT (CASEWHEN prodSTATUS = 1 THEN AWHEN prodSTATUS = 2 THEN BELSE CEND )FROM t1; SQL : coalesce & nullifCoalesce:sel
9、ect coalesce(f1,f2,f3) from t1Nullif:遇空值NULL时，返回指定的内容Select nullif(brand_id,0)=select coalesce(brand_id,0) SQL : extract从时间中抽取 year, month, day, hour, minute, second例：select extract(day from current_date);select extract(month from current_date);select extract(second from current_timestamp(0); SQL :
10、cast转换数据类型由于Teradata的日期型字段是以数字保存在相同中的，注意在将日期型的字段转成数字型字段时，需要加上19000000，以转换成正确的数字型日期；dt1 :数据类型是 date 或者是 timestamp(0) 练习三）练习三）请写出两种将系统日期转换成数字型格式的SQL：格式：YYYYMMDDSelect extract(year from current_date)*10000+ extract(month from current_date)*100 +extract(day from current_date)Select cast(current_date as
11、int)+19000000请写出将系统日期转换成字符串类型的SQL，格式：YYYY-MM-DDSelect CAST(cast(current_date AS format YYYY-MM-DD ) AS VARCHAR(10)请写出将字符串2019-01-03 转换成日期字段的SQLselect cast(2019-01-03 as date format yyyy-mm-dd)Select date 2019-01-03 SQL : Rank (名次名次)sales_personsales_regionsales_amountrank_salesGarabaldiEast1001Baker
12、East992FineEast893AdamsEast754EdwardsWest1001ConnorsWest992DavisWest992SELECT sales_person, sales_region, sales_amount,RANK() OVER (PARTITION BY sales_regionORDER BY sales_amount DESC) as rank_salesFROM sales_table; SQL : Rank (名次名次)SELECT sales_person, sales_region, sales_amount,RANK() OVER (PARTIT
13、ION BY sales_regionORDER BY sales_amount DESC) as rank_salesFROM sales_tableQUALIFY rank_sales = 1; 练习四）练习四）请从客户分析总表天表(Ft_mid_cus_vcall_total_daily)中，统计按照归属县(home_county)分类，得出不同品牌(brand_id)的总计费时长(total_call_dur60)排名由大至小的SQL：归属县品牌总计费时长排名101 500000000 3942 1 101 40901100150 3794 2 101 32000000000 3190
14、 3 102 35300000000 1843 1 102 4201900116 1828 2 102 4201900116 1739 3 102 40201900116 1736 4 select home_county,brand_id,total_call_dur60,rank() over(partition by home_county order by total_call_dur60 desc) as bill_dur_rankfrom PRDVIEW.Ft_mid_cus_vcall_total_dailywhere sum_date=20190410 SQL :Join In
15、ner 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中的记录都会返回Full o
16、uter join (全外关联)Select * from A full outer join B On a.user_id=b.user_id返回表A及表B的并集 Select a.staff_code , a.department, b.name from table1 a, table2 b where a.staff_code = b.staff codeSelect a.staff_code , a.department, b.name from table1 a left outer join table2 b on a.staff_code = b.staff_codeSQL :
17、 Left Outer Join(自动补空值自动补空值) Join on clause & where clauseSQL 1:SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings aLEFT JOIN Enrollment b ON Offerings.CourseNo =Enrollment.CourseNoWHERE b.Location = El Segundo;返回结果是：SQL 2:SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings aLEFT OUTE
18、R JOIN Enrollment b ON (b.Location = El Segundo)AND (Offerings.CourseNo = Enrollment.CourseNo);返回结果是：缘由：On的执行顺序在where之前； Join & group by 有两张表数据如下：SQL:Select a.user_id,sum(b.sum_fee) from A Left join B on a.user_id=b.user_id group by 1;Results:SQL返回的结果是：表表B表表A结果结果1结果结果2？ 练习五）练习五）user_idDec(15)用户编码PIB
19、rand_idDec(15)用户品牌Home_countyInteger归属县Sum_dateInteger统计日期Partitionuser_idDec(15)用户编码PIvoc_call_dur60Dec(15)计费时长voc_call_CntDec(15)话单数Sum_dateInteger话单加载日期Partition表一：表一：ft_mid_user_daily 表二：表二：ft_mid_vcall_detail_daily 问题：请书写问题：请书写SQL语句，查出话单加载日期为语句，查出话单加载日期为2019年年1月月1日，且计费日，且计费时长之和大于时长之和大于20分钟且话单数大
20、于分钟且话单数大于10条的用户信息，包括：用户编条的用户信息，包括：用户编码、品牌及归属县。码、品牌及归属县。 Select a.user_id,b.brand_id,b.home_countyFrom prdview.ft_mid_vcall_detail_daily aLeft join prdview.ft_mid_user_daily bOn a.user_id=b.user_id and b.sum_date=20190101Where a.voc_call_dur6020 and a.voc_call_cnt10 and a.sum_date=20190101 其他常用函数其他常用
21、函数 其他运算符其他运算符 运算符举例运算符举例“/”除法运算符号Integer/Integer返回字段类型：Integer；Decimal(m,n)/Integer返回字段类型：Decimal(m,n)；“intersect”返回在两个查询中都存在的相同记录交集）；Select user_id from A intersect select user_id from B;“minus”返回在第一查询中出现，但没有在第二个查询的记录补集）；Select user_id from A minus select user_id from B;“union/union all”返回在两个查询中存在的
22、所有记录选集）；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(100 as dec(10,2)/24=? 数据类型：数字型字段数据类型：数字型字段SIGNBYTEINT (1 byte)-128 TO +127SIGNSMALLINT (2 by
23、tes)-32,768 TO +32,767SIGNINTEGER (4 bytes)-2,147,483,648 TO +2,147,483,647DECIMAL ( n , m ) 1, 2, 4, OR 8 BYTES 数据类型：日期型字段数据类型：日期型字段TIME WITH ZONE (8 bytes)hh:mm:ss.ssssss +/-hh.hTIME (6 bytes)hh:mm:ss.ssssssDATE (4 bytes)( ( year - 1900) * 10000 ) + (mm * 100) + (dd)DATE + TIMEDATE + TIME + ZONETI
24、MESTAMP (10 bytes)TIMESTAMP WITH ZONE (12 bytes) 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.ta
25、ble 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 TableCreate Volatile A as(select * from
26、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_date=2
27、0190510 where a.sum_date=20190510 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=20190510) aleft join prdview.ft_mid_subtotalbill_daily b on a.user_id=b.user_id and b.sum_date=20190510 group by 1,2
28、;对比：SQL一：在执行的时候会把表a所有的字段都取道临时空间中，再用sum_date=20190510的条件去过滤；SQL二：只会把必需用到的字段及满足条件记录取出，减少IO消耗；使用时注意别名的使用(select user_id as user_code from prdview.ft_mid_user_daily ) t(user_code) 书写书写SQL的要点的要点对大表的关联尽量使用PI；对于大表的多表关联尽量拆分成更细的步骤来完成；在ON或WHERE子句中尽量避免过多的计算；注意分区Partition的使用；对于较大的SQL，请务必先使用Explain查看资源消耗情况；可以通过导
29、出表将where提前，减少临时空间的I/O操作；尽量不要去UPDATE一张大表，改用数据迁移的方法； INSERT操作一）操作一）插入空表比APPEND记录要快很多；INSERT SELECT：目标表是空表；源表和目标表的PI是相同的；目标表的数据需尽量均匀分布在各个AMP上倾斜度低）；一个倾斜度高的目标表可能造成性能上数百倍的差别；对于STAR SCHEMA的PI应该是所有维度的组合； INSERT操作二）操作二）并行插入（ Multi-Statement Insert/Select）Region_1Region_2Region_NOptimizedEmpty Target TableSpo
30、olINSERT into Summary_TableSELECT store, region,sum(sales),count(sale_item)FROM Region_1GROUP BY 1,2;INSERT intoSummary_TableSELECTregion2, sum (sales), count(sale_item)FROMRegion_2GROUP BY 1,2 ;INSERT intoSummary_TableSELECT region3, sum(sales), count(sale_item)FROM Region_NGROUP BY GROUP BY 1,2;1,
31、2; INSERT小结小结避免从大表中插入数据至小表中；源表及目标表的PI尽量一致；如果源表和目标表的体积相当，建议使用Insert Select方法；注意Multi insert select的正确使用目标表为空表PI一致不能把DDL及DML语句混用没有必要把可以用一条SQL写完的Insert，拆分成几条来并行Teradata 资源 2 0 0 4 年 5 月 Teradata 资源资源Teradata 的资源不是无尽的不合理的SQL会浪费资源E.g. select * from prdview.tb_huge;(在没有过滤条件的情况下抽取巨大的表）select * from tbl_a,tbl_b;（没有给表关联）一个滥用资源的SQL会拖慢其他的SQL SPOOL 空间空间SPOOL 空间是Teradata内部的临时工作空间每一个SQL都会用SPOOL每一个用户都有SPOOL上限SQL 超越了用户的SPOOL上限 - 马上停止运行现在的SPOOL上限可以应付绝大部分的需求 SPOOL 空间空间如果你的SQL 超越了SPOOL上限 是否SQL有错？有没有遗漏过滤条件？有没有遗漏表关联？是否用了正确的表？有没有汇总表可用？如果真的有需要大一点的SPOOL上限 联络数据仓库管理员 答疑答疑
