Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
关注 1
点赞 0
百科 捐赠
gooderp18绿色标准版
您最多选择25个主题 主题必须以字母或数字开头，可以包含连字符 (-)，并且长度不得超过35个字符
分支: master
分支列表 标签列表
${ item.name }
创建分支 ${ searchTerm }
从 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/cube.html

222 行
22KB
原始文件 永久链接 Blame 文件历史 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.9. cube</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="citext.html" title="F.8. citext" /><link rel="next" href="dblink.html" title="F.10. dblink" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.9. cube</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="citext.html" title="F.8. citext">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="dblink.html" title="F.10. dblink">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="CUBE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.9. cube</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.4">F.9.1. Syntax</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.5">F.9.2. Precision</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.6">F.9.3. Usage</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.7">F.9.4. Defaults</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.8">F.9.5. Notes</a></span></dt><dt><span class="sect2"><a href="cube.html#id-1.11.7.18.9">F.9.6. Credits</a></span></dt></dl></div><a id="id-1.11.7.18.2" class="indexterm"></a><p>

  3.   This module implements a data type <code class="type">cube</code> for

  4.   representing multidimensional cubes.

  5.  </p><div class="sect2" id="id-1.11.7.18.4"><div class="titlepage"><div><div><h3 class="title">F.9.1. Syntax</h3></div></div></div><p>

  6.    <a class="xref" href="cube.html#CUBE-REPR-TABLE" title="Table F.2. Cube External Representations">Table F.2</a> shows the valid external

  7.    representations for the <code class="type">cube</code>

  8.    type.  <em class="replaceable"><code>x</code></em>, <em class="replaceable"><code>y</code></em>, etc. denote

  9.    floating-point numbers.

  10.   </p><div class="table" id="CUBE-REPR-TABLE"><p class="title"><strong>Table F.2. Cube External Representations</strong></p><div class="table-contents"><table class="table" summary="Cube External Representations" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>External Syntax</th><th>Meaning</th></tr></thead><tbody><tr><td><code class="literal"><em class="replaceable"><code>x</code></em></code></td><td>A one-dimensional point

  11.        (or, zero-length one-dimensional interval)

  12.       </td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x</code></em>)</code></td><td>Same as above</td></tr><tr><td><code class="literal"><em class="replaceable"><code>x1</code></em>,<em class="replaceable"><code>x2</code></em>,...,<em class="replaceable"><code>xn</code></em></code></td><td>A point in n-dimensional space, represented internally as a

  13.       zero-volume cube

  14.       </td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x1</code></em>,<em class="replaceable"><code>x2</code></em>,...,<em class="replaceable"><code>xn</code></em>)</code></td><td>Same as above</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)</code></td><td>A one-dimensional interval starting at <em class="replaceable"><code>x</code></em> and ending at <em class="replaceable"><code>y</code></em> or vice versa; the

  15.        order does not matter

  16.       </td></tr><tr><td><code class="literal">[(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)]</code></td><td>Same as above</td></tr><tr><td><code class="literal">(<em class="replaceable"><code>x1</code></em>,...,<em class="replaceable"><code>xn</code></em>),(<em class="replaceable"><code>y1</code></em>,...,<em class="replaceable"><code>yn</code></em>)</code></td><td>An n-dimensional cube represented by a pair of its diagonally

  17.        opposite corners

  18.       </td></tr><tr><td><code class="literal">[(<em class="replaceable"><code>x1</code></em>,...,<em class="replaceable"><code>xn</code></em>),(<em class="replaceable"><code>y1</code></em>,...,<em class="replaceable"><code>yn</code></em>)]</code></td><td>Same as above</td></tr></tbody></table></div></div><br class="table-break" /><p>

  19.    It does not matter which order the opposite corners of a cube are

  20.    entered in.  The <code class="type">cube</code> functions

  21.    automatically swap values if needed to create a uniform

  22.    <span class="quote">“<span class="quote">lower left — upper right</span>”</span> internal representation.

  23.    When the corners coincide, <code class="type">cube</code> stores only one corner

  24.    along with an <span class="quote">“<span class="quote">is point</span>”</span> flag to avoid wasting space.

  25.   </p><p>

  26.    White space is ignored on input, so

  27.    <code class="literal">[(<em class="replaceable"><code>x</code></em>),(<em class="replaceable"><code>y</code></em>)]</code> is the same as

  28.    <code class="literal">[ ( <em class="replaceable"><code>x</code></em> ), ( <em class="replaceable"><code>y</code></em> ) ]</code>.

  29.   </p></div><div class="sect2" id="id-1.11.7.18.5"><div class="titlepage"><div><div><h3 class="title">F.9.2. Precision</h3></div></div></div><p>

  30.    Values are stored internally as 64-bit floating point numbers. This means

  31.    that numbers with more than about 16 significant digits will be truncated.

  32.   </p></div><div class="sect2" id="id-1.11.7.18.6"><div class="titlepage"><div><div><h3 class="title">F.9.3. Usage</h3></div></div></div><p>

  33.    <a class="xref" href="cube.html#CUBE-OPERATORS-TABLE" title="Table F.3. Cube Operators">Table F.3</a> shows the operators provided for

  34.    type <code class="type">cube</code>.

  35.   </p><div class="table" id="CUBE-OPERATORS-TABLE"><p class="title"><strong>Table F.3. Cube Operators</strong></p><div class="table-contents"><table class="table" summary="Cube Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Result</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">a = b</code></td><td><code class="type">boolean</code></td><td>The cubes a and b are identical.</td></tr><tr><td><code class="literal">a &amp;&amp; b</code></td><td><code class="type">boolean</code></td><td>The cubes a and b overlap.</td></tr><tr><td><code class="literal">a @&gt; b</code></td><td><code class="type">boolean</code></td><td>The cube a contains the cube b.</td></tr><tr><td><code class="literal">a &lt;@ b</code></td><td><code class="type">boolean</code></td><td>The cube a is contained in the cube b.</td></tr><tr><td><code class="literal">a &lt; b</code></td><td><code class="type">boolean</code></td><td>The cube a is less than the cube b.</td></tr><tr><td><code class="literal">a &lt;= b</code></td><td><code class="type">boolean</code></td><td>The cube a is less than or equal to the cube b.</td></tr><tr><td><code class="literal">a &gt; b</code></td><td><code class="type">boolean</code></td><td>The cube a is greater than the cube b.</td></tr><tr><td><code class="literal">a &gt;= b</code></td><td><code class="type">boolean</code></td><td>The cube a is greater than or equal to the cube b.</td></tr><tr><td><code class="literal">a &lt;&gt; b</code></td><td><code class="type">boolean</code></td><td>The cube a is not equal to the cube b.</td></tr><tr><td><code class="literal">a -&gt; n</code></td><td><code class="type">float8</code></td><td>Get <em class="replaceable"><code>n</code></em>-th coordinate of cube (counting from 1).</td></tr><tr><td><code class="literal">a ~&gt; n</code></td><td><code class="type">float8</code></td><td>

  36.         Get <em class="replaceable"><code>n</code></em>-th coordinate of cube in following way:

  37.         n = 2 * k - 1 means lower bound of <em class="replaceable"><code>k</code></em>-th

  38.         dimension, n = 2 * k means upper bound of

  39.         <em class="replaceable"><code>k</code></em>-th dimension.  Negative

  40.         <em class="replaceable"><code>n</code></em> denotes the inverse value of the corresponding

  41.         positive coordinate.  This operator is designed for KNN-GiST support.

  42.       </td></tr><tr><td><code class="literal">a &lt;-&gt; b</code></td><td><code class="type">float8</code></td><td>Euclidean distance between a and b.</td></tr><tr><td><code class="literal">a &lt;#&gt; b</code></td><td><code class="type">float8</code></td><td>Taxicab (L-1 metric) distance between a and b.</td></tr><tr><td><code class="literal">a &lt;=&gt; b</code></td><td><code class="type">float8</code></td><td>Chebyshev (L-inf metric) distance between a and b.</td></tr></tbody></table></div></div><br class="table-break" /><p>

  43.    (Before PostgreSQL 8.2, the containment operators <code class="literal">@&gt;</code> and <code class="literal">&lt;@</code> were

  44.    respectively called <code class="literal">@</code> and <code class="literal">~</code>.  These names are still available, but are

  45.    deprecated and will eventually be retired.  Notice that the old names

  46.    are reversed from the convention formerly followed by the core geometric

  47.    data types!)

  48.   </p><p>

  49.    The scalar ordering operators (<code class="literal">&lt;</code>, <code class="literal">&gt;=</code>, etc)

  50.    do not make a lot of sense for any practical purpose but sorting.  These

  51.    operators first compare the first coordinates, and if those are equal,

  52.    compare the second coordinates, etc.  They exist mainly to support the

  53.    b-tree index operator class for <code class="type">cube</code>, which can be useful for

  54.    example if you would like a UNIQUE constraint on a <code class="type">cube</code> column.

  55.   </p><p>

  56.    The <code class="filename">cube</code> module also provides a GiST index operator class for

  57.    <code class="type">cube</code> values.

  58.    A <code class="type">cube</code> GiST index can be used to search for values using the

  59.    <code class="literal">=</code>, <code class="literal">&amp;&amp;</code>, <code class="literal">@&gt;</code>, and

  60.    <code class="literal">&lt;@</code> operators in <code class="literal">WHERE</code> clauses.

  61.   </p><p>

  62.    In addition, a <code class="type">cube</code> GiST index can be used to find nearest

  63.    neighbors using the metric operators

  64.    <code class="literal">&lt;-&gt;</code>, <code class="literal">&lt;#&gt;</code>, and

  65.    <code class="literal">&lt;=&gt;</code> in <code class="literal">ORDER BY</code> clauses.

  66.    For example, the nearest neighbor of the 3-D point (0.5, 0.5, 0.5)

  67.    could be found efficiently with:

  68. </p><pre class="programlisting">

  69. SELECT c FROM test ORDER BY c &lt;-&gt; cube(array[0.5,0.5,0.5]) LIMIT 1;

  70. </pre><p>

  71.   </p><p>

  72.    The <code class="literal">~&gt;</code> operator can also be used in this way to

  73.    efficiently retrieve the first few values sorted by a selected coordinate.

  74.    For example, to get the first few cubes ordered by the first coordinate

  75.    (lower left corner) ascending one could use the following query:

  76. </p><pre class="programlisting">

  77. SELECT c FROM test ORDER BY c ~&gt; 1 LIMIT 5;

  78. </pre><p>

  79.    And to get 2-D cubes ordered by the first coordinate of the upper right

  80.    corner descending:

  81. </p><pre class="programlisting">

  82. SELECT c FROM test ORDER BY c ~&gt; 3 DESC LIMIT 5;

  83. </pre><p>

  84.   </p><p>

  85.    <a class="xref" href="cube.html#CUBE-FUNCTIONS-TABLE" title="Table F.4. Cube Functions">Table F.4</a> shows the available functions.

  86.   </p><div class="table" id="CUBE-FUNCTIONS-TABLE"><p class="title"><strong>Table F.4. Cube Functions</strong></p><div class="table-contents"><table class="table" summary="Cube Functions" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Result</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal">cube(float8)</code></td><td><code class="type">cube</code></td><td>Makes a one dimensional cube with both coordinates the same.

  87.       </td><td>

  88.        <code class="literal">cube(1) == '(1)'</code>

  89.       </td></tr><tr><td><code class="literal">cube(float8, float8)</code></td><td><code class="type">cube</code></td><td>Makes a one dimensional cube.

  90.       </td><td>

  91.        <code class="literal">cube(1,2) == '(1),(2)'</code>

  92.       </td></tr><tr><td><code class="literal">cube(float8[])</code></td><td><code class="type">cube</code></td><td>Makes a zero-volume cube using the coordinates

  93.        defined by the array.

  94.       </td><td>

  95.        <code class="literal">cube(ARRAY[1,2]) == '(1,2)'</code>

  96.       </td></tr><tr><td><code class="literal">cube(float8[], float8[])</code></td><td><code class="type">cube</code></td><td>Makes a cube with upper right and lower left

  97.        coordinates as defined by the two arrays, which must be of the

  98.        same length.

  99.       </td><td>

  100.        <code class="literal">cube(ARRAY[1,2], ARRAY[3,4]) == '(1,2),(3,4)'

  101.        </code>

  102.       </td></tr><tr><td><code class="literal">cube(cube, float8)</code></td><td><code class="type">cube</code></td><td>Makes a new cube by adding a dimension on to an existing cube,

  103.        with the same values for both endpoints of the new coordinate.  This

  104.        is useful for building cubes piece by piece from calculated values.

  105.       </td><td>

  106.        <code class="literal">cube('(1,2),(3,4)'::cube, 5) == '(1,2,5),(3,4,5)'</code>

  107.       </td></tr><tr><td><code class="literal">cube(cube, float8, float8)</code></td><td><code class="type">cube</code></td><td>Makes a new cube by adding a dimension on to an existing

  108.        cube. This is useful for building cubes piece by piece from calculated

  109.        values.

  110.       </td><td>

  111.        <code class="literal">cube('(1,2),(3,4)'::cube, 5, 6) == '(1,2,5),(3,4,6)'</code>

  112.       </td></tr><tr><td><code class="literal">cube_dim(cube)</code></td><td><code class="type">integer</code></td><td>Returns the number of dimensions of the cube.

  113.       </td><td>

  114.        <code class="literal">cube_dim('(1,2),(3,4)') == '2'</code>

  115.       </td></tr><tr><td><code class="literal">cube_ll_coord(cube, integer)</code></td><td><code class="type">float8</code></td><td>Returns the <em class="replaceable"><code>n</code></em>-th coordinate value for the lower

  116.        left corner of the cube.

  117.       </td><td>

  118.        <code class="literal">cube_ll_coord('(1,2),(3,4)', 2) == '2'</code>

  119.       </td></tr><tr><td><code class="literal">cube_ur_coord(cube, integer)</code></td><td><code class="type">float8</code></td><td>Returns the <em class="replaceable"><code>n</code></em>-th coordinate value for the

  120.        upper right corner of the cube.

  121.       </td><td>

  122.        <code class="literal">cube_ur_coord('(1,2),(3,4)', 2) == '4'</code>

  123.       </td></tr><tr><td><code class="literal">cube_is_point(cube)</code></td><td><code class="type">boolean</code></td><td>Returns true if the cube is a point, that is,

  124.        the two defining corners are the same.</td><td>

  125.       </td></tr><tr><td><code class="literal">cube_distance(cube, cube)</code></td><td><code class="type">float8</code></td><td>Returns the distance between two cubes. If both

  126.        cubes are points, this is the normal distance function.

  127.       </td><td>

  128.       </td></tr><tr><td><code class="literal">cube_subset(cube, integer[])</code></td><td><code class="type">cube</code></td><td>Makes a new cube from an existing cube, using a list of

  129.        dimension indexes from an array. Can be used to extract the endpoints

  130.        of a single dimension, or to drop dimensions, or to reorder them as

  131.        desired.

  132.       </td><td>

  133.        <code class="literal">cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2]) == '(3),(7)'</code>

  134.        <code class="literal">cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]) ==

  135.         '(5,3,1,1),(8,7,6,6)'</code>

  136.       </td></tr><tr><td><code class="literal">cube_union(cube, cube)</code></td><td><code class="type">cube</code></td><td>Produces the union of two cubes.

  137.       </td><td>

  138.       </td></tr><tr><td><code class="literal">cube_inter(cube, cube)</code></td><td><code class="type">cube</code></td><td>Produces the intersection of two cubes.

  139.       </td><td>

  140.       </td></tr><tr><td><code class="literal">cube_enlarge(c cube, r double, n integer)</code></td><td><code class="type">cube</code></td><td>Increases the size of the cube by the specified

  141.        radius <em class="replaceable"><code>r</code></em> in at least <em class="replaceable"><code>n</code></em> dimensions.

  142.        If the radius is negative the cube is shrunk instead.

  143.        All defined dimensions are changed by the radius <em class="replaceable"><code>r</code></em>.

  144.        Lower-left coordinates are decreased by <em class="replaceable"><code>r</code></em> and

  145.        upper-right coordinates are increased by <em class="replaceable"><code>r</code></em>.  If a

  146.        lower-left coordinate is increased to more than the corresponding

  147.        upper-right coordinate (this can only happen when <em class="replaceable"><code>r</code></em>

  148.        &lt; 0) than both coordinates are set to their average.

  149.        If <em class="replaceable"><code>n</code></em> is greater than the number of defined dimensions

  150.        and the cube is being enlarged (<em class="replaceable"><code>r</code></em> &gt; 0), then extra

  151.        dimensions are added to make <em class="replaceable"><code>n</code></em> altogether;

  152.        0 is used as the initial value for the extra coordinates.

  153.        This function is useful for creating bounding boxes around a point for

  154.        searching for nearby points.

  155.       </td><td>

  156.        <code class="literal">cube_enlarge('(1,2),(3,4)', 0.5, 3) ==

  157.         '(0.5,1.5,-0.5),(3.5,4.5,0.5)'</code>

  158.       </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="id-1.11.7.18.7"><div class="titlepage"><div><div><h3 class="title">F.9.4. Defaults</h3></div></div></div><p>

  159.    I believe this union:

  160.   </p><pre class="programlisting">

  161. select cube_union('(0,5,2),(2,3,1)', '0');

  162. cube_union

  163. -------------------

  164. (0, 0, 0),(2, 5, 2)

  165. (1 row)

  166. </pre><p>

  167.     does not contradict common sense, neither does the intersection

  168.    </p><pre class="programlisting">

  169. select cube_inter('(0,-1),(1,1)', '(-2),(2)');

  170. cube_inter

  171. -------------

  172. (0, 0),(1, 0)

  173. (1 row)

  174. </pre><p>

  175.     In all binary operations on differently-dimensioned cubes, I assume the

  176.     lower-dimensional one to be a Cartesian projection, i. e., having zeroes

  177.     in place of coordinates omitted in the string representation. The above

  178.     examples are equivalent to:

  179.    </p><pre class="programlisting">

  180. cube_union('(0,5,2),(2,3,1)','(0,0,0),(0,0,0)');

  181. cube_inter('(0,-1),(1,1)','(-2,0),(2,0)');

  182. </pre><p>

  183.     The following containment predicate uses the point syntax,

  184.     while in fact the second argument is internally represented by a box.

  185.     This syntax makes it unnecessary to define a separate point type

  186.     and functions for (box,point) predicates.

  187.    </p><pre class="programlisting">

  188. select cube_contains('(0,0),(1,1)', '0.5,0.5');

  189. cube_contains

  190. --------------

  191. t

  192. (1 row)

  193. </pre></div><div class="sect2" id="id-1.11.7.18.8"><div class="titlepage"><div><div><h3 class="title">F.9.5. Notes</h3></div></div></div><p>

  194.    For examples of usage, see the regression test <code class="filename">sql/cube.sql</code>.

  195.   </p><p>

  196.    To make it harder for people to break things, there

  197.    is a limit of 100 on the number of dimensions of cubes. This is set

  198.    in <code class="filename">cubedata.h</code> if you need something bigger.

  199.   </p></div><div class="sect2" id="id-1.11.7.18.9"><div class="titlepage"><div><div><h3 class="title">F.9.6. Credits</h3></div></div></div><p>

  200.    Original author: Gene Selkov, Jr. <code class="email">&lt;<a class="email" href="mailto:selkovjr@mcs.anl.gov">selkovjr@mcs.anl.gov</a>&gt;</code>,

  201.    Mathematics and Computer Science Division, Argonne National Laboratory.

  202.   </p><p>

  203.    My thanks are primarily to Prof. Joe Hellerstein

  204.    (<a class="ulink" href="https://dsf.berkeley.edu/jmh/" target="_top">https://dsf.berkeley.edu/jmh/</a>) for elucidating the

  205.    gist of the GiST (<a class="ulink" href="http://gist.cs.berkeley.edu/" target="_top">http://gist.cs.berkeley.edu/</a>), and

  206.    to his former student Andy Dong for his example written for Illustra.

  207.    I am also grateful to all Postgres developers, present and past, for

  208.    enabling myself to create my own world and live undisturbed in it. And I

  209.    would like to acknowledge my gratitude to Argonne Lab and to the

  210.    U.S. Department of Energy for the years of faithful support of my database

  211.    research.

  212.   </p><p>

  213.    Minor updates to this package were made by Bruno Wolff III

  214.    <code class="email">&lt;<a class="email" href="mailto:bruno@wolff.to">bruno@wolff.to</a>&gt;</code> in August/September of 2002. These include

  215.    changing the precision from single precision to double precision and adding

  216.    some new functions.

  217.   </p><p>

  218.    Additional updates were made by Joshua Reich <code class="email">&lt;<a class="email" href="mailto:josh@root.net">josh@root.net</a>&gt;</code> in

  219.    July 2006. These include <code class="literal">cube(float8[], float8[])</code> and

  220.    cleaning up the code to use the V1 call protocol instead of the deprecated

  221.    V0 protocol.

  222.   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="citext.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="dblink.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.8. citext </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.10. dblink</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1