Q Language - Query

Query in qsono più brevi e semplici ed estendono le capacità di sql. L'espressione di query principale è l '"espressione di selezione", che nella sua forma più semplice estrae le sotto-tabelle ma può anche creare nuove colonne.

La forma generale di a Select expression è il seguente -

Select columns by columns from table where conditions

**Note − by & where le frasi sono facoltative, solo l '"espressione da" è obbligatoria.

In generale, la sintassi sarà:

select [a] [by b] from t [where c]
update [a] [by b] from t [where c]

La sintassi di q le espressioni sembrano abbastanza simili a SQL, ma qle espressioni sono semplici e potenti. Un'espressione sql equivalente per quanto sopraq l'espressione sarebbe la seguente:

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

Tutte le clausole vengono eseguite sulle colonne e quindi qpuò trarre vantaggio dall'ordine. Poiché le query SQL non sono basate sull'ordine, non possono trarre tale vantaggio.

qle query relazionali sono generalmente di dimensioni molto più piccole rispetto al corrispondente sql. Le query ordinate e funzionali eseguono operazioni difficili in sql.

In un database storico, l'ordinamento del file whereLa clausola è molto importante perché influisce sulle prestazioni della query. Ilpartition variabile (data / mese / giorno) viene sempre prima seguita dalla colonna ordinata e indicizzata (generalmente la colonna sym).

Per esempio,

select from table where date in d, sym in s

è molto più veloce di

select from table where sym in s, date in d

Query di base

Scriviamo uno script di query nel blocco note (come di seguito), salviamo (come * .q) e quindi carichiamolo.

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.

Query con vincoli

* 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

Query con aggregazioni

* 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