where
where子句用来选择满足指定条件的记录。
例子
$ 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;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:34:07 |
C |
2200 |
49.6 |
09:36:42 |
MS |
1900 |
29.46 |
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
09:34:16 |
C |
1300 |
50.76 |
09:34:26 |
C |
2500 |
50.32 |
09:38:12 |
C |
8800 |
51.29 |
使用含有一个条件的where子句:
select * from t1 where sym=`IBM;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
select * from t1 where timestamp.minute()>=09:36m;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:36:42 |
MS |
1900 |
29.46 |
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:38:12 |
C |
8800 |
51.29 |
在where子句中,"=="等于"="。
select * from t1 where sym==`IBM;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
使用含有多个条件的where子句:
select * from t1 where sym=`IBM and qty>=2000 or timestamp>09:37:00;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
09:38:12 |
C |
8800 |
51.29 |
"and", "&&" 和 "," 表示必须同时满足用该符号连接的条件。
select * from t1 where qty>=2000, timestamp.minute()>=09:36m;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:38:12 |
C |
8800 |
51.29 |
select * from t1 where qty>=2000 and timestamp.minute()>=09:36m;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:38:12 |
C |
8800 |
51.29 |
select * from t1 where qty>=2000 && timestamp.minute()>=09:36m;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:38:12 |
C |
8800 |
51.29 |
where条件中的函数和模板:
下面的例子中,我们将价格高于平均价格的记录选择出来。这里对表中所有记录使用了函数avg。
select * from t1 where price>avg(price);
timestamp |
sym |
qty |
price |
---|---|---|---|
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
要计算每个股票的平均价格,可以使用 c/contextby。下面的例子查询每个股票的价格大于平均价格的记录。
select * from t1 where price>contextby(avg, price, sym) order by sym, price;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:34:16 |
C |
1300 |
50.76 |
09:38:12 |
C |
8800 |
51.29 |
09:35:26 |
IBM |
5400 |
175.23 |
09:36:59 |
MS |
3200 |
30.02 |
要随机抽取分区作为样本,只需在where子句中使用 sample 函数即可。
n=1000000
ID=rand(50, n)
x=rand(1.0, n)
t=table(ID, x)
db=database("C:/DolphinDB/Data/rangedb1", RANGE, 0 10 20 30 40 50)
pt = db.createPartitionedTable(t, `pt, `ID)
pt.append!(t)
pt=loadTable(db,`pt);
pt有5个分区,随机抽取两个分区的数据作为样本,可以使用下面的语句:
x = select * from pt where sample(ID, 0.4);
x = select * from pt where sample(ID, 2);