SAMPLE BY keyword

SAMPLE BY is used on time series data to summarize large datasets into aggregates of homogeneous time chunks as part of a SELECT statement. Users performing SAMPLE BY queries on datasets with missing data may make use of the FILL keyword to specify a fill behavior.

Sample trades table in 30 minute intervals
SELECT time, avg(price) FROM trades SAMPLE BY 30m
info

To use SAMPLE BY, a table column needs to be specified as a designated timestamp. Details about this concept can be found in the designated timestamp documentation.

Syntax#

Flow chart showing the syntax of the SAMPLE BY keywords Flow chart showing the syntax of the ALIGN TO keywords Flow chart showing the syntax of the FILL keyword

Sample units#

The size of sampled groups are specified with the following syntax:

SAMPLE BY n{units}

Where the unit for sampled groups may be one of the following:

unitdescription
Umicrosecond
Tmillisecond
ssecond
mminute
hhour
dday
Mmonth

For example, given a table trades, the following query returns the number of trades per hour:

SELECT ts, count() FROM trades SAMPLE BY 1h

Fill options#

The FILL keyword is optional and expects one or more fillOption strategies which will be applied to one or more aggregate columns. The following restrictions apply:

  • Keywords denoting fill strategies may not be combined. Only one option from NONE, NULL, PREV, LINEAR and constants may be used.
  • LINEAR strategy is not supported for keyed queries, i.e. queries that contain non-aggregated columns other than the timestamp in the SELECT clause.
fillOptionDescription
NONENo fill applied. If there is no data, the time sample will be skipped in the results. A table could be missing intervals.
NULLFills with NULL values.
PREVFills using the previous value.
LINEARFills by linear interpolation of the 2 surrounding points.
xFills with a constant value - where x is the desired value, for example FILL(100.05).

Consider an example table named prices which has no records during the entire third hour (2021-01-01T03):

tsprice
2021-01-01T01:00:00.000000Zp1
2021-01-01T02:00:00.000000Zp2
2021-01-01T04:00:00.000000Zp4
2021-01-01T05:00:00.000000Zp5

The following query returns the maximum price per hour. As there are missing values, an aggregate cannot be calculated:

SELECT ts, max(price) max FROM prices SAMPLE BY 1h;

A row is missing for the 2021-01-01T03:00:00.000000Z sample:

tsmax
2021-01-01T01:00:00.000000Zmax1
2021-01-01T02:00:00.000000Zmax2
2021-01-01T04:00:00.000000Zmax4
2021-01-01T05:00:00.000000Zmax5

A FILL strategy can be employed which fills with the previous value using PREV:

SELECT ts, max(price) max FROM prices SAMPLE BY 1h FILL(PREV);
tsmax
2021-01-01T01:00:00.000000Zmax1
2021-01-01T02:00:00.000000Zmax2
2021-01-01T03:00:00.000000Zmax2
2021-01-01T04:00:00.000000Zmax4
2021-01-01T05:00:00.000000Zmax5

Linear interpolation is done using the LINEAR fill option:

SELECT ts, max(price) max FROM prices SAMPLE BY 1h FILL(LINEAR);
tsmax
2021-01-01T01:00:00.000000Zmax1
2021-01-01T02:00:00.000000Zmax2
2021-01-01T03:00:00.000000Z(max2+max4)/2
2021-01-01T04:00:00.000000Zmax4
2021-01-01T05:00:00.000000Zmax5

A constant value can be used as a fillOption:

SELECT ts, max(price) max FROM prices SAMPLE BY 1h FILL(100.5);
tsmax
2021-01-01T01:00:00.000000Zmax1
2021-01-01T02:00:00.000000Zmax2
2021-01-01T03:00:00.000000Z100.5
2021-01-01T04:00:00.000000Zmax4
2021-01-01T05:00:00.000000Zmax5

Finally, NULL may be used as a fillOption:

SELECT ts, max(price) max FROM prices SAMPLE BY 1h FILL(NULL);
tsmax
2021-01-01T01:00:00.000000Zmax1
2021-01-01T02:00:00.000000Zmax2
2021-01-01T03:00:00.000000Znull
2021-01-01T04:00:00.000000Zmax4
2021-01-01T05:00:00.000000Zmax5
info

The FILL keyword must precede alignment described in the sample calculation section, i.e.:

SELECT ts, max(price) max FROM prices
SAMPLE BY 1h FILL(LINEAR)
ALIGN TO ...

Multiple fill values#

FILL() accepts a list of values where each value corresponds to a single aggregate column in the SELECT clause order:

SELECT min(price), max(price), avg(price), ts
FROM prices
SAMPLE BY 1h
FILL(NULL, 10, PREV);

In the above query min(price) aggregate will get FILL(NULL) strategy applied, max(price) will get FILL(10), and avg(price) will get FILL(PREV).

Sample calculation#

The default time calculation of sampled groups is an absolute value, in other words, sampling by one day is a 24 hour range which is not bound to calendar dates. To align sampled groups to calendar dates, the ALIGN TO keywords can be used and are described in the ALIGN TO CALENDAR section below.

Consider a table sensors with the following data spanning three calendar days:

tsval
2021-05-31T23:10:00.000000Z10
2021-06-01T01:10:00.000000Z80
2021-06-01T07:20:00.000000Z15
2021-06-01T13:20:00.000000Z10
2021-06-01T19:20:00.000000Z40
2021-06-02T01:10:00.000000Z90
2021-06-02T07:20:00.000000Z30

The following query can be used to sample the table by day. Note that the default sample calculation can be made explicit in a query using ALIGN TO FIRST OBSERVATION:

SELECT ts, count() FROM sensors
SAMPLE BY 1d
-- Equivalent to
SELECT ts, count() FROM sensors
SAMPLE BY 1d
ALIGN TO FIRST OBSERVATION

This query will return two rows:

tscount
2021-05-31T23:10:00.000000Z5
2021-06-01T23:10:00.000000Z2

The timestamp value for the 24 hour groups start at the first-observed timestamp.

ALIGN TO CALENDAR#

The option aligns data to calendar dates, with two optional parameters:

TIME ZONE#

A time zone may be provided for sampling with calendar alignment. Details on the options for specifying time zones with available formats are provided in the guide for working with timestamps and time zones.

SELECT ts, count() FROM sensors
SAMPLE BY 1d
ALIGN TO CALENDAR TIME ZONE 'Europe/Berlin'

In this case, the 24 hour samples begin at 2021-05-31T01:00:00.000000Z:

tscount
2021-05-31T01:00:00.000000Z1
2021-06-01T01:00:00.000000Z4
2021-06-02T01:00:00.000000Z2

Additionally, an offset may be applied when aligning sample calculation to calendar

SELECT ts, count() FROM sensors
SAMPLE BY 1d
ALIGN TO CALENDAR TIME ZONE 'Europe/Berlin' WITH OFFSET '00:45'

In this case, the 24 hour samples begin at 2021-05-31T01:45:00.000000Z:

tscount
2021-05-31T01:45:00.000000Z2
2021-06-01T01:45:00.000000Z4
2021-06-02T01:45:00.000000Z1

Local timezone output#

The timestamp values output from SAMPLE BY queries is in UTC. To have UTC values converted to specific timezones the to_timezone() function should be used.

SELECT to_timezone(ts, 'PST') ts, count
FROM (SELECT ts, count()
FROM sensors SAMPLE BY 2h
ALIGN TO CALENDAR TIME ZONE 'PST')

Time zone transitions#

Calendar dates may contain historical time zone transitions or may vary in the total number of hours due to daylight savings time. Considering the 31st October 2021, in the Europe/London calendar day which consists of 25 hours:

  • Sunday, 31 October 2021, 02:00:00 clocks are turned backward 1 hour to
  • Sunday, 31 October 2021, 01:00:00 local standard time

When a SAMPLE BY operation crosses time zone transitions in cases such as this, the first sampled group which spans a transition will include aggregates by full calendar range. Consider a table sensors with one data point per hour spanning three calendar hours:

tsval
2021-10-31T00:10:00.000000Z10
2021-10-31T01:10:00.000000Z20
2021-10-31T02:10:00.000000Z30
2021-10-31T03:10:00.000000Z40
2021-10-31T04:10:00.000000Z50

The following query will sample by hour with the Europe/London time zone and align to calendar ranges:

SELECT ts, count() FROM sensors
SAMPLE BY 1h
ALIGN TO CALENDAR TIME ZONE 'Europe/London'

The record count for the hour which encounters a time zone transition will contain two records for both hours at the time zone transition:

tscount
2021-10-31T00:00:00.000000Z2
2021-10-31T01:00:00.000000Z1
2021-10-31T02:00:00.000000Z1
2021-10-31T03:00:00.000000Z1

Similarly, given one data point per hour on this table, running SAMPLE BY 1d will have a count of 25 for this day when aligned to calendar time zone 'Europe/London'.

WITH OFFSET#

Aligning sampling calculation can be provided an arbitrary offset in the format '+/-HH:mm', for example:

  • '00:30' plus thirty minutes
  • '+00:30' plus thirty minutes
  • '-00:15' minus 15 minutes

The query uses the default offset '00:00' if the parameter is not set.

SELECT ts, count() FROM sensors
SAMPLE BY 1d
ALIGN TO CALENDAR WITH OFFSET '02:00'

In this case, the 24 hour samples begin at 2021-05-31T02:00:00.000000Z:

tscount
2021-05-31T02:00:00.000000Z2
2021-06-01T02:00:00.000000Z4
2021-06-02T02:00:00.000000Z1

Examples#

Assume the following table trades:

tsquantityprice
2021-05-31T23:45:10.000000Z10100.05
2021-06-01T00:01:33.000000Z5100.05
2021-06-01T00:15:14.000000Z200100.15
2021-06-01T00:30:40.000000Z300100.15
2021-06-01T00:45:20.000000Z10100
2021-06-01T01:00:50.000000Z50100.15

This query will return the number of trades per hour:

Hourly interval
SELECT ts, count() FROM trades SAMPLE BY 1h;
tscount
2021-05-31T23:45:10.000000Z3
2021-06-01T00:45:10.000000Z1
2021-05-31T23:45:10.000000Z1
2021-06-01T00:45:10.000000Z1

The following will return the trade volume in 30 minute intervals

30 minute interval
SELECT ts, sum(quantity*price) FROM trades SAMPLE BY 30m;
tssum
2021-05-31T23:45:10.000000Z1000.5
2021-06-01T00:15:10.000000Z16024
2021-06-01T00:45:10.000000Z8000
2021-06-01T00:15:10.000000Z8012
2021-06-01T00:45:10.000000Z8000

The following will return the average trade notional (where notional is = q * p) by day:

Daily interval
SELECT ts, avg(quantity*price) FROM trades SAMPLE BY 1d;
tsavg
2021-05-31T23:45:10.000000Z6839.416666666667

To make this sample align to calendar dates:

Calendar alignment
SELECT ts, avg(quantity*price) FROM trades SAMPLE BY 1d ALIGN TO CALENDAR;
tsavg
2021-05-31T00:00:00.000000Z1000.5
2021-06-01T00:00:00.000000Z8007.2

See also#

This section includes links to additional information such as tutorials.

  • SAMPLE BY: Blog post focusing on the data analysis ability of SAMPLE BY
Edit this page