SQL2 Select Statements
Introduction
This page outlines the components of a SQL statement currently supported by the new SQL2 implementation. SQL2 currently only supports a specific set of SELECT statements.
Supported Queries
The supported SELECT statements are designed to work with the SQL2 API and are limited to the following structure:
SELECT ns FROM t WHERE b GROUP BY f ORDER BY o LIMIT N
Warning
The SQL2 API does not support INSERT, CREATE, or DELETE statements.
Aggregations and Expressions
When using SQL2 to select data from a table, you can specify specific columns, aggregations of column data, and expressions that combine multiple operators and columns. Refer to the SQL2 Functions guide for a list of available operators and expressions.
Info
The selection of column and table names are case sensitive.
Warning
You cannot have two aggregate functions in the same expression. For example, SELECT max(avg(price)) FROM trade fails. A list of currently supported aggregate functions can be found in the Aggregations section of the SQL2 Functions guide.
Example - Calculating Maximum, Minimum, and Average Trade Prices
The following query demonstrates the use of aggregations and expressions in SQL2, calculating the maximum, minimum, and average trade prices:
SELECT max(price), min(price), avg(price)FROM trade
The output of this query is:
price price1 price2
--------------------------
713.8885 71.71424 279.6814
Note
Since the query doesn't rename the aggregations (using the as keyword), the implementation picks a name based on the column used. Alternatively, you can assign custom names to the aggregations for improved clarity.
Example - Calculating Maximum, Minimum, and Average Trade Prices with Renamed Aggregations
The following query demonstrates the use of aggregations and expressions in SQL2, calculating the maximum, minimum, and average trade prices with renamed aggregations:
SELECT max(price) as mx, min(price) as mn, avg(price) as ag FROM trade
The output of this query is:
mx mn ag
--------------------------
713.8885 71.71424 279.6814
Group By
The GROUP BY clause is used to group elements with the same values, often in conjunction with some aggregations. When performing a SQL SELECT with a GROUP BY, all columns in the SELECT need to either be in the GROUP BY or be aggregated.
Example - Calculating Maximum, Minimum, and Average Trade Prices Grouped by Symbol
The following query demonstrates the use of the GROUP BY clause in combination with aggregations, calculating the maximum, minimum, and average trade prices grouped by the sym column:
SELECT sym, max(price) as mx, min(price) as mn, avg(price) as ag FROM trade GROUP BY sym
sym mx mn ag
-----------------------------------
BBHB.CAN 264.4558 262.9887 263.7229
DNDF.CAN 713.8885 312.4958 341.5133
FNLH.CAN 448.8098 400.4496 420.1849
GIGE.CAN 403.8341 402.3122 402.9778
HEMA.CAN 72.00303 71.71424 71.87358
JBMH.CAN 138.2289 125.3691 131.4277
JFMB.CAN 445.4615 403.6521 422.6718
LATE.CAN 429.8842 87.76916 280.6936
MGPJ.CAN 175.5454 78.59578 83.18133
UPD.CAN 258.5807 258.5474 258.564
Example - Calculating Average Trade Prices Grouped by Symbol and Date
In this example, the GROUP BY clause is used to group the results by both sym and date columns, while calculating the average trade price:
SELECT sym, date, avg(price) FROM trade GROUP BY sym, date
sym date price
----------------------------
FNLH.CAN 2024.02.14 128.0927
JBMH.CAN 2024.02.14 396.6086
MGPJ.CAN 2024.02.14 161.7322
Warning
Positional based grouping is not currently supported. For example, SELECT avg(price) FROM trade GROUP BY 1 is not supported.
Warning
Support for the HAVING keyword is not currently supported.
Limit
You can limit the size of the response table using the LIMIT keyword. The number specified after the keyword is the maximum number of records to be returned by the query.
SELECT * FROM table ... LIMIT N
Example - Selecting Symbol, Time, and Price from Trade Data (Limited to 5 Rows)
The following query selects the symbol, time, and price from the trade table, limiting the result to 5 rows:
SELECT sym, realTime, price FROM trade LIMIT 5
sym realTime price
-----------------------------------------------
FNLH.CAN 2024.02.14D15:17:12.903251437 127.9444
FNLH.CAN 2024.02.14D15:17:13.403121927 127.9526
FNLH.CAN 2024.02.14D15:17:22.406010605 127.9606
FNLH.CAN 2024.02.14D15:17:26.408422590 127.9271
FNLH.CAN 2024.02.14D15:17:51.421995461 127.9528
Order By
Using the ORDER BY keyword, you can sort the payload in either ascending or descending order of the returned columns. This is done by passing a column-separated list of columns and the direction in which to sort.
SELECT * FROM table ORDER BY col1 , col2, ... {asc|desc} ...
Example - Selecting Symbol, Time, and Price from Trade Data Sorted by Time in Descending Order (Limited to 5 Rows)
The following query selects the symbol, time, and price from the trade table where the symbol is FNLH.CAN, sorts the result by time in descending order, and limits the output to 5 rows:
SELECT sym,realTime,price FROM trade WHERE sym='FNLH.CAN' ORDER BY realTime desc limit 5
sym realTime price
-----------------------------------------------
FNLH.CAN 2024.02.14D22:16:11.591376123 126.6398
FNLH.CAN 2024.02.14D22:16:07.589039047 126.6394
FNLH.CAN 2024.02.14D22:16:03.087055255 126.6601
FNLH.CAN 2024.02.14D22:15:59.082678241 126.6472
FNLH.CAN 2024.02.14D22:15:51.075511179 126.678
Note
When sorting multiple columns, you cannot use both ASC and DESC in the same query. For example, you cannot have a query with ORDER BY col1 ASC, col2 DESC in the same query. Additionally, sorting must be performed on a column included in the selected result.
Where clause
You can use the WHERE clause to filter the results returned by the API based on arbitrary expressions that return a list of booleans. Common functions and operators to used in a WHERE clause are the comparison operators defined in the SQL Functions section.
Example - Selecting Symbol, Time, Price, and Size from Trade Data for Specific Symbols, Price, and Size Range (Limited to 5 Rows)
The following query is an example of combining multiple WHERE clauses:
SELECT sym, realTime, price, size FROM trade WHERE sym in ('JBMH.CAN','MGPJ.CAN') and price > 127 and size between 400 and 600 LIMIT 5
sym realTime price size
----------------------------------------------------
JBMH.CAN 2024.01.06D00:00:49.500000000 127.975 401
JBMH.CAN 2024.01.06D00:02:19.500000000 127.9823 574
JBMH.CAN 2024.01.06D00:10:19.500000000 128.1451 558
JBMH.CAN 2024.01.06D00:16:44.500000000 128.0778 539
JBMH.CAN 2024.01.06D00:20:04.500000000 127.9972 484
Warning
Irreversible operations are not permitted in WHERE clauses when restricting the date or (partition) time column. For example, the following query is not supported:
SELECT * FROM trade WHERE DATE(realTime) = '2024-01-06'
// OK.
SELECT * FROM myTable WHERE date+1 = '2024-01-26'
// Not supported.
SELECT * FROM myTable WHERE date % 2 = 0