Version:

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.,

  • 'hello'
  • "world"
Numerical

Numerical constants can be expressed as:

  • decimal integers (31242)
  • hex integers (0x3420123a)
  • doubles (3.41e5)
  • floats (3.1415F)

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

> < >= <= == = != <> in

  • Only = and != are supported for unrestricted String fields
  • All comparison operators are supported for charN fields
Logical
Operator Details
and Both arguments are true
or Either argument is true
xor One argument is true, and one is false
not The argument is false
! Synonym for not
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 expr: if true, returns value_if_true; otherwise, value_if_false

  • expr - any true/false condition. Note that when an integer column is used directly, this function will interpret non-zero values as true and zero values as false)
  • value_if_true - any numeric value
  • value_if_false - any numeric value

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

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 interval_amount of interval_type units to timestamp

  • interval_type - valid values are:
    • YEAR - year is modified by interval amount (not affected by leap year, etc.)
    • MONTH - month is modified by interval amount and year adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month, e.g., Apr 31st -> Apr 30th
    • DAY - day is modified by interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
    • HOUR - hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
    • MINUTE - minute is modified by interval amount; hour & date are adjusted, if overflow/underflow occurs
    • SECOND - second is modified by interval amount; minute, hour, & date are adjusted, if overflow/underflow occurs
    • MILLISECOND - milliseconds are modified by interval amount; time & date are adjusted, if overflow/underflow occurs
    • QUARTER - month is modified by three times the interval amount, irrespective of the number of days in the months in between; day adjusting performed the same way as in MONTH description, but only on final month, i.e. Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February
    • WEEK - day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
  • interval_amount - positive (or negative) integer specifying how many interval_type units will be added (or subtracted) from timestamp
  • timestamp - any field with a timestamp annotation
TIMESTAMPDIFF(unit_type, begin_timestamp, end_timestamp)

Calculates the difference between two dates, returning the result in the units specified; more precisely, how many unit_type units need to be added to or subtracted from begin_timestamp to equal end_timestamp (or get as close as possible without going past it) using the rules specified in TIMESTAMPADD. NOTE: this is not symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, but the TIMESTAMPDIFF in MONTH units between those two dates is 0.

  • unit_type - same tokens as specified for the interval_type of TIMESTAMPADD begin_timestamp - any field with a timestamp annotation
  • end_timestamp - any field with a timestamp annotation

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_ISVALID to determine if a geometry object is valid. The functions below work best with valid geometry objects.
  • Use the REMOVE_NULLABLE function to remove any nullable column 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 x and y coordinate and geom using the specified solution type. Solution types available:

  • 0 (default) - Cartesian; returns 2-D Cartesian distance
  • 1 - Haversine; returns minimum sphere distance in meters
  • 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

Note: If the x and y coordinate and geom intersect (verify using ST_INTERSECTS), the distance will always be 0.

STXY_DWITHIN(x, y, geom, distance[, solution])

Returns 1 (true) if the x and y coordinate is within the specified distance from geom using the specified solution type. Solution types available:

  • 0 (default) - Euclidean; uses degrees to calculate distance
  • 1 - Spheric; uses meters to calculate distance
STXY_ENVDWITHIN(x, y, geom, distance[, solution])

Returns 1 (true) if the x and y coordinate is within the specified distance from the bounding box of geom using the specified solution type. Solution types available:

  • 0 (default) - Euclidean; uses degrees to calculate distance
  • 1 - Spheric; uses meters to calculate distance
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(lon, lat, 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 geom if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns 0 if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available:

  • 0 (default) - 2D Euclidean area
  • 1 - curved surface area on a sphere in meters
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)

Returns a geometry that represents all points whose distance from the given geometry geom is less than or equal to the given distance radius in degrees. Additional style parameters can be specified as a list of blank-separated key-value pairs, e.g., 'quad_segs=8 endcap=round' :

  • quad_segs -- the number of segments used to approximate a quarter circle (default is 8)
  • endcap -- the endcap style of the buffer (default is round); options are round, flat (or butt), and square
  • join -- the join style of the buffer (default is round); options are round, mitre (or miter), and bevel
  • mitre_limit -- the mitre ratio limit expressed as a floating point number (miter_limit is also acceptable)
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 POINT in geom1 that is closest to geom2 using the specified solution type. If geom1 or geom2 is empty, a null is returned. Solution types available:

  • 0 (default) - Cartesian; calculates the closest point using 2-D Cartesian distance
  • 1 - Haversine; calculates the closest point using sphere distance in meters

Note: Solution type 1 can only calculate the closest point from POINT to POINT, LINESTRING, or POLYGON; LINESTRING to POINT, LINESTRING, or POLYGON; or POLYGON to POINT or LINESTRING. Calculating the distance between any other combination of geometry types will return null

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 type from the given geometry collection. Type is a number that maps to the following:

  • 1 = POINT
  • 2 = LINESTRING
  • 3 = POLYGON
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_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, geom1 and geom2, using the specified solution type. Solution types available:

  • 0 (default) - Cartesian; returns 2-D Cartesian distance
  • 1 - Haversine; returns minimum sphere distance in meters
  • 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

Note: If geom1 and geom2 intersect (verify using ST_INTERSECTS), the distance will always be 0.

ST_DISTANCEPOINTS(x1, y1, x2, y2[, solution])

Calculates the minimum distance between the given points, x1, y1 and x2, y2, using the specified solution type. Solution types available:

  • 0 (default) - Cartesian; returns 2-D Cartesian distance
  • 1 - Haversine; returns minimum sphere distance in meters
  • 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance
ST_DFULLYWITHIN(geom1, geom2, distance) Returns 1 (true) if the maximum distance between geometries geom1 and geom2 is within the specified 2-D Euclidean distance of each other
ST_DWITHIN(geom1, geom2, distance[, solution])

Returns 1 (true) if the minimum distance between geometries geom1 and geom2 is within the specified distance of each other using the specified solution type. Solution types available:

  • 0 (default) - Euclidean; uses degrees to calculate distance
  • 1 - Spheric; uses meters to calculate distance
ST_ELLIPSE(centerx, centery, height, width)

Returns an ellipse using the following values:

  • centerx -- the x coordinate or longitude used to center the ellipse
  • centery -- the y coordinate or latitude used to center the ellipse
  • height -- the height of the ellipse (in degrees)
  • width -- the width of the ellipse (in degrees)
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 1 (true) if geom1 is within the specified distance of the bounding box of geom2 using the specified solution type. Solution types available:

  • 0 (default) - Euclidean; uses degrees to calculate distance
  • 1 - Spheric; uses meters to calculate distance
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 geom with specified precision. The longer the precision, the more precise the hash is. By default, precision is set to 20. Returns null if geom is an empty geometry.

Note: The hash returned will not be 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 geom. Type and ID mappings:

  • POINT = 0
  • LINESTRING = 1
  • LINEARRING = 2
  • POLYGON = 3
  • MULTIPOINT = 4
  • MULTILINESTRING = 5
  • MULTIPOLYGON = 6
  • GEOMETRYCOLLECTION = 7
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) Returns the 2-D length of the geometry (in degrees) if it is a LINESTRING or MULTILINESTRING. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, etc.
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) Returns the 2-D 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 geom1 or geom2 is empty, null is returned
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 geom if it is a MULTIPOINT. If geom is a POINT, there must be at least one other POINT to construct a LINESTRING. If geom is a LINESTRING, it must have at least two points. Returns null if geom is not a POINT, MULTIPOINT, or LINESTRING

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 geom. Inputs must be closed LINESTRINGs

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) Returns the maximum 2-D Euclidean distance between the given geom1 and geom2 geometries. If geom1 or geom2 are empty, null is returned
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 distance around the given geom geometry. Multiple distances are specified as comma-separated values in an array, e.g., [10,20,30]. Valid values for outside are:

  • FULL -- indicates that buffers will overlap or cover the given geom geometry. This is the default.
  • OUTSIDE_ONLY -- indicates that buffers will be rings around the given geom geometry.
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_POINT(x, y) Returns a POINT with the given x and y coordinates.
ST_POINTFROMGEOHASH(geohash, precision)

Returns a POINT 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.

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) Returns the given geom but segmentized n number of times based on how the max_segment_length distance (in meters) divides up the original geometry. The new geom is guaranteed to have segments that are smaller than the given max_segment_length. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can segmentized, but only the individual parts will be segmentized, not the collection as a whole
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 a vertices 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 geom (WKT)

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.
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 expr to the nearest decimal number with scale decimal places when scale is a positive number; rounds to the nearest number such that the result has -(scale) zeros to the left of the decimal point when scale is negative; use scale of 0 to round to the nearest integer. Examples:

  • ROUND(12345.678, 2) -> 12345.68
  • ROUND(12345.678, 0) -> 12346
  • ROUND(12345.678, -2) -> 12300
SIGN(expr)

Determines whether a number is positive, negative, or zero; returns one of the following three values:

  • positive: 1
  • zero: 0
  • negative: -1
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 expr down to the nearest decimal number with scale decimal places, following the same rules as ROUND. Examples:

  • TRUNCATE(12345.678, 2) -> 12345.67
  • TRUNCATE(12345.678, 0) -> 12345
  • TRUNCATE(12345.678, -2) -> 12300

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 expr_a & expr_b; use nested CONCAT calls to concatenate more than two strings

Note: The resulting field size of any CONCAT will be a charN field big enough to hold the concatenated fields. If the width is more than 256 (largest charN), the operation will fail.

CONCAT_TRUNCATE(expr_a, expr_b)

Returns the concatenation of expr_a and expr_b, truncated at the maximum size of expr_a. For columns, this size is explicit; for string constants, this will be the smallest charN type that can hold the expr_a string. Examples:

  • CONCAT_TRUNCATE('ABC123','!') -> ABC123!
  • CONCAT_TRUNCATE('ABC123','DEFG') -> ABC123DE (char8 is the minimum size required to hold ``ABC123``, so the result is truncated at 8 characters)
  • CONCAT_TRUNCATE('ABCD1234','DEFG') -> ABCD1234 (char8 is the minimum size required to hold ``ABCD1234``, so no more characters can be concatenated)
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 ref_expr matches the given match_expr. The match is a string literal one with the following exceptions:

  • % matches any string of 0 or more characters
  • _ matches any single character
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 base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256. Examples:

  • LPAD('test', 9, 'pad') -> padpatest
  • LPAD('test', 3, 'pad') -> tes
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 expr with the order of characters reversed. Example:

  • REVERSE('Because') -> esuaceB
RIGHT(expr, num_chars) Returns the rightmost num_chars characters from expr
RPAD(base_expr, length, pad_expr)

Right pads the given base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256. Examples:

  • RPAD('test', 9, 'pad') -> testpadpa
  • RPAD('test', 3, 'pad') -> tes
RTRIM(expr) Removes whitespace from the right side of expr
SOUNDEX(expr)

Returns a soundex value from expr. Only the first word in the string will be considered in the calculation.

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 expr into groups delimited by the delim character and returns the group_num split group. If group_num is positive, groups will be counted from the beginning of expr; if negative, groups will be counted from the end of expr going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of delim exist in expr, the entire string is available at group 1 (and -1). Group 0 returns nothing. Examples:

  • SPLIT('apple', 'p', 1) -> a
  • SPLIT('apple', 'p', 2) -> <empty string>
  • SPLIT('apple', 'p', -1) -> le
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 expr is part of the aggregation set used to calculate the values in a given result set row. Returns 0 if expr is part of the row's aggregation set, 1 if expr is not (meaning that aggregation took place across all expr values).

For example, in a ROLLUP(A) operation, there will be two potential rows with null in the result set for column A. One row will contain null values of A aggregated together, and the other will contain null, but be an aggregation over the entire table, irrespective of A values. In this case, GROUPING(A) will return 0 for the null values of A aggregated together (as well as all other grouped A values) and 1 for the row resulting from aggregating across all A values.

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))