Queries

Build a query using clauses and operators to specify its function. This section provides reference for queries, query clauses, and operators.

Syntax

select_clause 
from_clause
[matching_clause]
[where_clause]
[groupFilter_clause]
[groupBy_clause]
[groupOrder_clause]
[having_clause]

Components

`select_clause`	Defines the set of columns to be included in the output. See below and SELECT Clause for more information.
`from_clause`	Selects the source data is derived from. See below and FROM Clause for more information.
`matching_clause`	Used for pattern matching. See MATCHING Clause and Pattern Matching for more information.
`where_clause`	Performs a filter. See WHERE Clause and Filters for more information.
`groupFilter_clause`	Filters incoming data in aggregation. See GROUP FILTER Clause and Aggregation for more information.
`groupBy_clause`	Specifies what collection of rows to use the aggregation operation on. See GROUP BY Clause and Aggregation for more information.
`groupOrder_clause`	Orders the data in a group before aggregation. See GROUP ORDER BY Clause and Aggregation for more information.
`having_clause`	Filters data that is output by the derived components in aggregation. See HAVING Clause and Aggregation for more information.

Usage

Queries can use the aforementioned clauses to fulfill various functions, as outlined in the CCL Query Constructions chapter. However, the basic structure remains the same when starting a query. The example below demonstrates the use of both the SELECT clause and FROM clause as would be seen in any query.

The SELECT clause is used directly after the AS clause. The purpose of the SELECT clause is to determine which columns from the source or expressions the query is to use.

Following the SELECT clause, the FROM clause names the source used by the query. Following the FROM clause, implement available clauses to use filters, unions, joins, pattern matching, and aggregation on the queried data.

Example

This example obtains the total trades, volume, and VWAP per trading symbol in five minute intervals.

[...]
SELECT
    q.Symbol,
    (trunc(q.TradeTime) + (((q.TradeTime - trunc(q.TradeTime))/300)*300)) FiveMinuteBucket,
    sum(q.Shares * q.Price)/sum(q.Shares) Vwap,
    count(*) TotalTrades,
    sum(q.Shares) TotalVolume
FROM
    QTrades q 
[...]