Skip to content

Latest commit

 

History

History
418 lines (285 loc) · 14.2 KB

index.asciidoc

File metadata and controls

418 lines (285 loc) · 14.2 KB

Functions and Operators

{es-sql} provides a number of built-in operators and functions.

Comparison Operators

{es-sql} supports the following comparison operators:

  • Equality (=)

include-tagged::{sql-specs}/filter.sql-spec[whereFieldEquality]

  • Inequality (<> or != or <⇒)

include-tagged::{sql-specs}/filter.sql-spec[whereFieldNonEquality]

  • Comparison (<, ⇐, >, >=)

include-tagged::{sql-specs}/filter.sql-spec[whereFieldLessThan]

  • BETWEEN

include-tagged::{sql-specs}/filter.sql-spec[whereBetween]

  • IS NULL/IS NOT NULL

include-tagged::{sql-specs}/filter.sql-spec[whereIsNotNullAndIsNull]

Logical Operators

{es-sql} supports the following logical operators:

  • AND

include-tagged::{sql-specs}/filter.sql-spec[whereFieldAndComparison]

  • OR

include-tagged::{sql-specs}/filter.sql-spec[whereFieldOrComparison]

  • NOT

include-tagged::{sql-specs}/filter.sql-spec[whereFieldEqualityNot]

Math Operators

{es-sql} supports the following math operators:

  • Add (+)

include-tagged::{sql-specs}/arithmetic.sql-spec[plus]

  • Subtract (infix -)

include-tagged::{sql-specs}/arithmetic.sql-spec[minus]

  • Negate (unary -)

include-tagged::{sql-specs}/arithmetic.sql-spec[unaryMinus]

  • Multiply (*)

include-tagged::{sql-specs}/arithmetic.sql-spec[multiply]

  • Divide (/)

include-tagged::{sql-specs}/arithmetic.sql-spec[divide]
include-tagged::{sql-specs}/arithmetic.sql-spec[mod]

Math Functions

All math and trigonometric functions require their input (where applicable) to be numeric.

Generic

  • ABS

Absolute value, returns \[same type as input]

include-tagged::{sql-specs}/math.sql-spec[abs]

  • CBRT

Cube root, returns double

  • CEIL

Ceiling, returns double

  • CEILING

Same as CEIL

  • E

Euler’s number, returns 2.7182818284590452354

Note
 This rounds "half up" meaning that ROUND(-1.5) results in -1. 
include-tagged::{sql-specs}/math.sql-spec[log]
include-tagged::{sql-specs}/math.sql-spec[log10]
include-tagged::{sql-specs}/math.sql-spec[sqrt]

  • ex (EXP)

include-tagged::{sql-specs}/math.sql-spec[exp]
include-tagged::{sql-specs}/math.sql-spec[expm1]

Trigonometric

include-tagged::{sql-specs}/math.sql-spec[degrees]
include-tagged::{sql-specs}/math.sql-spec[degrees]
include-tagged::{sql-specs}/math.sql-spec[sin]
include-tagged::{sql-specs}/math.sql-spec[cos]
include-tagged::{sql-specs}/math.sql-spec[tan]
include-tagged::{sql-specs}/math.sql-spec[asin]
include-tagged::{sql-specs}/math.sql-spec[acos]
include-tagged::{sql-specs}/math.sql-spec[atan]
include-tagged::{sql-specs}/math.sql-spec[sinh]
include-tagged::{sql-specs}/math.sql-spec[cosh]

Date and Time Functions

  • Extract the year from a date (YEAR)

include-tagged::{sql-specs}/datetime.csv-spec[year]

  • Extract the month of the year from a date (MONTH_OF_YEAR or MONTH)

include-tagged::{sql-specs}/datetime.csv-spec[monthOfYear]

  • Extract the week of the year from a date (WEEK_OF_YEAR or WEEK)

include-tagged::{sql-specs}/datetime.csv-spec[weekOfYear]

  • Extract the day of the year from a date (DAY_OF_YEAR or DOY)

include-tagged::{sql-specs}/datetime.csv-spec[dayOfYear]

  • Extract the day of the month from a date (DAY_OF_MONTH, DOM, or DAY)

include-tagged::{sql-specs}/datetime.csv-spec[dayOfMonth]

  • Extract the day of the week from a date (DAY_OF_WEEK or DOW). Monday is 1, Tuesday is 2, etc.

include-tagged::{sql-specs}/datetime.csv-spec[dayOfWeek]

  • Extract the hour of the day from a date (HOUR_OF_DAY or HOUR). Monday is 1, Tuesday is 2, etc.

include-tagged::{sql-specs}/datetime.csv-spec[hourOfDay]

  • Extract the minute of the day from a date (MINUTE_OF_DAY).

include-tagged::{sql-specs}/datetime.csv-spec[minuteOfDay]

  • Extract the minute of the hour from a date (MINUTE_OF_HOUR, MINUTE).

include-tagged::{sql-specs}/datetime.csv-spec[minuteOfHour]

  • Extract the second of the minute from a date (SECOND_OF_MINUTE, SECOND).

include-tagged::{sql-specs}/datetime.csv-spec[secondOfMinute]

  • Extract

As an alternative, one can support EXTRACT to extract fields from datetimes. You can run any datetime function with EXTRACT(<datetime_function> FROM <expression>). So

include-tagged::{sql-specs}/datetime.csv-spec[extractDayOfYear]

is the equivalent to

include-tagged::{sql-specs}/datetime.csv-spec[dayOfYear]

Aggregate Functions

Basic

include-tagged::{sql-specs}/agg.sql-spec[avg]

  • Count the number of matching fields (COUNT)

include-tagged::{sql-specs}/agg.sql-spec[countStar]

  • Count the number of distinct values in matching documents (COUNT(DISTINCT)

include-tagged::{sql-specs}/agg.sql-spec[countDistinct]

  • Find the maximum value in matching documents (MAX)

include-tagged::{sql-specs}/agg.sql-spec[max]

  • Find the minimum value in matching documents (MIN)

include-tagged::{sql-specs}/agg.sql-spec[min]

  • Sum all values of matching documents (SUM).

include-tagged::{sql-specs}/agg.csv-spec[sum]