timestamp = [14:23:35,14:15:03,13:33:30,10:07:59,10:21:34,09:53:41,13:34:52,11:03:24,10:52:13,12:39:42];
qty = 1300 1900 6800 2200 8300 4600 1400 8800 5800 700;
price = 50.76 29.46 174.97 49.6 51.2 50.1 29.1 172.93 173.5 175.1;
sym = `C`MS`IBM`C`C`C`MS`IBM`IBM`IBM$SUMBOL;
qty[qty>4000]=NULL;
price[price<30||price>175]=NULL;
t1 = table(timestamp, sym, qty, price);
t1;
timestamp |
sym |
qty |
price |
14:23:35 |
C |
1300 |
50.76 |
14:15:03 |
MS |
1900 |
|
13:33:30 |
IBM |
|
174.97 |
10:07:59 |
C |
2200 |
49.6 |
10:21:34 |
C |
|
51.2 |
09:53:41 |
C |
|
50.1 |
13:34:52 |
MS |
1400 |
|
11:03:24 |
IBM |
|
172.93 |
10:52:13 |
IBM |
|
173.5 |
12:39:42 |
IBM |
700 |
|
select timestamp, qty, price from t1 where sym=`IBM order by price;
timestamp |
qty |
price |
12:39:42 |
700 |
|
11:03:24 |
|
172.93 |
10:52:13 |
|
173.5 |
13:33:30 |
|
174.97 |
// select records with prices less than the average price for all trades.
select * from t1 where price<avg(price) order by price;
timestamp |
sym |
qty |
price |
14:15:03 |
MS |
1900 |
|
13:34:52 |
MS |
1400 |
|
12:39:42 |
IBM |
700 |
|
10:07:59 |
C |
2200 |
49.6 |
09:53:41 |
C |
|
50.1 |
14:23:35 |
C |
1300 |
50.76 |
10:21:34 |
C |
|
51.2 |
// select records with prices greater than their group average
select * from t1 where price>contextby(avg, price, sym);
timestamp |
sym |
qty |
price |
14:23:35 |
C |
1300 |
50.76 |
13:33:30 |
IBM |
|
174.97 |
10:21:34 |
C |
|
51.2 |
// select records with prices lower than their group average
select * from t1 where price<contextby(avg, price, sym);
timestamp |
sym |
qty |
price |
10:07:59 |
C |
2200 |
49.6 |
09:53:41 |
C |
|
50.1 |
11:03:24 |
IBM |
|
172.93 |
10:52:13 |
IBM |
|
173.5 |
12:39:42 |
IBM |
700 |
|