Q语言 - 查询(Queries)
q中的查询更短更简单，并扩展了sql的功能。 主查询表达式是“选择表达式”，它以最简单的形式提取子表，但也可以创建新列。
Select expression的一般形式如下 -
<b class="notranslate">Select</b> columns <b class="notranslate">by</b> columns <b class="notranslate">from</b> table where conditions
**Note − by & where短语是可选的，只有'from expression'是必需的。
一般来说，语法将是 -
select [a] [by b] from t [where c]
update [a] [by b] from t [where c]
q表达式的语法看起来与SQL非常相似，但q表达式简单而强大。 上述q表达式的等效sql表达式如下 -
select [b] [a] from t [where c] [group by b order by b]
update t set [a] [where c]
所有子句都在列上执行，因此q可以利用顺序。 由于Sql查询不是基于订单，因此无法获得此优势。
与对应的sql相比，关系查询的大小通常要小得多。 有序和功能查询执行sql中很难的事情。
在历史数据库中， where子句的顺序非常重要，因为它会影响查询的性能。 partition变量（日期/月/日）始终首先出现，然后是已排序和索引的列（通常是sym列）。
例如，
select from table where date in d, sym in s
要快得多，
select from table where sym in s, date in d
基础知识查询
让我们在记事本中编写一个查询脚本（如下所示），保存（如* .q），然后加载它。
sym:asc`AIG`CITI`CSCO`IBM`MSFT;
ex:"NASDAQ"
dst:`$":c:/q/test/data/"; /database destination
@[dst;`sym;:;sym];
n:1000000;
trade:([]sym:n?`sym;time:10:30:00.0+til
n;price:n?3.3e;size:n?9;ex:n?ex);
quote:([]sym:n?`sym;time:10:30:00.0+til
n;bid:n?3.3e;ask:n?3.3e;bsize:n?9;asize:n?9;ex:n?ex);
{@[;`sym;`p#]`sym xasc x}each`trade`quote;
d:2014.08.07 2014.08.08 2014.08.09 2014.08.10 2014.08.11; /Date vector can also be changed by the user
dt:{[d;t].[dst;(`$string d;t;`);:;value t]};
d dt/:\:`trade`quote;
Note: Once you run this query, two folders .i.e. "test" and "data" will be created under "c:/q/", and date partition data can be seen inside data folder.
具有约束的查询
* Denotes HDB query
Select all IBM trades
select from trade where sym in `IBM
*Select all IBM trades on a certain day
thisday: 2014.08.11
select from trade where date=thisday,sym=`IBM
Select all IBM trades with a price 》 100
select from trade where sym=`IBM, price > 100.0
Select all IBM trades with a price less than or equal to 100
select from trade where sym=`IBM,not price > 100.0
*Select all IBM trades between 10.30 and 10.40, in the morning, on a certain date
thisday: 2014.08.11
select from trade where
date = thisday, sym = `IBM, time > 10:30:00.000,time < 10:40:00.000
Select all IBM trades in ascending order of price
`price xasc select from trade where sym =`IBM
*Select all IBM trades in descending order of price in a certain time frame
`price xdesc select from trade where date within 2014.08.07 2014.08.11, sym =`IBM
Composite sort − sort ascending order by sym and then sort the result in descending order of price
`sym xasc `price xdesc select from trade where date = 2014.08.07,size = 5
Select all IBM or MSFT trades
select from trade where sym in `IBM`MSFT
*Calculate count of all symbols in ascending order within a certain time frame
`numsym xasc select numsym: count i by sym from trade where date within 2014.08.07 2014.08.11
*Calculate count of all symbols in descending order within a certain time frame
`numsym xdesc select numsym: count i by sym from trade where date within 2014.08.07 2014.08.11
* What is the maximum price of IBM stock within a certain time frame, and when does this first happen?
select time,ask from quote where date within 2014.08.07 2014.08.11,
sym =`IBM, ask = exec first ask from select max ask from quote where
sym =`IBM
Select the last price for each sym in hourly buckets
select last price by hour:time.hh, sym from trade
具有聚合的查询
* Calculate vwap (Volume Weighted Average Price) of all symbols
select vwap:size wavg price by sym from trade
* Count the number of records (in millions) for a certain month
(select trade:1e-6*count i by date.dd from trade where date.month=2014.08m) + select quote:1e-6*count i by date.dd from quote where date.month=2014.08m
* HLOC – Daily High, Low, Open and Close for CSCO in a certain month
select high:max price,low:min price,open:first price,close:last price by date.dd from trade where date.month=2014.08m,sym =`CSCO
* Daily Vwap for CSCO in a certain month
select vwap:size wavg price by date.dd from trade where date.month = 2014.08m ,sym = `CSCO
* Calculate the hourly mean, variance and standard deviation of the price for AIG
select mean:avg price, variance:var price, stdDev:dev price by date, hour:time.hh from trade where sym = `AIG
Select the price range in hourly buckets
select range:max[price] – min price by date,sym,hour:time.hh from trade
* Daily Spread (average bid-ask) for CSCO in a certain month
select spread:avg bid-ask by date.dd from quote where date.month = 2014.08m, sym = `CSCO
* Daily Traded Values for all syms in a certain month
select dtv:sum size by date,sym from trade where date.month = 2014.08m
Extract a 5 minute vwap for CSCO
select size wavg price by 5 xbar time.minute from trade where sym = `CSCO
* Extract 10 minute bars for CSCO
select high:max price,low:min price,close:last price by date, 10 xbar time.minute from trade where sym = `CSCO
* Find the times when the price exceeds 100 basis points (100e-4) over the last price for CSCO for a certain day
select time from trade where date = 2014.08.11,sym = `CSCO,price > 1.01*last price
* Full Day Price and Volume for MSFT in 1 Minute Intervals for the last date in the database
select last price,last size by time.minute from trade where date = last date, sym = `MSFT
