# Numeric Functions The topics in this section describe the numeric functions for Amazon Kinesis Data Analytics streaming SQL. **Topics** + [ABS](sql-reference-abs.md) + [CEIL / CEILING](sql-reference-ceil.md) + [EXP](sql-reference-exp.md) + [FLOOR](sql-reference-floor.md) + [LN](sql-reference-ln.md) + [LOG10](sql-reference-log10.md) + [MOD](sql-reference-mod.md) + [POWER](sql-reference-power.md) + [STEP](sql-reference-step.md) # ABS Returns the absolute value of the input argument. Returns `null` if the input argument is null. ``` ABS (

) ``` ## Examples | Function | Result | | --- | --- | | ABS(2.0) | 2.0 | | ABS(-1.0) | 1.0 | | ABS(0) | 0 | | ABS(-3 \$1 3) | 9 | | ABS(INTERVAL '-3 4:20' DAY TO MINUTE) | INTERVAL '3 4:20' DAY TO MINUTE | If you use `cast as VARCHAR` in SQLline to show the output, the value is returned as `+3 04:20`. ``` values(cast(ABS(INTERVAL '-3 4:20' DAY TO MINUTE) AS VARCHAR(8))); +-----------+ EXPR$0 +-----------+ +3 04:20 +-----------+ 1 row selected ``` # CEIL / CEILING ``` CEIL | CEILING ( ) CEIL | CEILING ( TO ) CEIL | CEILING ( ) CEIL | CEILING ( TO <[[time-unit> ) ``` When called with a numeric argument, CEILING returns the smallest integer equal to or larger than the input argument. When called with a date, time, or timestamp expression, CEILING returns the smallest value equal to or larger than the input, subject to the precision specified by the . Returns null if any input argument is null. ## Examples | Function | Result | | --- | --- | | CEIL(2.0) | 2 | | CEIL(-1.0) | -1 | | CEIL(5.2) | 6 | | CEILING(-3.3) | -3 | | CEILING(-3 \$1 3.1) | -9 | | CEILING(TIMESTAMP '2004-09-30 13:48:23' TO HOUR) | TIMESTAMP '2004-09-30 14:00:00' | | CEILING(TIMESTAMP '2004-09-30 13:48:23' TO MINUTE) | TIMESTAMP '2004-09-30 13:49:00' | | CEILING(TIMESTAMP '2004-09-30 13:48:23' TO DAY) | TIMESTAMP '2004-10-01 00:00:00.0' | | CEILING(TIMESTAMP '2004-09-30 13:48:23' TO YEAR) | TIMESTAMP '2005-01-01 00:00:00.0' | ## Notes + CEIL and CEILING are synonyms for this function provided by the SQL:2008 standard. + CEIL( TO ) is an Amazon Kinesis Data Analytics extension. + For more information, see [FLOOR](sql-reference-floor.md). # EXP ``` EXP ( ) ``` Returns the value of e (approximately 2.7182818284590455) raised to the power of the input argument. Returns null if the input argument is null. ## Examples | Function | Result | | --- | --- | | EXP(1) | 2.7182818284590455 | | EXP(0) | 1.0 | | EXP(-1) | 0.36787944117144233 | | EXP(10) | 22026.465794806718 | | EXP(2.5) | 12.182493960703473 | # FLOOR ``` FLOOR ( ) ``` When called with a numeric argument, FLOOR returns the largest integer equal to or smaller than the input argument. When called with a date, time, or timestamp expression, FLOOR returns the largest value equal to or smaller than the input, subject to the precision specified by . FLOOR returns null if any input argument is null. ## Examples | Function | Result | | --- | --- | | FLOOR(2.0) | 2 | | FLOOR(-1.0) | -1 | | FLOOR(5.2) | 5 | | FLOOR(-3.3) | -4 | | FLOOR(-3 \$1 3.1) | -10 | | FLOOR(TIMESTAMP '2004-09-30 13:48:23' TO HOUR) | TIMESTAMP '2004-09-30 13:00:00' | | FLOOR(TIMESTAMP '2004-09-30 13:48:23' TO MINUTE) | TIMESTAMP '2004-09-30 13:48:00' | | FLOOR(TIMESTAMP '2004-09-30 13:48:23' TO DAY) | TIMESTAMP '2004-09-30 00:00:00.0' | | FLOOR(TIMESTAMP '2004-09-30 13:48:23' TO YEAR) | TIMESTAMP '2004-01-01 00:00:00.0' | ## Notes **Note** FLOOR ( TO ) is an Amazon Kinesis Data Analytics extension. The STEP function is similar to FLOOR but can round values down to arbitrary intervals, such as 30 seconds. For more information, see [STEP](sql-reference-step.md). # LN ``` LN ( ) ``` Returns the natural log (that is, the log with respect to base e) of the input argument. If the argument is negative or 0, an exception is raised. Returns null if the input argument is null. For more information, see [LOG10](sql-reference-log10.md) and [EXP](sql-reference-exp.md). ## Examples | Function | Result | | --- | --- | | LN(1) | 0.0 | | LN(10) | 2.302585092994046 | | LN(2.5) | 0.9162907318741551 | # LOG10 ``` LOG10 ( ) ``` Returns the base 10 logarithm of the input argument. If the argument is negative or 0, an exception is raised. Returns null if the input argument is null. ## Examples | Function | Result | | --- | --- | | LOG10(1) | 0.0 | | LOG10(100) | 2.0 | | log10(cast('23' as decimal)) | 1.3617278360175928 | ## **Note** LOG10 is not a SQL:2008 standard function; it is an Amazon Kinesis Data Analytics extension to the standard. # MOD ``` MOD ( , ) := := ``` Returns the remainder when the first argument (the dividend is divided by the second numeric argument (the divisor). If the divisor is zero, a divide by zero error is raised. ## Examples | Function | Result | | --- | --- | | MOD(4,2) | 0 | | MOD(5,3) | 2 | | MOD(-4,3) | -1 | | MOD(5,12) | 5 | ## Limitations The Amazon Kinesis Data Analytics MOD function only supports arguments of scale 0 (integers). This is a departure from the SQL:2008 standard, which supports any numeric argument. Other numeric arguments can be CAST to an integer, of course. # POWER ``` POWER ( , ) := := ``` Returns the value of the first argument (the base) raised to the power of the second argument (the exponent). Returns null if either the base or the exponent is null, and raises an exception if the base is zero and the exponent is negative, or if the base is negative and the exponent is not a whole number. ## Examples | Function | Result | | --- | --- | | POWER(3,2) | 9 | | POWER(-2,3) | -8 | | POWER(4,-2) | 1/16 ..or.. 0.0625 | | POWER(10.1,2.5) | 324.19285157140644 | # STEP ``` STEP ( BY INTERVAL '' ) STEP ( BY ) ``` STEP rounds down the input value ( or ) to the nearest multiple of . The STEP function works on datetime data types or integer types. STEP is a scalar function that performs an operation similar to [FLOOR](sql-reference-floor.md). However, by using STEP you can specify an arbitrary time or integer interval for rounding down the first argument. STEP returns null if any input argument is null. ## STEP with an Integer Argument When called with an integer argument, STEP returns the largest integer multiple of the argument equal to or smaller than the argument. For example, `STEP(23 BY 5)` returns `20`, because 20 is the greatest multiple of 5 that is less than 23. `STEP ( BY )` is equivalent to the following. ``` ( / ) * ``` ### Examples In the following examples, the return value is the largest multiple of that is equal to or less than . | Function | Result | | --- | --- | | STEP(23 BY 5) | 20 | | STEP(30 BY 10) | 30 | ## STEP with a Date Type Argument When called with a date, time, or timestamp argument, STEP returns the largest value equal to or smaller than the input, subject to the precision specified by . `STEP( BY )` is equivalent to the following. ``` ( - timestamp '1970-01-01 00:00:00') / ) * + timestamp '1970-01-01 00:00:00' ``` can be one of the following: + YEAR + MONTH + DAY + HOUR + MINUTE + SECOND ### Examples In the following examples, the return value is the latest multiple of of the unit specified by that is equal to or earlier than . | Function | Result | | --- | --- | | STEP(CAST('2004-09-30 13:48:23' as TIMESTAMP) BY INTERVAL '10' SECOND) | '2004-09-30 13:48:20' | | STEP(CAST('2004-09-30 13:48:23' as TIMESTAMP) BY INTERVAL '2' HOUR) | '2004-09-30 12:00:00' | | STEP(CAST('2004-09-30 13:48:23' as TIMESTAMP) BY INTERVAL '5' MINUTE) | '2004-09-30 13:45:00' | | STEP(CAST('2004-09-27 13:48:23' as TIMESTAMP) BY INTERVAL '5' DAY) | '2004-09-25 00:00:00.0' | | STEP(CAST('2004-09-30 13:48:23' as TIMESTAMP) BY INTERVAL '1' YEAR) | '2004-01-01 00:00:00.0' | ## STEP in a GROUP BY clause (tumbling window) In this example, an aggregate query has a `GROUP BY` clause with `STEP` applied to `ROWTIME` that groups the stream into finite rows. ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4), sum_price DOUBLE); -- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, SUM(price) AS sum_price FROM "SOURCE_SQL_STREAM_001" GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND); ``` ### Results The preceding example outputs a stream similar to the following. ![\[Data table showing ROWTIME, TICKER_SYMBOL, and SUM_PRICE columns with sample entries.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-step-example-1.png) ## STEP in an OVER clause (sliding window) ``` CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ingest_time TIMESTAMP, ticker_symbol VARCHAR(4), ticker_symbol_count integer); --Create pump data into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" -- select the ingest time used in the GROUP BY clause SELECT STREAM STEP(source_sql_stream_001.approximate_arrival_time BY INTERVAL '10' SECOND) as ingest_time, ticker_symbol, count(*) over w1 as ticker_symbol_count FROM source_sql_stream_001 WINDOW w1 AS ( PARTITION BY ticker_symbol, -- aggregate records based upon ingest time STEP(source_sql_stream_001.approximate_arrival_time BY INTERVAL '10' SECOND) -- use process time as a trigger, which can be different time window as the aggregate RANGE INTERVAL '10' SECOND PRECEDING); ``` ### Results The preceding example outputs a stream similar to the following. ![\[Table showing ROWTIME, INGEST_TIME, TICKER_SYMBOL, and TICKER_SYMBOL_COUNT columns with sample data.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-step-example-2.png) ## Notes STEP ( BY ) is an Amazon Kinesis Data Analytics extension. You can use STEP to aggregate results using tumbling windows. For more information on tumbling windows, see [Tumbling Window Concepts](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/tumbling-window-concepts.html).