# Function Index This page provides a comprehensive A-Z reference of the most commonly used functions in Apache Pinot, organized by category. Each category section links to its detailed documentation page with full syntax, parameters, and examples. Functions are available in the **Single-Stage Engine (SSE)**, the **Multi-Stage Engine (MSE)**, or **both**. Window functions require the multi-stage engine. *** ## Aggregation Functions For full details, see [Aggregation Functions](https://docs.pinot.apache.org/functions/aggregation). | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------- | ----------- | ------------------------------------------------------------------------------ | ------ | | [`COUNT`](https://docs.pinot.apache.org/functions/aggregation/count) | `COUNT(*)` or `COUNT(col)` | LONG | Counts the number of rows | Both | | [`MIN`](https://docs.pinot.apache.org/functions/aggregation/min) | `MIN(col)` | DOUBLE | Returns the minimum value | Both | | [`MAX`](https://docs.pinot.apache.org/functions/aggregation/max) | `MAX(col)` | DOUBLE | Returns the maximum value | Both | | [`SUM`](https://docs.pinot.apache.org/functions/aggregation/sum) | `SUM(col)` | DOUBLE | Returns the sum of values | Both | | [`AVG`](https://docs.pinot.apache.org/functions/aggregation/avg) | `AVG(col)` | DOUBLE | Returns the average of values | Both | | [`MODE`](https://docs.pinot.apache.org/functions/aggregation/mode) | `MODE(col)` | DOUBLE | Returns the most frequent value | Both | | [`MINMAXRANGE`](https://docs.pinot.apache.org/functions/aggregation/minmaxrange) | `MINMAXRANGE(col)` | DOUBLE | Returns the range (max - min) | Both | | [`DISTINCTCOUNT`](https://docs.pinot.apache.org/functions/aggregation/distinctcount) | `DISTINCTCOUNT(col)` | INT | Returns the exact distinct count | Both | | [`DISTINCTCOUNTHLL`](https://docs.pinot.apache.org/functions/aggregation/distinctcounthll) | `DISTINCTCOUNTHLL(col [, log2m])` | LONG | Approximate distinct count using HyperLogLog | Both | | [`DISTINCTCOUNTHLLPLUS`](https://docs.pinot.apache.org/functions/sketch/distinctcounthllplus) | `DISTINCTCOUNTHLLPLUS(col [, p])` | LONG | Approximate distinct count using HyperLogLog++ | Both | | [`DISTINCTCOUNTSMARTHLLPLUS`](https://docs.pinot.apache.org/sketch/distinctcounthllplus#distinctcountsmarthllplus) | `DISTINCTCOUNTSMARTHLLPLUS(col [, params])` | LONG | Exact-then-approximate distinct count that switches to HLL++ after a threshold | Both | | [`DISTINCTCOUNTBITMAP`](https://docs.pinot.apache.org/functions/aggregation/distinctcountbitmap) | `DISTINCTCOUNTBITMAP(col)` | INT | Distinct count using bitmap | Both | | [`DISTINCTCOUNTTHETASKETCH`](https://docs.pinot.apache.org/functions/aggregation/distinctcountthetasketch) | `DISTINCTCOUNTTHETASKETCH(col [, predicates...])` | LONG | Distinct count using Theta Sketch | Both | | [`DISTINCTCOUNTCPCSKETCH`](https://docs.pinot.apache.org/functions/sketch/distinctcountcpcsketch) | `DISTINCTCOUNTCPCSKETCH(col [, lgK])` | LONG | Distinct count using CPC Sketch | Both | | [`DISTINCTCOUNTULL`](https://docs.pinot.apache.org/functions/sketch/distinctcountull) | `DISTINCTCOUNTULL(col [, p])` | LONG | Distinct count using UltraLogLog | Both | | [`DISTINCTSUM`](https://docs.pinot.apache.org/functions/aggregation/distinctsum) | `DISTINCTSUM(col)` | DOUBLE | Sum of distinct values | Both | | [`DISTINCTAVG`](https://docs.pinot.apache.org/functions/aggregation/distinctavg) | `DISTINCTAVG(col)` | DOUBLE | Average of distinct values | Both | | [`SEGMENTPARTITIONEDDISTINCTCOUNT`](https://docs.pinot.apache.org/functions/aggregation/segmentpartitioneddistinctcount) | `SEGMENTPARTITIONEDDISTINCTCOUNT(col)` | LONG | Optimized distinct count for partitioned columns | Both | | [`PERCENTILE`](https://docs.pinot.apache.org/functions/aggregation/percentile) | `PERCENTILE(col, pct)` | DOUBLE | Exact percentile value | Both | | [`PERCENTILEEST`](https://docs.pinot.apache.org/functions/aggregation/percentileest) | `PERCENTILEEST(col, pct)` | LONG | Estimated percentile using Quantile Digest | Both | | [`PERCENTILETDIGEST`](https://docs.pinot.apache.org/functions/aggregation/percentiletdigest) | `PERCENTILETDIGEST(col, pct)` | DOUBLE | Estimated percentile using T-Digest | Both | | [`PERCENTILEKLL`](https://docs.pinot.apache.org/functions/aggregation/percentilekll) | `PERCENTILEKLL(col, pct)` | DOUBLE | Estimated percentile using KLL Sketch | Both | | [`HISTOGRAM`](https://docs.pinot.apache.org/functions/aggregation/histogram) | `HISTOGRAM(col, binEdges...)` | DOUBLE\[] | Computes histogram over bin edges | Both | | [`COVARPOP`](https://docs.pinot.apache.org/functions/statistical/covar_pop) | `COVARPOP(col1, col2)` | DOUBLE | Population covariance | Both | | [`COVARSAMP`](https://docs.pinot.apache.org/functions/statistical/covar_samp) | `COVARSAMP(col1, col2)` | DOUBLE | Sample covariance | Both | | [`VARPOP`](https://docs.pinot.apache.org/functions/statistical/varpop) | `VARPOP(col)` | DOUBLE | Population variance | Both | | [`VARSAMP`](https://docs.pinot.apache.org/functions/statistical/varsamp) | `VARSAMP(col)` | DOUBLE | Sample variance | Both | | [`STDDEVPOP`](https://docs.pinot.apache.org/functions/statistical/stddevpop) | `STDDEVPOP(col)` | DOUBLE | Population standard deviation | Both | | [`STDDEVSAMP`](https://docs.pinot.apache.org/functions/statistical/stddevsamp) | `STDDEVSAMP(col)` | DOUBLE | Sample standard deviation | Both | | [`SKEWNESS`](https://docs.pinot.apache.org/functions/aggregation/skewness) | `SKEWNESS(col)` | DOUBLE | Skewness of values | Both | | [`KURTOSIS`](https://docs.pinot.apache.org/functions/aggregation/kurtosis) | `KURTOSIS(col)` | DOUBLE | Kurtosis of values | Both | | [`FIRSTWITHTIME`](https://docs.pinot.apache.org/functions/aggregation/firstwithtime) | `FIRSTWITHTIME(dataCol, timeCol, 'type')` | varies | Value associated with the earliest timestamp | Both | | [`LASTWITHTIME`](https://docs.pinot.apache.org/functions/aggregation/lastwithtime) | `LASTWITHTIME(dataCol, timeCol, 'type')` | varies | Value associated with the latest timestamp | Both | | [`ANYVALUE`](https://docs.pinot.apache.org/functions/aggregation/anyvalue) | `ANYVALUE(col)` | varies | Returns any arbitrary value from the group | Both | | [`BOOLAND`](https://docs.pinot.apache.org/functions/aggregation/booland) | `BOOLAND(col)` | BOOLEAN | Logical AND across all values | Both | | [`BOOLOR`](https://docs.pinot.apache.org/functions/aggregation/boolor) | `BOOLOR(col)` | BOOLEAN | Logical OR across all values | Both | | [`EXPRMIN`](https://docs.pinot.apache.org/functions/aggregation/exprmin) | `EXPRMIN(measureCol, exprCol1, ...)` | varies | Columns at the row with minimum measure | Both | | [`EXPRMAX`](https://docs.pinot.apache.org/functions/aggregation/exprmax) | `EXPRMAX(measureCol, exprCol1, ...)` | varies | Columns at the row with maximum measure | Both | | [`FREQUENTSTRINGSSKETCH`](https://docs.pinot.apache.org/functions/sketch/frequentstringssketch) | `FREQUENTSTRINGSSKETCH(col, maxMapSize)` | STRING | Frequent items sketch for strings | Both | | [`FREQUENTLONGSSKETCH`](https://docs.pinot.apache.org/functions/sketch/frequentlongssketch) | `FREQUENTLONGSSKETCH(col, maxMapSize)` | STRING | Frequent items sketch for longs | Both | | [`FUNNELCOUNT`](https://docs.pinot.apache.org/functions/funnel/funnelcount) | `FUNNELCOUNT(stepCol, corCol, settings, step1, step2, ...)` | LONG\[] | Funnel step counts | Both | | [`FUNNELMAXSTEP`](https://docs.pinot.apache.org/functions/funnel/funnelmaxstep) | `FUNNELMAXSTEP(stepCol, corCol, settings, step1, step2, ...)` | INT | Maximum funnel step reached | Both | | [`FUNNELCOMPLETECOUNT`](https://docs.pinot.apache.org/functions/funnel/funnelcompletecount) | `FUNNELCOMPLETECOUNT(stepCol, corCol, settings, step1, step2, ...)` | INT | Count of completed funnels | Both | | [`FUNNELSTEPDURATIONSTATS`](https://docs.pinot.apache.org/functions/aggregation/funnelstepdurationstats) | `FUNNELSTEPDURATIONSTATS(timestampExpression, windowSize, numberSteps, step1, ..., 'durationFunctions=...')` | DOUBLE\[] | Step-to-step duration statistics for completed funnel runs | Both | | [`FUNNELEVENTSFUNCTIONEVAL`](https://docs.pinot.apache.org/functions/aggregation/funneleventsfunctioneval) | `FUNNELEVENTSFUNCTIONEVAL(timestampExpression, windowSize, numberSteps, step1, ..., extraFieldCount, extraField1, ...)` | STRING\[] | Extra-field values from matched funnel events | Both | | [`DISTINCTSUM`](https://docs.pinot.apache.org/functions/aggregation/distinctsum) | `DISTINCTSUM(col)` | DOUBLE | Sum of distinct values | Both | | [`DISTINCTAVG`](https://docs.pinot.apache.org/functions/aggregation/distinctavg) | `DISTINCTAVG(col)` | DOUBLE | Average of distinct values | Both | | [`SUMPRECISION`](https://docs.pinot.apache.org/functions/aggregation/sumprecision) | `SUMPRECISION(col [, precision])` | STRING | High-precision sum using BigDecimal | Both | | [`ARRAYAGG`](https://docs.pinot.apache.org/functions/aggregation/arrayagg) | `ARRAYAGG(col, 'type' [, distinct])` | ARRAY | Aggregates values into an array | Both | | [`LISTAGG`](https://docs.pinot.apache.org/functions/aggregation/listagg) | `LISTAGG(col [, delimiter])` | STRING | Aggregates values into delimited string | Both | | [`SUMARRAYLONG`](https://docs.pinot.apache.org/functions/aggregation/sumarraylong) | `SUMARRAYLONG(arrCol)` | LONG\[] | Element-wise sum of long arrays | Both | | [`SUMARRAYDOUBLE`](https://docs.pinot.apache.org/functions/aggregation/sumarraydouble) | `SUMARRAYDOUBLE(arrCol)` | DOUBLE\[] | Element-wise sum of double arrays | Both | *** ## String Functions For full details, see [String Functions](https://docs.pinot.apache.org/functions/string). | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ----------- | --------------------------------------------------------------------------------- | ------ | | [`UPPER`](https://docs.pinot.apache.org/functions/string/upper) | `UPPER(str)` | STRING | Converts string to uppercase | Both | | [`LOWER`](https://docs.pinot.apache.org/functions/string/lower) | `LOWER(str)` | STRING | Converts string to lowercase | Both | | [`INITCAP`](https://docs.pinot.apache.org/functions/string/initcap) | `INITCAP(str)` | STRING | Capitalizes first letter of each word | Both | | [`REVERSE`](https://docs.pinot.apache.org/functions/string/reverse) | `REVERSE(str)` | STRING | Reverses the string | Both | | [`SUBSTR`](https://docs.pinot.apache.org/functions/string/substr) | `SUBSTR(str, start [, end])` | STRING | Extracts substring by start/end position | Both | | `SUBSTRING` | `SUBSTRING(str, start [, length])` | STRING | Extracts substring by start position and length | Both | | `LEFT` | `LEFT(str, length)` | STRING | Returns leftmost N characters | Both | | `RIGHT` | `RIGHT(str, length)` | STRING | Returns rightmost N characters | Both | | [`CONCAT`](https://docs.pinot.apache.org/functions/string/concat) | `CONCAT(str1, str2)` | STRING | Concatenates two strings | Both | | [`TRIM`](https://docs.pinot.apache.org/functions/string/trim) | `TRIM(str)` | STRING | Removes leading and trailing whitespace | Both | | [`LTRIM`](https://docs.pinot.apache.org/functions/string/ltrim) | `LTRIM(str)` | STRING | Removes leading whitespace | Both | | [`RTRIM`](https://docs.pinot.apache.org/functions/string/rtrim) | `RTRIM(str)` | STRING | Removes trailing whitespace | Both | | [`LPAD`](https://docs.pinot.apache.org/functions/string/lpad) | `LPAD(str, size, pad)` | STRING | Left-pads string to specified size | Both | | [`RPAD`](https://docs.pinot.apache.org/functions/string/rpad) | `RPAD(str, size, pad)` | STRING | Right-pads string to specified size | Both | | [`LENGTH`](https://docs.pinot.apache.org/functions/string/length) | `LENGTH(str)` | INT | Returns the length of the string | Both | | [`STRPOS`](https://docs.pinot.apache.org/functions/string/strpos) | `STRPOS(str, find [, instance])` | INT | Returns position of substring | Both | | `STRRPOS` | `STRRPOS(str, find [, instance])` | INT | Returns last position of substring | Both | | [`STARTSWITH`](https://docs.pinot.apache.org/functions/string/startswith) | `STARTSWITH(str, prefix)` | BOOLEAN | Checks if string starts with prefix | Both | | `ENDSWITH` | `ENDSWITH(str, suffix)` | BOOLEAN | Checks if string ends with suffix | Both | | `CONTAINS` | `CONTAINS(str, substring)` | BOOLEAN | Checks if string contains substring | Both | | [`REPLACE`](https://docs.pinot.apache.org/functions/string/replace) | `REPLACE(str, target, replacement)` | STRING | Replaces occurrences of target | Both | | [`REMOVE`](https://docs.pinot.apache.org/functions/string/remove) | `REMOVE(str, search)` | STRING | Removes all occurrences of search string | Both | | `SPLIT` | `SPLIT(str, delimiter [, limit])` | STRING\[] | Splits string by delimiter | Both | | [`SPLITPART`](https://docs.pinot.apache.org/functions/string/splitpart) | `SPLITPART(str, delimiter, index)` or `SPLITPART(str, delimiter, limit, index)` | STRING | Returns the selected element after splitting; negative indices count from the end | Both | | `REPEAT` | `REPEAT(str, times)` | STRING | Repeats string N times | Both | | [`REGEXP_EXTRACT`](https://docs.pinot.apache.org/functions/string/regexpextract) | `REGEXP_EXTRACT(str, pattern [, group])` | STRING | Extracts regex match from string | Both | | [`CHR`](https://docs.pinot.apache.org/functions/string/chr) | `CHR(codepoint)` | STRING | Returns character for Unicode code point | Both | | [`CODEPOINT`](https://docs.pinot.apache.org/functions/string/codepoint) | `CODEPOINT(str)` | INT | Returns Unicode code point of first character | Both | | `NORMALIZE` | `NORMALIZE(str [, form])` | STRING | Normalizes Unicode string | Both | | `STRCMP` | `STRCMP(str1, str2)` | INT | Compares two strings lexicographically | Both | | `HAMMINGDISTANCE` | `HAMMINGDISTANCE(str1, str2)` | INT | Hamming distance between two strings | Both | | [`LEVENSTEINDISTANCE`](https://docs.pinot.apache.org/functions/string/levenshtein_distance) | `LEVENSTEINDISTANCE(str1, str2)` | INT | Levenshtein edit distance | Both | *** ## Math Functions For full details, see [Math Functions](https://docs.pinot.apache.org/functions/math). | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------- | ----------------------------- | ----------- | ------------------------------------------------- | ------ | | [`ABS`](https://docs.pinot.apache.org/functions/math/abs) | `ABS(val)` | DOUBLE | Absolute value | Both | | `CEIL` / `CEILING` | `CEIL(val)` | DOUBLE | Rounds up to nearest integer | Both | | [`FLOOR`](https://docs.pinot.apache.org/functions/math/floor) | `FLOOR(val)` | DOUBLE | Rounds down to nearest integer | Both | | [`EXP`](https://docs.pinot.apache.org/functions/math/exp) | `EXP(val)` | DOUBLE | Euler's number raised to the power | Both | | `LN` / `LOG` | `LN(val)` | DOUBLE | Natural logarithm | Both | | `LOG2` | `LOG2(val)` | DOUBLE | Base-2 logarithm | Both | | `LOG10` | `LOG10(val)` | DOUBLE | Base-10 logarithm | Both | | [`SQRT`](https://docs.pinot.apache.org/functions/math/sqrt) | `SQRT(val)` | DOUBLE | Square root | Both | | `SIGN` | `SIGN(val)` | DOUBLE | Sign of a number (-1, 0, 1) | Both | | `POW` / `POWER` | `POW(base, exp)` | DOUBLE | Raises base to exponent | Both | | [`MOD`](https://docs.pinot.apache.org/functions/math/mod) | `MOD(a, b)` | DOUBLE | Modulo operation | Both | | `ROUNDDECIMAL` | `ROUNDDECIMAL(val [, scale])` | DOUBLE | Rounds to specified decimal places | Both | | `TRUNCATE` | `TRUNCATE(val [, scale])` | DOUBLE | Truncates to specified decimal places | Both | | `DIV` / `DIVIDE` | `DIV(a, b [, default])` | DOUBLE | Division with optional default for divide-by-zero | Both | | `INTDIV` | `INTDIV(a, b)` | LONG | Integer division | Both | | `LEAST` | `LEAST(a, b)` | DOUBLE | Returns the smaller of two values | Both | | `GREATEST` | `GREATEST(a, b)` | DOUBLE | Returns the larger of two values | Both | | `GCD` | `GCD(a, b)` | LONG | Greatest common divisor | Both | | `LCM` | `LCM(a, b)` | LONG | Least common multiple | Both | | `HYPOT` | `HYPOT(a, b)` | DOUBLE | Hypotenuse (sqrt(a^2 + b^2)) | Both | | `NEGATE` | `NEGATE(val)` | DOUBLE | Negates the value | Both | | `RAND` | `RAND([seed])` | DOUBLE | Random number between 0 and 1 | Both | | `SIN` | `SIN(val)` | DOUBLE | Sine | Both | | `COS` | `COS(val)` | DOUBLE | Cosine | Both | | `TAN` | `TAN(val)` | DOUBLE | Tangent | Both | | `COT` | `COT(val)` | DOUBLE | Cotangent | Both | | `ASIN` | `ASIN(val)` | DOUBLE | Inverse sine | Both | | `ACOS` | `ACOS(val)` | DOUBLE | Inverse cosine | Both | | `ATAN` | `ATAN(val)` | DOUBLE | Inverse tangent | Both | | `ATAN2` | `ATAN2(y, x)` | DOUBLE | Two-argument inverse tangent | Both | | `DEGREES` | `DEGREES(radians)` | DOUBLE | Converts radians to degrees | Both | | `RADIANS` | `RADIANS(degrees)` | DOUBLE | Converts degrees to radians | Both | *** ## DateTime Functions For full details, see [DateTime Functions](https://docs.pinot.apache.org/functions/datetime). | Function | Signature | Return Type | Description | Engine | | -------------------------------------------------------------------------------------------- | -------------------------------------------------------------- | ----------- | -------------------------------------------- | ------ | | [`NOW`](https://docs.pinot.apache.org/functions/datetime/now) | `NOW()` | LONG | Current timestamp in milliseconds | Both | | [`AGO`](https://docs.pinot.apache.org/functions/datetime/ago) | `AGO('period')` | LONG | Timestamp for a period in the past | Both | | [`TODATETIME`](https://docs.pinot.apache.org/functions/datetime/todatetime) | `TODATETIME(millis, pattern [, tz])` | STRING | Converts epoch millis to formatted string | Both | | [`FROMDATETIME`](https://docs.pinot.apache.org/functions/datetime/fromdatetime) | `FROMDATETIME(str, pattern [, tz])` | LONG | Parses datetime string to epoch millis | Both | | [`TOEPOCHSECONDS`](https://docs.pinot.apache.org/functions/datetime/toepoch) | `TOEPOCHSECONDS(millis)` | LONG | Converts millis to epoch seconds | Both | | [`TOEPOCHMINUTES`](https://docs.pinot.apache.org/functions/datetime/toepoch) | `TOEPOCHMINUTES(millis)` | LONG | Converts millis to epoch minutes | Both | | [`TOEPOCHHOURS`](https://docs.pinot.apache.org/functions/datetime/toepoch) | `TOEPOCHHOURS(millis)` | LONG | Converts millis to epoch hours | Both | | [`TOEPOCHDAYS`](https://docs.pinot.apache.org/functions/datetime/toepoch) | `TOEPOCHDAYS(millis)` | LONG | Converts millis to epoch days | Both | | [`FROMEPOCHSECONDS`](https://docs.pinot.apache.org/functions/datetime/fromepoch) | `FROMEPOCHSECONDS(seconds)` | LONG | Converts epoch seconds to millis | Both | | [`FROMEPOCHMINUTES`](https://docs.pinot.apache.org/functions/datetime/fromepoch) | `FROMEPOCHMINUTES(minutes)` | LONG | Converts epoch minutes to millis | Both | | [`FROMEPOCHHOURS`](https://docs.pinot.apache.org/functions/datetime/fromepoch) | `FROMEPOCHHOURS(hours)` | LONG | Converts epoch hours to millis | Both | | [`FROMEPOCHDAYS`](https://docs.pinot.apache.org/functions/datetime/fromepoch) | `FROMEPOCHDAYS(days)` | LONG | Converts epoch days to millis | Both | | [`TOEPOCHSECONDSROUNDED`](https://docs.pinot.apache.org/functions/datetime/toepochrounded) | `TOEPOCHSECONDSROUNDED(millis, roundTo)` | LONG | Converts millis to rounded epoch seconds | Both | | [`TOEPOCHMINUTESROUNDED`](https://docs.pinot.apache.org/functions/datetime/toepochrounded) | `TOEPOCHMINUTESROUNDED(millis, roundTo)` | LONG | Converts millis to rounded epoch minutes | Both | | [`TOEPOCHHOURSROUNDED`](https://docs.pinot.apache.org/functions/datetime/toepochrounded) | `TOEPOCHHOURSROUNDED(millis, roundTo)` | LONG | Converts millis to rounded epoch hours | Both | | [`TOEPOCHSECONDSBUCKET`](https://docs.pinot.apache.org/functions/datetime/toepochbucket) | `TOEPOCHSECONDSBUCKET(millis, bucket)` | LONG | Buckets millis into epoch second intervals | Both | | [`FROMEPOCHSECONDSBUCKET`](https://docs.pinot.apache.org/functions/datetime/fromepochbucket) | `FROMEPOCHSECONDSBUCKET(seconds, bucket)` | LONG | Converts bucketed epoch seconds to millis | Both | | [`DATETRUNC`](https://docs.pinot.apache.org/functions/datetime/datetrunc) | `DATETRUNC(unit, timeVal [, inputUnit [, tz [, outputUnit]]])` | LONG | Truncates timestamp to specified granularity | Both | | `DATEBIN` | `DATEBIN(binWidth, sourceTs, originTs)` | TIMESTAMP | Bins a timestamp into intervals | Both | | `TIMESTAMPADD` / `DATEADD` | `TIMESTAMPADD(unit, interval, ts)` | LONG | Adds interval to timestamp | Both | | `TIMESTAMPDIFF` / `DATEDIFF` | `TIMESTAMPDIFF(unit, ts1, ts2)` | LONG | Difference between two timestamps | Both | | [`EXTRACT`](https://docs.pinot.apache.org/functions/datetime/extract) | `EXTRACT(field FROM ts)` | INT | Extracts date/time field from timestamp | Both | | [`YEAR`](https://docs.pinot.apache.org/functions/datetime/year) | `YEAR(millis [, tz])` | INT | Extracts year | Both | | [`QUARTER`](https://docs.pinot.apache.org/functions/datetime/quarter) | `QUARTER(millis [, tz])` | INT | Extracts quarter (1-4) | Both | | [`MONTH`](https://docs.pinot.apache.org/functions/datetime/month) | `MONTH(millis [, tz])` | INT | Extracts month (1-12) | Both | | [`WEEK`](https://docs.pinot.apache.org/functions/datetime/week) | `WEEK(millis [, tz])` | INT | Extracts ISO week of year | Both | | [`DAYOFYEAR`](https://docs.pinot.apache.org/functions/datetime/dayofyear) | `DAYOFYEAR(millis [, tz])` | INT | Extracts day of year (1-366) | Both | | `DAYOFMONTH` / `DAY` | `DAYOFMONTH(millis [, tz])` | INT | Extracts day of month (1-31) | Both | | [`DAYOFWEEK`](https://docs.pinot.apache.org/functions/datetime/dayofweek) / `DOW` | `DAYOFWEEK(millis [, tz])` | INT | Extracts day of week (1-7) | Both | | [`HOUR`](https://docs.pinot.apache.org/functions/datetime/hour) | `HOUR(millis [, tz])` | INT | Extracts hour (0-23) | Both | | [`MINUTE`](https://docs.pinot.apache.org/functions/datetime/minute) | `MINUTE(millis [, tz])` | INT | Extracts minute (0-59) | Both | | [`SECOND`](https://docs.pinot.apache.org/functions/datetime/second) | `SECOND(millis [, tz])` | INT | Extracts second (0-59) | Both | | [`MILLISECOND`](https://docs.pinot.apache.org/functions/datetime/millisecond) | `MILLISECOND(millis [, tz])` | INT | Extracts millisecond (0-999) | Both | | [`YEAROFWEEK`](https://docs.pinot.apache.org/functions/datetime/yearofweek) | `YEAROFWEEK(millis [, tz])` | INT | Extracts ISO year-of-week | Both | | [`TIMEZONEHOUR`](https://docs.pinot.apache.org/functions/datetime/timezonehour) | `TIMEZONEHOUR(tzId [, millis])` | INT | UTC offset hours for timezone | Both | | [`TIMEZONEMINUTE`](https://docs.pinot.apache.org/functions/datetime/timezoneminute) | `TIMEZONEMINUTE(tzId [, millis])` | INT | UTC offset minutes for timezone | Both | | `TOTIMESTAMP` | `TOTIMESTAMP(millis)` | TIMESTAMP | Converts millis to SQL Timestamp | Both | | `FROMTIMESTAMP` | `FROMTIMESTAMP(ts)` | LONG | Converts SQL Timestamp to millis | Both | | [`DATETIMECONVERT`](https://docs.pinot.apache.org/functions/datetime/datetimeconvert) | `DATETIMECONVERT(col, inFormat, outFormat, granularity)` | STRING/LONG | Converts datetime between formats | Both | *** ## JSON Functions For full details, see [JSON Functions](https://docs.pinot.apache.org/functions/json). | Function | Signature | Return Type | Description | Engine | | ----------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------- | ----------- | -------------------------------------- | ------ | | [`JSONPATH`](https://docs.pinot.apache.org/functions/json/jsonpath) | `JSONPATH(obj, path)` | OBJECT | Evaluates JSONPath expression | Both | | [`JSONPATHSTRING`](https://docs.pinot.apache.org/functions/json/jsonpathstring) | `JSONPATHSTRING(obj, path [, default])` | STRING | Extracts string via JSONPath | Both | | [`JSONPATHLONG`](https://docs.pinot.apache.org/functions/json/jsonpathlong) | `JSONPATHLONG(obj, path [, default])` | LONG | Extracts long via JSONPath | Both | | [`JSONPATHDOUBLE`](https://docs.pinot.apache.org/functions/json/jsonpathdouble) | `JSONPATHDOUBLE(obj, path [, default])` | DOUBLE | Extracts double via JSONPath | Both | | [`JSONPATHARRAY`](https://docs.pinot.apache.org/functions/json/jsonpatharray) | `JSONPATHARRAY(obj, path)` | OBJECT\[] | Extracts array via JSONPath | Both | | [`JSONPATHARRAYDEFAULTEMPTY`](https://docs.pinot.apache.org/functions/json/jsonpatharraydefaultempty) | `JSONPATHARRAYDEFAULTEMPTY(obj, path)` | OBJECT\[] | Extracts array, returns empty if null | Both | | [`JSONPATHEXISTS`](https://docs.pinot.apache.org/functions/json/jsonpathexists) | `JSONPATHEXISTS(obj, path)` | BOOLEAN | Checks if JSONPath exists | Both | | [`JSONKEYVALUEARRAYTOMAP`](https://docs.pinot.apache.org/functions/json/jsonkeyvaluearraytomap) | `JSONKEYVALUEARRAYTOMAP(arr [, keyColumnName, valueColumnName])` | MAP | Converts key/value-object array to map | Both | | [`JSONEXTRACTKEY`](https://docs.pinot.apache.org/functions/json/jsonextractkey) | `JSONEXTRACTKEY(obj, path)` | STRING\[] | Extracts keys at JSONPath | Both | | [`JSONEXTRACTSCALAR`](https://docs.pinot.apache.org/functions/json/jsonextractscalar) | `JSONEXTRACTSCALAR(json, path, type [, default])` | varies | Extracts scalar value from JSON | Both | | [`JSONFORMAT`](https://docs.pinot.apache.org/functions/json/jsonformat) | `JSONFORMAT(obj)` | STRING | Serializes object to JSON string | Both | | [`TOJSONMAPSTR`](https://docs.pinot.apache.org/functions/json/tojsonmapstr) | `TOJSONMAPSTR(map)` | STRING | Converts map to JSON string | Both | | `JSONSTRINGTOARRAY` | `JSONSTRINGTOARRAY(jsonStr)` | LIST | Parses JSON string to array | Both | | `JSONSTRINGTOMAP` | `JSONSTRINGTOMAP(jsonStr)` | MAP | Parses JSON string to map | Both | | `ISJSON` | `ISJSON(str)` | BOOLEAN | Checks if string is valid JSON | Both | *** ## Array Functions For full details, see [Array Functions](https://docs.pinot.apache.org/functions/array). | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------------------------------------ | ----------------------------------------- | ----------- | ------------------------------------------------ | ------ | | `ARRAY_AGG` | `ARRAY_AGG(col, 'type' [, distinct])` | ARRAY | Aggregates values into an array | Both | | [`LISTAGG`](https://docs.pinot.apache.org/functions/aggregation/listagg) | `LISTAGG(col [, delimiter [, distinct]])` | STRING | Aggregates values into a delimited string | Both | | [`ARRAYLENGTH`](https://docs.pinot.apache.org/functions/array/arraylength) | `ARRAYLENGTH(arr)` | INT | Returns the length of an array | Both | | [`ARRAYCONTAINSINT`](https://docs.pinot.apache.org/functions/array/arraycontainsint) | `ARRAYCONTAINSINT(arr, val)` | BOOLEAN | Checks if integer array contains value | Both | | [`ARRAYCONTAINSSTRING`](https://docs.pinot.apache.org/functions/array/arraycontainsstring) | `ARRAYCONTAINSSTRING(arr, val)` | BOOLEAN | Checks if string array contains value | Both | | [`ARRAYCONCATINT`](https://docs.pinot.apache.org/functions/array/arrayconcatint) | `ARRAYCONCATINT(arr1, arr2)` | INT\[] | Concatenates two integer arrays | Both | | [`ARRAYCONCATSTRING`](https://docs.pinot.apache.org/functions/array/arrayconcatstring) | `ARRAYCONCATSTRING(arr1, arr2)` | STRING\[] | Concatenates two string arrays | Both | | [`ARRAYDISTINCTINT`](https://docs.pinot.apache.org/functions/array/arraydistinctint) | `ARRAYDISTINCTINT(arr)` | INT\[] | Removes duplicates from integer array | Both | | [`ARRAYDISTINCTSTRING`](https://docs.pinot.apache.org/functions/array/arraydistinctstring) | `ARRAYDISTINCTSTRING(arr)` | STRING\[] | Removes duplicates from string array | Both | | [`ARRAYINDEXOFINT`](https://docs.pinot.apache.org/functions/array/arrayindexofint) | `ARRAYINDEXOFINT(arr, val)` | INT | Index of value in integer array | Both | | [`ARRAYINDEXOFSTRING`](https://docs.pinot.apache.org/functions/array/arrayindexofstring) | `ARRAYINDEXOFSTRING(arr, val)` | INT | Index of value in string array | Both | | [`ARRAYPUSHBACKINT`](https://docs.pinot.apache.org/functions/array/arraypushback) | `ARRAYPUSHBACKINT(arr, val)` | INT\[] | Appends an integer to the end of an array | Both | | [`ARRAYPUSHBACKLONG`](https://docs.pinot.apache.org/functions/array/arraypushback) | `ARRAYPUSHBACKLONG(arr, val)` | LONG\[] | Appends a long to the end of an array | Both | | [`ARRAYPUSHBACKFLOAT`](https://docs.pinot.apache.org/functions/array/arraypushback) | `ARRAYPUSHBACKFLOAT(arr, val)` | FLOAT\[] | Appends a float to the end of an array | Both | | [`ARRAYPUSHBACKDOUBLE`](https://docs.pinot.apache.org/functions/array/arraypushback) | `ARRAYPUSHBACKDOUBLE(arr, val)` | DOUBLE\[] | Appends a double to the end of an array | Both | | [`ARRAYPUSHBACKSTRING`](https://docs.pinot.apache.org/functions/array/arraypushback) | `ARRAYPUSHBACKSTRING(arr, val)` | STRING\[] | Appends a string to the end of an array | Both | | [`ARRAYPUSHFRONTINT`](https://docs.pinot.apache.org/functions/array/arraypushfront) | `ARRAYPUSHFRONTINT(arr, val)` | INT\[] | Prepends an integer to the beginning of an array | Both | | [`ARRAYPUSHFRONTLONG`](https://docs.pinot.apache.org/functions/array/arraypushfront) | `ARRAYPUSHFRONTLONG(arr, val)` | LONG\[] | Prepends a long to the beginning of an array | Both | | [`ARRAYPUSHFRONTFLOAT`](https://docs.pinot.apache.org/functions/array/arraypushfront) | `ARRAYPUSHFRONTFLOAT(arr, val)` | FLOAT\[] | Prepends a float to the beginning of an array | Both | | [`ARRAYPUSHFRONTDOUBLE`](https://docs.pinot.apache.org/functions/array/arraypushfront) | `ARRAYPUSHFRONTDOUBLE(arr, val)` | DOUBLE\[] | Prepends a double to the beginning of an array | Both | | [`ARRAYPUSHFRONTSTRING`](https://docs.pinot.apache.org/functions/array/arraypushfront) | `ARRAYPUSHFRONTSTRING(arr, val)` | STRING\[] | Prepends a string to the beginning of an array | Both | | [`ARRAYREMOVEINT`](https://docs.pinot.apache.org/functions/array/arrayremoveint) | `ARRAYREMOVEINT(arr, val)` | INT\[] | Removes first occurrence from integer array | Both | | [`ARRAYREMOVESTRING`](https://docs.pinot.apache.org/functions/array/arrayremovestring) | `ARRAYREMOVESTRING(arr, val)` | STRING\[] | Removes first occurrence from string array | Both | | [`ARRAYREVERSEINT`](https://docs.pinot.apache.org/functions/array/arrayreverseint) | `ARRAYREVERSEINT(arr)` | INT\[] | Reverses integer array | Both | | [`ARRAYREVERSESTRING`](https://docs.pinot.apache.org/functions/array/arrayreversestring) | `ARRAYREVERSESTRING(arr)` | STRING\[] | Reverses string array | Both | | [`ARRAYSLICEINT`](https://docs.pinot.apache.org/functions/array/arraysliceint) | `ARRAYSLICEINT(arr, start, end)` | INT\[] | Extracts subarray from integer array | Both | | [`ARRAYSLICESTRING`](https://docs.pinot.apache.org/functions/array/arrayslicestring) | `ARRAYSLICESTRING(arr, start, end)` | STRING\[] | Extracts subarray from string array | Both | | [`ARRAYSORTINT`](https://docs.pinot.apache.org/functions/array/arraysortint) | `ARRAYSORTINT(arr)` | INT\[] | Sorts integer array ascending | Both | | [`ARRAYSORTSTRING`](https://docs.pinot.apache.org/functions/array/arraysortstring) | `ARRAYSORTSTRING(arr)` | STRING\[] | Sorts string array ascending | Both | | [`ARRAYUNIONINT`](https://docs.pinot.apache.org/functions/array/arrayunionint) | `ARRAYUNIONINT(arr1, arr2)` | INT\[] | Union of two integer arrays (unique) | Both | | [`ARRAYUNIONSTRING`](https://docs.pinot.apache.org/functions/array/arrayunionstring) | `ARRAYUNIONSTRING(arr1, arr2)` | STRING\[] | Union of two string arrays (unique) | Both | | [`ARRAYS_OVERLAP`](https://docs.pinot.apache.org/functions/array/arraysoverlap) | `ARRAYS_OVERLAP(arr1, arr2)` | BOOLEAN | True if same-type arrays share any element | Both | | `ARRAYSUMINT` | `ARRAYSUMINT(arr)` | INT | Sum of integer array elements | Both | | `ARRAYSUMLONG` | `ARRAYSUMLONG(arr)` | LONG | Sum of long array elements | Both | | [`ARRAYTOSTRING`](https://docs.pinot.apache.org/functions/array/arraytostring) | `ARRAYTOSTRING(arr, delimiter [, null])` | STRING | Joins string array elements into one string | Both | | [`SUMARRAYLONG`](https://docs.pinot.apache.org/functions/aggregation/sumarraylong) | `SUMARRAYLONG(arrCol)` | LONG | Aggregate: sums all elements across rows | Both | | [`SUMARRAYDOUBLE`](https://docs.pinot.apache.org/functions/aggregation/sumarraydouble) | `SUMARRAYDOUBLE(arrCol)` | DOUBLE | Aggregate: sums all elements across rows | Both | *** ## Hash Functions For full details, see [Hash Functions](https://docs.pinot.apache.org/functions/hash). | Function | Signature | Return Type | Description | Engine | | ------------------- | -------------------------------- | ----------- | ------------------------------ | ------ | | `SHA` | `SHA(bytes)` | STRING | SHA-1 hash | Both | | `SHA224` | `SHA224(bytes)` | STRING | SHA-224 hash | Both | | `SHA256` | `SHA256(bytes)` | STRING | SHA-256 hash | Both | | `SHA512` | `SHA512(bytes)` | STRING | SHA-512 hash | Both | | `MD2` | `MD2(bytes)` | STRING | MD2 hash | Both | | `MD5` | `MD5(bytes)` | STRING | MD5 hash | Both | | `MURMURHASH2` | `MURMURHASH2(bytes)` | INT | 32-bit MurmurHash2 | Both | | `MURMURHASH2UTF8` | `MURMURHASH2UTF8(str)` | INT | 32-bit MurmurHash2 for strings | Both | | `MURMURHASH3BIT32` | `MURMURHASH3BIT32(bytes, seed)` | INT | 32-bit MurmurHash3 | Both | | `MURMURHASH3BIT64` | `MURMURHASH3BIT64(bytes, seed)` | LONG | 64-bit MurmurHash3 | Both | | `MURMURHASH3BIT128` | `MURMURHASH3BIT128(bytes, seed)` | BYTES | 128-bit MurmurHash3 | Both | | `CRC32` | `CRC32(bytes)` | INT | 32-bit CRC checksum | Both | | `CRC32C` | `CRC32C(bytes)` | INT | 32-bit CRC32C (Castagnoli) | Both | | `ADLER32` | `ADLER32(bytes)` | INT | 32-bit Adler checksum | Both | *** ## URL Functions For full details, see [URL Functions](https://docs.pinot.apache.org/functions/url). | Function | Signature | Return Type | Description | Engine | | ---------------------- | -------------------------------- | ----------- | ---------------------------------- | ------ | | `URLPROTOCOL` | `URLPROTOCOL(url)` | STRING | Extracts protocol/scheme | Both | | `URLDOMAIN` | `URLDOMAIN(url)` | STRING | Extracts domain | Both | | `URLDOMAINWITHOUTWWW` | `URLDOMAINWITHOUTWWW(url)` | STRING | Extracts domain without www prefix | Both | | `URLTOPLEVELDOMAIN` | `URLTOPLEVELDOMAIN(url)` | STRING | Extracts top-level domain | Both | | `URLPORT` | `URLPORT(url)` | INT | Extracts port number | Both | | `URLPATH` | `URLPATH(url)` | STRING | Extracts path component | Both | | `URLQUERYSTRING` | `URLQUERYSTRING(url)` | STRING | Extracts query string | Both | | `URLFRAGMENT` | `URLFRAGMENT(url)` | STRING | Extracts fragment identifier | Both | | `EXTRACTURLPARAMETER` | `EXTRACTURLPARAMETER(url, name)` | STRING | Extracts specific query parameter | Both | | `EXTRACTURLPARAMETERS` | `EXTRACTURLPARAMETERS(url)` | STRING\[] | Extracts all query parameters | Both | | `URLENCODE` | `URLENCODE(url)` | STRING | URL-encodes a string | Both | | `URLDECODE` | `URLDECODE(url)` | STRING | Decodes URL-encoded string | Both | | `CUTWWW` | `CUTWWW(url)` | STRING | Removes www prefix from URL | Both | | `CUTQUERYSTRING` | `CUTQUERYSTRING(url)` | STRING | Removes query string from URL | Both | | `CUTFRAGMENT` | `CUTFRAGMENT(url)` | STRING | Removes fragment from URL | Both | | `CUTURLPARAMETER` | `CUTURLPARAMETER(url, name)` | STRING | Removes specific query parameter | Both | *** ## Binary Functions For full details, see [Binary Functions](https://docs.pinot.apache.org/functions/binary). | Function | Signature | Return Type | Description | Engine | | -------------- | --------------------- | ----------- | ------------------------------ | ------ | | `TOUTF8` | `TOUTF8(str)` | BYTES | Converts string to UTF-8 bytes | Both | | `FROMUTF8` | `FROMUTF8(bytes)` | STRING | Converts UTF-8 bytes to string | Both | | `TOASCII` | `TOASCII(str)` | BYTES | Converts string to ASCII bytes | Both | | `FROMASCII` | `FROMASCII(bytes)` | STRING | Converts ASCII bytes to string | Both | | `TOBASE64` | `TOBASE64(bytes)` | STRING | Encodes bytes as Base64 string | Both | | `FROMBASE64` | `FROMBASE64(str)` | BYTES | Decodes Base64 string to bytes | Both | | `BASE64ENCODE` | `BASE64ENCODE(bytes)` | BYTES | Base64 encodes byte array | Both | | `BASE64DECODE` | `BASE64DECODE(bytes)` | BYTES | Base64 decodes byte array | Both | | `HEXTOBYTES` | `HEXTOBYTES(hex)` | BYTES | Converts hex string to bytes | Both | | `BYTESTOHEX` | `BYTESTOHEX(bytes)` | STRING | Converts bytes to hex string | Both | *** ## Geospatial Functions For full details, see [GeoSpatial Functions](https://docs.pinot.apache.org/functions/geospatial). | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------------------------------------------- | ---------------------------------- | ----------- | ------------------------------------------------ | ------ | | [`STPOINT`](https://docs.pinot.apache.org/functions/geospatial/stpoint) | `STPOINT(lng, lat)` | BYTES | Creates a geometry point | Both | | [`STPOLYGON`](https://docs.pinot.apache.org/functions/geospatial/stpolygon) | `STPOLYGON(wkt)` | BYTES | Creates a polygon from WKT | Both | | [`STDISTANCE`](https://docs.pinot.apache.org/functions/geospatial/stdistance) | `STDISTANCE(geo1, geo2)` | DOUBLE | Distance between two geometries | Both | | [`STCONTAINS`](https://docs.pinot.apache.org/functions/geospatial/stcontains) | `STCONTAINS(geo1, geo2)` | BOOLEAN | Tests if first geometry contains second | Both | | [`STGEOMFROMTEXT`](https://docs.pinot.apache.org/functions/geospatial/stgeomfromtext) | `STGEOMFROMTEXT(wkt)` | BYTES | Creates geometry from WKT | Both | | [`STGEOMFROMWKB`](https://docs.pinot.apache.org/functions/geospatial/stgeomfromwkb) | `STGEOMFROMWKB(wkb)` | BYTES | Creates geometry from WKB | Both | | [`STGEOMFROMGEOJSON`](https://docs.pinot.apache.org/functions/geospatial/st_geomfromgeojson) | `STGEOMFROMGEOJSON(json)` | BYTES | Creates geometry from GeoJSON | Both | | [`STGEOGFROMTEXT`](https://docs.pinot.apache.org/functions/geospatial/stgeogfromtext) | `STGEOGFROMTEXT(wkt)` | BYTES | Creates geography from WKT | Both | | [`STGEOGFROMWKB`](https://docs.pinot.apache.org/functions/geospatial/stgeogfromwkb) | `STGEOGFROMWKB(wkb)` | BYTES | Creates geography from WKB | Both | | [`STGEOGFROMGEOJSON`](https://docs.pinot.apache.org/functions/geospatial/st_geogfromgeojson) | `STGEOGFROMGEOJSON(json)` | BYTES | Creates geography from GeoJSON | Both | | [`STASTEXT`](https://docs.pinot.apache.org/functions/geospatial/stastext) | `STASTEXT(geo)` | STRING | Converts geometry to WKT | Both | | [`STASBINARY`](https://docs.pinot.apache.org/functions/geospatial/stasbinary) | `STASBINARY(geo)` | BYTES | Converts geometry to WKB | Both | | [`STASGEOJSON`](https://docs.pinot.apache.org/functions/geospatial/st_asgeojson) | `STASGEOJSON(geo)` | STRING | Converts geometry to GeoJSON | Both | | [`STGEOMETRYTYPE`](https://docs.pinot.apache.org/functions/geospatial/stgeometrytype) | `STGEOMETRYTYPE(geo)` | STRING | Returns geometry type | Both | | [`TOSPHERICALGEOGRAPHY`](https://docs.pinot.apache.org/functions/geospatial/tosphericalgeography) | `TOSPHERICALGEOGRAPHY(geo)` | BYTES | Converts geometry to spherical geography | Both | | [`GridDistance`](https://docs.pinot.apache.org/functions/geospatial/griddistance) | `gridDistance(h3Index1, h3Index2)` | LONG | H3 grid distance between two cells | Both | | [`GridDisk`](https://docs.pinot.apache.org/functions/geospatial/griddisk) | `gridDisk(h3Index, k)` | LONG\[] | H3 cells within `k` grid steps of a center cell | Both | | [`IDSET`](https://docs.pinot.apache.org/functions/aggregation/idset) | `IDSET(col [, params])` | BYTES | Serialized IdSet for use with IN\_ID\_SET filter | Both | | [`STUNION`](https://docs.pinot.apache.org/functions/geospatial/stunion) | `STUNION(geoCol)` | BYTES | Aggregation: union of geometries | Both | *** ## Vector / Similarity Functions For full details, see [Vector Functions](https://docs.pinot.apache.org/functions/vector). | Function | Signature | Return Type | Description | Engine | | ---------------- | ---------------------------- | ----------- | -------------------------------------- | ------ | | `COSINEDISTANCE` | `COSINEDISTANCE(arr1, arr2)` | DOUBLE | Cosine distance between two vectors | Both | | `INNERPRODUCT` | `INNERPRODUCT(arr1, arr2)` | DOUBLE | Inner (dot) product of two vectors | Both | | `L1DISTANCE` | `L1DISTANCE(arr1, arr2)` | DOUBLE | Manhattan distance between two vectors | Both | | `L2DISTANCE` | `L2DISTANCE(arr1, arr2)` | DOUBLE | Euclidean distance between two vectors | Both | | `VECTORDIMS` | `VECTORDIMS(arr)` | INT | Number of dimensions in a vector | Both | | `VECTORNORM` | `VECTORNORM(arr)` | DOUBLE | L2 norm (magnitude) of a vector | Both | *** ## IP Address Functions For full details, see [IP Address Functions](https://docs.pinot.apache.org/functions/ip-address). | Function | Signature | Return Type | Description | Engine | | ----------------------------------------------------------------------- | --------------------------- | ----------- | ---------------------------------- | ------ | | [`ISSUBNETOF`](https://docs.pinot.apache.org/functions/misc/issubnetof) | `ISSUBNETOF(prefix, addr)` | BOOLEAN | Checks if IP address is in subnet | Both | | `IPPREFIX` | `IPPREFIX(addr, prefixLen)` | STRING | Returns CIDR prefix for IP address | Both | | `IPSUBNETMIN` | `IPSUBNETMIN(prefix)` | STRING | Returns lowest IP in subnet | Both | | `IPSUBNETMAX` | `IPSUBNETMAX(prefix)` | STRING | Returns highest IP in subnet | Both | *** ## Null Handling Functions For full details, see [Null Handling Functions](https://docs.pinot.apache.org/functions/null-handling). | Function | Signature | Return Type | Description | Engine | | ---------------------- | --------------------------------- | ----------- | -------------------------------- | ------ | | `IS NULL` | `col IS NULL` | BOOLEAN | True if value is null | Both | | `IS NOT NULL` | `col IS NOT NULL` | BOOLEAN | True if value is not null | Both | | `COALESCE` | `COALESCE(val1, val2, ...)` | varies | Returns first non-null value | Both | | `NULLIF` | `NULLIF(val1, val2)` | varies | Returns null if values are equal | Both | | `IS DISTINCT FROM` | `a IS DISTINCT FROM b` | BOOLEAN | Null-safe inequality comparison | Both | | `IS NOT DISTINCT FROM` | `a IS NOT DISTINCT FROM b` | BOOLEAN | Null-safe equality comparison | Both | | `CASE WHEN` | `CASE WHEN cond THEN val ... END` | varies | Conditional expression | Both | *** ## Type Conversion Functions For full details, see [Type Conversion Functions](https://docs.pinot.apache.org/functions/type-conversion). | Function | Signature | Return Type | Description | Engine | | ------------------- | ---------------------------- | ----------- | -------------------------------- | ------ | | `CAST` | `CAST(val AS type)` | varies | Converts value to specified type | Both | | `BIGDECIMALTOBYTES` | `BIGDECIMALTOBYTES(decimal)` | BYTES | Converts BigDecimal to bytes | Both | | `BYTESTOBIGDECIMAL` | `BYTESTOBIGDECIMAL(bytes)` | BIGDECIMAL | Converts bytes to BigDecimal | Both | | `HEXDECIMALTOLONG` | `HEXDECIMALTOLONG(hex)` | LONG | Converts hex string to long | Both | | `LONGTOHEXDECIMAL` | `LONGTOHEXDECIMAL(val)` | STRING | Converts long to hex string | Both | | `TOUUIDBYTES` | `TOUUIDBYTES(uuid)` | BYTES | Converts UUID string to bytes | Both | | `FROMUUIDBYTES` | `FROMUUIDBYTES(bytes)` | STRING | Converts bytes to UUID string | Both | *** ## Window Functions For full details, see [Window Functions](https://docs.pinot.apache.org/functions/window). {% hint style="info" %} Window functions require the [multi-stage engine (MSE)](https://docs.pinot.apache.org/reference/configuration-reference/cluster). {% endhint %} | Function | Signature | Return Type | Description | Engine | | ------------------------------------------------------------------------- | --------------------------------------------- | ----------- | -------------------------------------- | ------ | | [`ROW_NUMBER`](https://docs.pinot.apache.org/functions/window/row_number) | `ROW_NUMBER() OVER (...)` | LONG | Sequential row number within partition | MSE | | [`RANK`](https://docs.pinot.apache.org/functions/window/rank) | `RANK() OVER (...)` | LONG | Rank with gaps for ties | MSE | | [`DENSE_RANK`](https://docs.pinot.apache.org/functions/window/dense_rank) | `DENSE_RANK() OVER (...)` | LONG | Rank without gaps for ties | MSE | | `LAG` | `LAG(col [, offset [, default]]) OVER (...)` | varies | Value from a preceding row | MSE | | `LEAD` | `LEAD(col [, offset [, default]]) OVER (...)` | varies | Value from a following row | MSE | | `FIRST_VALUE` | `FIRST_VALUE(col) OVER (...)` | varies | First value in the window frame | MSE | | `LAST_VALUE` | `LAST_VALUE(col) OVER (...)` | varies | Last value in the window frame | MSE | | [`SUM`](https://docs.pinot.apache.org/functions/aggregation/sum) | `SUM(col) OVER (...)` | DOUBLE | Running/windowed sum | MSE | | [`AVG`](https://docs.pinot.apache.org/functions/aggregation/avg) | `AVG(col) OVER (...)` | DOUBLE | Running/windowed average | MSE | | [`MIN`](https://docs.pinot.apache.org/functions/aggregation/min) | `MIN(col) OVER (...)` | DOUBLE | Running/windowed minimum | MSE | | [`MAX`](https://docs.pinot.apache.org/functions/aggregation/max) | `MAX(col) OVER (...)` | DOUBLE | Running/windowed maximum | MSE | | [`COUNT`](https://docs.pinot.apache.org/functions/aggregation/count) | `COUNT(col) OVER (...)` | LONG | Running/windowed count | MSE | | `BOOL_AND` | `BOOL_AND(col) OVER (...)` | BOOLEAN | Windowed boolean AND | MSE | | `BOOL_OR` | `BOOL_OR(col) OVER (...)` | BOOLEAN | Windowed boolean OR | MSE |