Q 언어-쿼리

검색어 q더 짧고 간단하며 SQL의 기능을 확장합니다. 기본 쿼리 표현식은 '선택 표현식'으로, 가장 간단한 형식으로 하위 테이블을 추출하지만 새 열을 생성 할 수도 있습니다.

일반적인 형태의 Select expression 다음과 같습니다-

Select columns by columns from 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표현은 간단하고 강력합니다. 위와 동등한 SQL 표현식q 표현은 다음과 같습니다-

select [b] [a] from t [where c] [group by b order by b]
update t set [a] [where c]

모든 절은 열에서 실행되므로 q주문을 이용할 수 있습니다. SQL 쿼리는 순서를 기반으로하지 않기 때문에 이러한 이점을 얻을 수 없습니다.

q관계형 쿼리는 일반적으로 해당 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