Aggregates

The following aggregations are supported:

AT_HIGH, AT_LOW, AVG, CORR, COUNT, EXP_TW_AVERAGE, EXP_W_AVERAGE, FIRST, FIRST_TIME, HIGH_TIME, LAST, LAST_TIME, LOW_TIME, MAX, MEDIAN, MIN, PERCENTILE_CONT, PERCENTILE_DISC, STANDARDIZED_MOMENT - KURTOSIS, STANDARDIZED_MOMENT - SKEWNESS, STDDEV, STDDEVP, SUM, TW_AVG, VAR, VARP, VWAP.

AT_HIGH

Returns the target field value at the first high of another fields set of values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

AT_HIGH([High Field Name],[Target Field Name])

Window Syntax:

AT_HIGH([High Field Name,[Target Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

AT_HIGH([High Field Name],[Target Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

AT_HIGH Aggregate Simple Example

select AT_HIGH(PRICE,SIZE) as SIZE_AT_HIGH_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AT_HIGH Aggregate Group By Example

select EXCHANGE, AT_HIGH(PRICE,SIZE) as SIZE_AT_HIGH_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

AT_HIGH Aggregate Window Example

select AT_HIGH(PRICE,SIZE) OVER(order by TIMESTAMP asc) as SIZE_AT_HIGH_ROLLING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AT_HIGH Aggregate Moving Window Example

select AT_HIGH(PRICE,SIZE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as SIZE_AT_HIGH_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AT_LOW

Returns the target field value at the first Low of another fields set of values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

AT_LOW([Low Field Name],[Target Field Name])

Window Syntax:

AT_LOW([Low Field Name,[Target Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

AT_LOW([Low Field Name],[Target Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

AT_LOW Aggregate Simple Example

select AT_LOW(PRICE,SIZE) as SIZE_AT_LOW_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AT_LOW Aggregate Group By Example

select EXCHANGE, AT_LOW(PRICE,SIZE) as SIZE_AT_LOW_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

AT_LOW Aggregate Window Example

select AT_LOW(PRICE,SIZE) OVER(order by TIMESTAMP asc) as SIZE_AT_LOW_ROLLING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AT_LOW Aggregate Moving Window Example

select AT_LOW(PRICE,SIZE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as SIZE_AT_LOW_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AVG

Returns the average of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

AVG([Field Name])

Window Syntax:

AVG([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

AVG([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

AVG - Average Aggregate Simple Example

select AVG(PRICE) as AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AVG - Average Aggregate Group By Example

select EXCHANGE, AVG(PRICE) as AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

AVG - Average Aggregate Window Example

SELECT AVG(PRICE) OVER(order by TIMESTAMP asc) as AVG_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

AVG Aggregate Moving Window Example

select AVG(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as AVG_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

CORR

Returns the correlation of non-NaN pairs of values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

CORR([Field Name 1],[Field Name 2])

Window Syntax:

CORR([Field Name 1],[Field Name 2]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

CORR([Field Name 1],[Field Name 2]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

CORR - Correlation Aggregate Simple Example

select CORRELATION(PRICE,SIZE) as CORR_PAIR
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

CORR - Correlation Aggregate Group By Example

select EXCHANGE, CORRELATION(PRICE,SIZE) as CORR_PAIR
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

CORR - Correlation Aggregate Window Example

select CORRELATION(PRICE,SIZE) OVER(order by TIMESTAMP asc) as CORR_ROLLING_PAIR
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

CORR - Correlation Moving Window Example

select CORRELATION(PRICE,SIZE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as CORR_MOVING_PAIR
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

COUNT

Returns the count of values.

Simple Syntax:

COUNT([Field Name]) or COUNT(*)

Window Syntax:

COUNT([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

COUNT([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

COUNT Aggregate Simple Example

select COUNT(PRICE) as COUNT_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

COUNT Aggregate Group By Example

select EXCHANGE, COUNT(PRICE) as COUNT_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

COUNT Aggregate Window Example

SELECT COUNT(PRICE) OVER(order by TIMESTAMP asc) as COUNT_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

COUNT Aggregate Moving Window Example

select COUNT(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as COUNT_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_W_AVERAGE

Returns the Exponential Weighted Average of a set of values. It expects the field to aggregate upon, plus the DECAY value, which has a decay value type of Lambda.

Simple Syntax:

EXP_W_AVERAGE([Field Name],DECAY=[Lambda Value])

Window Syntax:

EXP_W_AVERAGE([Field Name],DECAY=[Lambda Value]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

EXP_W_AVERAGE([Field Name],DECAY=[Lambda Value]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

EXP_W_AVERAGE - Exponential Weighted Average Aggregate Simple Example

select EXP_W_AVERAGE(PRICE,DECAY=0.1) as EXP_W_AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_W_AVERAGE - Exponential Weighted Average Aggregate Group By Example

select EXCHANGE, EXP_W_AVERAGE(PRICE,DECAY=0.1) as EXP_W_AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

EXP_W_AVERAGE - Exponential Weighted Average Aggregate Window Example

SELECT EXP_W_AVERAGE(PRICE,DECAY=0.1) OVER(order by TIMESTAMP asc) as EXP_W_AVG_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_W_AVERAGE - Exponential Weighted Average Aggregate Moving Window Example

select EXP_W_AVERAGE(PRICE,DECAY=0.1) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as EXP_W_AVG_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_TW_AVERAGE

Returns the Exponential Time Weighted Average of a set of values. It expects the field to aggregate upon, plus the DECAY value, which has a decay value type of half life in seconds.

Simple Syntax:

EXP_TW_AVERAGE([Field Name],DECAY=[Half life in seconds])

Window Syntax:

EXP_TW_AVERAGE([Field Name],DECAY=[Half life in seconds]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

EXP_TW_AVERAGE([Field Name],DECAY=[Half life in seconds]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

EXP_W_AVERAGE - Exponential Time Weighted Average Aggregate Simple Example

select EXP_TW_AVERAGE(PRICE,DECAY=0.1) as EXP_W_AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_TW_AVERAGE - Exponential Time Weighted Average Aggregate Group By Example

select EXCHANGE, EXP_TW_AVERAGE(PRICE,DECAY=0.1) as EXP_TW_AVERAGE_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

EXP_TW_AVERAGE - Exponential Time Weighted Average Aggregate Window Example

SELECT EXP_W_AVERAGE(PRICE,DECAY=0.1) OVER(order by TIMESTAMP asc) as EXP_TW_AVERAGE_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

EXP_TW_AVERAGE - Exponential Time Weighted Average Aggregate Moving Window Example

select EXP_TW_AVERAGE(PRICE,DECAY=0.1) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as EXP_TW_AVERAGE_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST

Returns the First value of a set of values

Simple Syntax:

FIRST([Field Name])

Window Syntax:

FIRST([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

FIRST([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

FIRST Aggregate Simple Example

select FIRST(PRICE) as FIRST_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST Aggregate Group By Example

select EXCHANGE, FIRST(PRICE) as FIRST_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

FIRST Aggregate Window Example

SELECT FIRST(PRICE) OVER(order by TIMESTAMP asc) as FIRST_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST Aggregate Moving Window Example

select FIRST(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as FIRST_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST_TIME

Returns the Timestamp of the first value of a set of values

Simple Syntax:

FIRST_TIME([Field Name]) or FIRST_TIME(*)

Window Syntax:

FIRST_TIME([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

FIRST_TIME([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

FIRST_TIME Aggregate Simple Example

select FIRST_TIME(PRICE) as FIRST_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST_TIME Aggregate Group By Example

select EXCHANGE, FIRST_TIME(PRICE) as FIRST_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

FIRST_TIME Aggregate Window Example

SELECT FIRST_TIME(PRICE) OVER(order by TIMESTAMP asc) as FIRST_TIME_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

FIRST_TIME Aggregate Moving Window Example

select FIRST_TIME(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as FIRST_TIME_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

HIGH_TIME

Returns the First Timestamp of the maximum value of a set of values

Simple Syntax:

HIGH_TIME([Field Name])

Window Syntax:

HIGH_TIME([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

HIGH_TIME([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

HIGH_TIME Aggregate Simple Example

select HIGH_TIME(PRICE) as HIGH_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

HIGH_TIME Aggregate Group By Example

select EXCHANGE, HIGH_TIME(PRICE) as HIGH_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

HIGH_TIME Aggregate Window Example

SELECT HIGH_TIME(PRICE) OVER(order by TIMESTAMP asc) as HIGH_TIME_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

HIGH_TIME Aggregate Moving Window Example

select HIGH_TIME(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as HIGH_TIME_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST

Returns the Last value of a set of values

Simple Syntax:

LAST([Field Name])

Window Syntax:

LAST([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

LAST([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

LAST Aggregate Simple Example

select LAST(PRICE) as LAST_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST Aggregate Group By Example

select EXCHANGE, LAST(PRICE) as LAST_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

LAST Aggregate Window Example

SELECT LAST(PRICE) OVER(order by TIMESTAMP asc) as LAST_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST Aggregate Moving Window Example

select LAST(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as LAST_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST_TIME

Returns the Timestamp of the last value of a set of values

Simple Syntax:

LAST_TIME([Field Name]) or LAST_TIME(*)

Window Syntax:

LAST_TIME([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

LAST_TIME([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

LAST_TIME Aggregate Simple Example

select LAST_TIME(PRICE) as LAST_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST_TIME Aggregate Group By Example

select EXCHANGE, LAST_TIME(PRICE) as LAST_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

LAST_TIME Aggregate Window Example

SELECT LAST_TIME(PRICE) OVER(order by TIMESTAMP asc) as LAST_TIME_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LAST_TIME Aggregate Moving Window Example

select LAST_TIME(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as LAST_TIME_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LOW_TIME

Returns the First Timestamp of the minimum value of a set of values

Simple Syntax:

LOW_TIME([Field Name])

Window Syntax:

LOW_TIME([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

LOW_TIME([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

LOW_TIME Aggregate Simple Example

select LOW_TIME(PRICE) as LOW_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LOW_TIME Aggregate Group By Example

select EXCHANGE, LOW_TIME(PRICE) as LOW_TIME_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

LOW_TIME Aggregate Window Example

SELECT LOW_TIME(PRICE) OVER(order by TIMESTAMP asc) as LOW_TIME_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

LOW_TIME Aggregate Moving Window Example

select LOW_TIME(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as LOW_TIME_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MAX

Returns the maximum of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

MAX([Field Name])

Window Syntax:

MAX([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

MAX([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

MAX - Maximum Aggregate Simple Example

select MAX(PRICE) as MAX_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MAX - Maximum Aggregate Group By Example

select EXCHANGE, MAX(PRICE) as MAX_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

MAX - Maximum Aggregate Window Example

SELECT MAX(PRICE) OVER(order by TIMESTAMP asc) as MAX_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MAX Maximum Moving Window Example

select MAX(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as MAX_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MEDIAN

Returns the Median of a set of values.

Simple Syntax:

MEDIAN([Field Name])

Window Syntax:

MEDIAN([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

MEDIAN([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

MEDIAN Aggregate Simple Example

select MEDIAN(PRICE) as MEDIAN_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MEDIAN Aggregate Group By Example

select EXCHANGE, MEDIAN(PRICE) as MEDIAN_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

MEDIAN Aggregate Window Example

SELECT MEDIAN(PRICE) OVER(order by TIMESTAMP asc) as MEDIAN_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MEDIAN Aggregate Moving Window Example

select MEDIAN(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as MEDIAN_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MIN

Returns the minimum of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

MIN([Field Name])

Window Syntax:

MIN([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

MIN([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

MIN - Minimum Aggregate Simple Example

select MIN(PRICE) as MIN_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MIN - Minimum Aggregate Group By Example

select EXCHANGE, MIN(PRICE) as MIN_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

MIN - Minimum Aggregate Window Example

SELECT MIN(PRICE) OVER(order by TIMESTAMP asc) as MIN_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

MIN Minimum Moving Window Example

select MIN(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as MIN_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

PERCENTILE_CONT

Returns the percentile of non-NaN values based on a continuous distribution. If all values inside the group are NaN the aggregate returns NaN. The Percentile is written as a decimal. e.g. 0.9 = 90%, 0.5 = 50%, 0.1 = 10%

Simple Syntax:

PERCENTILE_CONT([Percentile]) WITHIN GROUP (ORDER BY [Field Name] asc)

PERCENTILE_CONT - Continuous Distribution Percentile Aggregate Simple Example

select PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY PRICE asc) as P90C
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

PERCENTILE_DISC

Returns the percentile of non-NaN values based on a discrete distribution. If all values inside the group are NaN the aggregate returns NaN. The Percentile is written as a decimal. e.g. 0.9 = 90%, 0.5 = 50%, 0.1 = 10%

Simple Syntax:

PERCENTILE_DISC([Percentile]) WITHIN GROUP (ORDER BY [Field Name] asc)

PERCENTILE_DISC - Continuous Distribution Percentile Aggregate Simple Example

select PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY PRICE asc) as P90C
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - SKEWNESS

Returns the Skewness of a set of values. using the STANDARDIZED_MOMENT aggregate. It expects the field to aggregate upon, plus the degree" value.   ``3 for Skewness.

Simple Syntax:

STANDARDIZED_MOMENT([Field Name],degree=3)

Window Syntax:

STANDARDIZED_MOMENT([Field Name],degree=3) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

STANDARDIZED_MOMENT([Field Name],degree=3) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

STANDARDIZED_MOMENT - SKEWNESS Aggregate Simple Example

select STANDARDIZED_MOMENT(PRICE,degree=3) as SKEWNESS_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - SKEWNESS Aggregate Group By Example

select EXCHANGE, STANDARDIZED_MOMENT(PRICE,degree=3) as SKEWNESS_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

STANDARDIZED_MOMENT - SKEWNESS Aggregate Window Example

SELECT STANDARDIZED_MOMENT(PRICE,degree=3) OVER(order by TIMESTAMP asc) as SKEWNESS_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - SKEWNESS Aggregate Moving Window Example

select STANDARDIZED_MOMENT(PRICE,degree=3) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as SKEWNESS_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - KURTOSIS

Returns the Kurtosis of a set of values, using the STANDARDIZED_MOMENT aggregate. It expects the field to aggregate upon, plus the degree value. 4 for Kurtosis.

Simple Syntax:

STANDARDIZED_MOMENT([Field Name],degree=4)

Window Syntax:

STANDARDIZED_MOMENT([Field Name],degree=4) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

STANDARDIZED_MOMENT([Field Name],degree=4) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

STANDARDIZED_MOMENT - KURTOSIS Aggregate Simple Example

select STANDARDIZED_MOMENT(PRICE,degree=4) as KURTOSIS_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - KURTOSIS Aggregate Group By Example

select EXCHANGE, STANDARDIZED_MOMENT(PRICE,degree=4) as KURTOSIS_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

STANDARDIZED_MOMENT - KURTOSIS Aggregate Window Example

SELECT STANDARDIZED_MOMENT(PRICE,degree=4) OVER(order by TIMESTAMP asc) as KURTOSIS_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STANDARDIZED_MOMENT - KURTOSIS Aggregate Moving Window Example

select STANDARDIZED_MOMENT(PRICE,degree=4) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as KURTOSIS_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEV

Returns the Sample Standard Deviation of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

STDDEV([Field Name])

Window Syntax:

STDDEV([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

STDDEV([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

STDDEV - Sample Standard Deviation Aggregate Simple Example

select STDDEV(PRICE) as STDDEV_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEV Sample Standard Deviation Aggregate Group By Example

select EXCHANGE, STDDEV(PRICE) as STDDEV_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

STDDEV - Sample Standard Deviation Aggregate Window Example

SELECT STDDEV(PRICE) OVER(order by TIMESTAMP asc) as STDDEV_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEV - Sample Standard Deviation Aggregate Moving Window Example

select STDDEV(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as STDDEV_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEVP

Returns the Population Standard Deviation of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

STDDEVP([Field Name])

Window Syntax:

STDDEVP([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

STDDEVP([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

STDDEVP - Population Standard Deviation Aggregate Simple Example

select STDDEVP(PRICE) as STDDEVP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEVP - Population Standard Deviation Aggregate Group By Example

select EXCHANGE, STDDEVP(PRICE) as STDDEVP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

STDDEVP - Population Standard Deviation Aggregate Window Example

SELECT STDDEVP(PRICE) OVER(order by TIMESTAMP asc) as STDDEVP_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

STDDEVP - Population Standard Deviation Aggregate Moving Window Example

select STDDEVP(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as STDDEVP_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

SUM

Returns the sum of non-NaN values. If all values inside the group are NaN the aggregate returns 0.

Simple Syntax:

SUM([Field Name])

Window Syntax:

SUM([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

SUM([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

SUM Aggregate Simple Example

select SUM(SIZE) as SUM_SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

SUM Aggregate Group By Example

select EXCHANGE, SUM(SIZE) as SUM_SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

SUM Aggregate Window Example

select SUM(SIZE) OVER(order by TIMESTAMP asc) as SUM_ROLLING_SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

SUM Aggregate Moving Window Example

select SUM(SIZE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as SUM_MOVING_SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

TW_AVG

Returns the Time Weighted Average (TWAP) of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

TW_AVG([Field Name])

Window Syntax:

TW_AVG([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

TW_AVG([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

TW_AVG - Time Weighted Average Aggregate Simple Example

select TW_AVG(PRICE) as TW_AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

TW_AVG - Time Weighted Average Aggregate Group By Example

select EXCHANGE, TW_AVG(PRICE) as TW_AVG_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

TW_AVG - Time Weighted Average Aggregate Window Example

SELECT TW_AVG(PRICE) OVER(order by TIMESTAMP asc) as TW_AVG_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

TW_AVG - Time Weighted Average Aggregate Moving Window Example

select TW_AVG(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as TW_AVG_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VAR

Returns the Sample Variance of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

VAR([Field Name])

Window Syntax:

VAR([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

VAR([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

VAR - Sample Variance Aggregate Simple Example

select VAR(PRICE) as VAR_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VAR Sample Variance Aggregate Group By Example

select EXCHANGE, VAR(PRICE) as VAR_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

VAR - Sample Variance Aggregate Window Example

SELECT VAR(PRICE) OVER(order by TIMESTAMP asc) as VAR_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VAR - Sample Variance Aggregate Moving Window Example

select VAR(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as VAR_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VARP

Returns the Population Variance of non-NaN values. If all values inside the group are NaN the aggregate returns NaN.

Simple Syntax:

VARP([Field Name])

Window Syntax:

VARP([Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

VARP([Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

VARP - Population Variance Aggregate Simple Example

select VARP(PRICE) as VARP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VARP - Population Variance Aggregate Group By Example

select EXCHANGE, VARP(PRICE) as VARP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

VARP - Population Variance Aggregate Window Example

SELECT VARP(PRICE) OVER(order by TIMESTAMP asc) as VARP_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VARP - Population Variance Aggregate Moving Window Example

select VARP(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as VARP_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VWAP

Returns the Weighted Average, typically VWAP of non-NaN values. If all values inside the group are NaN the aggregate returns NaN. It can also be calculated using the Product of the two fields, divided by the sum of the two fields. e,g. SUM(PRICE*SIZE)/SUM(SIZE)

Simple Syntax:

VWAP(price_field_name=[Field Name],size_field_name=[Weight Field Name])

Alternative Syntax:

SUM([Field Name]*[Weight Field Name])/SUM([Weight Field Name])

Window Syntax:

VWAP(price_field_name=[Field Name],size_field_name=[Weight Field Name]) OVER(order by TIMESTAMP asc)

Moving Window Syntax:

VWAP(price_field_name=[Field Name],size_field_name=[Weight Field Name]) OVER(order by TIMESTAMP asc range interval '[Interval Value]' [Interval Period] preceding)

VWAP - Volume Weighted Average Aggregate Simple Example

select VWAP(price_field_name=PRICE,size_field_name=SIZE) as VWAP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

TW_AVG - Time Weighted Average Aggregate Group By Example

select EXCHANGE, VWAP(price_field_name=PRICE,size_field_name=SIZE) as VWAP_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'
group by EXCHANGE

VWAP - Volume Weighted Average Aggregate Window Example

SELECT VWAP(price_field_name=PRICE,size_field_name=SIZE) OVER(order by TIMESTAMP asc) as VWAP_ROLLING_PRICE
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'

VWAP - Volume Weighted Average Aggregate Moving Window Example

select VWAP(price_field_name=PRICE,size_field_name=SIZE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as VWAP_MOVING_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 14:00:00 UTC'
and TIMESTAMP < '2024-01-04 15:00:00 UTC'