context by

context by is a unique feature in DolphinDB. It is an extension to ANSI SQL for convenient time-series data manipulation.

The traditional relational database doesn't support time series data processing. In RDBMS, a table is a set of rows and the order of rows is not modeled. We can apply aggregate functions such as min, max, avg, stdev to a group of rows, but we can't use order sensitive aggregate functions such as first, last, etc, or order sensitive vector functions such as cumsum, cummax, ratios, deltas on groups of rows. DolphinDB supports time series data processing. The context by clause make it very convenient to perform time series data processing within each group.

Both context by and group by conduct grouping. However, with group by, each group returns a scalar value; with context by, each group returns a vector of the same size as the group's records. The group by clause can only be used with aggregate functions, whereas the context by clause can be used with aggregate functions, moving window functions, cumulative functions, etc. The context by clause is often used with update statement. The context by clause can also be used together with the having clause. Please refer to the section about having.

context by is often used together with time-series functions such as cumsum and mavg etc. The results of these functions are affected by the order of rows within each context by group. We can use keyword csort after context by. csort sorts the rows within each context by group before conducting calculations in select statements. csort can use multiple columns including calculated columns and can sort in both ascending order (asc, the default sorting order) and descending order (desc). csort can be used with top clause to get the most recent observations within each group.

The example below illustrates the difference between group by and context by.

sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price);

t1;

select wavg(price,qty) as wvap, sum(qty) as totalqty from t1 group by sym;

select sym, price, qty, wavg(price,qty) as wvap, sum(qty) as totalqty from t1 context by sym;

To calculate stock returns for each firm, we cannot use group by. Instead we can use context by. We need to make sure the records are sorted appropriately within each group before we use context by.

select sym, timestamp, price, eachPre(\,price)-1.0 as ret from t1 context by sym;

We can use template function contextby for the same calculation, but the result is a vector instead of a table.

contextby(eachPre{ratio}, t1.price, t1.sym);
// output
[,,1.002037,1.016938,,1.001486,1.023387,0.991332,1.019277]

Here we use a partial application eachPre{ratio}. Please refer to Partial Application for details.

Calculate cumulative sum of trading volume for each stock in every minute:

select *, cumsum(qty) from t1 context by sym, timestamp.minute();

Use top clause with context by clause:

select top 2 * from t1 context by sym;

Please note that we cannot specify a range for the top clause when it is used with the context by clause:

select top 2:3 * from t1 context by sym;
// output
Syntax Error: [line #2] When top clause uses together with context clause in SQL query, can't specify a range in top clause

Use top clause and csort clause together with context by clause to get the most recent 2 records for each stock:

select top 2 * from t1 context by sym csort timestamp desc;

Use context by clause together with limit clause to get the first or the last few records for each stock:

select * from t1 context by sym limit 2;

Use context by clause together with csort and limit clause to get the last 2 records for each stock after sorting by qty:

select * from t1 context by sym limit -2;

Use context by clause together with csort and limit clause to get the last 2 records for each stock after sorting by qty:

select * from t1 context by sym csort qty limit -2;

Calculate fitted values of price from the regression of price on qty for each stock:

select *, ols(price, qty)[0]+ols(price, qty)[1]*qty as fittedPrice from t1 context by sym;

The context by clause can be used with the order by clause. The order by columns must be among the output columns.

select *, ols(price, qty)[0]+ols(price, qty)[1]*qty as fittedPrice from t1 context by sym order by timestamp;

context by is different from the contextby function in 3 aspects:

(1) contextby generates a vector while context by is used in a select clause to produce a table.

(2) contextby is limited to one grouping column whereas a context by clause can be used on multiple columns.

(3) contextby calculates one item for every call, whereas a context by clause can calculate multiple items.

n=1000000
ID=rand(100, n)
x=rand(10.0, n)
ta=table(ID, x)
tb=select * from ta order by ID;

timer select (NULL \:P x)-1 as ret from ta context by ID;
// output
Time elapsed: 4.018 ms

timer select (NULL \:P x)-1 as ret from tb context by ID;
// output
Time elapsed: 2.991 ms