Repository: incubator-impala Updated Branches: refs/heads/master 581dfe002 -> 36cd610d6
IMPALA-5529: [DOCS] New trunc() signatures Change-Id: Ice4753dee4f7b8e09c35508a9cad1e36f4ab2826 Reviewed-on: http://gerrit.cloudera.org:8080/8189 Reviewed-by: Greg Rahn <[email protected]> Reviewed-by: Thomas Tauber-Marshall <[email protected]> Tested-by: Impala Public Jenkins Project: http://git-wip-us.apache.org/repos/asf/incubator-impala/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-impala/commit/36cd610d Tree: http://git-wip-us.apache.org/repos/asf/incubator-impala/tree/36cd610d Diff: http://git-wip-us.apache.org/repos/asf/incubator-impala/diff/36cd610d Branch: refs/heads/master Commit: 36cd610d6ba27104a40207ca34132b3349231d11 Parents: 581dfe0 Author: John Russell <[email protected]> Authored: Sun Oct 1 21:27:54 2017 -0700 Committer: Impala Public Jenkins <[email protected]> Committed: Fri Oct 6 22:29:55 2017 +0000 ---------------------------------------------------------------------- docs/topics/impala_datetime_functions.xml | 24 +++- docs/topics/impala_math_functions.xml | 163 +++++++++++++++++++++++-- 2 files changed, 170 insertions(+), 17 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/36cd610d/docs/topics/impala_datetime_functions.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_datetime_functions.xml b/docs/topics/impala_datetime_functions.xml index c0e3473..de8291b 100644 --- a/docs/topics/impala_datetime_functions.xml +++ b/docs/topics/impala_datetime_functions.xml @@ -2313,7 +2313,7 @@ select now() as 'Current time in California USA', <dlentry rev="1.4.0" id="trunc"> - <dt> + <dt id="trunc_timestamp"> <codeph>trunc(timestamp, string unit)</codeph> </dt> @@ -2321,7 +2321,8 @@ select now() as 'Current time in California USA', <indexterm audience="hidden">trunc() function</indexterm> <b>Purpose:</b> Strips off fields from a <codeph>TIMESTAMP</codeph> value. <p> - <b>Unit argument:</b> The <codeph>unit</codeph> argument value is case-sensitive. This argument string + <b>Unit argument:</b> The <codeph>unit</codeph> argument value for truncating + <codeph>TIMESTAMP</codeph> values is case-sensitive. This argument string can be one of: <!-- Some but not all of the arguments from http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084 are supported here. Impala doesn't support 2-digit years or ISO-related years or values derived from ISO years. @@ -2364,12 +2365,16 @@ select now() as 'Current time in California USA', </li> </ul> </p> + <p> + <b>Added in:</b> The ability to truncate numeric values is new starting in + <keyword keyref="impala210_full"/>. + </p> <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> <p> - Typically used in <codeph>GROUP BY</codeph> queries to aggregate results from the - same hour, day, week, month, quarter, and so on. You can also use this function in an <codeph>INSERT - ... SELECT</codeph> into a partitioned table to divide <codeph>TIMESTAMP</codeph> values into the - correct partition. + The <codeph>TIMESTAMP</codeph> form is typically used in <codeph>GROUP BY</codeph> + queries to aggregate results from the same hour, day, week, month, quarter, and so on. + You can also use this function in an <codeph>INSERT ... SELECT</codeph> into a + partitioned table to divide <codeph>TIMESTAMP</codeph> values into the correct partition. </p> <p> Because the return value is a <codeph>TIMESTAMP</codeph>, if you cast the result of @@ -2380,6 +2385,13 @@ select now() as 'Current time in California USA', original value, then run <codeph>EXTRACT()</codeph> on the result. </p> <p> + The <codeph>trunc()</codeph> function also has a signature that applies to + <codeph>DOUBLE</codeph> or <codeph>DECIMAL</codeph>values. <codeph>truncate()</codeph>, + <codeph>trunc()</codeph>, and <codeph>dtrunc()</codeph> are all aliased to the + same function. See <codeph>truncate()</codeph> under <xref keyref="math_functions"/> + for details. + </p> + <p> <b>Return type:</b> <codeph>timestamp</codeph> </p> <p conref="../shared/impala_common.xml#common/example_blurb"/> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/36cd610d/docs/topics/impala_math_functions.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_math_functions.xml b/docs/topics/impala_math_functions.xml index 94753b7..f8b2604 100644 --- a/docs/topics/impala_math_functions.xml +++ b/docs/topics/impala_math_functions.xml @@ -1327,64 +1327,205 @@ select x, unix_timestamp(now()), rand(unix_timestamp(now())) <dt rev="2.3.0"> <codeph>truncate(double_or_decimal a[, digits_to_leave])</codeph>, - <ph id="dtrunc"><codeph>dtrunc(double_or_decimal a[, digits_to_leave])</codeph></ph> + <ph id="dtrunc"><codeph>dtrunc(double_or_decimal a[, digits_to_leave])</codeph></ph>, + <ph rev="2.10.0 IMPALA-5529" id="trunc_number"><codeph>trunc(double_or_decimal a[, digits_to_leave])</codeph></ph> </dt> <dd rev="2.3.0"> <indexterm audience="hidden">truncate() function</indexterm> <indexterm audience="hidden">dtrunc() function</indexterm> + <indexterm audience="hidden">trunc() function</indexterm> <b>Purpose:</b> Removes some or all fractional digits from a numeric value. - With no argument, removes all fractional digits, leaving an integer value. - The optional argument specifies the number of fractional digits to include - in the return value, and only applies with the argument type is <codeph>DECIMAL</codeph>. - <codeph>truncate()</codeph> and <codeph>dtrunc()</codeph> are aliases for the same function. + <p> + <b>Arguments:</b> + With a single floating-point argument, removes all fractional digits, leaving an + integer value. The optional second argument specifies the number of fractional digits + to include in the return value, and only applies when the argument type is + <codeph>DECIMAL</codeph>. A second argument of 0 truncates to a whole integer value. + A second argument of negative N sets N digits to 0 on the left side of the decimal + </p> + <p rev="2.10.0 IMPALA-5529"> + <b>Scale argument:</b> The scale argument applies only when truncating + <codeph>DECIMAL</codeph> values. It is an integer specifying how many + significant digits to leave to the right of the decimal point. + A scale argument of 0 truncates to a whole integer value. A scale + argument of negative N sets N digits to 0 on the left side of the decimal + point. + </p> + <p> + <codeph>truncate()</codeph>, <codeph>dtrunc()</codeph>, + <ph rev="2.10.0 IMPALA-5529">and <codeph>trunc()</codeph></ph> are aliases for the + same function. + </p> <p> <b>Return type:</b> <codeph>decimal</codeph> for <codeph>DECIMAL</codeph> arguments; <codeph>bigint</codeph> for <codeph>DOUBLE</codeph> arguments </p> + <p> + <b>Added in:</b> The <codeph>trunc()</codeph> alias was added in + <keyword keyref="impala210_full"/>. + </p> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p rev="2.10.0 IMPALA-5529"> + You can also pass a <codeph>DOUBLE</codeph> argument, or <codeph>DECIMAL</codeph> + argument with optional scale, to the <codeph>dtrunc()</codeph> or + <codeph>truncate</codeph> functions. Using the <codeph>trunc()</codeph> + function for numeric values is common with other industry-standard database + systems, so you might find such <codeph>trunc()</codeph> calls in code that you + are porting to Impala. + </p> + <p> + The <codeph>trunc()</codeph> function also has a signature that applies to + <codeph>TIMESTAMP</codeph> values. See <xref keyref="datetime_functions"/> + for details. + </p> <p conref="../shared/impala_common.xml#common/example_blurb"/> -<codeblock>select truncate(3.45) + <p> + The following examples demonstrate the <codeph>truncate()</codeph> + and <codeph>dtrunc()</codeph> signatures for this function: + </p> +<codeblock>select truncate(3.45); +----------------+ | truncate(3.45) | +----------------+ | 3 | +----------------+ -select truncate(-3.45) +select truncate(-3.45); +-----------------+ | truncate(-3.45) | +-----------------+ | -3 | +-----------------+ -select truncate(3.456,1) +select truncate(3.456,1); +--------------------+ | truncate(3.456, 1) | +--------------------+ | 3.4 | +--------------------+ -select dtrunc(3.456,1) +select dtrunc(3.456,1); +------------------+ | dtrunc(3.456, 1) | +------------------+ | 3.4 | +------------------+ -select truncate(3.456,2) +select truncate(3.456,2); +--------------------+ | truncate(3.456, 2) | +--------------------+ | 3.45 | +--------------------+ -select truncate(3.456,7) +select truncate(3.456,7); +--------------------+ | truncate(3.456, 7) | +--------------------+ | 3.4560000 | +--------------------+ </codeblock> + <p rev="2.10.0 IMPALA-5529"> + The following examples demonstrate using <codeph>trunc()</codeph> with + <codeph>DECIMAL</codeph> or <codeph>DOUBLE</codeph> values, and with + an optional scale argument for <codeph>DECIMAL</codeph> values. + (The behavior is the same for the <codeph>truncate()</codeph> and + <codeph>dtrunc()</codeph> aliases also.) + </p> +<codeblock rev="2.10.0 IMPALA-5529"> +create table t1 (d decimal(20,7)); + +-- By default, no digits to the right of the decimal point. +insert into t1 values (1.1), (2.22), (3.333), (4.4444), (5.55555); +select trunc(d) from t1 order by d; ++----------+ +| trunc(d) | ++----------+ +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | ++----------+ + +-- 1 digit to the right of the decimal point. +select trunc(d,1) from t1 order by d; ++-------------+ +| trunc(d, 1) | ++-------------+ +| 1.1 | +| 2.2 | +| 3.3 | +| 4.4 | +| 5.5 | ++-------------+ + +-- 2 digits to the right of the decimal point, +-- including trailing zeroes if needed. +select trunc(d,2) from t1 order by d; ++-------------+ +| trunc(d, 2) | ++-------------+ +| 1.10 | +| 2.22 | +| 3.33 | +| 4.44 | +| 5.55 | ++-------------+ + +insert into t1 values (9999.9999), (8888.8888); + +-- Negative scale truncates digits to the left +-- of the decimal point. +select trunc(d,-2) from t1 where d > 100 order by d; ++--------------+ +| trunc(d, -2) | ++--------------+ +| 8800 | +| 9900 | ++--------------+ + +-- The scale of the result is adjusted to match the +-- scale argument. +select trunc(d,2), + precision(trunc(d,2)) as p, + scale(trunc(d,2)) as s +from t1 order by d; ++-------------+----+---+ +| trunc(d, 2) | p | s | ++-------------+----+---+ +| 1.10 | 15 | 2 | +| 2.22 | 15 | 2 | +| 3.33 | 15 | 2 | +| 4.44 | 15 | 2 | +| 5.55 | 15 | 2 | +| 8888.88 | 15 | 2 | +| 9999.99 | 15 | 2 | ++-------------+----+---+ +</codeblock> + +<codeblock rev="2.10.0 IMPALA-5529"> +create table dbl (d double); + +insert into dbl values + (1.1), (2.22), (3.333), (4.4444), (5.55555), + (8888.8888), (9999.9999); + +-- With double values, there is no optional scale argument. +select trunc(d) from dbl order by d; ++----------+ +| trunc(d) | ++----------+ +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | +| 8888 | +| 9999 | ++----------+ +</codeblock> </dd> </dlentry>
