apache-ignite
980 строк · 14.5 Кб
1// Licensed to the Apache Software Foundation (ASF) under one or more2// contributor license agreements. See the NOTICE file distributed with3// this work for additional information regarding copyright ownership.4// The ASF licenses this file to You under the Apache License, Version 2.05// (the "License"); you may not use this file except in compliance with6// the License. You may obtain a copy of the License at7//8// http://www.apache.org/licenses/LICENSE-2.09//10// Unless required by applicable law or agreed to in writing, software11// distributed under the License is distributed on an "AS IS" BASIS,12// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.13// See the License for the specific language governing permissions and14// limitations under the License.15= Numeric Functions16== ABS18[source,sql]20----21ABS (expression)22----23=== Parameters25- `expression` - may be a column name, a result of another function, or a math operation.26=== Description28Returns the absolute value of an expression.29[discrete]31=== Example32Calculate an absolute value:33[source,sql]35----36SELECT transfer_id, ABS (price) from Transfers;37----38== ACOS41[source,sql]43----44ACOS (expression)45----46=== Parameters49- `expression` - may be a column name, a result of another function, or a math operation.50=== Description52Calculates the arc cosine. This method returns a `double`.53[discrete]55=== Example56Get arc cos value:57[source,sql]60----61SELECT acos(angle) FROM Triangles;62----63== ASIN66[source,sql]68----69ASIN (expression)70----71=== Parameters74- `expression` - may be a column name, a result of another function, or a math operation.75=== Description77Calculates the arc sine. This method returns a `double`.78[discrete]80=== Example81Calculate an arc sine:82[source,sql]85----86SELECT asin(angle) FROM Triangles;87----88== ATAN91[source,sql]93----94ATAN (expression)95----96=== Parameters99- `expression` - may be a column name, a result of another function, or a math operation.100=== Description102Calculates the arc tangent. This method returns a `double`.103[discrete]105=== Example106Get an arc tangent:107[source,sql]110----111SELECT atan(angle) FROM Triangles;112----113== COS116[source,sql]118----119COS (expression)120----121=== Parameters124- `expression` - may be a column name, a result of another function, or a math operation.125=== Description127Calculates the trigonometric cosine. This method returns a `double`.128[discrete]130=== Example131Get a cosine:132[source,sql]135----136SELECT COS(angle) FROM Triangles;137----138== COSH141[source,sql]143----144COSH (expression)145----146=== Parameters149- `expression` - may be a column name, a result of another function, or a math operation.150=== Description152Calculates the hyperbolic cosine. This method returns a `double`.153[discrete]155=== Example156Get an hyperbolic cosine:157[source,sql]160----161SELECT HCOS(angle) FROM Triangles;162----163== COT166[source,sql]168----169COT (expression)170----171=== Parameters174- `expression` - may be a column name, a result of another function, or a math operation.175=== Description177Calculates the trigonometric cotangent (1/TAN(ANGLE)). This method returns a `double`.178[discrete]180=== Example181Gets a trigonometric cotangent:182[source,sql]185----186SELECT COT(angle) FROM Triangles;187----188== SIN191[source,sql]193----194SIN (expression)195----196=== Parameters199- `expression` - may be a column name, a result of another function, or a math operation.200=== Description202Calculates the trigonometric sine. This method returns a `double`.203[discrete]205=== Example206Get a trigonometric sine:207[source,sql]210----211SELECT SIN(angle) FROM Triangles;212----213== SINH216[source,sql]218----219SINH (expression)220----221=== Parameters224- `expression` - may be a column name, a result of another function, or a math operation.225=== Description227Calculates the hyperbolic sine. This method returns a `double`.228[discrete]230=== Example231Get a hyperbolic sine:232[source,sql]235----236SELECT SINH(angle) FROM Triangles;237----238== TAN241[source,sql]243----244TAN (expression)245----246=== Parameters249- `expression` - may be a column name, a result of another function, or a math operation.250=== Description252Calculates the trigonometric tangent. This method returns a `double`.253[discrete]255=== Example256Get a trigonometric tangent:257[source,sql]260----261SELECT TAN(angle) FROM Triangles;262----263== TANH266[source,sql]268----269TANH (expression)270----271=== Parameters274- `expression` - may be a column name, a result of another function, or a math operation.275=== Description277Calculates the hyperbolic tangent. This method returns a `double`.278[discrete]280=== Example281Get a hyperbolic tangent:282[source,sql]285----286SELECT TANH(angle) FROM Triangles;287----288== ATAN2291[source,sql]293----294ATAN2 (y, x)295----296=== Parameters299- `x and y` - the arguments.300=== Description302Calculates the angle when converting the rectangular coordinates to polar coordinates. This method returns a `double`.303[discrete]305=== Example306Get a hyperbolic tangent:307[source,sql]310----311SELECT ATAN2(X, Y) FROM Triangles;312----313== BITAND316[source,sql]318----319BITAND (y, x)320----321=== Parameters324- `x and y` - the arguments.325=== Description327The bitwise AND operation. This method returns a `long`.328[discrete]330=== Example331[source,sql]333----334SELECT BITAND(X, Y) FROM Triangles;335----336== BITGET339[source,sql]341----342BITGET (y, x)343----344=== Parameters347- `x and y` - the arguments.348=== Description350Returns true if and only if the first parameter has a bit set in the position specified by the second parameter. This method returns a `boolean`. The second parameter is zero-indexed; the least significant bit has position 0.351[discrete]353=== Example354Check that 3rd bit is 1:355[source,sql]358----359SELECT BITGET(X, 3) from Triangles;360----361== BITOR364[source,sql]366----367BITOR (y, x)368----369=== Parameters372- `x and y` - the arguments.373=== Description375The bitwise OR operation. This method returns a `long`.376[discrete]378=== Example379Calculate OR between two fields:380[source,sql]383----384SELECT BITGET(X, Y) from Triangles;385----386== BITXOR389[source,sql]391----392BITXOR (y, x)393----394=== Parameters397- `x and y` - the arguments.398=== Description400The bitwise XOR operation. This method returns a `long`.401[discrete]403=== Example404Calculate XOR between two fields:405[source,sql]408----409SELECT BITXOR(X, Y) FROM Triangles;410----411== MOD414[source,sql]416----417MOD (y, x)418----419=== Parameters422- `x and y` - the arguments.423=== Description425The modulo operation. This method returns a `long`.426[discrete]428=== Example429Calculate MOD between two fields:430[source,sql]433----434SELECT BITXOR(X, Y) FROM Triangles;435----436== CEILING439[source,sql]441----442CEIL (expression)443CEILING (expression)444----445=== Parameters448- `expression` - any valid numeric expression.449=== Description451See also Java Math.ceil. This method returns a `double`.452[discrete]454=== Example455Calculate a ceiling price for items:456[source,sql]459----460SELECT item_id, CEILING(price) FROM Items;461----462== DEGREES465[source,sql]468----469DEGREES (expression)470----471=== Parameters474- `expression` - any valid numeric expression.475=== Description477See also `Java Math.toDegrees`. This method returns a `double`.478[discrete]480=== Example481Converts the argument value to degrees:482[source,sql]485----486SELECT DEGREES(X) FROM Triangles;487----488== EXP491[source,sql]493----494EXP (expression)495----496=== Parameters499- `expression` - any valid numeric expression.500=== Description502See also `Java Math.exp`. This method returns a `double`.503[discrete]505=== Example506Calculates exp:507[source,sql]510----511SELECT EXP(X) FROM Triangles;512----513== FLOOR516[source,sql]518----519FLOOR (expression)520----521=== Parameters524- `expression` - any valid numeric expression.525=== Description527See also `Java Math.floor`. This method returns a `double`.528[discrete]530=== Example531Calculates floor price:532[source,sql]535----536SELECT FLOOR(X) FROM Items;537----538== LOG541[source,sql]543----544LOG (expression)545LN (expression)546----547=== Parameters550- `expression` - any valid numeric expression.551=== Description553See also `Java Math.log`. This method returns a `double`.554[discrete]556=== Example557Calculates LOG:558[source,sql]561----562SELECT LOG(X) from Items;563----564== LOG10567[source,sql]569----570LOG10 (expression)571----572=== Parameters575- `expression` - any valid numeric expression.576=== Description578See also `Java Math.log10` (in Java 5). This method returns a `double`.579[discrete]581=== Example582Calculate LOG10:583[source,sql]586----587SELECT LOG(X) FROM Items;588----589== RADIANS592[source,sql]594----595RADIANS (expression)596----597=== Parameters600- `expression` - any valid numeric expression.601=== Description603See also Java Math.toRadians. This method returns a double.604[discrete]606=== Example607Calculates RADIANS:608[source,sql]611----612SELECT RADIANS(X) FROM Items;613----614== SQRT617[source,sql]619----620SQRT (expression)621----622=== Parameters625- `expression` - any valid numeric expression.626=== Description628See also `Java Math.sqrt`. This method returns a `double`.629[discrete]631=== Example632Calculates SQRT:633[source,sql]636----637SELECT SQRT(X) FROM Items;638----639== PI642[source,sql]645----646PI (expression)647----648=== Parameters651- `expression` - any valid numeric expression.652=== Description654See also `Java Math.PI`. This method returns a `double`.655[discrete]657=== Example658Calculates PI:659[source,sql]662----663SELECT PI(X) FROM Items;664----665== POWER668[source,sql]671----672POWER (X, Y)673----674=== Parameters677- `expression` - any valid numeric expression.678=== Description680See also `Java Math.pow`. This method returns a `double`.681[discrete]683=== Example684Calculate the power of 2:685[source,sql]688----689SELECT pow(2, n) FROM Rows;690----691== RAND694[source,sql]696----697{RAND | RANDOM} ([expression])698----699=== Parameters702- `expression` - any valid numeric expression seeds the session's random number generator.703=== Description705Calling the function without a parameter returns the next a pseudo random number. Calling it with a parameter seeds the session's random number generator. This method returns a `double` between 0 (including) and 1 (excluding).706[discrete]708=== Example709Gets a random number for every play:710[source,sql]713----714SELECT random() FROM Play;715----716== RANDOM_UUID719[source,sql]721----722{RANDOM_UUID | UUID} ()723----724=== Description727Returns a new UUID with 122 pseudo random bits.728[discrete]730=== Example731Gets random number for every Player:732[source,sql]735----736SELECT UUID(),name FROM Player;737----738== ROUND741[source,sql]743----744ROUND ( expression [, precision] )745----746=== Parameters749- `expression` - any valid numeric expression.750- `precision` - the number of digits after the decimal to round to. Rounds to the nearest long if the number of digits if not set.751=== Description753Rounds to a number of digits, or to the nearest long if the number of digits if not set. This method returns a `numeric` (the same type as the input).754[discrete]756=== Example757Convert every Player's age to an integer number:758[source,sql]761----762SELECT name, ROUND(age) FROM Player;763----764== ROUNDMAGIC767[source,sql]769----770ROUNDMAGIC (expression)771----772=== Parameters775- `expression` - any valid numeric expression.776=== Description778This function is good for rounding numbers, but it can be slow. It has special handling for numbers around 0. Only numbers smaller than or equal to `+/-1000000000000` are supported. The value is converted to a String internally, and then the last 4 characters are checked. '000x' becomes '0000' and '999x' becomes '999999', which is rounded automatically. This method returns a `double`.779[discrete]781=== Example782Round every Player's age:783[source,sql]786----787SELECT name, ROUNDMAGIC(AGE/3*3) FROM Player;788----789== SECURE_RAND792[source,sql]794----795SECURE_RAND (int)796----797=== Parameters800- `int` - specifies the number of digits.801=== Description803Generate a number of cryptographically secure random numbers. This method returns `bytes`.804[discrete]806=== Example807Get a truly random number:808[source,sql]811----812SELECT name, SECURE_RAND(10) FROM Player;813----814== SIGN817[source,sql]819----820SIGN (expression)821----822=== Parameters825- `expression` - any valid numeric expression.826=== Description828Return -1 if the value is smaller 0, 0 if zero, and otherwise 1.829[discrete]831=== Example832Get a sign for every value:833[source,sql]836----837SELECT name, SIGN(VALUE) FROM Player;838----839== ENCRYPT842[source,sql]844----845ENCRYPT (algorithmString , keyBytes , dataBytes)846----847=== Parameters850- `algorithmString` - sets a supported AES algorithm.851- `keyBytes` - sets a key.852- `dataBytes` - sets data.853=== Description855Encrypt data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns `bytes`.856[discrete]858=== Example859Encrypt players name:860[source,sql]863----864SELECT ENCRYPT('AES', '00', STRINGTOUTF8(Name)) FROM Player;865----866== DECRYPT869[source,sql]871----872DECRYPT (algorithmString , keyBytes , dataBytes)873----874=== Parameters877- `algorithmString` - sets a supported AES algorithm.878- `keyBytes` - sets a key.879- `dataBytes` - sets data.880=== Description882Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.883[discrete]885=== Example886Decrypt Players' names:887[source,sql]890----891SELECT DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) FROM Player;892----893== TRUNCATE896[source,sql]899----900{TRUNC | TRUNCATE} (\{\{numeric, digitsInt} | timestamp | date | timestampString})901----902=== Description905Truncates to a number of digits (to the next value closer to 0). This method returns a `double`. When used with a timestamp, truncates a timestamp to a date (day) value. When used with a date, truncates a date to a date (day) value less time part. When used with a timestamp as string, truncates a timestamp to a date (day) value.906[discrete]908=== Example909[source,sql]911----912TRUNCATE(VALUE, 2);913----914== COMPRESS917[source,sql]919----920COMPRESS(dataBytes [, algorithmString])921----922=== Parameters925- `dataBytes` - data to compress.926- `algorithmString` - an algorithm to use for compression.927=== Description929Compress the data using the specified compression algorithm. Supported algorithms are: LZF (faster but lower compression; default), and DEFLATE (higher compression). Compression does not always reduce size. Very small objects and objects with little redundancy may get larger. This method returns `bytes`.930[discrete]932=== Example933[source,sql]935----936COMPRESS(STRINGTOUTF8('Test'))937----938== EXPAND941[source,sql]943----944EXPAND(dataBytes)945----946=== Parameters949- `dataBytes` - data to expand.950=== Description952Expand data that was compressed using the COMPRESS function. This method returns `bytes`.953[discrete]955=== Example956[source,sql]958----959UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))960----961== ZERO964[source,sql]966----967ZERO()968----969=== Description972Return the value 0. This function can be used even if numeric literals are disabled.973[discrete]975=== Example976[source,sql]978----979ZERO()980----981