实用教程(Teradata)

实用教程 Teradata 陆世潮2008年9月 问题总结 常见问题分类 表属性不对 Set Multiset问题 INSERT操作慢主索引 PI 设置不合理问题1 数据倾斜度大 空间爆满 问题2 JOIN操作 数据需要重分布 分区索引 PPI 设置不合理问题 全表扫描连接条件过于复杂问题 系统无法优化执行计划缺乏统计信息问题 系统无法找到最优化的执行计划 SQL跑得慢哈 提纲 Teradata架构常见问题 及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化 Teradata体系架构 TeradataandMPPSystems RDBMSARCH LogicalExampleofNPPIversusPPI 提纲 Teradata架构常见问题 及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化 表属性 Set MultiSet SetTable不允许记录重复MultiSetTable允许记录重复默认值 SetTableCreateTable AS 生成的目标表属性默认为SetTable对SETTable进行INSERT操作 需要检查是否存在重复记录相当的耗资源若真要限定唯一性 可以通过UPI或USI实现 CREATESETTABLEpmart RPT NM GRP PRE WARN MON CAL MonthINTEGERTITLE 统计月份 ORG NUMCHAR 12 TITLE 集团编号 City IDCHAR 3 TITLE 地市标识 ORG SUBS GRP NUMCHAR 10 TITLE 集团用户群编号 ORG TitleVARCHAR 200 TITLE 集团名称 ORG LevelCHAR 2 TITLE 集团级别 STAT Item CodeCHAR 2 TITLE 统计项 STAT ValueDECIMAL 18 2 TITLE 统计值 PRIMARYINDEX ORG NUM 例子 pmart RPT NM GRP PRE WARN MON内蒙移动集团客户预警指标月报表 假设原有1286449条记录插入 152853条记录耗时 15秒 表属性 Set MultiSet cont CREATEMULTISETTABLEpmart RPT NM GRP PRE WARN MON CAL MonthINTEGERTITLE 统计月份 ORG NUMCHAR 12 TITLE 集团编号 City IDCHAR 3 TITLE 地市标识 ORG SUBS GRP NUMCHAR 10 TITLE 集团用户群编号 ORG TitleVARCHAR 200 TITLE 集团名称 ORG LevelCHAR 2 TITLE 集团级别 STAT Item CodeCHAR 2 TITLE 统计项 STAT ValueDECIMAL 18 2 TITLE 统计值 PRIMARYINDEX ORG NUM 例子 pmart RPT NM GRP PRE WARN MON内蒙移动集团客户预警指标月报表 建议 Teradata中都用MultiSet 假设原有1286449条记录插入 152853条记录耗时 1秒 例子 CREATEMULTISETTABLEtttemp VT SUBS VIOC QUANas SELECT FROMtttemp MID SUBS VIOC QUANWHERECAL MONTH 200802AND WITHDATAPRIMARYINDEX subs id 临时表 默认为 Set需要指定为 Multiset 字段越多 记录越多差别越明显 PI PrimaryIndex主索引 的选择 PI影响数据的存储与访问 其选择标准 不同值尽量多的字段 MoreUniqueValues 使用频繁的字段 包括值访问和连接访问少更新PI字段不宜太多最好是手动指定PI 例子 用户语音业务量中间表CREATEMULTISETTABLEtttemp MID SUBS VIOC QUAN CAL MonthINTEGERTITLE 统计月份 City IDCHAR 4 TITLE 地市标识 Channel IDCHAR 8 TITLE 渠道标识 Subs idCHAR 12 TITLE 用户标识 PRIMARYINDEX subs id 例子 用户语音业务量临时表CREATEMULTISETTABLEtttemp VT SUBS VIOC QUANas SELECT FROMtttemp MID SUBS VIOC QUANWHERECAL MONTH 200802AND WITHDATAPRIMARYINDEX subs id Subs ID 频繁使用UniqueValue多 如果不指定PI 系统默认为 Cal Month PI PrimaryIndex主索引 的选择 cont 例子 梦网客户活跃客户分析CREATEMULTISETTABLEPMART FCT DATA MONNET ACTIVE MON CAL MonthINTEGERTITLE 统计月份 City IDCHAR 4 TITLE 地市标识 Channel IDCHAR 8 TITLE 渠道标识 Mont SVC Type CodCHAR 3 TITLE 梦网业务类型编码 Mont SVC CAT MicroCls CodCHAR 3 TITLE 梦网业务分类小类编码 Mont SVC CHRG Type CodCHAR 2 TITLE 梦网业务计费类型编码 THR Brand CodCHAR 1 TITLE 三大品牌编码 Mont Consume Level CodCHAR 2 TITLE 梦网消费层次编码 Consume Level CodCHAR 2 TITLE 消费层次编码 PRIMARYINDEX CAL Month City ID Channel ID Mont SVC Type Cod Mont SVC CAT MicroCls Cod Mont SVC CHRG Type Cod THR Brand Cod Mont Consume Level Cod Consume Level Cod PI 9字段 2字段 City ID Channel ID调整PI后 在右边的SQL中 PI是否起作用 以下SQL PI是否起作用 1 值访问Select FromFCT DATA MONNET ACTIVE MONWhereCity ID 070010 andChannel ID 0100 andcal month 2007072 连接访问Select FromFCT DATA MONNET ACTIVE MONALEFTJOINMID CHANNEL INFO DAILYBONA Channel ID B Channel IDandA City ID b City IDLEFTJOINVW CDE REGION TYPECONA City ID C City ID3 值访问 连接访问Select FromFCT DATA MONNET ACTIVE MONA VT INFOBWHEREA Channel ID B Channel IDANDA City ID B City IDANDA CAL MONTH 200707ANDA Consume Level Cod B Consume Level Cod PPI的使用 PPI PartitionPrimaryIndex 分区索引 把具有相同分区值的数据聚簇存放在一起 类似于SQLServer的聚簇索引 ClusterIndex Oracle的聚簇表 ClusterTable 利用PPI 可以快速插入 访问同一个Partition 分区 的数据 CREATEMULTISETTABLEqdata TB DQC KPI CHECK RESULT TX DATEDATEFORMAT YYYYMMDD TITLE 数据日期 NOTNULL KPI CODEINTEGERTITLE 指标代码 NOTNULL PRIMARYINDEX KPI CODE PARTITIONBYRANGE N TX DATEBETWEENCAST 20030101 ASDATEFORMAT YYYYMMDD ANDCAST 20191231 ASDATEFORMAT YYYYMMDD EACHINTERVAL 1 DAY NORANGEORUNKNOWN Select FromTB DQC KPI CHECK RESULTWheretx date 20070701 或Wheretx datebetween 20070701 and 20070731 或Wheretx date 20070701 但Wheretx datelike 200707 不起作用 PPI的使用 cont Partition上不要使用表达式 否则Partition不能被正确使用 T1 tx date 100 CAST 20070917 ASDATEFORMAT YYYYMMDD 100Substring T1 tx datefrom1for6 200709 应该修改为T1 tx date CAST 20070901 ASDATEFORMAT YYYYMMDD PPI的使用 cont 脚本 tb 030040270 pl 删除当月 2小时delBASS1 tb 03004whereproc dt 200709 insertintoBASS1 tb 03004 7小时 sel frompview vw evt cust socustwhereacpt date cast 200710 01 asdate cast 200710 01 asdate 写法错误 PPI不起作用日期的正确写法 Cast 20071001 asdateformat YYYYMMDD 在proc dt建立PPI PPI字段从Load Date调整为acpt date 创建可变临时表 它仅存活于同一个Session之内注意指定可变临时表为multiset 通常也要指定PI 可变临时表不能带有PPI例子1 createvolatilemultisettablevt RETAIN ANLY MONas selectcol1 col2 from where groupby withdataPRIMARYINDEX PI Cols ONCOMMITPRESERVEROWS 例子2 createvolatilemultisettablevt RETAIN ANLY MON col1char 2 col2varchar 12 NOTNULL PRIMARYINDEX PI Cols ONCOMMITPRESERVEROWS 创建可变临时表 cont 例子3 createvolatilemultisettablevt RETAIN ANLY MONas selectcol1 cast adc asvarchar 12 col2from where withnodataPRIMARYINDEX col1 ONCOMMITPRESERVEROWS 例子4 createvolatilemultisettablevt net gsm nlaspdata tb net gsm nlwithnodataONCOMMITPRESERVEROWS 字段col2将用unicode字符集 当跟普通字段 latin字符集 join时 需要进行数据重新分布 不建议 失败 因为pdata tb net gsm nl有PPI而可变临时表不允许有PPI 固化临时表 固化临时表 就是把查询结果存放到一张物理表 共下次分析或他人使用Session断开之后 仍然可以使用 示例1 CREATEMULTISETTABLEtttemp TMP BOSS VOICas select frompview vw net gsm nl WITHnoDATAPRIMARYINDEX subs id INSERTINTOtttemp TMP BOSS VOICSELECT FROMpview vw net gsm nlWHERE 示例2 CREATEMULTISETTABLEtttemp TMP BOSS VOICas select frompview vw net gsm nlWHERE WITHDATAPRIMARYINDEX subs id 示例3 复制表 数据备份 CREATEMULTISETTABLEtttemp TMP BOSS VOICASpdata tb net gsm nlWITHDATA 数据类型 注意非日期字段与日期字段char date的转换与关联 如果数据类型一致可以直接使用 在CASEWHENorCOALESCE一定要使用显式的类型转换 CAST CASEWHENA BTHENDATE1ELSE 20061031 END应写成CASEWHENA BTHENDATE1ELSECAST 20061031 ASDATE END数值运算时 确保运算过程中不丢失计算精度 CAST 100 3ASDEC 5 2 应该写成CAST 100 3 00ASDEC 5 2 字符 串 与数字相比较 比较规则 1 比较两个值 字段 它们的类型必须一样 2 当字符 串 与数字相比较时 先把字符 串 转换成数字 再进行比较 3 经分系统中容易出错的 有Cal Month字段 Case1 Table1CREATETABLEEmp1 Emp noCHAR 6 Emp nameCHAR 20 PRIMARYINDEX Emp no Statement1SELECT FROMEmp1WHEREEmp no 1234 Statement2SELECT FROMEmp1WHEREEmp no 1234 Table1CREATETABLEEmp2 Emp noINTEGER Emp nameCHAR 20 PRIMARYINDEX Emp no Statement1SELECT FROMEmp2WHEREEmp no 1234 Statement2SELECT FROMEmp2WHEREEmp no 1234 Case2 ResultsinFullTableScan Resultsinunnecessaryconversion 目标列的选择 减少目标列 可以少消耗SPOOL空间 从而提高SQL的效率当系统任务繁忙 系统内存少的时候 效果尤为明显 举例 GSM语言话单表 PDATA TB NET GSM NL共有73字段 以下SQL供返回1 6亿条记录左边的SQL 记录最长为 698字节 平均399字节右边的SQL 记录最长为 59字节 平均30字节两者相差400多GB的SPOOL空间 IO次数也随着相差甚大 SPOOL空间估计 497GB SPOOL空间估计 42GB SELECTSUBS ID MSISDN Begin Date Begin Time Call DUR CHRG DURFROMPDATA TB NET GSM NLWHEREPROC DATEBETWEEN 20070701 AND 20070731 SELECT FROMPDATA TB NET GSM NLWHEREPROC DATEBETWEEN 20070701 AND 20070731 Where条件的限定 根据Where条件先进行过滤数据集 再进行连接 JOIN 等操作这样 可以减少参与连接操作的数据集大小 从而提高效率好的查询引擎 可以自动优化 但有些复杂SQL 查询引擎优化得并不好 注意 系统的SQL优化 只是避免最差的 选择相对优的 未必能够得到最好的优化结果 SELECTA TX DATE A KPI CODE B SRC NAME A KPI VALUEFROM select fromqdata tb dqc kpi check resultwhereTX DATE 20070701 ANDKPI CODE 65 ALEFTJOIN SELECT FROMqdata tb dqc kpi defwhereKPI CODE 65andN TYPE M BONA KPI CODE B KPI CODE SELECTA TX DATE A KPI CODE coalesce B SRC NAME noname A KPI VALUEFROMqdata tb dqc kpi check resultALEFTJOINqdata tb dqc kpi defBONA KPI CODE B KPI CODEWHEREA TX DATE 20070701 ANDA KPI CODE 65ANDB N TYPE M rewrite 用CaseWhen替代UNION selcity id channel id cust brand id sum stat values asstat valuesfrom selectt city id 语音杂志计费量 coalesce v channel id b channel id aschannel id cust brand id sum casewhenSMS SVC Type Level SECND 017 andCall Type Codein 00 10 01 11 thensms quanelse0END asstat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 groupby1 2 3unionallselectt city id 梦网短信计费量 coalesce v channel id b channel id aschannel id cust brand id sum sms quan asstat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 andSMS SVC Type Level SECNDlike 02 andSMS SVC Type Level SECNDnotin 021 022 groupby1 2 3 tmpGroupby1 2 3 两个子查询的表连接部分完全一样两个子查询除了取数据条件 其它都一样 Unionall是多余的 它需要重复扫描数据 进行重复的JOIN可以用Casewhen替代union 作业 KPI NWR SMS BILL QUAN描述 点对点短信计费量脚本 kpi nwr sms bill quan0600 pl 用CaseWhen替代UNION cont selcity id channel id cust brand id sum stat values asstat valuesfrom selectt city id coalesce v channel id b channel id aschannel id cust brand id sum CASEWHENSMS SVC Type Level SECND 017 andCall Type Codein 00 10 01 11 THENsms quan 语音杂志计费量WHENSMS SVC Type Level SECNDlike 02 andSMS SVC Type Level SECNDnotin 021 022 THENsms quan 梦网短信计费量ELSE0END asstat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 tmpGroupby1 2 3 SQL优化重写 用OR替代UNION Selectcity id channel id cust brand id sum sms quan stat valuesfrom selectt city id 语音杂志计费量 coalesce v channel id b channel id aschannel id cust brand id sum sms quan stat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 andSMS SVC Type Level SECND 017 andCall Type Codein 00 10 01 11 groupby1 2 3unionallselectt city id 梦网短信计费量 coalesce v channel id b channel id aschannel id cust brand id sum sms quan asstat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 andSMS SVC Type Level SECNDlike 02 andSMS SVC Type Level SECNDnotin 021 022 groupby1 2 3 TGroupby1 2 3 两个子查询的表连接部分完全一样两个子查询除了取数据条件 其它都一样 Unionall是多余的 它需要重复扫描数据 进行重复的JOIN可以用OR替代union此类的问题 在脚本中经常见到 用OR替代UNION cont selectt city id coalesce v channel id b channel id aschannel id cust brand id sum sms quan asstat valuesfromPVIEW vw mid sms svc quan dailytleftjoinVT SUBSvont subs id v subs idleftjoinPVIEW vw FCT CDE BUSN CITY TYPEbont city id b City IDwherecal date 20070914 and SMS SVC Type Level SECND 017 语音杂志计费量andCall Type Codein 00 10 01 11 OR SMS SVC Type Level SECNDlike 02 梦网短信计费量andSMS SVC Type Level SECNDnotin 021 022 Groupby1 2 3 SQL优化重写 去掉多余的Distinct与Groupby selt operator t acpt channel id t acpt city id t subs id t acpt dateasevt dateFrom seloperator ACPT Channel ID acpt city id subs id acpt datefrompview vw evt cust socustwhereacpt date 20071007 andso meth codein 0 1 2 andPROC STS Code 1 groupby1 2 3 4 5unionallseloperator numasoperator ACPT Channel ID acpt city id subs subs id charge dateasacpt datefrompview vw fin busi recbusjoincrmmart subs day info dailysubsonsubs msisdn bus msisdnwherecharge date 20071007 groupby1 2 3 4 5 tgroupby1 2 3 4 5 既然t查询外层有groupby操作去重 那么子查询内的Groupby去重是多余的 而且 两个子查询groupby后再用unionall 就可能再产生重复记录 那么groupby也失去意义了 解决方法 把t查询内部的两个groupby去掉即可类似的Distinct问题 可效仿解决 去重 去重 去重 Groupbyvs Distinct Distinct是去除重复的操作Groupby是聚集操作某些情况下 两者可以起到相同的作用 两者的执行计划不一样 效率也不一样建议 使用Groupby selectsubs id acct idfromPVIEW VW FIN ACCT SUBS HISwhereefct date 20070701 groupby1 2 selectDISTINCTsubs id acct idfromPVIEW VW FIN ACCT SUBS HISwhereefct date 20070701 Unionvs Unionall Union与Unionall的作用是将多个SQL的结果进行合并 Union将自动剔除集合操作中的重复记录 需要耗更多资源 Unionall则保留重复记录 一般建议使用Unionall 第一个SELECT语句 决定输出的字段名称 标题 格式等要求所有的SELECT语句 1 必须要有同样多的表达式数目 2 相关表达式的域必须兼容 select from select a T1 col1 unionselect from select bc T2 col2 select from select bc T3 col3 unionallselect from select a T1 col1 unionallselect from select bc T2 col2 col3 a bc bc col1 a b 先Groupby再join 脚本 rpt mart new comm mon0400 pl11小时Selectcasewhenb CUST Brand IDisnullthen 5020 whenb CUST Brand IDin 2000 5010 then 5020 elseb CUST Brand IDend sum COALESCE b Bas CHRG DUR Unit 0 asThsy Accum New SUBS CHRG DUR sum casewhenb call type code 20 thenb Bas CHRG DUR Unitelse0END fromVTNEW SUBS THISYEARtinnerjoinVTDUR MONbont Subs ID b Subs IDleftjoinPVIEW vw MID CDE LONG CALL TYPE LVLconb Long Type Level SECND c Long Type Level SECNDleftjoinPVIEW vw MID CDE ROAM TYPE LVLdonb Roam Type Level SECND d Roam Type Level SECNDgroupby1 记录数情况 t 580万 b 9400万 c 8 d 8主要问题 假如连接顺序为 bjoinc joind joint 则是 9400万join8 join8 join580万 数据分布时间长 IO多 连接次数多解决方法 先执行 tjoinb 然后groupby 再joinc d 先Groupby再join cont 脚本 rpt mart new comm mon0400 pl40秒Selectcasewhenb CUST Brand IDisnullthen 5020 whenb CUST Brand IDin 2000 5010 then 5020 elseb CUST Brand IDend sum COALESCE b Bas CHRG DUR Unit 0 asThsy Accum New SUBS CHRG DUR sum casewhenb call type code 20 thenb Bas CHRG DUR Unitelse0END from selectCUST Brand ID call type code Long Type Level SECND Roam Type Level SECND sum Bas CHRG DUR Unit Bas CHRG DUR Unit count quanfromVTDUR MONwheresubs idin selectsubs idfromVTNEW SUBS THISYEAR groupby1 2 3 4 bleftjoinPVIEW vw MID CDE LONG CALL TYPE LVLconb Long Type Level SECND c Long Type Level SECNDleftjoinPVIEW vw MID CDE ROAM TYPE LVLdonb Roam Type Level SECND d Roam Type Level SECNDgroupby1 记录数情况 t 580万 b 9400万 c 8 d 8处理过程 先执行 tjoinb 然后groupby 再joinc d结果 1 VTDUR MONjoinVTNEW SUBS THISYEARPI相同 mergejoin 只需10秒2 经过groupby b表只有332记录3 bjoincjoind 就是 332 8 84 最终结果 5记录 共40秒 先Groupby再join cont 先汇总再连接 可以减少参与连接的数据集大小 减少比较次数 从而提高效率 以下面SQL为例 假设历史表 History 有1亿条记录左边的SQL 需要进行1亿 90次比较右边的SQL 则只需要1亿 1次比较 SELECTH product id sum H account num FROMHistoryH CalendarDTWHEREH sale date DT calendar dateANDDT quarter 3 GROUPBY1 SELECTH product id SUM H account num FROMHistoryH SELECTmin calendar date min date max calendar date max dateFROMCalendarWHEREquarter 3 DTWHEREH sale dateBETWEENDT min dateandDT max dateGROUPBY1 提取公共SQL形成临时表 脚本 rpt nmmart comm subs mon0403 pl出现以下SQL代码段 共5次 平均每次执行需10分钟 FROMPVIEW VW MID VOIC SVC QUAN MONa PVIEW VW MID CDE SUBS BRAND LVLb vt subscWHEREa CUST Brand ID b SUBS Brand Level ThirdANDa CAL Month 200708ANDa SUBS ID c SUBS ID 整个脚本需要扫描以下SQL14次 平均每次执行需3分钟PVIEW VW MID VOIC SVC QUAN MONwhereCAL Month 200708提取公共SQL 形成临时表 较少扫描 IO 次数 该脚本 经过优化之后 从50分钟缩减至10分钟 关联条件 1 SelectA a2 B b2fromAjoinBonsubstring A a1from1for7 B b1应该写为SelectA a2 B b2from selectsubstring a1from1for7 asa1 new a2fromA A newjoinBona1 new b1 关联条件 2 SelectA a2 B b2fromAjoinBonTRIM A a1 TRIM B b1 应该写为SelectA a2 B b2fromAjoinBonA a1 B b1 SQL书写不当可能会引起笛卡儿积 以下面两个SQL为例 它们将进行笛卡儿积操作 例子1 Selectemployee emp no employee emp nameFromemployeeA例子2 SELECTA EMP Name B Dept NameFROMemployeeA DepartmentBWherea dept no b dept no 修改表定义 常见的表定义修改操作 增加字段修改字段长度建议的操作流程Renametabledb tablexasdb tabley 通过Showtable语句获得原表db tablex的定义定义新表 db tablexInsertintodb tablex select Fromdb tabley Droptabledb tabley Teradata提供ALTERTABLE语句 可进行修改表定义但 不建议采用ALTERTABLE方式 插入 更新 删除记录时 尽量不要Abort 当目标表有数据时 插入和更新操作 以及部分删除 都产生TJ如果此时abort该操作 系统将会回滚 DeleteBASS1 tb 03004whereproc dt 200709 UPDATECustomerSETCredit Limit Credit Limit 1 20 DELETEFROMTransWHERETrans Date 990101 Update Delete操作 UPDATECustomerSETCredit Limit Credit Limit 1 20 CREATEmultisetTABLECustomer NASCustomerwithnodata INSERTINTOCustomer NSELECTCredit Limit 1 20FROMCustomer DROPTABLECustomer RENAMETABLECustomer NTOCustomer CREATEmultisetTABLETrans NasTranswithnodata INSERTINTOTrans NSELECT FROMTransWHERETrans Date 981231 DROPTABLETrans RENAMETABLETrans NTOTrans 先建立空表 通过insert select方式插入数据 这是非常快的操作 先备份 然后做变更操作 更加安全 对于大表进行Update DELETE操作 将耗费相当多的资源与相当长的时间 Update Delete操作 需要事务日志TJ TransientJournal 以防意外中断导致数据受到破坏在Update Delete操作中途被Cancel 系统则需回滚 这将耗更多的资源与时间 在经分系统中 应严防此类事件发生 DELETEFROMTransWHERETrans Date 990101 经分系统的实体命名规范 实体的命名 最长不超过30个字母 通常要求都是大写 实体的命名 后缀前缀 表 基础表以TB 开头中间表以MID 开头应用模块的表以相应的主体缩写开头视图 一般地 视图名称与表名称一一对应 以VW 开头 对于TB 开头的表 把TB 替换成VW 对于其他表 加上VW 即可 宏 以M 或者Macro 开头后缀 历史表 HIS月表 MON日表 DAILY 实体的命名规范 示例 TB OFR SUBS HIS用户历史Efct date End date示例 Select Frompview vw ofr subs hisWhereefct datecast 20080401 asdateformat yyyymmdd MID COMP OPPN DISTRICT MON区域管理月中间表RPT CHK FREE RES LOS DAILY免费资源促销资料丢失用户表VW MID SUBS INFO MON视图 用户资料月中间表 提纲 Teradata架构常见问题 及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化 Teradata扩展SQL 1 SHOW SHOWTABLEpdata tb net gsm nl 显示表pdata tb net gsm nl的定义 Teradata扩展SQL 2 HELP HELPDATABASEMIS 列举数据库MIS的内容HELPTABLEpdata tb net gsm nl 列举该表的字段 Teradata扩展SQL 3 MACRO 宏 MACRO 是Teradata对ANSISQL的扩展 宏 MACRO 是一组SQL的集合SQL之间用分号 隔开没有ifthenelse语句与存储过程 StoreProcedure 不一样 存储过程类似于C语言 需要先编译 才能执行 而宏不需要 Teradata扩展SQL 4 临时表 可变临时表 VolatileTable 是一种比较常用的Teradata临时表一般用它来存储公共部分的数据 以提高程序的执行效率 它仅存活于同一个Session之内 INSERTINTOTarget tableSelect From selproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 02 unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 03unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 04 unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 14 unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 15 unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 16 unionselproc dttx date kpi code kpi valuefrompview vw kpi daywherekpi code 22 T1Wheretx date 20070701 需要访问表pview vw kpi day7次 效率低下 Teradata扩展SQL 4 临时表 Createvolatilemultisettablevt kpi dayas Selectproc dttx date kpi code kpi valuefrompview vw kpi daywheretx date 20070701 WITHDATAPrimaryIndex kpi code ONCOMMITPRESERVEROWS INSERTINTOTarget tableSelect From selproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 02 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 03 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 04 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 14 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 15 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 16 unionselproc dttx date kpi code kpi valuefromvt kpi daywherekpi code 22 T1只需要访问表pview vw kpi day1次 效率提高 SQL变量 SELECTDATABASE 显示当前数据库PVIEWSELECTUSER 显示当前Session登陆的用户名luscSELECTDATE CURRENT DATE 显示当前日期20070806 20070806定义格式 SELECTCAST DATEASDATEFORMAT YYYYMMDD SelectTIME CURRENT TIMESTAMP 0 显示当前时间18 46 35 2007 08 0618 46 34 00 00转换 SELECTCAST CURRENT TIMESTAMP 0 ASCHAR 19 2007 08 0618 47 59 日期 DATE 的操作 取当前天 selectcast current dateasDATEFORMAT YYYYMMDD 取当前天的前一天 后一天selectcast current date 1asDATEFORMAT YYYYMMDD selectcast current date 1asDATEFORMAT YYYYMMDD 取前 后 一个月的同一天Selectadd months current date 1 Selectadd months current date 1 若current date为20070331 结果是什么 取当前天所在月的第一天selectsubstr cast current dateasdateformat YYYYMMDD 1 6 01 取当前天所在月的最后一天selectcast substr cast add months curr