6.5. Select¶

6.5.1. Synopsis¶

SELECT [ { expression | * } [, ...] ]
FROM { table_name | find_expression }

[ IN { RANGE range_spec | '[' RANGE range_spec [, ...] ']' }
[ WITH DAYS IN ( day, day ) ]
[ WITH TIME IN ( time, time ) ] ]

[ WHERE condition ]

[ GROUP BY group [, ... ] ]

[ LIMIT limit ]

[ OFFSET offset ]

where expression is one of:

column_name
function ( column_name )

where range_spec is one of:

( timestamp, timestamp )
( timestamp, time_offset )

where group is one of:

expression
interval


6.5.2. Description¶

SELECT retrieves rows from one or more tables. A SELECT statement can perform calculations on rows prior to returning the result which are performed server-side and distributed over the entire cluster.

6.5.3. Parameters¶

table_name
The name of the table to retrieve rows from.
find_expression
When your tables are tagged, you can use inline key/value lookups to perform your query over multiple tables. To match all tables that have the tag “stocks”, you can use FIND(tag='stocks' AND type=ts).
column_name
A column name to read data from. Must be part of the table’s schema or will throw an error otherwise.
function
An aggregate function to apply over. Can only be used in combination with GROUP BY. For valid functions, please refer to the function reference.
condition
A WHERE condition is any expression that evaluates to a boolean. Any row that does not match this predicate will be filtered from the results. For an overview of valid operators, please refer to the comparison operators reference.
limit
Accepts a non-negative integer to limit the maximum number of rows returned by the query. Typical use cases include pagination and returning the top-X results.
offset
Accepts a non-negative integer to skip rows of the result. Most common use cases include pagination in combination with limit.
timestamp
An absolute timestamp. This can be either a date or a date + time. Supports precision for days, seconds or nanoseconds. For more information, please refer to the documentation for timestamps.
time_offset
A relative offset, can only be used in combination with an absolute_timestamp. For more information, please refer to the documentation for timestamps.
day
A weekday. Can be one of mon, tue, wed, thu, fri, sat or sun. When defining a subrange using WITH DAYS IN, is both left and right inclusive: WITH DAYS IN (mon, tue) will match both Monday and Tuesday.
time
Time of day, having a precision of either minutes, seconds or nanoseconds. When defining a subrange using WITH TIME IN, is left inclusive and right exclusive: WITH TIME IN (09:22:00, 09:22:01) will not match a row whose time is exactly 09:22:01 AM.
interval
An interval (in time) to group agregates by. Valid values are any durations as specific in the documentation for durations.

6.5.4. Examples¶

Select all columns from all rows:

SELECT * FROM example


Note

The examples below assume typical open, high, low, close, volume stocks time series.

Get everything between January 1st 2007 and January 1st 2008 (left inclusive) for the time series “stocks.apple”:

SELECT * FROM stocks.apple IN RANGE(2007, 2008)


Get everything between November 3rd 2017, 20:01:22 and December 2nd, 2017, 06:20:10 (left inclusive) for the time series “stocks.apple”:

SELECT * FROM stocks.apple IN RANGE(2017-11-03T20:01:22, 2017-12-02T06:20:10)


Get the first 10 days of 2007 for “stocks.apple”:

SELECT * FROM stocks.apple IN RANGE(2007, +10d)


Get the last second of 2016 for “stocks.apple”:

SELECT * FROM stocks.apple IN RANGE(2017, -1s)


Get the close and open values that are greater than 3 of “stocks.apple” for the first 10 days of 2016:

SELECT close, open FROM stocks.apple IN RANGE(2016, +10d) WHERE (close > 3) AND (open > 3)


Get the last close value for March 28th 2016:

SELECT LAST(close) FROM stocks.apple IN RANGE(2016-03-28, +1d)


Note

The examples below assume typical open, high, low, close, volume stocks time series.

Get the open and close value when volume is greater than 0 for the first 5 days of 2016 and 2017 for “stocks.apple”:

SELECT open, close FROM stocks.apple IN [range(2016, +5d), range(2017, +5d)] WHERE volume > 0


Get the hourly arithmetic mean of volume exchanged for all nasdaq stocks for yesterday:

SELECT arithmetic_mean(volume) FROM find(tag='nasdaq' AND type=ts) IN RANGE(yesterday, +1d) GROUP BY hour


Get the sum of volumes for every Friday of January 2008 between 16:00 and 17:00 for “stocks.apple”:

SELECT sum(volume) FROM stocks.apple IN RANGE(2008, +month) WITH DAYS IN (fri, fri) WITH TIME IN (16:00, 17:00)


Get the daily open, high, low, close, volume for “stocks.apple” for the last 30 days:

SELECT first(open), max(high), min(low), last(close), sum(volume) FROM stocks.apple IN RANGE(today, -30d) GROUP BY day


Get the sum of volume and the number of lines for the last hour by 10 seconds group:

SELECT sum(volume), count(volume) FROM stocks.apple IN RANGE(now, -1h) GROUP BY 10s


Get the sum of volumes for “stocks.apple” the year 2008 and 2010, grouped by month (gregorian calendar):

SELECT sum(volume) FROM stocks.apple IN [RANGE(2008, +1y), RANGE(2010, +1y)] GROUP BY month

6.4. Insert
6.6. Timestamps