PowerCenter功能函数.docx
《PowerCenter功能函数.docx》由会员分享，可在线阅读，更多相关《PowerCenter功能函数.docx（159页珍藏版）》请在冰豆网上搜索。
PowerCenter功能函数
InformaticaPowerCenterV8
函数功能使用指南
目录
第一章聚合函数4
1、简介4
1.1AVG4
1.2COUNT5
1.3FIRST6
1.4LAST7
1.5MAX（Dates）8
1.6MAX（Numbers）8
1.7MEDIAN9
1.8MIN（Dates）10
1.9MIN（Numbers）11
1.10PERCENTILE12
1．11STDDEV13
1.12SUM14
1.13VARIANCE15
第二章字符函数16
1、简介16
1.1ASCII18
1.2CHR19
1.3CHRCODE19
1.4CONCAT20
1.5INITCAP21
1.6INSTR21
1.7LENGTH23
1.8LOWER24
1.9LPAD24
1.10LTRIM25
1.11REPLACECHR26
1.12REPLACESTR28
1.13RPAD30
1.14RTRIM31
1.15SUBSTR32
1.16UPPER33
第三章转换函数33
1、简介33
1.1TO_CHAR（Dates）34
1.3TO_DATE37
1.4TO_DECIMAL39
1.5TO_FLOAT39
1.6TO_INTEGER40
第四章数据整理函数41
1、简介41
1.1IS_DATE42
1.2IS_NUMBER43
1.3IS_SPACES44
1.4ISNULL45
1.5SOUNDEX45
第五章日期函数46
1、简介46
1.1ADD_TO_DATE47
1.2DATE_COMPARE49
1.3DATE_DIFF49
1.4GET_DATE_PART51
1.5LAST_DAY52
1.6ROUND（Dates）53
1.7ROUND（Numbers）54
1.8SET_DATE_PART56
1.9TRUNC（Dates）57
1.10TRUNC（Numbers）59
第六章数值函数60
1、简介60
1.1ABS61
1.2CEIL61
1.3CUME62
1.4EXP63
1.5FLOOR63
1.6LN64
1.7LOG64
1.8MOD65
1.9MOVINGAVG66
1.10MOVINGSUM66
1.11POWER67
1.12SIGN68
1.13SQRT68
第七章科学函数69
1、简介69
1.1COS69
1.2COSH70
1.3SIN70
1.4SINH71
1.5TAN71
1.6TANH72
第八章特殊函数72
1、简介72
1.1ABORT73
1.2DECODE73
1.3ERROR75
1.4IIF75
1.5LOOKUP77
第九章可变函数78
1、简介78
1.1SETCOUNVRIABLE78
1.2SETMAXVARIABLE79
1.3SETMINVARIABLE80
1.4SETVARIABLE81
第十章译码函数83
1、简介83
1.1AES_DECRYPT83
1.2AES_ENCRYPT84
1.3COMPRESS84
1.4CRC3284
1.5DEC_BASE6485
1.6DECOMPRESS85
1.7ENC_BASE6485
1.8MD586
第一章聚合函数
1、简介
聚合函数返回Aggregator组件选定Port中非空值的汇总值，使用聚合函数，您可以：
◇计算组中所有行的单值；
◇返回聚合转换中每个组的单值；
◇应用过滤器以计算选定端口中特定行的值；
◇使用运算符在函数中执行算术运算；
◇计算单次传递中来自同一源列的两个或多个聚合值。
转换语言包括下列聚合函数：
函数
语法
说明
AVG
AVG（numeric_value[,filter_condition]）
返回组中所有值的平均值。
在Designer中提供。
COUNT
COUNT（value[,filter_condition]）或COUNT（*[,filter_conditon]）
返回组中包含非空值的记录数。
在Designer中提供。
FIRST
FIRST（value[,filter_condition]）
返回组中的第一条记录。
在Designer中提供。
LAST
LAST（value[,filter_condition]）
返回组中最后一条记录。
在Designer中提供。
MAX
MAX（value[,filter_condition]）
返回在组中找到的最大值或最后日期。
在Designer中提供。
MEDIAN
MEDIAN（numeric_value[,filter_condition]）
返回所选端口中所有值得中值。
在Designer中提供。
MIN
MIN（value[,filter_condition]）
返回在组中找到最小值或最早日期。
在Designer中提供。
PERCENTILE
PERCENTILE（numeric_value,percentile[,filter_condition]）
计算一组数中，达到指定百分点的值。
在Designer中提供。
STDDEV
STDDEV（numeric_value[,filter_condition]）
返回组的标准偏差。
在Designer中提供。
SUM
SUM（numeric_value[,filter_condition]）
返回组中所有记录的总和。
在Designer中提供。
VARIANCE
VARIANCE（numeric_value[,filter_condition]）
返回组中所有记录的反差。
在Designer中提供。
1.1AVG
适用范围：
Designer
返回一系列行中所有值的平均值。
您也可以选择应用过滤器来限制您所读取的行，然后计算平均值。
AVG是几个聚合函数之一。
您只能在AVG函数中嵌入一个其它类型的聚合函数，且嵌入的函数必须返回数字数据类型。
聚合函数只能在聚合转换中使用。
◎语法
AVG（numeric_value[,filter_condition]）
参数
含义
numeric_value
必须。
数字数据类型。
传递您要为其计算平均值的值。
您可以输入任何有效的转换表达式。
filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
数值。
如果传递给函数的所有值都为NULL，或未选定任何行，则返回NULL。
例如，对于所有行，过滤条件的计算结果均为FALSE或NULL。
注：
如果返回值是精确到15位以后的小数型，您可以在会话特性中启用高精度，以确保可精确到小数点后28位。
◎空值
如果值为NULL，则AVG函数将忽略该行。
但是，如果从端口传递的所有值都为NULL，则AVG将返回NULL。
注：
默认情况下，PowerCenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值端口或空值组，则函数将返回NULL。
但是，配置PowercenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎分组依据
AVG函数将根据您在转换中定义的“分组依据”端口对值进行分组，并为每个组返回一个结果，如果没有“分组依据”端口，则AVG函数会将所有行都视为一个组，并返回一个值。
◎示例
以下表达式返回闪关灯的平均批发成本：
AVG（WHOLESALE_COST,ITEM_NAME=’Flashlight’）
ITEM_NAME
WHOLESALE_COST
Flashlight
35
NavigationCompass
8.05
RegulatorSystem
150
Flashlight
29
Depth/PressureGauge
88
Flashlight
31
返回值：
31.66
◎提示
您可以在AVG函数计算平均值之前对传递给AVG函数的值进行算术运算。
例如：
AVG（QTY*PRICE-DISCOUNT）
1.2COUNT
适用范围：
Designer
返回组中包含非空值的行数。
此外，您也可以选择包括星号（*）参数，以计算转换中的所有输入值数。
COUNT是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
只能在COUNT函数中嵌入一个其他类型的聚合函数。
在计算行数之前，可以应用一些条件来过滤行。
◎语法
COUNT（value[，filter_condition]）
或
COUNT（*[,filter_condition]）
参数
含义
value
必需。
任何数据类型（二进制除外）。
传递您要计算的值。
您可以输入任何有效的转换表达式。
*
可选。
用于计算转换中的所有行数。
filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
整数
如果所有传递给此函数的值都为NULL（除非您包括星号参数），则返回零。
◎空值
如果所有值都为NULL，则函数将返回0。
如果您应用星号参数，则此函数将计算所有行数（不管行中的列是否包含空值）。
如果您应用value参数，则此函数将忽略包含空值的列。
注：
默认情况下，PowercenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值或空值组，则函数将返回NULL。
但是，配置PowercenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎示例
以下表达式计算库存量少于5个的项目数（空值除外）：
COUNT（ITEM_VALUE,IN_STOCK<5）
ITEM_VALUE
IN_STOCK
Flashlight
10
NULL
2
Compass
NULL
RegulatorSystem
5
SagetyKnife
8
HalogenFlashlight
1
返回值：
1
在此示例中，函数计算Halogen闪关灯而不是NULL项目的数量。
该函数计算转换中的所有行数（包括空值），如下列示例中所示：
COUNT（*，QTY<5）
ITEM_NAEM
QTY
Flashlight
10
NULL
2
Compass
NULL
RegulatorSystem
5
SafetyKnife
8
HalogenFlashlight
1
返回值：
2
在此示例中，函数计算NULL项目和Halogen闪光灯。
如果您包括星号函数，但不使用过滤器，则函数将计算传递到转换中的所有行数。
例如：
COUNT（*）
ITEM_NAME
QTY
Flashlight
10
NULL
2
Compass
NULL
RegulatorSystem
5
SafetyKnife
8
HalogenFlashlight
1
返回值：
6
1.3FIRST
适用范围：
Designer
返回在端口或组中找到的第一个值。
您还可以选择应用过滤器以限制PowerCenterServer读取的行。
只能在FIRST函数中嵌入一个其它类型的聚合函数。
FIRST是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
◎语法
FIRST（value[,filter_condition]）
参数
含义
Value
必需。
任何数据类型（二进制除外）。
传递您要为其返回第一个值的值。
您可以输入任何有效的转换表达式。
filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
组中的一个值。
如果传递给函数的所有值为NULL，或未选择任何行（如对于所有行，过滤条件的计算结果为FALSE或NULL），则返回NULL。
◎空值
如果值为NULL，则FIRST函数将忽略该行。
但是，如果从端口传递的所有值都为NULL，则FIRST将返回NULL。
注：
默认情况下，PowerCenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值端口或空值组，则函数将返回NULL。
但是，配置PowerCenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎分组依据
FIRST函数将根据您在转换中定义的“分组依据”端口对值进行分组，并为每个组返回一个结果。
如果没有“分组依据”端口，FIRST会将所有行视为一组，并返回一个值。
◎示例
以下表达式返回ITEM_NAME端口中价格大于$10.00的第一个值：
FIRST（ITEM_NAME,ITEM_PRICE>10）
ITEM_NAME
ITEM_PRICE
Flashlight
35
NavigatonCompass
8.05
RegulatorSystem
150
Flashlight
29
Depth/PressureGauge
88
Flashlight
31
返回值：
Flashlight
以下表达式返回ITEM_NAME端口中价格大于$40.00的第一个值：
FIRST（ITEM_NAEM,ITEM_PRICE>40）
ITEM_NAME
ITEM_PRICE
Flashlight
35
Navigation
8.05
RegulatorSystem
150
Flashlight
29
Depth/PressureGauge
88
Flashlight
31
返回值：
RegulatorSystem
1.4LAST
适用范围：
Designer
返回所选端口中的最后一行。
您还可以选择应用过滤器以限制PowerCenterServer读取的行。
您可以只将另外一个聚合函数嵌入LAST。
LAST是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
◎语法
LAST（value[,filter_condition]）
参数
含义
Value
必需。
任何数据类型（二进制除外）。
传递您要为其返回最后一行的值。
您可以输入任何有效的转换表达式。
Filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
端口中的最后一行。
如果传递给函数的所有值为NULLL，或未选择任何行（如对于所有行，过滤条件的计算结果FALSE或NULL），则返回NULL。
注：
默认情况下，PowerCenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值端口或空值组，则函数将返回NULL。
但是，配置PowerCenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎示例
以下表达式返回价格大于$10.00的ITEMS_NAME端口中的最后一行：
LAST（ITEM_NAME,ITEM_PRICE>10）
ITEM_NAME
ITEM_PRICE
Flashlight
35
NavigationCompass
8.05
RegulatorSystem
150
Flashlight
29
Depth/PressureGauge
88
Vest
31
返回值：
Vest
1.5MAX（Dates）
适用范围：
Designer
返回在端口或组中找到最后日期。
您可以应用过滤器来限制搜索的行。
您只能将另外一个聚合函数嵌入MAX。
MAX是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
您还可以使用MAX返回端口或组中的最大数值。
◎语法
MAX（date[,filter_condition]）
参数
含义
Date
必需。
日期/时间数据类型。
传递您要为其返回最大日期的日期。
您可以输入任何有效的转换表达式。
Filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
日期。
如果传递给函数的所有值为NULL，或未选择任何行（如对于所有行，过滤条件的计算结果为FALSE或NULL），则返回NULL。
◎示例
您可以返回端口或组的最大日期。
以下表达式将返回flashlights的最大顺序日期：
MAX（ORDER_DATE,ITEM_NAME=‘Flashlight’）
ITEM_NAME
ORDER_DATE
Flashlight
Apr201998
RegulatorSystem
May151998
Flashlight
Sep211998
DivingHood
Aug181998
HalogenFlashlight
Feb11998
Flashlight
Oct101998
Flashlight
NULL
1.6MAX（Numbers）
适用范围：
Designer
返回在端口或组中找到的最大数值。
您可以应用过滤器来限制搜索的行。
您只能将另外一个聚合函数嵌入MAX。
MAX是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
您还可以使用MAX返回端口或组中的最后日期。
◎语法
MAX（numeric_value[,filter_condition]）
参数
含义
Numeric_value
必需。
数字数据类型。
传递您要为其返回最大数值的数值。
您可以输入任何有效的转换表达式。
Filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
数值。
如果传递给函数的所有值为NULL，或未选择任何行（如对于所有行，过滤条件的计算结果为FALSE或NULL），则返回NULL。
如果返回值是精确到15位以后的小数型，您可以在会话特性中启用高精度，以确保可精确到小数点后28位。
◎空值
如果值为NULL，则MAX将忽略它。
但是，如果从端口传递的值都为NULL，MAX将返回NULL。
注：
默认情况下，PowerCenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值端口或空值组，则函数将返回NULL。
但是，配置PowerCenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎分组依据
MAX将根据您在转换中定义的“分组依据”端口对值进行分组，并为每组返回一个结果。
如果没有“分组依据”端口，则MAX会将所有行都视为一个组，并返回一个值。
◎示例
第一个表达式将返回flashlights的最高价格：
MAX（PRICE,ITEM_NAME=‘Flashlight’）
ITEM_NAME
PRICE
Flashlight
10
RegulatorSystem
360
Flashlight
55
DivingHood
79
HalogenFlashlight
162
Flashlight
85
Flashlight
NULL
返回值:
85.00
1.7MEDIAN
适用范围:
Designer
返回所选端口中所有值的中值。
如果端口中有偶数个值，则当所有值都按顺序排列在数轴上时，中值将是中间两个值的平均值。
如果端口中有奇数个值，则中值是中间的数字。
您也可以选择应用过滤器来限制您所读取的行，然后计算中值。
您只能在MEDIAN函数中嵌入一个其它类型的聚合函数，且嵌入的函数必须返回数字数据类型。
MEDIAN是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
◎语法
MEDIAN（numeric_value[,filter_condition]）
参数
含义
Numeric_value
必需。
数字数据类型。
传递您要为其返回最大数值的数值。
您可以输入任何有效的转换表达式。
Filter_condition
可选。
限制搜索的行。
过滤条件必须为数值或计算结果为TRUE、FALSE或NULL。
您可以输入任何有效的转换表达式。
◎返回值
数值。
如果传递给函数的所有值为NULL，或未选择任何行（如对于所有行，过滤条件的计算结果为FALSE或NULL），则返回NULL。
注：
如果返回值是精确到15位以后的小数型，您可以在会话特性中启用高精度，以确保可精确到小数点后28位。
◎空值
如果值为NULL，则MEDIAN函数将忽略该行。
但是，如果从端口传递的值都为NULL，MEDIAN将返回NULL。
注：
默认情况下，PowerCenterServer在聚合函数中将空值视为NULL。
如果您传递整个空值端口或空值组，则函数将返回NULL。
但是,配置PowerCenterServer时，您可以选择要如何处理聚合函数中的空值。
您可以在聚合函数中将空值视为零或NULL。
◎分组依据
MEDIAN将根据您在转换中定义的“分组依据”端口对值进行分组，并为每组返回一个结果。
如果没有“分组依据”端口，则MEDIAN会将所有行都视为一个组，并返回一个值。
◎示例
要计算所有部门的平均薪水，请创建按部门（带有指定以下表达式的端口）分组的Aggregator转换：
MEDIAN（SALARY）
以下表达式将返回stabilizingvests顺序的中值：
MEDIAN（SALES.ITEM=‘StabilizingVest’）
ITEM
SALES
Flashlight
85
StabilizingVest
504
StabIlizingVest
36
SagelyKnife
5
MediumTitaniumKnife
150
Tank
NULL
StabilizingVest
441
ChiselPointKnife
60
StabilizingVest
NULL
StabilizingVest
1044
WristBandThermometer
110
返回值：
472.5
1.8MIN（Dates）
适用范围：
Designer
返回在端口或组中找到的最早日期。
您可以应用过滤器来限制搜索的行。
您只能在MIN中嵌入另外一个聚合函数，且嵌入的函数必须返回日期数据类型。
MIN是几个聚合函数之一。
聚合函数只能在聚合转换中使用。
您还可以使用MIN返回端口或组中的最小数值。
◎语法
MIN（date[,fi
升级会员 