impala-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jruss...@apache.org
Subject incubator-impala git commit: IMPALA-5529: [DOCS] New trunc() signatures
Date Fri, 06 Oct 2017 22:33:36 GMT
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 <grahn@cloudera.com>
Reviewed-by: Thomas Tauber-Marshall <tmarshall@cloudera.com>
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 <jrussell@cloudera.com>
Authored: Sun Oct 1 21:27:54 2017 -0700
Committer: Impala Public Jenkins <impala-public-jenkins@gerrit.cloudera.org>
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>


Mime
View raw message