Expressions¶
Kinetica has native support for a variety of expressions, which are used as inputs while querying data (for supported SQL expressions, see SQL Support ). These native expressions can involve one or more constants (both numeric and string) and table columns; however, expressions cannot be applied to store-only columns. The expressions follow certain constraints based on where they are used, but all the expressions should follow the basic guidelines outlined below:
Important
Use parentheses liberally to ensure correct order-of-operations.
Constants¶
| Types | Details |
|---|---|
| String | String constants must be enclosed in single quotes or double quotes, e.g.,
|
| Numerical | Numerical constants can be expressed as:
|
Operators¶
Important
When these operators are applied to numeric columns, they will
interpret non-zero values as true and zero values as false,
returning 1 for true and 0 for false.
| Types | Details | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Bitwise | & | << >> ~ ^ |
||||||||||||
| Comparison |
|
||||||||||||
| Logical |
|
||||||||||||
| Mathematical | + - * / |
Functions¶
Conditional Functions¶
| Function | Description |
|---|---|
CASE(expr, {match_a, ..., match_N}, {value_a, ..., value_N}, value_if_no_match) |
Evaluates expr: returns the first value whose corresponding match is equal to
expr; returns value_if_no_match if expr is not equal to any of match_a through
match_N |
IF(expr, value_if_true, value_if_false)) |
Evaluates
|
Conversion Functions¶
For the CAST() and CONVERT() functions, valid destination types are:
- int
- int16
- int8
- long
- timestamp
- float
- double
- string
- char256
- char128
- char64
- char32
- char16
- char8
- char4
- char2
- char1
- decimal
- date
- time
- datetime
- geometry
- bool
| Function | Description |
|---|---|
CAST(original value, destination type) |
Returns the equivalent of original value converted to the destination type.
Useful for converting strings to numbers and numbers to strings |
CHAR(int) |
Returns the character associated with the ASCII code in int |
CHAR1(charN) |
Converts the given charN to char1 type |
CHAR2(charN) |
Converts the given charN to char2 type |
CHAR4(charN) |
Converts the given charN to char4 type |
CHAR8(charN) |
Converts the given charN to char8 type |
CHAR16(charN) |
Converts the given charN to char16 type |
CHAR32(charN) |
Converts the given charN to char32 type |
CHAR64(charN) |
Converts the given charN to char64 type |
CHAR128(charN) |
Converts the given charN to char128 type |
CHAR256(charN) |
Converts the given charN to char256 type |
CONVERT(original value, destination type, style) |
Returns the equivalent of original value converted to the destination type. The
style parameter is currently only applicable when the destination type is
string and the original value is of timestamp or datetime type. The
supported style values are 0 (mon dd yyyy hh:mi AM or PM) or 21 /
121 (yyy-mm-dd hh:mi:ss.mmm) |
Date, Time, and Timestamp Functions¶
This section comprises the following functions:
- Date/Time Functions, which can convert to or provide a
datetype ortimetype response - Timestamp/Date Conversion Functions, which can convert
timestamptype and/ordatetype - Timestamp-only Functions, which can convert or manipulate
timestamptype values
Date/Time Functions¶
Note
Integer fields are assumed to be seconds since the epoch; long/timestamp fields are assumed to be milliseconds since the epoch.
| Function | Description |
|---|---|
CURRENT_DATE() |
Returns the date as YYYY-MM-DD |
CURRENT_DATETIME() |
Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm |
CURRENT_TIME() |
Returns the time as HH24:MI:SS.mmm |
CURRENT_TIMESTAMP() |
Returns the date & time as the number of milliseconds since the epoch |
DATE(expr) |
Returns date in the format YYYY-MM-DD |
DATETIME(expr) |
Returns expr (as a string) as a datetime (YYYY-MM-DD HH:MI:SS.mmm) |
DAY(expr) |
Returns day of month [ 1 - 31 ] |
DAYNAME(expr) |
Returns day name [ Sunday - Saturday ] |
DAYOFMONTH(expr) |
Returns day of month [ 1 - 31 ] |
DAYOFWEEK(expr) |
Returns day of week [ 1 - 7 ], with 1 being Sunday |
DAY_OF_WEEK(expr) |
Alias for DAY_OF_WEEK(expr) |
DAYOFYEAR(expr) |
Returns day of year [ 1 - 366 ] |
DAY_OF_YEAR(expr) |
Alias for DAYOFYEAR(timestamp) |
HOUR(expr) |
Returns hour of day [ 0 - 23 ] |
LAST_DAY(date) |
Returns the last day of the month provided in date. The given date can be of date or datetime type. |
MINUTE(expr) |
Returns minute of hour [ 0 - 59 ] |
MONTH(expr) |
Returns number of month [ 1 - 12 ] |
MONTHNAME(expr) |
Extracts the month of the year from expr and converts it to the corresponding month name [ January - December] |
MSEC(expr) |
Returns millisecond of second [ 0 - 999 ] |
NEXT_DAY(date, expr) |
Returns the next day (as provided by expr) after the given date, e.g., NEXT_DAY('2000-10-10', 'Friday') would return
2000-10-13 because 2000-10-10 is a Wednesday. The given date can be of date or datetime type. |
NOW() |
Alias for CURRENT_DATETIME() |
QUARTER(expr) |
Returns quarter of year [ 1 - 4 ]; (1 = Jan, Feb, & Mar) |
SEC(expr) |
Alias for SECOND(expr) |
SECOND(expr) |
Returns second of minute [ 0 - 59 ] |
TIME(expr) |
Returns time (HH:MI:SS.mmm) of full timestamp |
WEEK(timestamp) |
Returns week (or partial week) of year [ 1 - 53 ]; each full week starts on Sunday (1 = week containing Jan 1st) |
YEAR(timestamp) |
Returns 4-digit year (A.D.) |
Timestamp/Date Conversion Functions¶
| Function | Description |
|---|---|
DATE_TO_EPOCH_SECS(year, month, day, hours, minutes, seconds) |
Converts the full date to seconds since the epoch. Negative values are
accepted (e.g., DATE_TO_EPOCH_SECS(2017,06,-15,09,22,15) would return
1494926535, which resolves to Tuesday, May 16, 2017 9:22:15 AM) |
DATE_TO_EPOCH_MSECS(year, month, day, hours, minutes, seconds, milliseconds) |
Converts the full date to milliseconds since the epoch. Negative values are accepted |
WEEK_TO_EPOCH_SECS(year, week_number) |
Converts the year and week number to seconds since the epoch. Negative
values are accepted (e.g., WEEK_TO_EPOCH_SECS(2017,-32) would return
1463270400, which resolves to Sunday, May 15, 2016 12:00:00 AM).
Each new week begins Sunday at midnight |
WEEK_TO_EPOCH_MSECS(year, week_number) |
Converts the year and week number to seconds since the epoch. Negative values are accepted |
MSECS_SINCE_EPOCH(timestamp) |
Converts the timestamp to millseconds since the epoch |
TIMESTAMP_FROM_DATE_TIME(date, time) |
converts the date and time (as strings) to timestamp format, e.g.,
TIMESTAMP_FROM_DATE_TIME('2017-06-15', '10:37:30') would return
1497523050000, which resolves to
Thursday, June 15, 2017 10:37:30 AM. |
Timestamp-only Functions¶
Note
The operated-on field must have timestamp annotation
| Function | Description |
|---|---|
STRING(timestamp) |
Converts timestamp to a string in YYYY-MM-DD hh:mm:ss.mmm format |
TIMEBOUNDARYDIFF(unit_type, begin_timestamp, end_timestamp) |
Similarly to TIMESTAMPDIFF, this function calculates the difference between two dates
except TIMEBOUNDARYDIFF is symmetric with TIMESTAMPADD. This means the
TIMEBOUNDARYDIFF in MONTH units between Mar 31st and Apr 30th is 1. |
TIMESTAMPADD(interval_type, interval_amount, timestamp) |
Adds the positive or negative
|
TIMESTAMPDIFF(unit_type, begin_timestamp, end_timestamp) |
Calculates the difference between two dates, returning the result in the units specified;
more precisely, how many
|
Encoding Functions¶
| Function | Description |
|---|---|
HASH(expr[, seed]) |
Returns an 8-byte hash (long type) of the given value expr. An optional
seed can be provided. |
SHA256(expr) |
Returns the hex digits of the SHA-256 hash of the given value expr as a char64 string. |
Geospatial Functions¶
Tip
- Use
ST_ISVALIDto determine if a geometry object is valid. The functions below work best with valid geometry objects. - Use the
REMOVE_NULLABLEfunction to remove anynullablecolumn types that could result from calculating a derived column (e.g., as in Projections) using one of the functions below.
Enhanced Performance Functions¶
The functions below all compare x and y coordinates to geometry objects
(or vice versa), thus increasing their performance in queries. Each of these
functions have a geometry-to-geometry version listed in the next section.
| Function | Description |
|---|---|
STXY_CONTAINS(geom, x, y) |
Returns 1 (true) if geom contains the x and y coordinate, e.g. lies in the interior
of geom. The coordinate cannot be on the boundary and also be contained because geom does not
contain its boundary |
STXY_CONTAINSPROPERLY(geom, x, y) |
Returns 1 (true) if the x and y coordinate intersects the interior of geom but not
the boundary (or exterior) because geom does not contain its boundary but does contain itself |
STXY_COVEREDBY(x, y, geom) |
Returns 1 (true) if the x and y coordinate is covered by geom |
STXY_COVERS(geom, x, y) |
Returns 1 (true) if geom covers the x and y coordinate |
STXY_DISJOINT(x, y, geom) |
Returns 1 (true) if the given x and y coordinate and the geometry geom do not
spatially intersect. |
STXY_DISTANCE(x, y, geom[, solution]) |
Calculates the minimum distance between the given
Note: If the |
STXY_DWITHIN(x, y, geom, distance[, solution]) |
Returns
|
STXY_ENVDWITHIN(x, y, geom, distance[, solution]) |
Returns
|
STXY_ENVINTERSECTS(x, y, geom) |
Returns 1 (true) if the bounding box of the given geometry geom intersects the x and
y coordinate. |
STXY_INTERSECTION(x, y, geom) |
Returns the shared portion between the x and y coordinate and the given geometry geom,
i.e. the point itself. |
STXY_INTERSECTS(x, y, geom) |
Returns 1 (true) if the x and y coordinate and geom intersect in 2-D. |
STXY_TOUCHES(x, y, geom) |
Returns 1 (true) if the x and y coordinate and geometry geom have at least one point
in common but their interiors do not intersect. If geom is a GEOMETRYCOLLECTION, a 0 is
returned regardless if the point and geometry touch |
STXY_WITHIN(x, y, geom) |
Returns 1 (true) if the x and y coordinate is completely inside the geom geometry
i.e., not on the boundary |
Geometry Functions¶
| Function | Description |
|---|---|
DIST(x1, y1, x2, y2) |
Computes the Euclidean distance (in degrees), i.e. SQRT( (x1-x2)*(x1-x2) + (y1-y2)*(y1-y2) ). |
GEODIST(lon1, lat1, lon2, lat2) |
Computes the geographic great-circle distance (in meters) between two lat/lon points. |
GEOHASH_DECODE_LATITUDE(geohash) |
Decodes a given geohash and returns the latitude value for the given hash string. Supports a
maximum geohash character length of 16. |
GEOHASH_DECODE_LONGITUDE(geohash) |
Decodes a given geohash and returns the longitude value for the given hash string. Supports a
maximum geohash character length of 16. |
GEOHASH_ENCODE(lat, lon, precision) |
Encodes a given coordinate pair and returns a hash string with a given precision. |
ST_ADDPOINT(linestring, point, position) |
Adds a the given point geometry to the given linestring geometry at the specified
position, which is a 0-based index. |
ST_ALMOSTEQUALS(geom1, geom2, decimal) |
Returns 1 (true) if given geometries, geom1 and geom2, are almost spatially equal within
the given amount of decimal scale. Note that geometries will still be considered equal if the
decimal scale for the geometries is within a half order of magnitude of each other, e.g, if
decimal is set to 2, then POINT(63.4 123.45) and POINT(63.4 123.454) are equal, but
POINT(63.4 123.45) and POINT(63.4 123.459) are not equal. The geometry types must match to be
considered equal. |
ST_AREA(geom[, solution]) |
Returns the area of the given geometry
|
ST_AZIMUTH(geom1, geom2) |
Returns the azimuth in radians defined by the segment between two POINTs, geom1 and geom2.
Returns a null if the input geometry type is MULTIPOINT, (MULTI)LINESTRING, or (MULTI)POLYGON. |
ST_BOUNDARY(geom) |
Returns the closure of the combinatorial boundary of a given geometry geom. Returns an empty
geometry if geom is an empty geometry. Returns a null if geom is a GEOMETRYCOLLECTION |
ST_BOUNDINGDIAGONAL(geom) |
Returns the diagonal of the given geometry's (geom) bounding box. |
ST_BUFFER(geom, radius[, style[, solution]]) |
Returns a geometry that represents all points whose distance from the given geometry Available
Available
Tip To create a 5-meter buffer around |
ST_CENTROID(geom) |
Calculates the center of the given geometry geom as a POINT. For (MULTI)POINTs, the center is
calculated as the average of the input coordinates. For (MULTI)LINESTRINGs, the center is calculated
as the weighted length of each given LINESTRING. For (MULTI)POLYGONs, the center is calculated as
the weighted area of each given POLYGON. If geom is an empty geometry, an empty
GEOMETRYCOLLECTION is returned |
ST_CLIP(geom1, geom2) |
Returns the geometry shared between given geometries geom1 and geom2 |
ST_CLOSESTPOINT(geom1, geom2[, solution]) |
Calculates the 2-D
|
ST_COLLECT(geom1, geom2) |
Returns a MULTI* or GEOMETRYCOLLECTION comprising geom1 and geom2. If geom1 and geom2
are the same, singular geometry type, a MULTI* is returned, e.g., if geom1 and geom2 are both
POINTs (empty or no), a MULTIPOINT is returned. If geom1 and geom2 are neither the same type
nor singular geometries, a GEOMETRYCOLLECTION is returned. |
ST_COLLECTIONEXTRACT(collection, type) |
Returns only the specified
|
ST_COLLECTIONHOMOGENIZE(collection) |
Returns the simplest form of the given collection, e.g., a collection with a single POINT will
be returned as POINT(x y), and a collection with multiple individual points will be returned as a
MULTIPOINT. |
ST_CONCAVEHULL(geom, target_percent[, allow_holes]) |
Returns a potentially concave geometry that encloses all geometries found in the given geom set.
Use target_percent (values between 0 and 1) to determine the percent of area of a convex hull the
concave hull will attempt to fill; 1 will return the same geometry as an ST_CONVEXHULL
operation. Set allow_holes to 1 (true) to allow holes in the resulting geometry; default
value is 0 (false). Note that allow_holes is independent of the area of target_percent. |
ST_CONTAINS(geom1, geom2) |
Returns 1 (true) if no points of geom2 lie in the exterior of geom1 and at least one
point of geom2 lies in the interior of geom1. Note that geom1 does not contain its
boundary but does contain itself. |
ST_CONTAINSPROPERLY(geom1, geom2) |
Returns 1 (true) if geom2 intersects the interior of geom1 but not the boundary
(or exterior). Note that geom1 does not contain its boundary but does contain itself. |
ST_CONVEXHULL(geom) |
Returns the minimum convex geometry that encloses all geometries in the given geom set. |
ST_COORDDIM(geom) |
Returns the coordinate dimension of the given geom, e.g., a geometry with x, y, and z
coordinates would return 3. |
ST_COVEREDBY(geom1, geom2) |
Returns 1 (true) if no point in geom1 is outside geom2. |
ST_COVERS(geom1, geom2) |
Returns 1 (true) if no point in geom2 is outside geom1. |
ST_CROSSES(geom1, geom2) |
Returns 1 (true) if the given geometries, geom1 and geom2, spatially cross, meaning some
but not all interior points in common. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a
0 is returned regardless if the two geometries cross |
ST_DIFFERENCE(geom1, geom2) |
Returns a geometry that represents the part of geom1 that does not intersect with geom2. |
ST_DIMENSION(geom) |
Returns the dimension of the given geometry geom, which is less than or equal to the coordinate
dimension. If geom is a single geometry, a 0 is for POINT, a 1 is for LINESTRING,
and a 2 is for POLYGON. If geom is a collection, it will return the largest dimension from
the collection. If geom is empty, 0 is returned. |
ST_DISJOINT(geom1, geom2) |
Returns 1 (true) if the given geometries, geom1 and geom2, do not spatially intersect. |
ST_DISTANCE(geom1, geom2[, solution]) |
Calculates the minimum distance between the given geometries,
Note: If |
ST_DISTANCEPOINTS(x1, y1, x2, y2[, solution]) |
Calculates the minimum distance between the given points,
|
ST_DFULLYWITHIN(geom1, geom2, distance[, solution]) |
Returns
|
ST_DWITHIN(geom1, geom2, distance[, solution]) |
Returns
|
ST_ELLIPSE(centerx, centery, height, width) |
Returns an ellipse using the following values:
|
ST_ENDPOINT(geom) |
Returns the last point of the given geom as a POINT if it's a LINESTRING. If geom is not a
a LINESTRING, null is returned. |
ST_ENVDWITHIN(geom1, geom2, distance[, solution]) |
Returns
|
ST_ENVELOPE(geom) |
Returns the bounding box of a given geometry geom. |
ST_ENVINTERSECTS(geom1, geom2) |
Returns 1 (true) if the bounding box of the given geometries, geom1 and geom2, intersect. |
ST_EQUALS(geom1, geom2) |
Returns 1 (true) if the given geometries, geom1 and geom2, are spatially equal. Note that
order does not matter. |
ST_EQUALSEXACT(geom1, geom2, tolerance) |
Returns 1 (true) if the given geometries, geom1 and geom2, are almost spatially equal
within some given tolerance. If the values within the given geometries are within the
tolerance value of each other, they're considered equal, e.g., if tolerance is 2,
POINT(1 1) and POINT(1 3) are considered equal, but POINT(1 1) and POINT(1 3.1) are not. Note that
the geometry types have to match for them to be considered equal. |
ST_ERASE(geom1, geom2) |
Returns the result of erasing a portion of geom1 equal to the size of geom2. |
ST_EXPAND(geom, units) |
Returns the bounding box expanded in all directions by the given units of the given geom. The
expansion can also be defined for separate directions by providing separate parameters for each
direction, e.g., ST_EXPAND(geom, unitsx, unitsy, unitsz, unitsm). |
ST_EXPANDBYRATE(geom, rate) |
Returns the bounding box expanded by a given rate (a ratio of width and height) for the given
geometry geom. The rate must be between 0 and 1. |
ST_EXTERIORRING(geom) |
Returns a LINESTRING representing the exterior ring of the given POLYGON geom |
ST_GENERATEPOINTS(geom, num) |
Creates a MULTIPOINT containing a number num of randomly generated points within the boundary of
geom. |
ST_GEOHASH(geom, precision) |
Returns a hash string representation of the given geometry Note The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry |
ST_GEOMETRYN(geom, index) |
Returns the index geometry back from the given geom geometry. The index starts from 1 to
the number of geometry in geom. |
ST_GEOMETRYTYPE(geom) |
Returns the type of geometry from the given geom. |
ST_GEOMETRYTYPEID(geom) |
Returns the type ID of from
|
ST_GEOMFROMGEOHASH(geohash, precision) |
Returns a POLYGON boundary box using the given geohash with a precision set by the integer
precision. If precision is specified, the function will use as many characters in the hash
equal to precision to create the geometry. If no precision is specified, the full length of
the geohash is used. |
ST_GEOMFROMTEXT(wkt) |
Returns a geometry from the given Well-Known text representation wkt. Note that this function is
only compatible with constants |
ST_INTERIORRINGN(geom, n) |
Returns the n-th interior LINESTRING ring of the POLYGON geom. If geom is not a POLYGON
or the given n is out of range, a null is returned. The index begins at 1 |
ST_INTERSECTION(geom1, geom2) |
Returns the shared portion between given geometries geom1 and geom2 |
ST_INTERSECTS(geom1, geom2) |
Returns 1 (true) if the given geometries, geom1 and geom2, intersect in 2-D |
ST_ISCLOSED(geom) |
Returns 1 (true) if the given geometry's (geom) start and end points coincide |
ST_ISCOLLECTION(geom) |
Returns 1 (true) if geom is a collection, e.g., GEOMETRYCOLLECTION, MULTIPOINT,
MULTILINESTRING, etc. |
ST_ISEMPTY(geom) |
Returns 1 (true) if geom is empty |
ST_ISRING(geom) |
Returns 1 (true) if LINESTRING geom is both closed (per ST_ISCLOSED) and "simple"
(per ST_ISSIMPLE). Returns 0 if geom is not a LINESTRING |
ST_ISSIMPLE(geom) |
Returns 1 (true) if geom has no anomalous geometric points, e.g., self-intersection or
self-tangency |
ST_ISVALID(geom) |
Returns 1 (true) if geom (typically a [MULTI]POLYGON) is well formed. A POLYGON is valid if
its rings do not cross and its boundary intersects only at POINTs (not along a line). The POLYGON must
also not have dangling LINESTRINGs. A MULTIPOLYGON is valid if all of its elements are also valid and
the interior rings of those elements do not intersect. Each element's boundaries may touch but only
at POINTs (not along a line) |
ST_LENGTH(geom[, solution]) |
Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns
|
ST_LINEFROMMULTIPOINT(geom) |
Creates a LINESTRING from geom if it is a MULTIPOINT. Returns null if geom is not a
MULTIPOINT |
ST_LINEINTERPOLATEPOINT(geom, fraction) |
Returns a POINT that represents the specified fraction of the LINESTRING geom. If geom is
either empty or not a LINESTRING, null is returned |
ST_LINEMERGE(geom) |
Returns a LINESTRING or MULTILINESTRING from a given geom. If geom is a MULTILINESTRING
comprising LINESTRINGs with shared endpoints, a contiguous LINESTRING is returned. If geom is a
LINESTRING or a MULTILINESTRING comprising LINESTRINGS without shared endpoints, geom is returned
If geom is an empty (MULTI)LINESTRING or a (MULTI)POINT or (MULTI)POLYGON, an empty
GEOMETRYCOLLECTION is returned. |
ST_LINESUBSTRING(geom, start_fraction, end_fraction) |
Returns the fraction of a given geom LINESTRING where start_fraction and end_fraction are
between 0 and 1. For example, given LINESTRING(1 1, 2 2, 3 3) a start_fraction of
0 and an end_fraction of 0.25 would yield the first quarter of the given LINESTRING, or
LINESTRING(1 1, 1.5 1.5). Returns null if start_fraction is greater than
end_fraction. Returns null if input geometry is (MULTI)POINT, MULTILINESTRING, or
(MULTI)POLYGON. Returns null if start_fraction and/or end_fraction are less than 0 or
more than 1. |
ST_LONGESTLINE(geom1, geom2[, solution]) |
Returns the LINESTRING that represents the longest line of points between the two geometries. If
multiple longest lines are found, only the first line found is returned. If
|
ST_MAKEENVELOPE(xmin, ymin, xmax, ymax) |
Creates a rectangular POLYGON from the given min and max parameters |
ST_MAKELINE(geom[, geom2]) |
Creates a LINESTRING from Note This function can be rather costly in terms of performance |
ST_MAKEPOINT(x, y) |
Creates a POINT at the given coordinate Note This function can be rather costly in terms of performance |
ST_MAKEPOLYGON(geom) |
Creates a POLYGON from Note This function can be rather costly in terms of performance |
ST_MAKETRIANGLE2D(x1, y1, x2, y2, x3, y3) |
Creates a closed 2-D POLYGON with three vertices |
ST_MAKETRIANGLE3D(x1, y1, z1, x2, y2, z2,
x3, y3, z3) |
Creates a closed 3-D POLYGON with three vertices |
ST_MAXDISTANCE(geom1, geom2[, solution]) |
Returns the maximum distance between the given
|
ST_MAXX(geom) |
Returns the maximum x coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MAXY(geom) |
Returns the maximum y coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MAXZ(geom) |
Returns the maximum z coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MINX(geom) |
Returns the minimum x coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MINY(geom) |
Returns the minimum y coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MINZ(geom) |
Returns the minimum z coordinate of a bounding box for the given geom geometry. This function
works for 2-D and 3-D geometries. |
ST_MULTI(geom) |
Returns geom as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT. |
ST_MULTIPLERINGBUFFERS(geom, distance, outside) |
Creates multiple buffers at specified
|
ST_NEAR(geom1, geom2) |
Returns the portion of geom2 that is closest to geom1. If geom2 is a singular geometry
object (e.g., POINT, LINESTRING, POLYGON), geom2 will be returned. If geom2 a multi-geometry,
e.g., MULTIPOINT, MULTILINESTRING, etc., the nearest singular geometry in geom2 will be
returned. |
ST_NORMALIZE(geom) |
Returns geom in its normalized (canonical) form, which may rearrange the points in lexicographical
order. |
ST_NPOINTS(geom) |
Returns the number of points (vertices) in geom. |
ST_NUMGEOMETRIES(geom) |
If geom is a collection or MULTI- geometry, returns the number of geometries. If geom is a
single geometry, returns 1. |
ST_NUMINTERIORRINGS(geom) |
Returns the number of interior rings if geom is a POLYGON. Returns null if geom is
anything else. |
ST_NUMPOINTS(geom) |
Returns the number of points in the geom LINESTRING. Returns null if geom is not a
LINESTRING. |
ST_OVERLAPS(geom1, geom2) |
Returns 1 (true) if given geometries geom1 and geom2 share space. If geom1 and/or
geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless if the two geometries overlap |
ST_PARTITION(geom, threshold) |
Returns a MULTIPOLYGON representing the given geom partitioned into a number of POLYGONs with a
maximum number of vertices equal to the given threshold. Minimum value for threshold is
10; default value is 10000. If geom is not a POLYGON or MULTIPOLYGON, geom is
returned. If the number of vertices in geom is less than the threshold, geom is returned. |
ST_POINT(x, y) |
Returns a POINT with the given x and y coordinates. |
ST_POINTFROMGEOHASH(geohash, precision) |
Returns a POINT using the given Note The POINT returned represents the center of the bounding box of the geohash |
ST_POINTN(geom, n) |
Returns the n-th point in LINESTRING geom. Negative values are valid, but note that they are
counted backwards from the end of geom. A null is returned if geom is not a LINESTRING. |
ST_POINTS(geom) |
Returns a MULTIPOINT containing all of the coordinates of geom. |
ST_REMOVEPOINT(geom, offset) |
Remove a point from LINESTRING geom using offset to skip over POINTs in the LINESTRING. The
offset is 0-based. |
ST_REMOVEREPEATEDPOINTS(geom, tolerance) |
Removes points from geom if the point's vertices are greater than or equal to the tolerance
of the previous point in the geometry's list. If geom is not a MULTIPOINT, MULTILINESTRING, or a
MULTIPOLYGON, no points will be removed. |
ST_REVERSE(geom) |
Return the geometry with its coordinate order reversed. |
ST_SCALE(geom, x, y) |
Scales geom by multiplying its respective vertices by the given x and y values.
This function also supports scaling geom using another geometry object, e.g.,
ST_SCALE('POINT(3 4)', 'POINT(5 6)') would return POINT(15 24). If specifying x and y
for scale, note that the default value is 0, e.g., ST_SCALE('POINT(1 3)', 4)
would return POINT(4 0). |
ST_SEGMENTIZE(geom, max_segment_length[, solution]) |
Returns the given
|
ST_SETPOINT(geom1, position, geom2) |
Replace a point of LINESTRING geom1 with POINT geom2 at position (base 0). Negative
values are valid, but note that they are counted backwards from the end of geom. |
ST_SHAREDPATH(geom1, geom2) |
Returns a collection containing paths shared by geom1 and geom2. |
ST_SHORTESTLINE(geom1, geom2) |
Returns the 2-D LINESTRING that represents the shortest line of points between the two geometries. If
multiple shortest lines are found, only the first line found is returned. If geom1 or geom2
is empty, null is returned |
ST_SNAP(geom1, geom2, tolerance) |
Snaps geom1 to geom2 within the given tolerance. If the tolerance causes geom1
to not snap, the geometries will be returned unchanged. |
ST_SPLIT(geom1, geom2) |
Returns a collection of geometries resulting from the split between geom1 and geom2
geometries. |
ST_STARTPOINT(geom) |
Returns the first point of LINESTRING geom as a POINT. Returns null if geom is not a
LINESTRING. |
ST_SYMDIFFERENCE(geom1, geom2) |
Returns a geometry that represents the portions of geom1 and geom2 geometries that do not
intersect. |
ST_TOUCHES(geom1, geom2) |
Returns 1 (true) if the given geometries, geom1 and geom2, have at least one point in
common but their interiors do not intersect. If geom1 and/or geom2 are a GEOMETRYCOLLECTION,
a 0 is returned regardless if the two geometries touch |
ST_TRANSLATE(geom, deltax, deltay[, deltaz]) |
Translate geom by given offsets deltax and deltay. A z-coordinate offset can be applied
using deltaz. |
ST_UNION(geom1, geom2) |
Returns a geometry that represents the point set union of the two given geometries, geom1 and
geom2. |
ST_UNIONCOLLECTION(geom) |
Returns a geometry that represents the point set union of a single given geometry geom. |
ST_UPDATE(geom1, geom2) |
Returns a geometry that is geom1 geometry updated by geom2 geometry |
ST_VORONOIPOLYGONS(geom, tolerance) |
Returns a GEOMETRYCOLLECTION containing Voronoi polygons (regions consisting of points closer to
a vertex in geom than any other vertices in geom) calculated from the vertices in geom
and the given tolerance. The tolerance determines the distance at which points will be
considered the same. An empty GEOMETRYCOLLECTION is returned if geom is an empty geometry, a
single POINT, or a LINESTRING or POLYGON composed of equivalent vertices (e.g.,
POLYGON((0 0, 0 0, 0 0, 0 0)), LINESTRING(0 0, 0 0)). |
ST_WITHIN(geom1, geom2) |
Returns 1 (true) if the geom1 geometry is inside the geom2 geometry. Note that as long as
at least one point is inside of geom2, geom1 is considered within geom2 even if the rest
of the geom1 lies along the boundary of geom2 |
ST_WKTTOWKB(geom) |
Returns the binary form (WKB) of a Note This function can only be used in queries against a single table. |
ST_X(geom) |
Returns the X coordinate of the POINT geom; if the coordinate is not available, null is
returned. geom must be a POINT. |
ST_Y(geom) |
Returns the Y coordinate of the POINT geom; if the coordinate is not available, null is
returned. geom must be a POINT. |
Geospatial Aggregation Functions¶
| Function | Description |
|---|---|
ST_AGGREGATE_COLLECT(geom) |
Alias for ST_COLLECT_AGGREGATE() |
ST_COLLECT_AGGREGATE(geom) |
Returns a GEOMETRYCOLLECTION comprising all geometries found in geom. Any MULTI* geometries will be divided
into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and
POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in geom. Any empty
geometries in geom are ignored even if they are part of a GEOMETRYCOLLECTION. |
ST_DISSOLVE(geom) |
Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Line geometries (LINESTRING, LINEARRING, and MULTILINESTRING) are ignored when calculating the resulting geometry. |
ST_LINESTRINGFROMORDEREDPOINTS(x, y, t) |
Returns a LINESTRING that represents a "track" of the given points (x, y) ordered by the given sort
column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are
null, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point"
in the source table, a POINT is returned. If there are no non-null "points" in the source table, a null is
returned |
Math Functions¶
| Function | Description |
|---|---|
ABS(expr) |
Calculates the absolute value of expr |
ACOS(expr) |
Returns the inverse cosine (arccosine) of expr as a double |
ACOSF(expr) |
Returns the inverse cosine (arccosine) of expr as a float |
ACOSH(expr) |
Returns the inverse hyperbolic cosine of expr as a double |
ACOSHF(expr) |
Returns the inverse hyperbolic cosine of expr as a float |
ASIN(expr) |
Returns the inverse sine (arcsine) of expr as a double |
ASINF(expr) |
Returns the inverse sine (arcsine) of expr as a float |
ASINH(expr) |
Returns the inverse hyperbolic sine of expr as a double |
ASINHF(expr) |
Returns the inverse hyperbolic sine of expr as a float |
ATAN(expr) |
Returns the inverse tangent (arctangent) of expr as a
double |
ATANF(expr) |
Returns the inverse tangent (arctangent) of expr as a
float |
ATANH(expr) |
Returns the inverse hyperbolic tangent of expr as a double |
ATANHF(expr) |
Returns the inverse hyperbolic tangent of expr as a float |
ATAN2(x, y) |
Returns the inverse tangent (arctangent) using two arguments as a double |
ATAN2F(x, y) |
Returns the inverse tangent (arctangent) using two arguments as a float |
ATN2(x, y) |
Alias for ATAN2 |
ATN2F(x, y) |
Alias for ATAN2F |
CBRT(expr) |
Returns the cube root of expr as a double |
CBRTF(expr) |
Returns the cube root of expr as a float |
CEIL(expr) |
Alias for CEILING |
CEILING(expr) |
Rounds expr up to the next highest integer |
COS(expr) |
Returns the cosine of expr as a double |
COSF(expr) |
Returns the cosine of expr as a float |
COSH(expr) |
Returns the hyperbolic cosine of expr as a double |
COSHF(expr) |
Returns the hyperbolic cosine of expr as a float |
COT(expr) |
Returns the cotangent of expr as a double |
COTF(expr) |
Returns the cotangent of expr as a float |
DEGREES(expr) |
Returns the conversion of expr (in radians) to degrees as a
double |
DEGREESF(expr) |
Returns the conversion of expr (in radians) to degrees as a
float |
DIVZ(a, b, c) |
Returns the quotient a / b unless b == 0, in which case
it returns c |
EXP(expr) |
Returns e to the power of expr as a double |
EXPF(expr) |
Returns e to the power of expr as a float |
FLOOR(expr) |
Rounds expr down to the next lowest integer |
GREATER(expr_a, expr_b) |
Returns whichever of expr_a and expr_b has the larger
value, based on typed comparison |
HYPOT(x, y) |
Returns the hypotenuse of x and y as a double |
HYPOTF(x, y) |
Returns the hypotenuse of x and y as a float |
ISNAN(expr) |
Returns 1 (true) if expr is not a number by IEEE
standard; otherwise, returns 0 (false) |
IS_NAN(expr) |
Alias for ISNAN |
ISINFINITY(expr) |
Returns 1 (true) if expr is infinity by IEEE standard;
otherwise, returns 0 (false) |
IS_INFINITY(expr) |
Alias for ISINFINITY |
LDEXP(x, exp) |
Returns the value of x * 2exp as a double |
LDEXPF(x, exp) |
Returns the value of x * 2exp as a float |
LESSER(expr_a, expr_b) |
Returns whichever of expr_a and expr_b has the smaller
value, based on typed comparison |
LN(expr) |
Returns the natural logarithm of expr as a double |
LNF(expr) |
Returns the natural logarithm of expr as a float |
LOG(expr) |
Alias for LN |
LOGF(expr) |
Alias for LNF |
LOG10(expr) |
Returns the base-10 logarithm of expr as a double |
LOG10F(expr) |
Returns the base-10 logarithm of expr as a float |
MAX_CONSECUTIVE_BITS(expr) |
Calculates the length of the longest series of consecutive 1
bits in the integer expr |
MOD(dividend, divisor) |
Calculates the remainder after integer division of dividend
by divisor |
PI() |
Returns the value of pi |
POW(base, exponent) |
Alias for POWER |
POWF(base, exponent) |
Alias for POWERF |
POWER(base, exponent) |
Returns base raised to the power of exponent as a
double |
POWERF(base, exponent) |
Returns base raised to the power of exponent as a
float |
RADIANS(expr) |
Returns the conversion of expr (in degrees) to radians as a
double |
RADIANSF(expr) |
Returns the conversion of expr (in degrees) to radians as a
float |
RAND() |
Returns a random floating-point value. |
ROUND(expr, scale) |
Rounds
|
SIGN(expr) |
Determines whether a number is positive, negative, or zero; returns one of the following three values:
|
SIN(expr) |
Returns the sine of expr as a double |
SINF(expr) |
Returns the sine of expr as a float |
SINH(expr) |
Returns the hyperbolic sine of expr as a double |
SINHF(expr) |
Returns the hyperbolic sine of expr as a float |
SQRT(expr) |
Returns the square root of expr as a double |
SQRTF(expr) |
Returns the square root of expr as a float |
TAN(expr) |
Returns the tangent of expr as a double |
TANF(expr) |
Returns the tangent of expr as a float |
TANH(expr) |
Returns the hyperbolic tangent of expr as a double |
TANHF(expr) |
Returns the hyperbolic tangent of expr as a float |
TRUNCATE(expr, scale) |
Rounds
|
Null Functions¶
Important
Be mindful that no error is thrown when Kinetica tries to convert different data type in the Null functions below, so if the output is unexpected, it may be that the types used aren't of the same type.
| Function | Description |
|---|---|
IS_NULL(expr) |
Returns 1 (true) if expr is null; otherwise, returns 0
(false) |
NULLIF(expr_a, expr_b) |
Returns null if expr_a equals expr_b; otherwise, returns the
value of expr_a. Both expressions should be of the same convertible
data type |
NVL(expr_a, expr_b) |
Returns expr_a if it is not null; otherwise, returns expr_b.
Both expressions should be of the same convertible data type |
NVL2(expr, value_if_not_null, value_if_null) |
Evaluates expr: if expr does not return a null,
value_if_not_null is returned. If expr does return a null,
value_if_null is returned. Both value_if_not_null and
value_if_null should be of the same data type as expr or
implicitly convertible |
REMOVE_NULLABLE(expr) |
Alias for ZEROIFNULL |
ZEROIFNULL(expr) |
Replaces null values with appropriate values based on the column type (e.g., 0 if
numeric column, an empty string if charN column, etc.). Also removes the nullable
column property if used to calculate a derived column. |
String Functions¶
Important
These functions will only work with
fixed-width string fields (char1 - char256).
| Function | Description |
|---|---|
ASCII(expr) |
Returns the ASCII code for the first character in expr |
CHAR(expr) |
The character represented by the standard ASCII code expr in the
range [ 0 - 127 ] |
CONCAT(expr_a, expr_b) |
Performs a string concatenation of Note The resulting field size of any |
CONCAT_TRUNCATE(expr_a, expr_b) |
Returns the concatenation of
|
CONTAINS(match_expr, ref_expr) |
Returns 1 if ref_expr contains match_expr by
string-literal comparison; otherwise, returns 0 |
DIFFERENCE(expr_a, expr_b) |
Returns a value between 0 and 4 that represents the difference
between the sounds of expr_a and expr_b based on the
SOUNDEX() value of the strings--a value of 4 is the best
possible sound match |
EDIT_DISTANCE(expr_a, expr_b) |
Returns the Levenshtein edit distance between expr_a and
expr_b; the lower the the value, the more similar the two strings
are |
ENDS_WITH(match_expr, ref_expr) |
Returns 1 if ref_expr ends with match_expr by
string-literal comparison; otherwise, returns 0 |
INITCAP(expr) |
Returns expr with the first letter of each word in uppercase |
IS_IPV4(expr) |
Returns 1 if expr is an IPV4 address; returns 0 otherwise |
LCASE(expr) |
Converts expr to lowercase |
LEFT(expr, num_chars) |
Returns the leftmost num_chars characters from expr |
LENGTH(expr) |
Returns the number of characters in expr |
LIKE(ref_expr, match_expr) |
Returns whether
|
LOCATE(match_expr, ref_expr, [start_pos]) |
Returns the starting position of the first match of match_expr in
ref_expr, starting from position 1 or start_pos (if specified) |
LOWER(expr) |
Alias for LCASE |
LPAD(base_expr, length, pad_expr) |
Left pads the given
|
LTRIM(expr) |
Removes whitespace from the left side of expr |
POSITION(match_expr, ref_expr, [start_pos]) |
Alias for LOCATE |
REPLACE(ref_expr, match_expr, repl_expr) |
Replaces every occurrence of match_expr in ref_expr with
repl_expr |
REVERSE(expr) |
Returns
|
RIGHT(expr, num_chars) |
Returns the rightmost num_chars characters from expr |
RPAD(base_expr, length, pad_expr) |
Right pads the given
|
RTRIM(expr) |
Removes whitespace from the right side of expr |
SOUNDEX(expr) |
Returns a soundex value from Note: This is the algorithm used by most programming languages |
SPACE(n) |
Returns a string consisting of n space characters. The value of
n can only be within the range of 0-256. |
SPLIT(expr, delim, group_num) |
Splits
|
STARTS_WITH(match_expr, ref_expr) |
Returns 1 if ref_expr starts with match_expr by
string-literal comparison; otherwise, returns 0 |
STRCMP(expr_a, expr_b) |
Returns 0 if expr_a and expr_b are the same, -1 if
expr_a comes before expr_b in a lexigraphical sort, and 1
if expr_b comes before expr_a |
SUBSTR(expr, start_pos, num_chars) |
Alias for SUBSTRING |
SUBSTRING(expr, start_pos, num_chars) |
Returns num_chars characters from the expr, starting at the
1-based start_pos |
TRIM(expr) |
Removes whitespace from both sides of expr |
UCASE(expr) |
Converts expr to uppercase |
UPPER(expr) |
Alias for UCASE |
Column Expressions¶
Many of the functions above accept expressions as inputs in place of column names for selecting data from tables e.g. /aggregate/minmax. Given below are some examples of column expressions:
(x + y)
(2 * col1) + col2
Filter Expressions¶
Data can be filtered with the use of filter expressions within many endpoints;
e.g., /filter. These expressions may contain
column expressions as well as tests for equality/inequality for selecting
records from the database. A filter expression cannot contain
aggregation functions and should evaluate to a logical value
( true or false ). When the result of an expression evaluation is a
numerical value, the result is converted to a logical value as follows: 0
is considered false and any other value is considered as true. Some
examples of filter expressions are given below:
(x > y)
(a != b) or (c = d)
(timestamp > 1456749296789) and (x <= 10.0)
ABS(timestamp - 1456749296789) < 60 * 60 * 1000
QUARTER(timestamp) = 1 and MOD(YEAR(timestamp), 4) = 0
msg_id == 'MSGID1'
(x = 5) and y in (10,20,30)
Aggregate Expressions¶
Some endpoints accept aggregation expressions as inputs for selecting data from tables, e.g., /aggregate/groupby. Such expressions can only contain aggregation functions and non-nested functions of aggregation functions.
| Function | Description |
|---|---|
ARG_MIN(agg_expr, ret_expr) |
The value of ret_expr where agg_expr is the minimum value (e.g. ARG_MIN(cost, product_id) returns the product ID of the
lowest cost product) |
ARG_MAX(agg_expr, ret_expr) |
The value of ret_expr where agg_expr is the maximum value (e.g. ARG_MAX(cost, product_id) returns the product ID of the
highest cost product) |
AVG(expr) |
The average of values of expr |
CORR(expr1, expr2) |
Calculates the correlation coefficient of expr1 and expr2 |
CORRELATION(expr1, expr2) |
Alias for CORR |
CORRCOEF(expr1, expr2) |
Alias for CORR |
COUNT(expr) |
Count of non-null values of expr; use * to count all values within an aggregation group or over an entire table |
COUNT_DISTINCT(expr) |
Count of the distinct values of expr |
COV(expr1, expr2) |
Alias for COVAR_POP |
COVAR(expr1, expr2) |
Alias for COVAR_POP |
COVARIANCE(expr1, expr2) |
Alias for COVAR_POP |
COVAR_POP(expr1, expr2) |
Calculates the population covariance of expr1 and expr2 |
COVAR_SAMP(expr1, expr2) |
Calculates the sample covariance of expr1 and expr2 |
GROUPING(expr) |
Used primarily with Rollup, Cube, and Grouping Sets, to distinguish the source of
null values in an aggregated result set, returns whether For example, in a |
KURT(expr) |
Alias for KURTOSIS_POP |
KURTOSIS(expr) |
Alias for KURTOSIS_POP |
KURTOSIS_POP(expr) |
Calculate the population kurtosis of expr |
KURTOSIS_SAMP(expr) |
Calculate the sample kurtosis of expr |
KURT_POP(expr) |
Alias for KURTOSIS_POP |
KURT_SAMP(expr) |
Alias for KURTOSIS_SAMP |
MAX(expr) |
The maximum of values of expr |
MEAN(expr) |
Alias for AVG |
MIN(expr) |
The minimum of values of expr |
SKEW(expr) |
Alias for SKEWNESS_POP |
SKEWNESS(expr) |
Alias for SKEWNESS_POP |
SKEWNESS_POP(expr) |
Calculate the population skew of expr |
SKEWNESS_SAMP(expr) |
Calculate the sample skew of expr |
SKEW_POP(expr) |
Alias for SKEWNESS_POP |
SKEW_SAMP(expr) |
Alias for SKEWNESS_SAMP |
STDDEV(expr) |
The population standard deviation over values of expr (i.e. the denominator is N) |
STDDEV_POP(expr) |
The population standard deviation over values of expr (i.e. the denominator is N) |
STDDEV_SAMP(expr) |
The sample standard deviation over values of expr (i.e. the denominator is N-1) |
SUM(expr) |
The sum of values of expr |
VAR(expr) |
The population variance over values of expr (i.e. the denominator is N) |
VAR_POP(expr) |
The population variance over values of expr (i.e. the denominator is N) |
VAR_SAMP(expr) |
The sample variance over values of expr (i.e. the denominator is N-1) |
Some examples of aggregate expressions:
SUM(sale_price) - SUM(base_price)
MAX(CEIL(x)) - MIN(FLOOR(x))
AVG(ABS(z - 100.0))