This section contains reference documentation for the arrayConcatDouble function.
Concatenates two arrays of doubles.
Signature
arrayConcatDouble('colName1', 'colName2')
Usage Examples
This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type DOUBLE with singleValueField in the table schema set to false.
arrayConcatLong
This section contains reference documentation for the arrayConcatLong function.
Concatenates two arrays of longs.
Signature
arrayConcatLong('colName1', 'colName2')
Usage Examples
This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type LONG with singleValueField in the table schema set to false.
ABS
This section contains reference documentation for the abs function.
Absolute of a value
Signature
ABS(col1)
Usage Examples
value
value
ADD
This section contains reference documentation for the ADD function.
Sum of at least two values
Signature
ADD(col1, col2, col3...)
Usage Examples
These examples are based on the .
homeRuns
baseOnBalls
total
arrayConcatInt
This section contains reference documentation for the arrayConcatInt function.
Concatenates two arrays of ints.
Signature
arrayConcatInt('colName1', 'colName2')
arrayConcatFloat
This section contains reference documentation for the arrayConcatFloat function.
Concatenates two arrays of floats.
Signature
arrayConcatFloat('colName1', 'colName2')
arrayContainsString
This section contains reference documentation for the arrayContainsString function.
Checks if string value exists in array.
Signature
arrayContainsString('colName', valueToFind)
ago
This section contains reference documentation for the ago function.
Return time as epoch millis before the given period (in ISO-8601 duration format).
Examples:
"PT20.345S" -- parses as "20.345 seconds"
"PT15M" -- parses as "15 minutes" (where a minute is 60 seconds)
arrayIndexOfString
This section contains reference documentation for the arrayIndexOfString function.
Finds the last index of the given value in the array starting at the given index.
Signature
arrayIndexOfString('colName', valueToFind)
arrayContainsInt
This section contains reference documentation for the arrayContainsInt function.
Checks if int value exists in array.
Signature
arrayContainsInt('colName', valueToFind)
ARRAYLENGTH
This section contains reference documentation for the ARRAYLENGTH function.
Returns the length of a multi-value column
Signature
ARRAYLENGTH('colName')
select mvCol1,
arrayConcatDouble(mvCol1, mvCol2) AS concatDoubles
from multiValueTable
WHERE arraylength(mvCol1) >= 2
limit 5
select mvCol1,
arrayConcatLong(mvCol1, mvCol2) AS concatLongs
from multiValueTable
WHERE arraylength(mvCol1) >= 2
limit 5
select ABS(-12.1) AS value
from ignoreMe
12.1
select ABS(12.1) AS value
from ignoreMe
12.1
select homeRuns, baseOnBalls, ADD(homeRuns, baseOnBalls) AS total
from baseballStats
WHERE teamID = 'ML1'
AND yearID = 1956
AND playerName = 'Henry Louis'
The count(*) values will increase each time we execute the query as data is constantly being ingested by the Hybrid Quick Start.
select DivWheelsOffs,
arrayConcatInt(DivWheelsOffs, DivWheelsOns) AS concatIds
from airlineStats
WHERE arraylength(DivWheelsOffs) >= 2
limit 5
select ago('P1D') AS oneDayAgo
FROM ignoreMe
SELECT *
FROM tableName
WHERE tsInMillis > ago('P1D')
select DivAirportIDs,
arrayContainsInt(DivAirportIDs, 14683) AS containsValue
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
select ARRAYLENGTH(RandomAirports) AS length, count(*)
from airlineStats
GROUP BY length
ORDER BY count(*) DESC
LIMIT 5
arrayDistinctInt
This section contains reference documentation for the arrayDistinctInt function.
Returns unique values in an array of ints.
Signature
arrayDistinctInt('colName')
Usage Examples
These examples are based on the .
DivAirportIDs
unique
EXPR_MIN / EXPR_MAX
This section contains reference documentation for the EXPR_MIN and EXPR_MAX function.
This function scans the given dataset to identify the maximum and minimum values in the specified measuring columns. Once these extreme values (the maxima and minima) are found, the function locates the corresponding entries in the projection column. These entries are associated with the rows where the extreme values were found in the measuring columns. The function then returns these projection column values, providing a way to link the extreme measurements with their corresponding data in another part of the dataset.
Find the user with maximum activity. If there are multiple users, break the tie with their last_activity_date. If still a tie, break with user_id. And project user_id.
More useful is that this multiple such aggregation function can be used with GROUP BY
Note:
In cases where multiple rows share the same extreme values in the measuring columns, all such rows will be returned by the function.
If the goal is to project multiple different columns that correspond to the same set of measuring columns, you can achieve this by invoking the function multiple times, each time specifying a different projection column.
This impl does not work with AS clause (e.g. SELECT exprmin(longCol, doubleCol) AS exprmin won't work)
For more detailed examples, see:
arrayIndexOfInt
This section contains reference documentation for the arrayIndexOfInt function.
Finds the last index of the given value in the array starting at the given index.
Signature
arrayIndexOfInt('colName', valueToFind)
Usage Examples
These examples are based on the .
DivAirportIDs
index
arrayConcatString
This section contains reference documentation for the arrayConcatString function.
Concatenates two arrays of strings.
Signature
arrayConcatString('colName1', 'colName2')
Usage Examples
These examples are based on the .
DivTailNums
concatIds
arrayDistinctString
This section contains reference documentation for the arrayDistinctString function.
Returns unique values in an array of strings.
Signature
arrayDistinctString('colName')
Usage Examples
These examples are based on the .
DivTailNums
unique
Functions
This page contains reference documentation for functions in Apache Pinot.
This page contains reference documentation for functions in Apache Pinot.
AVGMV
This section contains reference documentation for the AVGMV function.
Get the avg of values in a group
Signature
AVGMV(colName)
DISTINCTCOUNTBITMAPMV
This section contains reference documentation for the DISTINCTCOUNTHLLMV function.
Returns an approximate distinct count using HyperLogLog in a group.
Putting exprmin/exprmax column inside order by clause (e.g. SELECT intCol, exprmin(longCol, doubleCol) FROM table GROUP BY intCol ORDER BY exprmin(longCol, doubleCol)) is not supported as semantically ordering multi-column multi-row exprmin/exprmax results doesn't make sense
Currently projecting MV bytes column doesn't work for now due to an issue
select DISTINCTCOUNTHLLMV(DivLongestGTimes) AS value
from airlineStats
where arraylength(DivLongestGTimes) > 1
select DivAirportIDs,
arrayDistinctInt(DivAirportIDs) AS unique
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
SELECT EXPR_MAX(user_id, activity, last_activity_date, user_id)
FROM userEngagmentTable
SELECT user_region, EXPR_MAX(user_id, activity, last_activity_date, user_id),
EXPR_MIN(user_id, user_satisfaction)
FROM userEngagmentTable
GROUP BY user_region
select DivAirportIDs,
arrayIndexOfInt(DivAirportIDs, 14683) AS index
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
select DivTailNums,
arrayConcatString(DivTailNums, DivTailNums) AS concatIds
from airlineStats
WHERE arraylength(DivTailNums) >= 2
limit 5
select DivTailNums,
arrayDistinctString(DivTailNums) AS unique
from airlineStats
WHERE arraylength(DivTailNums) >= 2
limit 5
arrayRemoveInt
This section contains reference documentation for the arrayRemoveInt function.
Removes value from array of ints.
Signature
arrayRemoveInt('colName', value)
Usage Examples
These examples are based on the .
DivAirportIDs
value
arrayRemoveString
This section contains reference documentation for the arrayRemoveString function.
Removes value from array of strings.
Signature
arrayRemoveString('colName', value)
Usage Examples
These examples are based on the .
DivAirportIDs
value
count
This section contains reference documentation for the count function.
Get the count of rows in a group
Signature
COUNT(colName)
Usage Examples
These examples are based on the .
value
DISTINCTAVGMV
This section contains reference documentation for the DISTINCTAVGMV function.
Returns the average of distinct row values in a group
Signature
DISTINCTAVGMV(colName)
Usage Examples
These examples are based on the .
VALUE
CHR
This section contains reference documentation for the CHR function.
the character corresponding to the Unicode codepoint
Signature
CHR(codepoint)
Usage Examples
value
max
This section contains reference documentation for the max function.
Get the maximum value in a group
Signature
MAX(colName)
Usage Examples
These examples are based on the .
value
codepoint
This section contains reference documentation for the CODEPOINT function.
the Unicode codepoint of the first character of the string
Signature
CODEPOINT(col)
Usage Examples
value
MINMAXRANGEMV
This section contains reference documentation for the MINMAXRANGEMV function.
Returns the max - min value in a group
Signature
MINMAXRANGEMV(colName)
Usage Examples
These examples are based on the .
value
DISTINCTSUMMV
This section contains reference documentation for the DISTINCTSUMMV function.
Returns the sum of the distinct row values in a group
Signature
DISTINCTSUMMV(colName)
Usage Examples
These examples are based on the .
VALUE
arrayReverseString
This section contains reference documentation for the arrayReverseString function.
Reverses array of strings.
Signature
arrayReverseString('colName')
arraySliceInt
This section contains reference documentation for the arraySliceInt function.
Returns the values in the array between the start and end positions.
Signature
arraySliceInt('colName', start, end)
arrayUnionInt
This section contains reference documentation for the arrayUnionInt function.
Create a union of two arrays of ints.
Signature
arrayUnionInt('colName1', 'colName2')
FromEpochBucket
This section contains reference documentation for the fromEpochBucket functions.
Convert epoch to epoch milliseconds. e.g. 10 seconds since epoch or 5 minutes since Epoch. The following time units are supported:
SECONDS
MINUTES
arraySortInt
This section contains reference documentation for the arraySortInt function.
Sorts array of ints.
Signature
arraySortInt('colName')
concat
This section contains reference documentation for the concat function.
Concatenate two input strings using the seperator
Signature
CONCAT(col1, col2, seperator)
LASTWITHTIME
This section contains reference documentation for the lastwithtime function.
Returns the value of dataColumn with the largest timeColumn value where:
timeColumn is used to define the time of dataColumn, which can be of type TIMESTAMP, INT, LONG
DISTINCTCOUNTHLLMV
This section contains reference documentation for the DISTINCTCOUNTBITMAPMV function.
Returns the count of distinct row values in a group. This function is accurate for an INT or dictionary encoded column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collision.
Signature
DISTINCTCOUNTBITMAPMV(colName)
FLOOR
This section contains reference documentation for the FLOOR function.
Rounded down to the nearest integer.
Signature
FLOOR(col1)
FromEpoch
This section contains reference documentation for the fromEpoch functions.
Convert epoch to epoch milliseconds. The following time units are supported:
SECONDS
MINUTES
FromDateTime
This section contains reference documentation for the FromDateTime function.
Converts a formatted date-time string to milliseconds, based on the provided .
Signature
FromDateTime(dateTimeString, pattern)
dayOfWeek
This section contains reference documentation for the dayOfWeek function.
Returns the day of the week from the given epoch millis in UTC timezone. The value ranges from 1(Monday) to 7(Sunday).
Signature
dayOfWeek(tsInMillis)
dayOfWeek(tsInMillis, timeZoneId)
arrayUnionString
This section contains reference documentation for the arrayUnionString function.
Create a union of two arrays of strings.
Signature
arrayUnionString('colName1', 'colName2')
minmaxrange
This section contains reference documentation for the minmaxrange function.
Returns the max - min value in a group
Signature
MINMAXRANGE(colName)
COVAR_POP
This section contains reference documentation for the COVAR_POP function.
Returns the population covariance between of 2 numerical columns.
Signatures
COVAR_POP(col1, col2) -> double
min
This section contains reference documentation for the min function.
Get the minimum value in a group
Signature
MIN(colName)
MINMV
This section contains reference documentation for the MINMV function.
Get the minimum value in a group
Signature
MINMV(colName)
lpad
This section contains reference documentation for the LPAD function.
string padded from the left side with pad to reach final size
Signature
LPAD(col, size, pad)
MOD
This section contains reference documentation for the MOD function.
Modulo of two values
Signature
MOD(col1, col2)
ceil
This section contains reference documentation for the CEIL function.
Rounded up to the nearest integer.
Signature
CEIL(col1)
lower
This section contains reference documentation for the lower function.
Converts string to lower case.
Signature
LOWER(col)
length
This section contains reference documentation for the length function.
calculate length of the string
Signature
LENGTH(col)
exp
This section contains reference documentation for the exp function.
Euler’s number(e) raised to the power of col.
Signature
EXP(col1)
ltrim
This section contains reference documentation for the ltrim function.
select MINMV(DivLongestGTimes) AS value
from airlineStats
where arraylength(DivLongestGTimes) > 1
Usage Examples
value
********Hello, World
SELECT LPAD('Hello, World', '20', '*') AS value
FROM ignoreMe
Usage Examples
value
2
value
0
select MOD(12, 5) AS value
from ignoreMe
select MOD(12, 2) AS value
from ignoreMe
Usage Examples
value
13
value
-12
select CEIL(12.1) AS value
from ignoreMe
select CEIL(-12.1) AS value
from ignoreMe
Usage Examples
name
pinot
select LOWER('Pinot') AS name
FROM ignoreMe
Usage Examples
value
5
SELECT length('Pinot') AS value
FROM ignoreMe
Usage Examples
value
2.718281828459045
value
162754.79141900392
select EXP(1) AS value
from ignoreMe
select EXP(12) AS value
from ignoreMe
Usage Examples
notTrimmed
trimmed
" Pinot with spaces "
"Pinot with spaces "
SELECT ' Pinot with spaces ' AS notTrimmed,
ltrim(' Pinot with spaces ') AS trimmed
FROM ignoreMe
select DivAirportIDs,
arrayRemoveInt(DivAirportIDs, 12892) AS value
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
AND arrayContainsInt(DivAirportIDs, 12892) = 1
limit 5
select RandomAirports,
arrayRemoveString(RandomAirports, 'SEA') AS value
from airlineStats
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5
select FromEpochSecondsBucket(1613472303, 1) AS bucket
FROM ignoreMe
select FromEpochSecondsBucket(1613472303, 2) AS bucket
FROM ignoreMe
select FromEpochMinutesBucket(2689120, 10) AS bucket
FROM ignoreMe
select FromEpochHoursBucket(89637, 5) AS bucket
FROM ignoreMe
select FromEpochDaysBucket(1867, 10) AS bucket
FROM ignoreMe
select DivAirportIDs,
arraySortInt(DivAirportIDs) AS sortedIds
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
select LASTWITHTIME(group_name, __metadata$recordTimestamp, 'STRING')
from meetupRsvp
select DISTINCTCOUNTBITMAPMV(DivTailNums) AS value
from airlineStats
where arraylength(DivTailNums) > 1
select FromEpochSeconds(1613472303) AS epochMillis
FROM ignoreMe
select FromEpochMinutes(26891205) AS epochMillis
FROM ignoreMe
select FromEpochHours(448186) AS epochMillis
FROM ignoreMe
select FromEpochDays(18674) AS epochMillis
FROM ignoreMe
SELECT FromDateTime('2019-08-07', 'yyyy-MM-dd') AS epochMillis
FROM ignoreMe
SELECT FromDateTime(
'2019-08-07 3:12:13 PM',
'yyyy-MM-dd hh:mm:ss a'
) AS epochMillis
FROM ignoreMe
SELECT FromDateTime(
'2019-08-07T15:12:13',
'yyyy-MM-dd''T''HH:mm:ss'
) AS epochMillis
FROM ignoreMe
SELECT FromDateTime(
'2019-08-07T07:12:13-0800',
'yyyy-MM-dd''T''HH:mm:ssZ'
) AS epochMillis
FROM ignoreMe
select dayOfWeek(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe
select dow(1639351800000) AS dayOfWeek
FROM ignoreMe
select dow(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe
toBase64 returns Base64 encoded string of input binary data (bytes type).
fromBase64 returns binary data (represented as a Hex string) from Base64-encoded string.
Signature
toBase64(bytesCol)
fromBase64(stringCol)
Usage Examples
For better readability, the following examples converts string hello! into BYTES using function and converts the decoded BYTES into string using .
encoded
decoded
Note that without UTF8 string conversion, returned BYTES will be represented as a Hex string following Pinot's . See the example below.
decoded
Note that the following query will throw compilation error as string is not a valid input type for toBase64.
arraySliceString
This section contains reference documentation for the arraySliceString function.
Returns the values in the array between the start and end positions.
Signature
arraySliceString('colName', start, end)
Usage Examples
These examples are based on the .
FlightNum
airports
RandomAirports
arraySortString
This section contains reference documentation for the arraySortString function.
Sorts array of strings.
Signature
arraySortString('colName')
Usage Examples
These examples are based on the .
FlightNum
sortedAirports
RandomAirports
day
This section contains reference documentation for the day function.
Returns the day of the month from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 31.
Signature
day(tsInMillis)
day(tsInMillis, timeZoneId)
dayOfMonth(tsInMillis)
dayOfMonth(tsInMillis, timeZoneId)
Usage Examples
day
day
day
day
DISTINCTCOUNT
This section contains reference documentation for the DISTINCTCOUNT function.
Returns the count of distinct row values in a group
Signature
DISTINCTCOUNT(colName)
Usage Examples
These examples are based on the .
value
value
MD5
This section contains reference documentation for the MD5 function.
Return MD5 digest of binary column(bytes type) as hex string
Signature
MD5(bytesCol)
Usage Examples
These examples are based on the .
event_id
location
hash
The row returned will be different if you run this example as the data is ingested in real-time.
FrequentStringsSketch
This section contains reference documentation for the FREQUENTSTRINGSSKETCH function.
FREQUENTSTRINGSSKETCH is an estimation data-sketch function which can be used to estimate the frequency of an item. It is based on Apache Datasketches library and returns a serialized sketch object which can be merged with other sketches.
column (required): Name of the column to aggregate on. Needs to be a type which can be cast into 'STRING'.
maxMapSize: This value specifies the maximum physical length of the internal hash map. The maxMapSize must be a power of 2 and the default value is 256.
Usage Example
frequentstringssketch(AirlineID)
Which can be used, for example in Java as:
For more examples on the sketch API, refer to the Datasketches .
arrayReverseInt
This section contains reference documentation for the arrayReverseInt function.
Reverses array of ints.
Signature
arrayReverseInt('colName')
Usage Examples
These examples are based on the .
DivAirportIDs
reversedIds
Histogram
This section contains reference documentation for the HISTOGRAM function.
Returns the count of data points that fall within each bin as a vector. The bins are left-inclusive and right-exclusive, i.e. [a, b), except for the last one which is inclusive on both sides [a, b].
This section contains reference documentation for the DISTINCT function.
Returns the distinct row values in a group
Signature
DISTINCT(colName)
Usage Examples
These examples are based on the .
value
value
DISTINCTCOUNTRAWHLL
This section contains reference documentation for the DISTINCTCOUNTRAWHLL function.
Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.
Signature
DISTINCTCOUNTRAWHLL(colName, log2m)
Usage Examples
These examples are based on the .
value
value
mode
This section contains reference documentation for the mode function.
Get the most frequent value in a group. When multiple modes are present it gives the minimum of all the modes. This behavior can be overridden to get the maximum or the average mode.
Signature
MODE(colName, [reducerType])
Usage Examples
These examples are based on the .
value
value
value
value
minute
This section contains reference documentation for the minute function.
Returns the minute of the hour from the given epoch millis in UTC or specified timezone. The value ranges from 0 to 59.
Signature
minute(tsInMillis)
minute(tsInMillis, timeZoneId)
Usage Examples
minute
minute
isSubnetOf
This section contains reference documentation for the isSubnetOf function.
Takes 2 arguments of type STRING. The first argument is an ipPrefix, and the second argument is a single ipAddress. This function handles both IPv4 and IPv6 arguments.
Returns a boolean value checking if ipAddress is in the subnet of ipPrefix
Signatures
isSubnetOf(ipPrefix, ipAddress) -> boolean
Usage Examples
See the following sample queries where isSubnetOf is used in different parts of the query.
COUNTMV
This section contains reference documentation for the COUNTMV function.
Get the count of rows in a group
Signature
COUNTMV(colName)
Usage Examples
These examples are based on the .
The following query returns the documents that have a DivTailNums with more than one value:
DivTailNums
You can count the number of items in these rows by running the following query:
value
dayOfYear
This section contains reference documentation for the dayOfYear function.
Returns the day of the year from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 366.
Signature
dayOfYear(tsInMillis)
dayOfYear(tsInMillis, timeZoneId)
doy(tsInMillis)
doy(tsInMillis, timeZoneId)
Usage Examples
dayOfYear
dayOfYear
dayOfYear
dayOfYear
DISTINCTCOUNTMV
This section contains reference documentation for the DISTINCTCOUNTMV function.
Returns the count of distinct row values in a group
Signature
DISTINCTCOUNTMV(colName)
Usage Examples
These examples are based on the .
The following query returns the documents that have a DivTailNums with more than one value:
DivTailNums
You can count the distinct number of items in these rows by running the following query:
value
FIRSTWITHTIME
This section contains reference documentation for the firstwithtime function.
Returns the value of dataColumn with the smallest timeColumn value where:
timeColumn is used to define the time of dataColumn, which can be of type TIMESTAMP, INT, LONG
dataType specifies the type for dataColumn, which can be BOOLEAN, INT, LONG, FLOAT, DOUBLE, STRING
Signature
FIRSTWITHTIME(dataColumn, timeColumn, 'dataType')
Example
This example is based on the .
value
millisecond
This section contains reference documentation for the millisecond function.
Returns the millisecond of the second from the given epoch millis in UTC or specified timezone. The value ranges from 0 to 999.
Signature
millisecond(tsInMillis)
millisecond(tsInMillis, timeZoneId)
Usage Examples
millisecond
millisecond
DISTINCTCOUNTHLL
This section contains reference documentation for the DISTINCTCOUNTHLL function.
Returns an approximate distinct count using HyperLogLog. It also takes an optional second argument to configure the log2m for the HyperLogLog.
For accurate distinct counting, see DISTINCTCOUNT.
Signature
DISTINCTCOUNTHLL(colName, log2m)
Usage Examples
These examples are based on the .
value
value
ln
This section contains reference documentation for the ln function.
Natural log of value i.e. ln(col1)
Signature
LN(col1)
Usage Examples
value
value
DISTINCTCOUNTRAWHLLMV
This section contains reference documentation for the DISTINCTCOUNTRAWHLLMV function.
Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.
Signature
DISTINCTCOUNTRAWHLLMV(colName, log2m)
Usage Examples
These examples are based on the .
value
value
MAXMV
This section contains reference documentation for the MAXMV function.
Get the maximum value in a group
Signature
MAXMV(colName)
Usage Examples
These examples are based on the .
value
DISTINCTAVG
This section contains reference documentation for the DISTINCTAVG function.
Returns the average of distinct row values in a group
Signature
DISTINCTAVG(colName) or avg(distinct col)
Usage Examples
These examples are based on the .
VALUE
VALUE
jsonextractkey
This section contains reference documentation for the JSONEXTRACTKEY function.
Extracts all matched JSON field keys based on 'jsonPath' into a STRING_ARRAY.
Signature
JSONEXTRACTKEY(jsonField, 'jsonPath')
Arguments
Description
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
Usage Examples
The examples in this section are based on the . In particular we'll be querying the row WHERE id = 7044874109.
id
repo
keys
month
This section contains reference documentation for the month function.
Returns the month of the year from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 12.
Signature
month(tsInMillis)
month(tsInMillis, timeZoneId)
Usage Examples
month
month
DISTINCTSUM
This section contains reference documentation for the DISTINCTSUM function.
Returns the sum of distinct row values in a group
Signature
DISTINCTSUM(colName) or sum(distinct col)
Usage Examples
These examples are based on the .
VALUE
VALUE
now
This section contains reference documentation for the now function.
Return current time as epoch millis.
Signature
now()
Usage Examples
now
This function is typically used in predicate to filter on timestamp for recent data. e.g. filter data on recent 1 day(86400 seconds)
DIV
This section contains reference documentation for the DIV function.
Quotient of two values
Signature
DIV(col1, col2)
Usage Examples
These examples are based on the .
homeRuns
numberOfGames
total
JSONFORMAT
This section contains reference documentation for the JSONFORMAT function.
Extracts the object value from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an .
Signature
JSONFORMAT(object)
caseWhen
This section contains reference documentation for the caseWhen function.
Returns values depending on boolean expressions. This function can only be used in an .
This section contains reference documentation for the hour function.
Returns the hour of the day from the given epoch millis in UTC or specified timezone. The value ranges from 0 to 23.
Signature
hour(tsInMillis)
hour(tsInMillis, timeZoneId)
FrequentLongsSketch
This section contains reference documentation for the FREQUENTLONGSSKETCH function.
FREQUENTLONGSSKETCH is an estimation data-sketch function which can be used to estimate the frequency of an item. It is based on and returns a serialized sketch object which can be merged with other sketches.
This section contains reference documentation for the DISTINCTCOUNTBITMAP function.
Returns the count of distinct row values in a group. This function is accurate for INT column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collisions.
For accurate distinct counting on all column types, see .
Signature
DISTINCTCOUNTBITMAP(colName)
mult
This section contains reference documentation for the MULT function.
Product of at least two values
Signature
MULT(col1, col2, col3...)
select FlightNum,
arrayReverseString(RandomAirports) AS reversedAirports,
RandomAirports
from airlineStats
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5
select FlightNum,
arraySliceInt(DivAirportIDs, 0, 1) AS airports,
DivAirportIDs
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
select DivWheelsOffs,
DivWheelsOns,
arrayUnionInt(DivWheelsOffs, DivWheelsOns) AS unionIds
from airlineStats
WHERE arraylength(DivWheelsOffs) >= 2
limit 5
select DivTailNums,
DivAirports,
arrayUnionString(DivTailNums, DivAirports) AS unionIds
from airlineStats
WHERE arraylength(DivTailNums) >= 2
limit 5
select minute(1639351800000) AS minute
FROM ignoreMe
30
select minute(1639351800000, 'America/St_Johns') AS minute
FROM ignoreMe
0
SELECT isSubnetOf('192.168.0.1/24', '192.168.0.225')
AS result
FROM myTable;
---> returns true
SELECT isSubnetOf('1.2.3.128/26', '1.2.5.1')
AS result
FROM myTable;
---> returns false
SELECT isSubnetOf('2001:4800:7825:103::/64', '2001:4800:7825:103::2050')
AS result
FROM myTable;
---> returns true
SELECT isSubnetOf('7890:db8:113::8a2e:370:7334/127', '7890:db8:113::8a2e:370:7336')
AS result
FROM myTable;
---> returns false
select DivTailNums
from airlineStats
where arraylength(DivTailNums) > 1
SELECT *
FROM tableName
WHERE tsInMillis > now() - 86400000
select homeRuns, numberOfGames, DIV(homeRuns, numberOfGames) AS total
from baseballStats
WHERE teamID = 'ML1'
AND yearID = 1956
AND playerName = 'Henry Louis'
select hour(1639351800000, 'CET') AS hour
FROM ignoreMe
column (required): Name of the column to aggregate on. Needs to be a type which can be cast into 'LONG'.
maxMapSize: This value specifies the maximum physical length of the internal hash map. The maxMapSize must be a power of 2 and the default value is 256.
Usage Example
frequentlongssketch(AirlineID)
BAEKCAUAAAAOAAAAAA...
Which can be used, for example in Java as:
For more examples on the sketch API, refer to the Datasketches documentation.
select homeRuns, baseOnBalls, MULT(homeRuns, baseOnBalls) AS total
from baseballStats
WHERE teamID = 'ML1'
AND yearID = 1956
AND playerName = 'Henry Louis'
SELECT toBase64(toUtf8('hello!')) AS encoded
FROM ignoreMe
SELECT fromUtf8(fromBase64('aGVsbG8h')) AS decoded
FROM ignoreMe
SELECT fromBase64('aGVsbG8h') AS decoded
FROM ignoreMe
SELECT toBase64('hello!') AS encoded
FROM ignoreMe
select FlightNum,
arraySliceString(RandomAirports, 0, 2) AS airports,
RandomAirports
from airlineStats
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5
select FlightNum,
arraySortString(RandomAirports) AS sortedAirports,
RandomAirports
from airlineStats
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5
select day(1639351800000, 'CET') AS day
FROM ignoreMe
select dayOfMonth(1639351800000) AS day
FROM ignoreMe
select dayOfMonth(1639351800000, 'CET') AS day
FROM ignoreMe
select FREQUENTSTRINGSSKETCH(AirlineID, 16) from airlineStats
byte[] byteArr = Base64.getDecoder().decode(encodedSketch);
ItemsSketch<String> sketch = ItemsSketch.getInstance(Memory.wrap(byteArr), new ArrayOfStringsSerDe());
ItemsSketch.Row[] items = sketch.getFrequentItems(ErrorType.NO_FALSE_NEGATIVES);
for (int i = 0; i < items.length; i++) {
ItemsSketch.Row item = items[i];
System.out.printf("Airline: %s, Frequency: %d %n", item.getItem(), item.getEstimate());
}
select DivAirportIDs,
arrayReverseInt(DivAirportIDs) AS reversedIds
from airlineStats
WHERE arraylength(DivAirportIDs) >= 2
limit 5
SELECT HISTOGRAM(numberOfGames, 0, 200, 10) AS histogram
FROM baseballStats
select HISTOGRAM(AtBatting, Array['-Infinity', 1, 10, 50, 100, 500, 1000]) AS histogram
from baseballStats
select DISTINCT league AS value
from baseballStats
select DISTINCT(league) AS value
from baseballStats
select DISTINCTCOUNTRAWHLL(teamID, 1) AS value
from baseballStats
select mode(yearID, 'AVG') AS value
from baseballStats
WHERE AtBatting != 0 AND yearID > 2001
select mode(yearID, 'MIN') AS value
from baseballStats
WHERE AtBatting != 0 AND yearID > 2001
select mode(yearID, 'MAX') AS value
from baseballStats
WHERE AtBatting != 0 AND yearID > 2001
SELECT count(*)
FROM myTable
WHERE isSubnetOf('192.168.0.1/24', ipAddressCol);
SELECT count(*)
FROM myTable
WHERE isSubnetOf('192.168.0.1/24', ipAddressCol)
OR isSubnetOf(ipPrefixCol, '7890:db8:113::8a2e:370:7336');
SELECT
CASE
WHEN isSubnetOf('105.25.245.115/27', srcIPAddress) THEN 'case1'
WHEN isSubnetOf('105.25.245.115/27', dstIPAddress) THEN 'case2'
ELSE 'case3'
END AS differentFlow
FROM myTable;
select COUNTMV(DivTailNums) AS value
from airlineStats
where arraylength(DivTailNums) > 1
select dayOfYear(1639351800000, 'CET') AS dayOfYear
FROM ignoreMe
select doy(1639351800000) AS dayOfYear
FROM ignoreMe
select doy(1639351800000, 'CET') AS dayOfYear
FROM ignoreMe
select DISTINCTCOUNTMV(DivTailNums) AS value
from airlineStats
where arraylength(DivTailNums) > 1
select FIRSTWITHTIME(group_name, __metadata$recordTimestamp, 'STRING')
from meetupRsvp
select DISTINCTCOUNTHLL(teamID, 12) AS value
from baseballStats
select DISTINCTCOUNTRAWHLLMV(DivAirports, 1) AS value
from airlineStats
where arraylength(DivAirports) > 1
SELECT AVG(DISTINCT AtBatting) AS VALUE
FROM baseballStats
select id, repo, JSONEXTRACTKEY(repo, '$.*') AS keys
from githubEvents
WHERE id = 7044874109
select DISTINCTCOUNTBITMAP(teamID) AS value
from baseballStats
DISTINCTCOUNTRAWTHETASKETCH
This section contains reference documentation for the DISTINCTCOUNTRAWTHETASKETCH function.
The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:4.2.0 to perform distinct counting as well as evaluating set operations.
thetaSketchColumn (required): Name of the column to aggregate on.
thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.
Usage Examples
These examples are based on the .
value
value
We can also provide predicates and a post aggregation expression to compute more complicated cardinalities:
value
JSONPATHARRAYDEFAULTEMPTY
This section contains reference documentation for the JSONPATHARRAYDEFAULTEMPTY function.
Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. Returns empty array for null or parsing error. This function can only be used in an ingestion transformation function.
Signature
JSONPATHARRAYDEFAULTEMPTY(jsonField, 'jsonPath')
Arguments
Description
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
Expression
Value
This function can be used in the to extract the name, score, and second value of homework_grades into their respective columns , as described below:
DATETRUNC
This section contains reference documentation for the DATETRUNC function.
(Presto) SQL compatible date truncation, equivalent to the Presto function date_trunc.
Converts the value into a specified output granularity seconds since UTC epoch that is bucketed on a unit in a specified timezone.
inputTimeUnitStr and outputTimeUnitStr support the following values:
NANOSECONDS
MICROSECONDS
MILLISECONDS
Usage Examples
Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight):
or
ts
Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the UTC time zone, returning a result in epoch in seconds in UTC timezone:
ts
Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the CET time zone, returning a result in epoch in seconds in UTC timezone:
ts
Truncates an epoch in milliseconds at QUARTER in the Los Angeles time zone (where a Quarter begins on Jan 1st, April 1st, July 1st, October 1st in Los Angeles timezone), returning a result in hours since UTC epoch:
ts
JSONPATHDOUBLE
This section contains reference documentation for the JSONPATHDOUBLE function.
Extracts the Double value from jsonField based on 'jsonPath', use optional defaultValuefor null or parsing error. This function can only be used in an ingestion transformation function.
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
Expression
Value
This function can be used in the to extract the age property into the age column, as described below:
JSONPATH
This section contains reference documentation for the JSONPATH function.
Extracts the object value from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an .
Signature
JSONPATH(jsonField, 'jsonPath')
jsonextractscalar
This section contains reference documentation for the JSONEXTRACTSCALAR function.
Evaluates the 'jsonPath' on jsonField, returns the result as the type 'resultsType', use optional defaultValuefor null or parsing error.
This section contains reference documentation for the JSONPATHLONG function.
Extracts the Long value from jsonField based on 'jsonPath', use optional defaultValuefor null or parsing error. This function can only be used in an .
Signature
JSONPATHARRAY
This section contains reference documentation for the JSONPATHARRAY function.
Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an .
Signature
JSONPATHARRAY(jsonField, 'jsonPath')
Currently, the only supported parameter is nominalEntries (defaults to 4096).
predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.
postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.
select distinctCountRawThetaSketch(teamID) AS value
from baseballStats
select distinctCountRawThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats
select distinctCountRawThetaSketch(
yearID,
'nominalEntries=4096',
'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
'SET_INTERSECT($1, $2)'
) AS value
from baseballStats
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the Jayway JsonPath Evaluator Tool to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
Expression
Value
JSONPATH(data, '$.name')
"Pete"
JSONPATH(data, '$.age')
24
This function can be used in the table config to extract the name property into the name column and age property into the age column, as described below:
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the Jayway JsonPath Evaluator Tool to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
Expression
Value
JSONPATHLONG(data, '$.age')
24
This function can be used in the table config to extract the age property into the age column, as described below:
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the Jayway JsonPath Evaluator Tool to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
This function can be used in the table config to extract the name, score, and second value of homework_grades into their respective columns , as described below:
This section contains reference documentation for the JSONPATHSTRING function.
Extracts the String value from jsonField based on 'jsonPath', use optional defaultValuefor null or parsing error. This function can only be used in an ingestion transformation function.
'jsonPath'` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Usage Examples
The usage examples are based on extracting fields from the following JSON document:
Expression
Value
This function can be used in the to extract the age property into the age column, as described below:
DISTINCTCOUNTTHETASKETCH
This section contains reference documentation for the DISTINCTCOUNTTHETASKETCH function.
The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:4.2.0 to perform distinct counting as well as evaluating set operations.
Signature
distinctCountThetaSketch(<thetaSketchColumn>, <thetaSketchParams>, predicate1, predicate2..., postAggregationExpressionToEvaluate) -> Long
thetaSketchColumn (required): Name of the column to aggregate on.
thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.
Usage Examples
These examples are based on the .
value
value
We can also provide predicates and a post aggregation expression to compute more complicated cardinalities. For example, we could can find the intersection of the following queries:
yearID
yearID
(the yearId 1986 is the only one in common)
By running the following query:
value
DATETIMECONVERT
This section contains reference documentation for the DATETIMECONVERT function.
Converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity.
Currently, the only supported parameter is nominalEntries (defaults to 4096).
predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.
postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.
select distinctCountThetaSketch(teamID) AS value
from baseballStats
select distinctCountThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats
select yearID
from baseballStats
where teamID = 'SFN' AND numberOfGames = 28 AND homeRuns = 1
select yearID
from baseballStats
where teamID = 'CHN' AND numberOfGames = 28 AND homeRuns = 1
select distinctCountThetaSketch(
yearID,
'nominalEntries=4096',
'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
'SET_INTERSECT($1, $2)'
) AS value
from baseballStats
and
outputFormat
are defined using the following structure:
<time size>:<time unit>:<time format>:<pattern>
where:
time size - size of the time unit eg: 1, 10
time unit - DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS
time format
EPOCH
SIMPLE_DATE_FORMAT pattern - defined in case of
granularity is specified in the format <time size>:<time unit>.
created_at_timestamp from milliseconds since epoch to days since epoch, bucketed to 1 day granularity:
id
created_at_timestamp
timeInMs
convertedTime
7044874134
2018-01-01 11:00:00.0
1514804402000
17532
created_at_timestamp bucketed to 15 minutes granularity:
id
created_at_timestamp
timeInMs
convertedTime
7044874134
2018-01-01 11:00:00.0
1514804402000
1514804400000
created_at_timestamp to format yyyy-MM-dd, bucketed to 1 days granularity:
id
created_at_timestamp
timeInMs
convertedTime
7044874134
2018-01-01 11:00:00.0
1514804402000
2018-01-01
created_at_timestamp to format yyyy-MM-dd HH:mm, in timezone Pacific/Kiritimati:
id
created_at_timestamp
timeInMs
convertedTime
7044874134
2018-01-01 11:00:00.0
1514804402000
2018-01-02 01:00
created_at_timestamp to format yyyy-MM-dd, in timezone Pacific/Kiritimati and bucketed to 1 day granularity:
id
created_at_timestamp
timeInMs
convertedTime
7044874134
2018-01-01 11:00:00.0
1514804402000
2018-01-02 00:00
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERT(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:DAYS:EPOCH',
'1:DAYS'
) AS convertedTime
from githubEvents
WHERE id = 7044874134
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERT(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:EPOCH',
'15:MINUTES'
) AS convertedTime
from githubEvents
WHERE id = 7044874134
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERT(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd',
'1:DAYS'
) AS convertedTime
from githubEvents
WHERE id = 7044874134
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERT(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)',
'1:MILLISECONDS'
) AS convertedTime
from githubEvents
WHERE id = 7044874134
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERT(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)',
'1:DAYS'
) AS convertedTime
from githubEvents
WHERE id = 7044874134
SIMPLE_DATE_FORMAT
e.g.
yyyy-MM-dd
. A specific timezone can be passed using
tz(timezone)
. Timezone can be long or short string format timezone. e.g.
Asia/Kolkata
or
PDT
FUNNELCOUNT
This section contains reference documentation for the FUNNELCOUNT function.
Funnel analytics aggregation function.
Returns array of distinct correlated counts for each funnel step.
Signature
FUNNEL_COUNT (
STEPS ( predicate1, predicate2 ... ),
CORRELATED_BY ( correlation_column ),
SETTINGS ( setting1, setting2 ... ) )
Parameter
Arguments
Description
Usage Examples
Many datasets are time series in nature, tracking events of an entity over time. An example of such a dataset could be a user analytics activity log from a commerce web application.
Example
user_id
event_time
url
Funnel
We want to analyse the following checkout funnel:
/cart/add
/checkout/start
/checkout/confirmation
Counts
We want to answer the following questions about the above funnel:
How many users entered the top of the funnel?
How many of these users proceeded to the second step?
How many users reached the bottom of the funnel after completing all steps?
Query
counts
Notes
Notice that although U1 user added to cart twice, it still counted as one conversion in the first step, as we report on unique counts rather than total events. Also notice that although U2 events were logged out of order, we still counted the user as converted.
Equivalence
The above query is equivalent to the below presto SQL query:
Settings
For a large dataset we could use for example a theta_sketch strategy, or furthermore, partition the data by user_id and leverage a partitioned strategy. It is also important to filter in the where clause so to aggregate only necessary rows.
counts
Another Example
We now want to learn how many users checkout after a text search; as opposed to other entry points such as browsing a product category listing. We want to then analyse the following funnel:
/product/search
/cart/add
/checkout/start
Query
counts
Notes
Notice that U1 is not counted in this funnel, as the user did not perform any product search. Both U2 and U3 entered the top of the funnel and performed the second step, but only U2 converted to the bottom of the funnel.
2021-10-01 09:47:00.000
/cart/add
U3
2021-10-01 10:02:00.000
/product/listing
U3
2021-10-01 10:05:00.000
/product/search
U2
2021-10-01 10:06:00.000
/product/search
U2
2021-10-01 10:15:00.000
/checkout/start
U2
2021-10-01 10:16:00.000
/cart/add
U3
2021-10-01 11:17:00.000
/product/details
U2
2021-10-01 11:18:00.000
/checkout/confirmation
U3
2021-10-01 11:21:00.000
/cart/add
U1
2021-10-01 11:33:00.000
/cart/add
U1
2021-10-01 11:46:00.000
/checkout/start
U1
2021-10-01 11:54:00.000
/checkout/confirmation
/checkout/confirmation
STEPS
predicates 1...n
(required) These are individual predicates representing funnel steps which are applied on rows selected by the where clause. Distinct values from the correlation_column that satisfy these predicates are counted per step. For example, all filtered rows that match url = '/checkout' are unionized into a set. Sets are intersected with the sets resulted from the preceding steps, each step retaining only individuals present in previous steps. Finally, unique counts are returned for each step in the funnel.
CORRELATED_BY
correlation_column
(required) Column to leverage for funnel correlation, distinct values from this column are counted per step during aggregation. Only dictionary-encoded columns are supported.
SETTINGS
settings 1...n
(optional) Settings to select and configure a funnel counting strategy:
bitmap (default): This strategy is accurate for INT column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collisions. For accurate distinct counting on all column types, use 'set' instead. See also DISTINCTCOUNTBITMAP.
set: This strategy uses fastutil hash sets. Use with care, unbounded memory cost. See also DISTINCTCOUNT.
theta_sketch: This strategy leverages Theta Sketch framework to provide an approximate funnel count with a small memory footprint. See also DISTINCTCOUNTTHETASKETCH.
nominalEntries: theta-sketch strategy parameter (defaults to 4096). Can only be used in conjunction with theta_sketch setting.
partitioned: This strategy counts funnel steps per segment, then sums up step counts across segments. Correlation column should be configured as partition column for this strategy. See also .
sorted: This strategy counts funnel steps per segment with zero memory footprint. Correlation column should be configured as sort column for this strategy. Can only be used in conjunction with partitioned setting.
U1
2021-10-01 09:01:00.000
/product/listing
U2
2021-10-01 09:17:00.000
/product/search
U1
2021-10-01 09:33:00.000
/product/details
3, 2, 2
3, 2, 2
2, 2, 1, 1
U1
select
FUNNEL_COUNT(
STEPS(
url = '/cart/add',
url = '/checkout/start',
url = '/checkout/confirmation'),
CORRELATED_BY(user_id)
) AS counts
from user_log
select
ARRAY[
count_if(steps[1]),
count_if(steps[1] and steps[2]),
count_if(steps[1] and steps[2] and steps[3])
] as counts
from (
select
ARRAY[
bool_or(url = '/cart/add'),
bool_or(url = '/checkout/start'),
bool_or(url = '/checkout/confirmation')
] as steps
from user_log
group by user_id
)
select
FUNNEL_COUNT(
STEPS(
url = '/cart/add',
url = '/checkout/start',
url = '/checkout/confirmation'),
CORRELATED_BY(user_id),
SETTINGS('theta_sketch', 'nominalEntries=4096')
) AS counts
from user_log
where url in ('/cart/add', '/checkout/start', '/checkout/confirmation')