# 映射数据流中的数据转换表达式Data transformation expressions in mapping data flow

## 表达式函数Expression functions

### abs

abs(<value1> : number) => number

• abs(-20) -> 20
• abs(10) -> 10

### acos

acos(<value1> : number) => double

• acos(1) -> 0.0

add(<value1> : any, <value2> : any) => any

• 10 + 20 -> 30
• 'ice' + 'cream' + ' cone' -> 'icecream cone'
• toDate('2012-12-12') + 3 -> toDate('2012-12-15')
• [10, 20] + [30, 40] -> [10, 20, 30, 40]
• toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') + (days(1) + hours(2) - seconds(10)) -> toTimestamp('2019-02-04 07:19:18.871', 'yyyy-MM-dd HH:mm:ss.SSS')

addMonths(<date/timestamp> : datetime, <months to add> : integral, [<value3> : string]) => datetime

• addMonths(toTimestamp('2016-09-30 10:10:10'), -1) -> toTimestamp('2016-08-31 10:10:10')

### and

and(<value1> : boolean, <value2> : boolean) => boolean

“逻辑与”运算符。Logical AND operator. 与 && 相同。Same as &&.

• and(true, false) -> false
• true && false -> false

### asin

asin(<value1> : number) => double

• asin(0) -> 0.0

### atan

atan(<value1> : number) => double

• atan(0) -> 0.0

### atan2

atan2(<value1> : number, <value2> : number) => double

• atan2(0, 0) -> 0.0

### between

between(<value1> : any, <value2> : any, <value3> : any) => boolean

### bitwiseAnd

bitwiseAnd(<value1> : integral, <value2> : integral) => integralNumeric, string and datetime values can be compared * between(10, 5, 24) * true * between(currentDate(), currentDate() + 10, currentDate() + 20) * false

### bitwiseAnd

bitwiseAnd(<value1> : integral, <value2> : integral) => integral

### bitwiseOr

bitwiseOr(<value1> : integral, <value2> : integral) => integralSame as & operator * bitwiseAnd(0xf4, 0xef) * 0xe4 * (0xf4 & 0xef) * 0xe4

### bitwiseOr

bitwiseOr(<value1> : integral, <value2> : integral) => integral

### bitwiseXor

bitwiseXor(<value1> : any, <value2> : any) => anySame as | operator * bitwiseOr(0xf4, 0xef) * 0xff * (0xf4 | 0xef) * 0xff

### bitwiseXor

bitwiseXor(<value1> : any, <value2> : any) => any

### blake2b

blake2b(<value1> : integer, <value2> : any, ...) => stringSame as | operator * bitwiseXor(0xf4, 0xef) * 0x1b * (0xf4 ^ 0xef) * 0x1b * (true ^ false) * true * (true ^ true) * false

### blake2b

blake2b(<value1> : integer, <value2> : any, ...) => string

### blake2bBinary

blake2bBinary(<value1> : integer, <value2> : any, ...) => binaryIt can be used to calculate a fingerprint for a row * blake2b(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) * 'c9521a5080d8da30dffb430c50ce253c345cc4c4effc315dab2162dac974711d'

### blake2bBinary

blake2bBinary(<value1> : integer, <value2> : any, ...) => binary

### case

case(<condition> : boolean, <true_expression> : any, <false_expression> : any, ...) => anyIt can be used to calculate a fingerprint for a row * blake2bBinary(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) * unHex('c9521a5080d8da30dffb430c50ce253c345cc4c4effc315dab2162dac974711d')

### case

case(<condition> : boolean, <true_expression> : any, <false_expression> : any, ...) => any

• case(10 + 20 == 30, 'dumbo', 'gumbo') -> 'dumbo'
• case(10 + 20 == 25, 'bojjus', 'do' < 'go', 'gunchus') -> 'gunchus'
• isNull(case(10 + 20 == 25, 'bojjus', 'do' > 'go', 'gunchus')) -> true
• case(10 + 20 == 25, 'bojjus', 'do' > 'go', 'gunchus', 'dumbo') -> 'dumbo'

### cbrt

cbrt(<value1> : number) => double

• cbrt(8) -> 2.0

### ceil

ceil(<value1> : number) => number

• ceil(-0.1) -> 0

### coalesce

coalesce(<value1> : any, ...) => any

• coalesce(10, 20) -> 10
• coalesce(toString(null), toString(null), 'dumbo', 'bo', 'go') -> 'dumbo'

### columnNames

columnNames(<value1> : string) => array

• columnNames()
• columnNames('DeriveStream')

### columns

columns([<stream name> : string]) => any

• columns()
• columns('DeriveStream')

### compare

compare(<value1> : any, <value2> : any) => integer

• (compare(12, 24) < 1) -> true
• (compare('dumbo', 'dum') > 0) -> true

### concat

concat(<this> : string, <that> : string, ...) => string

• concat('dataflow', 'is', 'awesome') -> 'dataflowisawesome'
• 'dataflow' + 'is' + 'awesome' -> 'dataflowisawesome'
• isNull('sql' + null) -> true

### concatWS

concatWS(<separator> : string, <this> : string, <that> : string, ...) => string

• concatWS(' ', 'dataflow', 'is', 'awesome') -> 'dataflow is awesome'
• isNull(concatWS(null, 'dataflow', 'is', 'awesome')) -> true
• concatWS(' is ', 'dataflow', 'awesome') -> 'dataflow is awesome'

### cos

cos(<value1> : number) => double

• cos(10) -> -0.8390715290764524

### cosh

cosh(<value1> : number) => double

• cosh(0) -> 1.0

### crc32

crc32(<value1> : any, ...) => long

• crc32(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> 3630253689L

### currentDate

currentDate([<value1> : string]) => date

• currentDate() == toDate('2250-12-31') -> false
• currentDate('PST') == toDate('2250-12-31') -> false
• currentDate('America/New_York') == toDate('2250-12-31') -> false

### currentTimestamp

currentTimestamp() => timestamp

• currentTimestamp() == toTimestamp('2250-12-31 12:12:12') -> false

### currentUTC

currentUTC([<value1> : string]) => timestamp

• currentUTC() == toTimestamp('2050-12-12 19:18:12') -> false
• currentUTC() != toTimestamp('2050-12-12 19:18:12') -> true
• fromUTC(currentUTC(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true

### dayOfMonth

dayOfMonth(<value1> : datetime) => integer

• dayOfMonth(toDate('2018-06-08')) -> 8

### dayOfWeek

dayOfWeek(<value1> : datetime) => integer

• dayOfWeek(toDate('2018-06-08')) -> 6

### dayOfYear

dayOfYear(<value1> : datetime) => integer

• dayOfYear(toDate('2016-04-09')) -> 100

### days

days(<value1> : integer) => long

• days(2) -> 172800000L

### degrees

degrees(<value1> : number) => double

• degrees(3.141592653589793) -> 180

### divide

divide(<value1> : any, <value2> : any) => any

• divide(20, 10) -> 2
• 20 / 10 -> 2

### endsWith

endsWith(<string> : string, <substring to check> : string) => boolean

• endsWith('dumbo', 'mbo') -> true

### equals

equals(<value1> : any, <value2> : any) => boolean

“等于”比较运算符。Comparison equals operator. 与 == 运算符相同。Same as == operator.

• equals(12, 24) -> false
• 12 == 24 -> false
• isNull('good' == toString(null)) -> true
• isNull(null == null) -> true

### equalsIgnoreCase

equalsIgnoreCase(<value1> : string, <value2> : string) => boolean

• 'abc'<=>'Abc' -> true
• equalsIgnoreCase('abc', 'Abc') -> true

### escape

escape(<string_to_escape> : string, <format> : string) => string

### expr

expr(<expr> : string) => any

### factorial

factorial(<value1> : number) => long* expr(‘price * discount’) => any ___

### factorial

factorial(<value1> : number) => long

• factorial(5) -> 120

### false

false() => boolean

• (10 + 20 > 30) -> false
• (10 + 20 > 30) -> false()

### floor

floor(<value1> : number) => number

• floor(-0.1) -> -1

### fromBase64

fromBase64(<value1> : string) => string

• fromBase64('Z3VuY2h1cw==') -> 'gunchus'

### fromUTC

fromUTC(<value1> : timestamp, [<value2> : string]) => timestamp

• fromUTC(currentTimestamp()) == toTimestamp('2050-12-12 19:18:12') -> false
• fromUTC(currentTimestamp(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true

### greater

greater(<value1> : any, <value2> : any) => boolean

“大于”比较运算符。Comparison greater operator. 与 > 运算符相同。Same as > operator.

• greater(12, 24) -> false
• ('dumbo' > 'dum') -> true
• (toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS') > toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> true

### greaterOrEqual

greaterOrEqual(<value1> : any, <value2> : any) => boolean

“大于等于”比较运算符。Comparison greater than or equal operator. 与 >= 运算符相同。Same as >= operator.

• greaterOrEqual(12, 12) -> true
• ('dumbo' >= 'dum') -> true

### greatest

greatest(<value1> : any, ...) => any

• greatest(10, 30, 15, 20) -> 30
• greatest(10, toInteger(null), 20) -> 20
• greatest(toDate('2010-12-12'), toDate('2011-12-12'), toDate('2000-12-12')) -> toDate('2011-12-12')
• greatest(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS'), toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')) -> toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')

### hasColumn

hasColumn(<column name> : string, [<stream name> : string]) => boolean

• hasColumn('parent')

### hour

hour(<value1> : timestamp, [<value2> : string]) => integer

• hour(toTimestamp('2009-07-30 12:58:59')) -> 12
• hour(toTimestamp('2009-07-30 12:58:59'), 'PST') -> 12

### hours

hours(<value1> : integer) => long

• hours(2) -> 7200000L

### iif

iif(<condition> : boolean, <true_expression> : any, [<false_expression> : any]) => any

• iif(10 > 30, 'dumbo', 'gumbo') -> 'gumbo'
• iif(month(toDate('2018-12-01')) == 12, 345.12, 102.67) -> 345.12

### iifNull

iifNull(<value1> : any, [<value2> : any], ...) => any

• iifNull(10, 20) -> 10
• iifNull(null, 20, 40) -> 20
• iifNull('azure', 'data', 'factory') -> 'factory'
• iifNull(null, 'data', 'factory') -> 'data'

### initCap

initCap(<value1> : string) => string

• initCap('cool iceCREAM') -> 'Cool Icecream'

### instr

instr(<string> : string, <substring to find> : string) => integer

• instr('dumbo', 'mbo') -> 3
• instr('microsoft', 'o') -> 5

### isDelete

isDelete([<value1> : integer]) => boolean

• isDelete()
• isDelete(1)

### isError

isError([<value1> : integer]) => boolean

• isError()
• isError(1)

### isIgnore

isIgnore([<value1> : integer]) => boolean

• isIgnore()
• isIgnore(1)

### isInsert

isInsert([<value1> : integer]) => boolean

• isInsert()
• isInsert(1)

### isMatch

isMatch([<value1> : integer]) => boolean

• isMatch()
• isMatch(1)

### isNull

isNull(<value1> : any) => boolean

• isNull(NULL()) -> true
• isNull('') -> false

### isUpdate

isUpdate([<value1> : integer]) => boolean

• isUpdate()
• isUpdate(1)

### isUpsert

isUpsert([<value1> : integer]) => boolean

• isUpsert()
• isUpsert(1)

### lastDayOfMonth

lastDayOfMonth(<value1> : datetime) => date

• lastDayOfMonth(toDate('2009-01-12')) -> toDate('2009-01-31')

### least

least(<value1> : any, ...) => any

“小于等于”比较运算符。Comparison lesser than or equal operator. 与 <= 运算符相同。Same as <= operator.

• least(10, 30, 15, 20) -> 10
• least(toDate('2010-12-12'), toDate('2011-12-12'), toDate('2000-12-12')) -> toDate('2000-12-12')

### left

left(<string to subset> : string, <number of characters> : integral) => string

• left('bojjus', 2) -> 'bo'
• left('bojjus', 20) -> 'bojjus'

### length

length(<value1> : string) => integer

• length('dumbo') -> 5

### lesser

lesser(<value1> : any, <value2> : any) => boolean

“小于”比较运算符。Comparison less operator. 与 < 运算符相同。Same as < operator.

• lesser(12, 24) -> true
• ('abcd' < 'abc') -> false
• (toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') < toTimestamp('2019-02-05 08:21:34.890', 'yyyy-MM-dd HH:mm:ss.SSS')) -> true

### lesserOrEqual

lesserOrEqual(<value1> : any, <value2> : any) => boolean

“小于等于”比较运算符。Comparison lesser than or equal operator. 与 <= 运算符相同。Same as <= operator.

• lesserOrEqual(12, 12) -> true
• ('dumbo' <= 'dum') -> false

### levenshtein

levenshtein(<from string> : string, <to string> : string) => integer

• levenshtein('boys', 'girls') -> 4

### like

like(<string> : string, <pattern match> : string) => boolean

• like('icecream', 'ice%') -> true

### locate

locate(<substring to find> : string, <string> : string, [<from index - 1-based> : integral]) => integer

• locate('mbo', 'dumbo') -> 3
• locate('o', 'microsoft', 6) -> 7

### log

log(<value1> : number, [<value2> : number]) => double

• log(100, 10) -> 2

### log10

log10(<value1> : number) => double

• log10(100) -> 2

### lower

lower(<value1> : string) => string

• lower('GunChus') -> 'gunchus'

• lpad('dumbo', 10, '-') -> '-----dumbo'
• lpad('dumbo', 4, '-') -> 'dumb'
* lpad('dumbo', 8, '<>') -> '<><dumbo'

### ltrim

ltrim(<string to trim> : string, [<trim characters> : string]) => string* lpad('dumbo', 8, '<>') -> '<><dumbo'

### ltrim

ltrim(<string to trim> : string, [<trim characters> : string]) => string

• ltrim(' dumbo ') -> 'dumbo '
• ltrim('!--!du!mbo!', '-!') -> 'du!mbo!'

### md5

md5(<value1> : any, ...) => string

• md5(5, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> '4ce8a880bd621a1ffad0bca905e1bc5a'

### millisecond

millisecond(<value1> : timestamp, [<value2> : string]) => integer

• millisecond(toTimestamp('2009-07-30 12:58:59.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> 871

### milliseconds

milliseconds(<value1> : integer) => long

• milliseconds(2) -> 2L

### minus

minus(<value1> : any, <value2> : any) => any

• minus(20, 10) -> 10
• 20 - 10 -> 10
• minus(toDate('2012-12-15'), 3) -> toDate('2012-12-12')
• toDate('2012-12-15') - 3 -> toDate('2012-12-12')
• toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS') + (days(1) + hours(2) - seconds(10)) -> toTimestamp('2019-02-04 07:19:18.871', 'yyyy-MM-dd HH:mm:ss.SSS')
• toTimestamp('2019-02-03 05:21:34.851', 'yyyy-MM-dd HH:mm:ss.SSS') - toTimestamp('2019-02-03 05:21:36.923', 'yyyy-MM-dd HH:mm:ss.SSS') -> -2072

### minute

minute(<value1> : timestamp, [<value2> : string]) => integer

• minute(toTimestamp('2009-07-30 12:58:59')) -> 58
• minute(toTimestamp('2009-07-30 12:58:59'), 'PST') -> 58

### minutes

minutes(<value1> : integer) => long

• minutes(2) -> 120000L

### mod

mod(<value1> : any, <value2> : any) => any

• mod(20, 8) -> 4
• 20 % 8 -> 4

### month

month(<value1> : datetime) => integer

• month(toDate('2012-8-8')) -> 8

### monthsBetween

monthsBetween(<from date/timestamp> : datetime, <to date/timestamp> : datetime, [<roundoff> : boolean], [<time zone> : string]) => double

• monthsBetween(toTimestamp('1997-02-28 10:30:00'), toDate('1996-10-30')) -> 3.94959677

### multiply

multiply(<value1> : any, <value2> : any) => any

• multiply(20, 10) -> 200
• 20 * 10 -> 200

### negate

negate(<value1> : number) => number

• negate(13) -> -13

### nextSequence

nextSequence() => long

• nextSequence() == 12313112 -> false

### normalize

normalize(<String to normalize> : string) => string

• regexReplace(normalize('bo²s'), `\p{M}`, '') -> 'boys'

### not

not(<value1> : boolean) => boolean

• not(true) -> false
• not(10 == 20) -> true

### notEquals

notEquals(<value1> : any, <value2> : any) => boolean

“不等于”比较运算符。Comparison not equals operator. 与 != 运算符相同。Same as != operator.

• 12 != 24 -> true
• 'bojjus' != 'bo' + 'jjus' -> false

### notNull

notNull(<value1> : any) => boolean

• notNull(NULL()) -> false
• notNull('') -> true

### null

null() => null

• isNull('dumbo' + null) -> true
• isNull(10 * null) -> true
• isNull('') -> false
• isNull(10 + 20) -> false
• isNull(10/0) -> true

### or

or(<value1> : boolean, <value2> : boolean) => boolean

“逻辑或”运算符。Logical OR operator. 与 || 相同。Same as ||.

• or(true, false) -> true
• true || false -> true

### pMod

pMod(<value1> : any, <value2> : any) => any

• pmod(-20, 8) -> 4

### partitionId

partitionId() => integer

• partitionId()

### power

power(<value1> : number, <value2> : number) => double

• power(10, 2) -> 100

### random

random(<value1> : integral) => long

### regexExtract

regexExtract(<string> : string, <regex to find> : string, [<match group 1-based index> : integral]) => stringThe seed should be a fixed value and is used in conjunction with the partitionId to produce random values * random(1) == 1 -> false

### regexExtract

regexExtract(<string> : string, <regex to find> : string, [<match group 1-based index> : integral]) => string

• regexExtract('Cost is between 600 and 800 dollars', '(\\d+) and (\\d+)', 2) -> '800'
• regexExtract('Cost is between 600 and 800 dollars', `(\d+) and (\d+)`, 2) -> '800'

### regexMatch

regexMatch(<string> : string, <regex to match> : string) => boolean

• regexMatch('200.50', '(\\d+).(\\d+)') -> true
• regexMatch('200.50', `(\d+).(\d+)`) -> true

### regexReplace

regexReplace(<string> : string, <regex to find> : string, <substring to replace> : string) => string

• regexReplace('100 and 200', '(\\d+)', 'bojjus') -> 'bojjus and bojjus'
• regexReplace('100 and 200', `(\d+)`, 'gunchus') -> 'gunchus and gunchus'

### regexSplit

regexSplit(<string to split> : string, <regex expression> : string) => array

• regexSplit('bojjusAgunchusBdumbo', `[CAB]`) -> ['bojjus', 'gunchus', 'dumbo']
• regexSplit('bojjusAgunchusBdumboC', `[CAB]`) -> ['bojjus', 'gunchus', 'dumbo', '']
• (regexSplit('bojjusAgunchusBdumboC', `[CAB]`)[1]) -> 'bojjus'
• isNull(regexSplit('bojjusAgunchusBdumboC', `[CAB]`)[20]) -> true

### replace

replace(<string> : string, <substring to find> : string, [<substring to replace> : string]) => string

• replace('doggie dog', 'dog', 'cat') -> 'catgie cat'
• replace('doggie dog', 'dog', '') -> 'gie '
• replace('doggie dog', 'dog') -> 'gie '

### reverse

reverse(<value1> : string) => string

• reverse('gunchus') -> 'suhcnug'

right(<string to subset> : string, <number of characters> : integral) => string

• right('bojjus', 2) -> 'us'
• right('bojjus', 20) -> 'bojjus'

### rlike

rlike(<string> : string, <pattern match> : string) => boolean

• rlike('200.50', `(\d+).(\d+)`) -> true
• rlike('bogus', `M[0-9]+.*`) -> false

### round

round(<number> : number, [<scale to round> : number], [<rounding option> : integral]) => double

• round(100.123) -> 100.0
• round(2.5, 0) -> 3.0
• round(5.3999999999999995, 2, 7) -> 5.40

• rpad('dumbo', 10, '-') -> 'dumbo-----'
• rpad('dumbo', 4, '-') -> 'dumb'
• rpad('dumbo', 8, '<>') -> 'dumbo<><'
___

### rtrimrtrim

rtrim(<string to trim> : string, [<trim characters> : string]) => string___

### rtrimrtrim

rtrim(<string to trim> : string, [<trim characters> : string]) => string

• rtrim(' dumbo ') -> ' dumbo'
• rtrim('!--!du!mbo!', '-!') -> '!--!du!mbo'

### second

second(<value1> : timestamp, [<value2> : string]) => integer

• second(toTimestamp('2009-07-30 12:58:59')) -> 59

### seconds

seconds(<value1> : integer) => long

• seconds(2) -> 2000L

### sha1

sha1(<value1> : any, ...) => string

• sha1(5, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> '46d3b478e8ec4e1f3b453ac3d8e59d5854e282bb'

### sha2

sha2(<value1> : integer, <value2> : any, ...) => string

• sha2(256, 'gunchus', 8.2, 'bojjus', true, toDate('2010-4-4')) -> 'afe8a553b1761c67d76f8c31ceef7f71b66a1ee6f4e6d3b5478bf68b47d06bd3'

### sin

sin(<value1> : number) => double

• sin(2) -> 0.9092974268256817

### sinh

sinh(<value1> : number) => double

• sinh(0) -> 0.0

### soundex

soundex(<value1> : string) => string

• soundex('genius') -> 'G520'

### split

split(<string to split> : string, <split characters> : string) => array

• split('bojjus,guchus,dumbo', ',') -> ['bojjus', 'guchus', 'dumbo']
• split('bojjus,guchus,dumbo', '|') -> ['bojjus,guchus,dumbo']
• split('bojjus, guchus, dumbo', ', ') -> ['bojjus', 'guchus', 'dumbo']
• split('bojjus, guchus, dumbo', ', ')[1] -> 'bojjus'
• isNull(split('bojjus, guchus, dumbo', ', ')[0]) -> true
• isNull(split('bojjus, guchus, dumbo', ', ')[20]) -> true
• split('bojjusguchusdumbo', ',') -> ['bojjusguchusdumbo']

### sqrt

sqrt(<value1> : number) => double

• sqrt(9) -> 3

### startsWith

startsWith(<string> : string, <substring to check> : string) => boolean

• startsWith('dumbo', 'du') -> true

### subDays

subDays(<date/timestamp> : datetime, <days to subtract> : integral) => datetime

• subDays(toDate('2016-08-08'), 1) -> toDate('2016-08-07')

### subMonths

subMonths(<date/timestamp> : datetime, <months to subtract> : integral) => datetime

• subMonths(toDate('2016-09-30'), 1) -> toDate('2016-08-31')

### substring

substring(<string to subset> : string, <from 1-based index> : integral, [<number of characters> : integral]) => string

• substring('Cat in the hat', 5, 2) -> 'in'
• substring('Cat in the hat', 5, 100) -> 'in the hat'
• substring('Cat in the hat', 5) -> 'in the hat'
• substring('Cat in the hat', 100, 100) -> ''

### tan

tan(<value1> : number) => double

• tan(0) -> 0.0

### tanh

tanh(<value1> : number) => double

• tanh(0) -> 0.0

### translate

translate(<string to translate> : string, <lookup characters> : string, <replace characters> : string) => string

• translate('(bojjus)', '()', '[]') -> '[bojjus]'
• translate('(gunchus)', '()', '[') -> '[gunchus'

### trim

trim(<string to trim> : string, [<trim characters> : string]) => string

• trim(' dumbo ') -> 'dumbo'
• trim('!--!du!mbo!', '-!') -> 'du!mbo'

### true

true() => boolean

• (10 + 20 == 30) -> true
• (10 + 20 == 30) -> true()

### typeMatch

typeMatch(<type> : string, <base type> : string) => boolean

• typeMatch(type, 'number')
• typeMatch('date', 'datetime')

### unescape

unescape(<string_to_escape> : string, <format> : string) => string

• unescape('{\\\\\"value\\\\\": 10}', 'json')
• '{\\\"value\\\": 10}'

### upper

upper(<value1> : string) => string

• upper('bojjus') -> 'BOJJUS'

uuid() => string

• uuid()

### weekOfYear

weekOfYear(<value1> : datetime) => integer

• weekOfYear(toDate('2008-02-20')) -> 8

### weeks

weeks(<value1> : integer) => long

• weeks(2) -> 1209600000L

### xor

xor(<value1> : boolean, <value2> : boolean) => boolean

• xor(true, false) -> true
• xor(true, true) -> false
• true ^ false -> true

### year

year(<value1> : datetime) => integer

• year(toDate('2012-8-8')) -> 2012

## 聚合函数Aggregate functions

### avg

avg(<value1> : number) => number

• avg(sales)

### avgIf

avgIf(<value1> : boolean, <value2> : number) => number

• avgIf(region == 'West', sales)

### collect

collect(<value1> : any) => array

• collect(salesPerson)
• collect(firstName + lastName))
• collect(@(name = salesPerson, sales = salesAmount) )

### count

count([<value1> : any]) => long

• count(custId)
• count(custId, custName)
• count()
• count(iif(isNull(custId), 1, NULL))

### countDistinct

countDistinct(<value1> : any, [<value2> : any], ...) => long

• countDistinct(custId, custName)

### countIf

countIf(<value1> : boolean, [<value2> : any]) => long

• countIf(state == 'CA' && commission < 10000, name)

### covariancePopulation

covariancePopulation(<value1> : number, <value2> : number) => double

• covariancePopulation(sales, profit)

### covariancePopulationIf

covariancePopulationIf(<value1> : boolean, <value2> : number, <value3> : number) => double

• covariancePopulationIf(region == 'West', sales)

### covarianceSample

covarianceSample(<value1> : number, <value2> : number) => double

• covarianceSample(sales, profit)

### covarianceSampleIf

covarianceSampleIf(<value1> : boolean, <value2> : number, <value3> : number) => double

• covarianceSampleIf(region == 'West', sales, profit)

### first

first(<value1> : any, [<value2> : boolean]) => any

• first(sales)
• first(sales, false)

### isDistinct

isDistinct(<value1> : any , <value1> : any) => boolean

### kurtosis

kurtosis(<value1> : number) => doubleIt does not count null as a distinct value * isDistinct(custId, custName) => boolean * ___

### kurtosis

kurtosis(<value1> : number) => double

• kurtosis(sales)

### kurtosisIf

kurtosisIf(<value1> : boolean, <value2> : number) => double

• kurtosisIf(region == 'West', sales)

### last

last(<value1> : any, [<value2> : boolean]) => any

• last(sales)
• last(sales, false)

### max

max(<value1> : any) => any

• max(sales)

### maxIf

maxIf(<value1> : boolean, <value2> : any) => any

• maxIf(region == 'West', sales)

### mean

mean(<value1> : number) => number

• mean(sales)

### meanIf

meanIf(<value1> : boolean, <value2> : number) => number

• meanIf(region == 'West', sales)

### min

min(<value1> : any) => any

• min(sales)

### minIf

minIf(<value1> : boolean, <value2> : any) => any

• minIf(region == 'West', sales)

### skewness

skewness(<value1> : number) => double

• skewness(sales)

### skewnessIf

skewnessIf(<value1> : boolean, <value2> : number) => double

• skewnessIf(region == 'West', sales)

### stddev

stddev(<value1> : number) => double

• stdDev(sales)

### stddevIf

stddevIf(<value1> : boolean, <value2> : number) => double

• stddevIf(region == 'West', sales)

### stddevPopulation

stddevPopulation(<value1> : number) => double

• stddevPopulation(sales)

### stddevPopulationIf

stddevPopulationIf(<value1> : boolean, <value2> : number) => double

• stddevPopulationIf(region == 'West', sales)

### stddevSample

stddevSample(<value1> : number) => double

• stddevSample(sales)

### stddevSampleIf

stddevSampleIf(<value1> : boolean, <value2> : number) => double

• stddevSampleIf(region == 'West', sales)

### sum

sum(<value1> : number) => number

• sum(col)

### sumDistinct

sumDistinct(<value1> : number) => number

• sumDistinct(col)

### sumDistinctIf

sumDistinctIf(<value1> : boolean, <value2> : number) => number

• sumDistinctIf(state == 'CA' && commission < 10000, sales)
• sumDistinctIf(true, sales)

### sumIf

sumIf(<value1> : boolean, <value2> : number) => number

• sumIf(state == 'CA' && commission < 10000, sales)
• sumIf(true, sales)

### variance

variance(<value1> : number) => double

• variance(sales)

### varianceIf

varianceIf(<value1> : boolean, <value2> : number) => double

• varianceIf(region == 'West', sales)

### variancePopulation

variancePopulation(<value1> : number) => double

• variancePopulation(sales)

### variancePopulationIf

variancePopulationIf(<value1> : boolean, <value2> : number) => double

• variancePopulationIf(region == 'West', sales)

### varianceSample

varianceSample(<value1> : number) => double

• varianceSample(sales)

### varianceSampleIf

varianceSampleIf(<value1> : boolean, <value2> : number) => double

• varianceSampleIf(region == 'West', sales)

## 数组函数Array functions

• #acc 表示在减少数组时希望包含在单个输出中的值#acc represents a value that you wish to include in your single output when reducing an array
• #index 表示当前的数组索引以及数组索引编号 #index2, #index3 ...#index represents the current array index, along with array index numbers #index2, #index3 ...
• #item 表示数组中的当前元素值#item represents the current element value in the array

### array

array([<value1> : any], ...) => array

• array('Seattle', 'Washington')
• ['Seattle', 'Washington']
• ['Seattle', 'Washington'][1]
• 'Washington'

### contains

contains(<value1> : array, <value2> : unaryfunction) => boolean

• contains([1, 2, 3, 4], #item == 3) -> true
• contains([1, 2, 3, 4], #item > 5) -> false

### filter

filter(<value1> : array, <value2> : unaryfunction) => array

• filter([1, 2, 3, 4], #item > 2) -> [3, 4]
• filter(['a', 'b', 'c', 'd'], #item == 'a' || #item == 'b') -> ['a', 'b']

### find

find(<value1> : array, <value2> : unaryfunction) => any

• find([10, 20, 30], #item > 10) -> 20
• find(['azure', 'data', 'factory'], length(#item) > 4) -> 'azure'
• find([ @( name = 'Daniel', types = [ @(mood = 'jovial', behavior = 'terrific'), @(mood = 'grumpy', behavior = 'bad') ] ), @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] ) ], contains(#item.types, #item.mood=='happy') /*Filter out the happy kid*/ )
• @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] )

### in

in(<array of items> : array, <item to find> : any) => boolean

* in([10, 20, 30], 10) -> true
* ``in(['good', 'kid'], 'bad') -> fa

Checks if an item is in the array.
* in([10, 20, 30], 10) -> true
* ``in(['good', 'kid'], 'bad') -> fa
lse``

### map

map(<value1> : array, <value2> : unaryfunction) => any

Maps each element of the array to a new element using the provided expression.
Map 需要引用表达式函数中的一个元素作为 #item。nction as #item.
* map([1, 2, 3, 4], #item + 2) -> [3, 4, 5, 6]
* ``map(['a', 'b', 'c', 'd'], #item + '_processed') -> ['a_processed', 'b_processed', 'c_processed', 'd_processe（备注：原文网址为https://docs.microsoft.com/zh-cn/azure/data-factory/data-flow-expression-functions，tag 处理有误。）
Map expects a reference to one element in the expression function as #item.
* map([1, 2, 3, 4], #item + 2) -> [3, 4, 5, 6]
* ``map(['a', 'b', 'c', 'd'], #item + '_processed') -> ['a_processed', 'b_processed', 'c_processed', 'd_processe
d']``

### mapIf

mapIf ( : array, : binaryfunction, : binaryFunction) => any

Conditionally maps an array to another array of same or smaller length.

### mapIndex

mapIndex(<value1> : array, <value2> : binaryfunction) => any

Maps each element of the array to a new element using the provided expression.
Map 需要引用表达式函数中的一个元素作为 #item，并需要引用元素索引作为 #index。index as #index.
* ``mapIndex([1, 2, 3, 4], #item + 2 + #index) -> [4, 6, 8,
Map expects a reference to one element in the expression function as #item and a reference to the element index as #index.
* ``mapIndex([1, 2, 3, 4], #item + 2 + #index) -> [4, 6, 8,
10]``

### mapLoop

mapLoop( : integer, : unaryfunction) => any

Loops through from 1 to length to create an array of that length.

### reduce

reduce(<value1> : array, <value2> : any, <value3> : binaryfunction, <value4> : unaryfunction) => any

Accumulates elements in an array.
Reduce 需要引用第一个表达式函数中的累加器和一个元素作为 #acc 和 #item，并且需要在第二个表达式函数中使用结果值作为 #result。ession function.
* ``toString(reduce(['1', '2', '3', '4'], '0', #acc + #item, #result)) -> '012
Reduce expects a reference to an accumulator and one element in the first expression function as #acc and #item and it expects the resulting value as #result to be used in the second expression function.
* ``toString(reduce(['1', '2', '3', '4'], '0', #acc + #item, #result)) -> '012
34'``

### size

size(<value1> : any) => integer

* size(['element1', 'element2']) -> 2 * ``size([1,2,3])

Finds the size of an array or map type
* size(['element1', 'element2']) -> 2 * ``size([1,2,3])
-> 3``

### slice

slice(<array to slice> : array, <from 1-based index> : integral, [<number of items> : integral]) => array

Extracts a subset of an array from a position.

* slice([10, 20, 30, 40], 1, 2) -> [10, 20]
* slice([10, 20, 30, 40], 2) -> [20, 30, 40]
* slice([10, 20, 30, 40], 2)[1] -> 20
* isNull(slice([10, 20, 30, 40], 2)[0]) -> true
* isNull(slice([10, 20, 30, 40], 2)[20]) -> true
* ``slice(['a', 'b', 'c', 'd'], 8) ->
If the length is omitted, it is defaulted to end of the string.
* slice([10, 20, 30, 40], 1, 2) -> [10, 20]
* slice([10, 20, 30, 40], 2) -> [20, 30, 40]
* slice([10, 20, 30, 40], 2)[1] -> 20
* isNull(slice([10, 20, 30, 40], 2)[0]) -> true
* isNull(slice([10, 20, 30, 40], 2)[20]) -> true
* ``slice(['a', 'b', 'c', 'd'], 8) ->
[]``

### sort

sort(<value1> : array, <value2> : binaryfunction) => array

Sorts the array using the provided predicate function.
Sort 需要引用表达式函数中的两个连续元素作为 #item1 和 #item2。tem1 and #item2.
* sort([4, 8, 2, 3], compare(#item1, #item2)) -> [2, 3, 4, 8]
* sort(['a3', 'b2', 'c1'], iif(right(#item1, 1) >= right(#item2, 1), 1, -1)) -> ['c1', 'b2', 'a3']* @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] )

### in

in(<array of items> : array, <item to find> : any) => boolean

Checks if an item is in the array.
* in([10, 20, 30], 10) -> true
* in(['good', 'kid'], 'bad') -> false

### map

map(<value1> : array, <value2> : unaryfunction) => any

Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item.
* map([1, 2, 3, 4], #item + 2) -> [3, 4, 5, 6]
* map(['a', 'b', 'c', 'd'], #item + '_processed') -> ['a_processed', 'b_processed', 'c_processed', 'd_processed']

### mapIf

mapIf ( : array, : binaryfunction, : binaryFunction) => any

Conditionally maps an array to another array of same or smaller length. The values can be of any datatype including structTypes. It takes a mapping function where you can address the item in the array as #item and current index as #index. For deeply nested maps you can refer to the parent maps using the #item_[n](#item_1, #index_1...) notation. * mapIf([10, 20, 30], #item > 10, #item + 5) -> [25, 35] * mapIf(['icecream', 'cake', 'soda'], length(#item) > 4, upper(#item)) -> ['ICECREAM', 'CAKE']

### mapIndex

mapIndex(<value1> : array, <value2> : binaryfunction) => any

Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item and a reference to the element index as #index.
* mapIndex([1, 2, 3, 4], #item + 2 + #index) -> [4, 6, 8, 10]

### mapLoop

mapLoop( : integer, : unaryfunction) => any

Loops through from 1 to length to create an array of that length. It takes a mapping function where you can address the index in the array as #index. For deeply nested maps you can refer to the parent maps using the #index_n(#index_1, #index_2...) notation. * mapLoop(3, #index * 10) -> [10, 20, 30]

### reduce

reduce(<value1> : array, <value2> : any, <value3> : binaryfunction, <value4> : unaryfunction) => any

Accumulates elements in an array. Reduce expects a reference to an accumulator and one element in the first expression function as #acc and #item and it expects the resulting value as #result to be used in the second expression function.
* toString(reduce(['1', '2', '3', '4'], '0', #acc + #item, #result)) -> '01234'

### size

size(<value1> : any) => integer

Finds the size of an array or map type
* size(['element1', 'element2']) -> 2 * size([1,2,3]) -> 3

### slice

slice(<array to slice> : array, <from 1-based index> : integral, [<number of items> : integral]) => array

Extracts a subset of an array from a position. Position is 1 based. If the length is omitted, it is defaulted to end of the string.
* slice([10, 20, 30, 40], 1, 2) -> [10, 20]
* slice([10, 20, 30, 40], 2) -> [20, 30, 40]
* slice([10, 20, 30, 40], 2)[1] -> 20
* isNull(slice([10, 20, 30, 40], 2)[0]) -> true
* isNull(slice([10, 20, 30, 40], 2)[20]) -> true
* slice(['a', 'b', 'c', 'd'], 8) -> []

### sort

sort(<value1> : array, <value2> : binaryfunction) => array

Sorts the array using the provided predicate function. Sort expects a reference to two consecutive elements in the expression function as #item1 and #item2.
* sort([4, 8, 2, 3], compare(#item1, #item2)) -> [2, 3, 4, 8]
* sort(['a3', 'b2', 'c1'], iif(right(#item1, 1) >= right(#item2, 1), 1, -1)) -> ['c1', 'b2', 'a3']Sort expects a reference to two consecutive elements in the expression function as #item1 and #item2.
* sort([4, 8, 2, 3], compare(#item1, #item2)) -> [2, 3, 4, 8]
* sort(['a3', 'b2', 'c1'], iif(right(#item1, 1) >= right(#item2, 1), 1, -1)) -> ['c1', 'b2', 'a3']* @( name = 'Mark', types = [ @(mood = 'happy', behavior = 'awesome'), @(mood = 'calm', behavior = 'reclusive') ] )

### in

in(<array of items> : array, <item to find> : any) => boolean

Checks if an item is in the array.
* in([10, 20, 30], 10) -> true
* in(['good', 'kid'], 'bad') -> false

### map

map(<value1> : array, <value2> : unaryfunction) => any

Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item.
* map([1, 2, 3, 4], #item + 2) -> [3, 4, 5, 6]
* map(['a', 'b', 'c', 'd'], #item + '_processed') -> ['a_processed', 'b_processed', 'c_processed', 'd_processed']

### mapIf

mapIf ( : array, : binaryfunction, : binaryFunction) => any

Conditionally maps an array to another array of same or smaller length. The values can be of any datatype including structTypes. It takes a mapping function where you can address the item in the array as #item and current index as #index. For deeply nested maps you can refer to the parent maps using the #item_[n](#item_1, #index_1...) notation. * mapIf([10, 20, 30], #item > 10, #item + 5) -> [25, 35] * mapIf(['icecream', 'cake', 'soda'], length(#item) > 4, upper(#item)) -> ['ICECREAM', 'CAKE']

### mapIndex

mapIndex(<value1> : array, <value2> : binaryfunction) => any

Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item and a reference to the element index as #index.
* mapIndex([1, 2, 3, 4], #item + 2 + #index) -> [4, 6, 8, 10]

### mapLoop

mapLoop( : integer, : unaryfunction) => any

Loops through from 1 to length to create an array of that length. It takes a mapping function where you can address the index in the array as #index. For deeply nested maps you can refer to the parent maps using the #index_n(#index_1, #index_2...) notation. * mapLoop(3, #index * 10) -> [10, 20, 30]

### reduce

reduce(<value1> : array, <value2> : any, <value3> : binaryfunction, <value4> : unaryfunction) => any

Accumulates elements in an array. Reduce expects a reference to an accumulator and one element in the first expression function as #acc and #item and it expects the resulting value as #result to be used in the second expression function.
* toString(reduce(['1', '2', '3', '4'], '0', #acc + #item, #result)) -> '01234'

### size

size(<value1> : any) => integer

Finds the size of an array or map type
* size(['element1', 'element2']) -> 2 * size([1,2,3]) -> 3

### slice

slice(<array to slice> : array, <from 1-based index> : integral, [<number of items> : integral]) => array

Extracts a subset of an array from a position. Position is 1 based. If the length is omitted, it is defaulted to end of the string.
* slice([10, 20, 30, 40], 1, 2) -> [10, 20]
* slice([10, 20, 30, 40], 2) -> [20, 30, 40]
* slice([10, 20, 30, 40], 2)[1] -> 20
* isNull(slice([10, 20, 30, 40], 2)[0]) -> true
* isNull(slice([10, 20, 30, 40], 2)[20]) -> true
* slice(['a', 'b', 'c', 'd'], 8) -> []

### sort

sort(<value1> : array, <value2> : binaryfunction) => array

Sorts the array using the provided predicate function. Sort expects a reference to two consecutive elements in the expression function as #item1 and #item2.
* sort([4, 8, 2, 3], compare(#item1, #item2)) -> [2, 3, 4, 8]
* sort(['a3', 'b2', 'c1'], iif(right(#item1, 1) >= right(#item2, 1), 1, -1)) -> ['c1', 'b2', 'a3']

## 缓存的 lookup 函数Cached lookup functions

### lookup

lookup(key, key2, ...) => complex[]

• cacheSink#lookup(movieId)

### mlookup

mlookup(key, key2, ...) => complex[]

• cacheSink#mlookup(movieId)

output() => any

output() => any

## 转换函数Conversion functions

### isBitSet

isBitSet ( : array, :integer ) => boolean

### setBitSet

setBitSet ( : array, :array) => arrayChecks if a bit position is set in this bitset * isBitSet(toBitSet([10, 32, 98]), 10) => true

### setBitSet

setBitSet ( : array, :array) => array

### isBoolean

isBoolean( : string) => booleanSets bit positions in this bitset * setBitSet(toBitSet([10, 32]), [98]) => [4294968320L, 17179869184L]

### isBoolean

isBoolean( : string) => boolean

### isByte

isByte( : string) => boolean 规则检查 string 值是否是 boolean 值Checks if the string value is a boolean value according to the rules of toBoolean() * isBoolean('true') -> true * isBoolean('no') -> true * isBoolean('microsoft') -> false

### isByte

isByte( : string) => boolean

### isDate

isDate ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 byte 值Checks if the string value is a byte value given an optional format according to the rules of toByte() * isByte('123') -> true * isByte('chocolate') -> false

### isDate

isDate ( : string, [: string]) => boolean

### isShort

isShort ( : string, [: string]) => booleanAccepted formats are [ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ] * isDate('2012-8-18') -> true * isDate('12/18--234234' -> 'MM/dd/yyyy') -> false

### isShort

isShort ( : string, [: string]) => boolean

### isInteger

isInteger ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 short 值Checks of the string value is a short value given an optional format according to the rules of toShort() * isShort('123') -> true * isShort('\$123' -> '\$###') -> true * isShort('microsoft') -> false

### isInteger

isInteger ( : string, [: string]) => boolean

### isLong

isLong ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 integer 值Checks of the string value is a integer value given an optional format according to the rules of toInteger() * isInteger('123') -> true * isInteger('\$123' -> '\$###') -> true * isInteger('microsoft') -> false

### isLong

isLong ( : string, [: string]) => boolean

### isFloat

isFloat ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 long 值Checks of the string value is a long value given an optional format according to the rules of toLong() * isLong('123') -> true * isLong('\$123' -> '\$###') -> true * isLong('gunchus') -> false

### isFloat

isFloat ( : string, [: string]) => boolean

### isDouble

isDouble ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 float 值Checks of the string value is a float value given an optional format according to the rules of toFloat() * isFloat('123') -> true * isFloat('\$123.45' -> '\$###.00') -> true * isFloat('icecream') -> false

### isDouble

isDouble ( : string, [: string]) => boolean

### isDecimal

isDecimal ( : string) => boolean 规则检查 string 值是否是给定可选格式的 double 值Checks of the string value is a double value given an optional format according to the rules of toDouble() * isDouble('123') -> true * isDouble('\$123.45' -> '\$###.00') -> true * isDouble('icecream') -> false

### isDecimal

isDecimal ( : string) => boolean

### isTimestamp

isTimestamp ( : string, [: string]) => boolean 规则检查 string 值是否是给定可选格式的 decimal 值Checks of the string value is a decimal value given an optional format according to the rules of toDecimal() * isDecimal('123.45') -> true * isDecimal('12/12/2000') -> false

### isTimestamp

isTimestamp ( : string, [: string]) => boolean

• isTimestamp('2016-12-31 00:12:00') -> true
• isTimestamp('2016-12-31T00:12:00' -> 'yyyy-MM-dd\\'T\\'HH:mm:ss' -> 'PST') -> true
• isTimestamp('2012-8222.18') -> false

### toBase64

toBase64(<value1> : string) => string

• toBase64('bojjus') -> 'Ym9qanVz'

### toBinary

toBinary(<value1> : any) => binary

• toBinary(3) -> [0x11]

### toBoolean

toBoolean(<value1> : string) => boolean

• toBoolean('true') -> true
• toBoolean('n') -> false
• isNull(toBoolean('truthy')) -> true

### toByte

toByte(<value> : any, [<format> : string], [<locale> : string]) => byte

• toByte(123)
• 123
• toByte(0xFF)
• -1
• toByte('123')
• 123

### toDate

toDate(<string> : any, [<date format> : string]) => date

• toDate('2012-8-18') -> toDate('2012-08-18')
• toDate('12/18/2012', 'MM/dd/yyyy') -> toDate('2012-12-18')

### toDecimal

toDecimal(<value> : any, [<precision> : integral], [<scale> : integral], [<format> : string], [<locale> : string]) => decimal(10,0)

• toDecimal(123.45) -> 123.45
• toDecimal('123.45', 8, 4) -> 123.4500
• toDecimal('\$123.45', 8, 4,'\$###.00') -> 123.4500
• toDecimal('Ç123,45', 10, 2, 'Ç###,##', 'de') -> 123.45

### toDouble

toDouble(<value> : any, [<format> : string], [<locale> : string]) => double

• toDouble(123.45) -> 123.45
• toDouble('123.45') -> 123.45
• toDouble('\$123.45', '\$###.00') -> 123.45
• toDouble('Ç123,45', 'Ç###,##', 'de') -> 123.45

### toFloat

toFloat(<value> : any, [<format> : string], [<locale> : string]) => float

• toFloat(123.45) -> 123.45f
• toFloat('123.45') -> 123.45f
• toFloat('\$123.45', '\$###.00') -> 123.45f

### toInteger

toInteger(<value> : any, [<format> : string], [<locale> : string]) => integer

• toInteger(123) -> 123
• toInteger('123') -> 123
• toInteger('\$123', '\$###') -> 123

### toLong

toLong(<value> : any, [<format> : string], [<locale> : string]) => long

• toLong(123) -> 123
• toLong('123') -> 123
• toLong('\$123', '\$###') -> 123

### toShort

toShort(<value> : any, [<format> : string], [<locale> : string]) => short

• toShort(123) -> 123
• toShort('123') -> 123
• toShort('\$123', '\$###') -> 123

### toString

toString(<value> : any, [<number format/date format> : string]) => string

• toString(10) -> '10'
• toString('engineer') -> 'engineer'
• toString(123456.789, '##,###.##') -> '123,456.79'
• toString(123.78, '000000.000') -> '000123.780'
• toString(12345, '##0.#####E0') -> '12.345E3'
• toString(toDate('2018-12-31')) -> '2018-12-31'
• isNull(toString(toDate('2018-12-31', 'MM/dd/yy'))) -> true
• toString(4 == 20) -> 'false'

### toTimestamp

toTimestamp(<string> : any, [<timestamp format> : string], [<time zone> : string]) => timestamp

• toTimestamp('2016-12-31 00:12:00') -> toTimestamp('2016-12-31 00:12:00')
• toTimestamp('2016-12-31T00:12:00', 'yyyy-MM-dd\'T\'HH:mm:ss', 'PST') -> toTimestamp('2016-12-31 00:12:00')
• toTimestamp('12/31/2016T00:12:00', 'MM/dd/yyyy\'T\'HH:mm:ss') -> toTimestamp('2016-12-31 00:12:00')
• millisecond(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> 871

### toUTC

toUTC(<value1> : timestamp, [<value2> : string]) => timestamp

• toUTC(currentTimestamp()) == toTimestamp('2050-12-12 19:18:12') -> false
• toUTC(currentTimestamp(), 'Asia/Seoul') != toTimestamp('2050-12-12 19:18:12') -> true

## 元函数Metafunctions

### byItem

byItem(<parent column> : any, <column name> : string) => any

• byItem( byItem( byName('customer'), 'orderItems'), 'itemName') ? string

### byOrigin

byOrigin(<column name> : string, [<origin stream name> : string]) => anyComputed inputs are not supported but you can use parameter substitutions * byItem( byName('customer'), 'orderItems') ? (itemName as string, itemQty as integer) * ````

• byItem( byItem( byName('customer'), 'orderItems'), 'itemName') ? string

### byOrigin

byOrigin(<column name> : string, [<origin stream name> : string]) => any

• toString(byOrigin('ancestor', 'ancestorStream'))

### byOrigins

byOrigins(<column names> : array, [<origin stream name> : string]) => any

• toString(byOrigins(['ancestor1', 'ancestor2'], 'ancestorStream'))

### byName

byName(<column name> : string, [<stream name> : string]) => any

• toString(byName('parent'))
• toLong(byName('income'))
• toBoolean(byName('foster'))
• toLong(byName(\$debtCol))
• toString(byName('Bogus Column'))
• toString(byName('Bogus Column', 'DeriveStream'))

### byNames

byNames(<column names> : array, [<stream name> : string]) => any

• toString(byNames(['parent', 'child']))
• byNames(['parent']) ? string
• toLong(byNames(['income']))
• byNames(['income']) ? long
• toBoolean(byNames(['foster']))
• toLong(byNames(\$debtCols))
• toString(byNames(['a Column']))
• toString(byNames(['a Column'], 'DeriveStream'))
• byNames(['orderItem']) ? (itemName as string, itemQty as integer)

### byPath

byPath(<value1> : string, [<streamName> : string]) => any

• byPath('grandpa.parent.child') => column

### byPosition

byPosition(<position> : integer) => any

• toString(byPosition(1))
• toDecimal(byPosition(2), 10, 2)
• toBoolean(byName(4))
• toString(byName(\$colName))
• toString(byPosition(1234))

### hasPath

hasPath(<value1> : string, [<streamName> : string]) => boolean

• hasPath('grandpa.parent.child') => boolean

### hex

hex(: binary) => string

### unhex

unhex(: string) => binaryReturns a hex string representation of a binary value * hex(toBinary([toByte(0x1f), toByte(0xad), toByte(0xbe)])) -> '1fadbe'

### unhex

unhex(: string) => binary

## 开窗函数Window functions

### cumeDist

cumeDist() => integer

CumeDist 函数计算某个值相对于分区中所有值的位置。The CumeDist function computes the position of a value relative to all values in the partition. 结果是前面的行数，或者等于当前行在分区中的顺序除以窗口分区中的总行数。The result is the number of rows preceding or equal to the current row in the ordering of the partition divided by the total number of rows in the window partition. 顺序中的任何关联值将计算为相同的位置。Any tie values in the ordering will evaluate to the same position.

• cumeDist()

### denseRank

denseRank() => integer

• denseRank()

### lag

lag(<value> : any, [<number of rows to look before> : number], [<default value> : any]) => any

• lag(amount, 2)
• lag(amount, 2000, 100)

lead(<value> : any, [<number of rows to look after> : number], [<default value> : any]) => any

### nTile

nTile([<value1> : integer]) => integer

NTile 函数将每个窗口分区的行分割为从 1 到 nn 桶。The NTile function divides the rows for each window partition into n buckets ranging from 1 to at most n. 桶值最大相差 1。Bucket values will differ by at most 1. 如果分区中的行数不能均匀分割成桶数，则余值将逐个分布在每个桶中，从第一个桶开始。If the number of rows in the partition does not divide evenly into the number of buckets, then the remainder values are distributed one per bucket, starting with the first bucket. NTile 函数适合用于计算 tertiles、四分位数、十分位数和其他常见的摘要统计数据。The NTile function is useful for the calculation of tertiles, quartiles, deciles, and other common summary statistics. 在初始化期间，该函数将计算两个变量：常规桶的大小将额外添加一行。The function calculates two variables during initialization: The size of a regular bucket will have one extra row added to it. 这两个变量都以当前分区的大小为基础。Both variables are based on the size of the current partition. 在计算过程中，该函数将跟踪当前行号、当前桶号，以及发生桶更改的行号 (bucketThreshold)。During the calculation process the function keeps track of the current row number, the current bucket number, and the row number at which the bucket will change (bucketThreshold). 如果当前行号达到桶的阈值，则桶值将会加 1，阈值将按桶大小增加（如果当前桶已填充，则额外加 1）。When the current row number reaches bucket threshold, the bucket value is increased by one and the threshold is increased by the bucket size (plus one extra if the current bucket is padded).

• nTile()
• nTile(numOfBuckets)

### rank

rank() => integer

• rank()

### rowNumber

rowNumber() => integer

• rowNumber()