Mawonekedwe a chilankhulo cha Q ndi KDB+ pogwiritsa ntchito chitsanzo cha nthawi yeniyeni

Mutha kuwerenga zomwe maziko a KDB+, chilankhulo cha pulogalamu ya Q, mphamvu zawo ndi zofooka zawo zili m'mbuyomu. nkhani ndipo mwachidule m’mawu oyamba. M'nkhaniyi, tidzagwiritsa ntchito ntchito pa Q yomwe idzakonza mtsinje wa data womwe ukubwera ndikuwerengera ntchito zosiyanasiyana zophatikizira mphindi iliyonse mu "nthawi yeniyeni" (ie, idzakhala ndi nthawi yowerengera zonse gawo lotsatira la deta). Chofunikira chachikulu cha Q ndikuti ndi chilankhulo cha vector chomwe chimakulolani kuti mugwiritse ntchito osati ndi chinthu chimodzi, koma ndi masanjidwe awo, magulu amitundu ndi zinthu zina zovuta. Zilankhulo monga Q ndi abale ake K, J, APL ndizodziwika chifukwa chakufupika. Nthawi zambiri, pulogalamu yomwe imatenga ma code angapo m'chilankhulo chodziwika bwino monga Java imatha kulembedwa pamizere ingapo. Izi ndi zomwe ndikufuna kuwonetsa m'nkhaniyi.

Mau oyamba

KDB+ ndi nkhokwe yachitsanzo yomwe imayang'ana pazambiri zambiri, zokonzedwa mwanjira inayake (makamaka ndi nthawi). Amagwiritsidwa ntchito makamaka m'mabungwe azachuma - mabanki, ndalama zogulira ndalama, makampani a inshuwaransi. Chiyankhulo cha Q ndi chilankhulo chamkati cha KDB+ chomwe chimakulolani kuti mugwiritse ntchito bwino datayi. Lingaliro la Q ndi lalifupi komanso logwira mtima, pomwe kumveka kumaperekedwa nsembe. Izi zimatsimikiziridwa ndi mfundo yakuti chinenero cha vector chidzakhala chovuta kumvetsa mulimonsemo, ndipo kufupikitsa ndi kulemera kwa kujambula kumakulolani kuti muwone gawo lalikulu la pulogalamuyo pawindo limodzi, lomwe pamapeto pake limapangitsa kuti likhale losavuta kumvetsa.

Munkhaniyi tikhazikitsa pulogalamu yokwanira mu Q ndipo mungafune kuyesa. Kuti muchite izi, mufunika Q yeniyeni. Mutha kutsitsa mtundu waulere wa 32-bit patsamba la kampani ya kx - www.kx.com. Kumeneko, ngati mukufuna, mudzapeza zambiri za Q, bukuli Q Kwa Anthu Achivundi ndi nkhani zosiyanasiyana pamutuwu.

Kupanga kwa vuto

Pali gwero lomwe limatumiza tebulo lokhala ndi data pama 25 milliseconds aliwonse. Popeza KDB+ imagwiritsidwa ntchito makamaka pazachuma, tidzaganiza kuti iyi ndi tebulo lazochita (malonda), lomwe lili ndi magawo otsatirawa: nthawi (nthawi mu milliseconds), sym (matchulidwe akampani pa stock exchange - IBM, AAPL,…), mtengo (mtengo womwe magawowo adagulidwa), kukula (kukula kwa malonda). Nthawi ya 25 millisecond ndiyosakhazikika, osati yaying'ono komanso yosatalika. Kukhalapo kwake kumatanthauza kuti deta imabwera kuntchito yomwe ili kale ndi buffer. Kungakhale kosavuta kukhazikitsa buffering kumbali ya ntchito, kuphatikiza kusungitsa kwamphamvu kutengera katundu wapano, koma kuphweka, timayang'ana pakanthawi kokhazikika.

Ntchitoyi iyenera kuwerengera mphindi iliyonse pachizindikiro chilichonse chomwe chikubwera kuchokera pamndandanda wazophatikiza - mtengo wokulirapo, mtengo wapakati, kukula kwake, ndi zina zambiri. mfundo zothandiza. Kuti zikhale zosavuta, tidzaganiza kuti ntchito zonse zikhoza kuwerengedwa mowonjezereka, i.e. kuti mupeze mtengo watsopano, ndikwanira kudziwa manambala awiri - akale ndi omwe akubwera. Mwachitsanzo, ma function max, avareji, sum ali ndi katundu uyu, koma ntchito yapakati ilibe.

Tidzaganizanso kuti mtsinje wa data womwe ukubwera ndi nthawi yolamulidwa. Izi zidzatipatsa mwayi wogwira ntchito ndi mphindi yomaliza. M'zochita, ndikwanira kuti mutha kugwira ntchito ndi mphindi zapano ndi zam'mbuyomu ngati zosintha zina zachedwa. Kuti zikhale zosavuta, sitingaganizire nkhaniyi.

Ntchito zophatikizira

Ntchito zophatikizira zofunika zandalikidwa pansipa. Ndinatenga ambiri aiwo kuti ndiwonjezere katundu pautumiki:

• mkulu - mtengo wapamwamba - mtengo wapamwamba pamphindi.
• otsika - mtengo wotsika - mtengo wocheperako pamphindi.
• firstPrice - mtengo woyamba - mtengo woyamba pamphindi.
• LastPrice - mtengo wotsiriza - mtengo wotsiriza pamphindi.
• firstSize - kukula koyamba - kukula koyamba kwa malonda pamphindi.
• LastSize - kukula komaliza - kukula komaliza kwamalonda mumphindi imodzi.
• numTrades - count i - chiwerengero cha malonda pamphindi.
• voliyumu - kukula kwake - kuchuluka kwa makulidwe amalonda pamphindi.
• pvolume - mtengo wamtengo - kuchuluka kwamitengo pamphindi, yofunikira pa avgPrice.
• - Kuchulukitsa mtengo * kukula - kuchuluka kwazomwe zikuchitika pamphindi.
• avgPrice - pvolume%numTrades - mtengo wapakati pamphindi.
• avgSize - voliyumu% numTrades - kukula kwapakati pa mphindi imodzi.
• vwap - zochulukira% voliyumu - mtengo wapakati pamphindi wolemedwa ndi kukula kwake.
• cumVolume - kuchuluka kwa voliyumu - kuchuluka kwazinthu zomwe zachitika nthawi yonseyi.

Tiyeni tikambirane nthawi yomweyo mfundo imodzi yosadziwika bwino - momwe tingayambitsire mizati iyi kwa nthawi yoyamba komanso mphindi iliyonse yotsatira. Mizati ina yamtundu woyambaPrice iyenera kukhazikitsidwa kuti ikhale yopanda phindu nthawi iliyonse; mtengo wake sudziwika. Mitundu ina ya voliyumu nthawi zonse iyenera kukhazikitsidwa ku 0. Palinso mizati yomwe imafuna njira yophatikizira - mwachitsanzo, cumVolume iyenera kukopera kuchokera pamphindi yapitayi, ndipo yoyamba ikhale 0. Tiyeni tiyike magawo onsewa pogwiritsa ntchito deta ya mtanthauzira mawu. mtundu (wofanana ndi mbiri):

// list ! list – создать словарь, 0n – float null, 0N – long null, `sym – тип символ, `sym1`sym2 – список символов
initWith:`sym`time`high`low`firstPrice`lastPrice`firstSize`lastSize`numTrades`volume`pvolume`turnover`avgPrice`avgSize`vwap`cumVolume!(`;00:00;0n;0n;0n;0n;0N;0N;0;0;0.0;0.0;0n;0n;0n;0);
aggCols:reverse key[initWith] except `sym`time; // список всех вычисляемых колонок, reverse объяснен ниже

Ndinawonjezera sym ndi nthawi ku dikishonale kuti zitheke, tsopano initWith ndi mzere wopangidwa mokonzeka kuchokera patebulo lophatikizana lomaliza, pomwe limatsalira kukhazikitsa sym ndi nthawi yoyenera. Mutha kugwiritsa ntchito kuwonjezera mizere yatsopano patebulo.

Tidzafunika aggCols popanga ntchito yophatikiza. Mndandanda uyenera kutembenuzidwa chifukwa cha dongosolo lomwe mawu a Q amawunikidwa (kuchokera kumanja kupita kumanzere). Cholinga ndikuwonetsetsa kuti kuwerengera kumachokera kumtunda kupita ku cumVolume, popeza zigawo zina zimadalira zam'mbuyo.

Mizati yomwe ikuyenera kukopera ku miniti yatsopano kuchokera m'mbuyomu, ndime ya sym imawonjezedwa kuti ikhale yosavuta:

rollColumns:`sym`cumVolume;

Tsopano tiyeni tigawane zipilalazo m'magulu malinga ndi momwe ziyenera kusinthidwa. Mitundu itatu ingasiyanitsidwe:

1. Accumulators (voliyumu, kubweza, ..) - tiyenera kuwonjezera mtengo womwe ukubwera kwa wam'mbuyo.
2. Ndi mfundo yapadera (yapamwamba, yotsika, ..) - mtengo woyamba mumphindi umatengedwa kuchokera ku deta yomwe ikubwera, zina zonse zimawerengedwa pogwiritsa ntchito ntchitoyi.
3. Mpumulo. Kuwerengera nthawi zonse pogwiritsa ntchito ntchito.

Tiyeni tifotokoze zosintha zamakalasi awa:

accumulatorCols:`numTrades`volume`pvolume`turnover;
specialCols:`high`low`firstPrice`firstSize;

Mawerengedwe dongosolo

Tisintha tebulo lophatikizidwa mu magawo awiri. Kuti tigwire bwino ntchito, timayamba kuchepetsa tebulo lomwe likubwera kuti pakhale mzere umodzi wokha pamtundu uliwonse ndi mphindi. Mfundo yakuti ntchito zathu zonse ndizowonjezereka komanso zogwirizana zimatsimikizira kuti zotsatira za sitepe yowonjezerayi sizidzasintha. Mutha kuchepetsa tebulo pogwiritsa ntchito kusankha:

select high:max price, low:min price … by sym,time.minute from table

Njirayi ili ndi vuto - seti ya mizati yowerengeka imatanthauziridwa. Mwamwayi, mu Q, kusankha kumagwiritsidwanso ntchito ngati ntchito momwe mungasinthire mikangano yopangidwa mwamphamvu:

?[table;whereClause;byClause;selectClause]

Sindidzafotokoza mwatsatanetsatane momwe mikangano imakhalira; kwa ife, pokhapokha ndi kusankha mawu omwe sangakhale ang'onoang'ono ndipo ayenera kukhala otanthauzira amitundu! Choncho, kuchepa kwa ntchito kungatanthauzidwe motere:

selExpression:`high`low`firstPrice`lastPrice`firstSize`lastSize`numTrades`volume`pvolume`turnover!parse each ("max price";"min price";"first price";"last price";"first size";"last size";"count i";"sum size";"sum price";"sum price*size"); // each это функция map в Q для одного списка
preprocess:?[;();`sym`time!`sym`time.minute;selExpression];

Kuti zimveke bwino, ndidagwiritsa ntchito parse, yomwe imatembenuza chingwe chokhala ndi mawu a Q kukhala mtengo womwe ungathe kuperekedwa ku ntchito ya eval komanso yomwe ikufunika pakusankha ntchito. Komanso dziwani kuti preprocess imatanthauzidwa ngati chiwonetsero (ie, ntchito yokhala ndi mfundo zofotokozedwa pang'ono) ya ntchito yosankhidwa, mkangano umodzi (tebulo) ulibe. Ngati tigwiritsa ntchito preprocess patebulo, tipeza tebulo loponderezedwa.

Gawo lachiwiri ndikukonzanso tebulo lophatikizidwa. Choyamba tiyeni tilembe algorithm mu pseudocode:

for each sym in inputTable
  idx: row index in agg table for sym+currentTime;
  aggTable[idx;`high]: aggTable[idx;`high] | inputTable[sym;`high];
  aggTable[idx;`volume]: aggTable[idx;`volume] + inputTable[sym;`volume];
  …

Mu Q, ndizofala kugwiritsa ntchito mapu/kuchepetsa magwiridwe antchito m'malo mwa malupu. Koma popeza Q ndi chilankhulo cha vector ndipo titha kugwiritsa ntchito mawonekedwe onse pazizindikiro zonse nthawi imodzi, ndiye pakuyerekeza koyamba titha kuchita popanda lupu konse, kugwira ntchito pazizindikiro zonse nthawi imodzi:

idx:calcIdx inputTable;
row:aggTable idx;
aggTable[idx;`high]: row[`high] | inputTable`high;
aggTable[idx;`volume]: row[`volume] + inputTable`volume;
…

Koma titha kupita patsogolo, Q ili ndi wogwiritsa ntchito wapadera komanso wamphamvu kwambiri - wogwira ntchito wamba. Zimakuthandizani kuti musinthe zikhalidwe zamadongosolo ovuta kugwiritsa ntchito mndandanda wa ma index, ntchito ndi mikangano. M'malo athu zikuwoneka ngati izi:

idx:calcIdx inputTable;
rows:aggTable idx;
// .[target;(idx0;idx1;..);function;argument] ~ target[idx 0;idx 1;…]: function[target[idx 0;idx 1;…];argument], в нашем случае функция – это присваивание
.[aggTable;(idx;aggCols);:;flip (row[`high] | inputTable`high;row[`volume] + inputTable`volume;…)];

Tsoka ilo, kuti mugawire tebulo pamafunika mndandanda wa mizere, osati mizere, ndipo muyenera kusintha matrix (mndandanda wamizere kuti mulembe mizere) pogwiritsa ntchito flip. Izi ndizokwera mtengo patebulo lalikulu, kotero m'malo mwake timagwiritsa ntchito gawo lililonse pagawo lililonse padera, pogwiritsa ntchito mapu (omwe amawoneka ngati apostrophe):

.[aggTable;;:;]'[(idx;)each aggCols; (row[`high] | inputTable`high;row[`volume] + inputTable`volume;…)];

Timagwiritsanso ntchito projekiti ya ntchito. Komanso dziwani kuti mu Q, kupanga mndandanda ndi ntchito ndipo tikhoza kuyitcha pogwiritsa ntchito (mapu) ntchito kuti tipeze mndandanda wa mndandanda.

Kuonetsetsa kuti mizati yowerengeredwa sinakhazikitsidwe, tidzapanga mawu omwe ali pamwambapa mwamphamvu. Tiyeni tifotokoze kaye ntchito kuti tiwerengere gawo lililonse, pogwiritsa ntchito mizere ndi ma inp kutengera zomwe zaphatikizidwa ndi zolowetsa:

aggExpression:`high`low`firstPrice`lastPrice`firstSize`lastSize`avgPrice`avgSize`vwap`cumVolume!
 ("row[`high]|inp`high";"row[`low]&inp`low";"row`firstPrice";"inp`lastPrice";"row`firstSize";"inp`lastSize";"pvolume%numTrades";"volume%numTrades";"turnover%volume";"row[`cumVolume]+inp`volume");

Mizati ina ndi yapadera; mtengo wake woyamba suyenera kuwerengedwa ndi ntchitoyo. Titha kudziwa kuti ndi yoyamba ndi mzere [`numTrades] - ngati ili ndi 0, ndiye kuti mtengo ndi woyamba. Q ili ndi ntchito yosankha - ?[Boolean list;list1;list2] - yomwe imasankha mtengo kuchokera pamndandanda 1 kapena 2 kutengera momwe zilili mumtsutso woyamba:

// high -> ?[isFirst;inp`high;row[`high]|inp`high]
// @ - тоже обобщенное присваивание для случая когда индекс неглубокий
@[`aggExpression;specialCols;{[x;y]"?[isFirst;inp`",y,";",x,"]"};string specialCols];

Apa ndidayitanira ntchito yokhazikika ndi ntchito yanga (mawu opindika). Imalandila mtengo wapano (mkangano woyamba) ndi mkangano wowonjezera, womwe ndimadutsa pagawo la 4.

Tiyeni tiwonjeze okamba za batri padera, popeza ntchito yake ndi yofanana kwa iwo:

// volume -> row[`volume]+inp`volume
aggExpression[accumulatorCols]:{"row[`",x,"]+inp`",x } each string accumulatorCols;

Uwu ndi ntchito wamba ndi miyezo ya Q, koma ndikupereka mndandanda wamakhalidwe nthawi imodzi. Pomaliza, tiyeni tipange ntchito yayikulu:

// ":",/:aggExprs ~ map[{":",x};aggExpr] => ":row[`high]|inp`high" присвоим вычисленное значение переменной, потому что некоторые колонки зависят от уже вычисленных значений
// string[cols],'exprs ~ map[,;string[cols];exprs] => "high:row[`high]|inp`high" завершим создание присваивания. ,’ расшифровывается как map[concat]
// ";" sv exprs – String from Vector (sv), соединяет список строк вставляя “;” посредине
updateAgg:value "{[aggTable;idx;inp] row:aggTable idx; isFirst_0=row`numTrades; .[aggTable;;:;]'[(idx;)each aggCols;(",(";"sv string[aggCols],'":",/:aggExpression aggCols),")]}";

Ndi mawu awa, ndimapanga ntchito kuchokera ku chingwe chomwe chili ndi mawu omwe ndapereka pamwambapa. Chotsatiracho chidzawoneka motere:

{[aggTable;idx;inp] rows:aggTable idx; isFirst_0=row`numTrades; .[aggTable;;:;]'[(idx;)each aggCols ;(cumVolume:row[`cumVolume]+inp`cumVolume;… ; high:?[isFirst;inp`high;row[`high]|inp`high])]}

Dongosolo lowunikira ndime latembenuzidwa chifukwa mu Q dongosolo lowunika limachokera kumanja kupita kumanzere.

Tsopano tili ndi ntchito ziwiri zazikulu zofunika kuwerengera, timangofunika kuwonjezera pang'ono zomangamanga ndipo ntchitoyo ndi yokonzeka.

Masitepe omaliza

Tili ndi preprocess ndi updateAgg ntchito zomwe zimagwira ntchito yonse. Koma m'pofunikabe kuonetsetsa kusintha kolondola kupyolera mu mphindi ndikuwerengera ma index a aggregation. Choyamba, tiyeni tifotokoze ntchito ya init:

init:{
  tradeAgg:: 0#enlist[initWith]; // создаем пустую типизированную таблицу, enlist превращает словарь в таблицу, а 0# означает взять 0 элементов из нее
  currTime::00:00; // начнем с 0, :: означает, что присваивание в глобальную переменную
  currSyms::`u#`symbol$(); // `u# - превращает список в дерево, для ускорения поиска элементов
  offset::0; // индекс в tradeAgg, где начинается текущая минута 
  rollCache:: `sym xkey update `u#sym from rollColumns#tradeAgg; // кэш для последних значений roll колонок, таблица с ключом sym
 }

Tidzafotokozeranso ntchito ya roll, yomwe isintha miniti yomwe ilipo:

roll:{[tm]
  if[currTime>tm; :init[]]; // если перевалили за полночь, то просто вызовем init
  rollCache,::offset _ rollColumns#tradeAgg; // обновим кэш – взять roll колонки из aggTable, обрезать, вставить в rollCache
  offset::count tradeAgg;
  currSyms::`u#`$();
 }

Tidzafunika ntchito kuti tiwonjezere zilembo zatsopano:

addSyms:{[syms]
  currSyms,::syms; // добавим в список известных
  // добавим в таблицу sym, time и rollColumns воспользовавшись обобщенным присваиванием.
  // Функция ^ подставляет значения по умолчанию для roll колонок, если символа нет в кэше. value flip table возвращает список колонок в таблице.
  `tradeAgg upsert @[count[syms]#enlist initWith;`sym`time,cols rc;:;(syms;currTime), (initWith cols rc)^value flip rc:rollCache ([] sym: syms)];
 }

Ndipo potsiriza, ntchito ya upd (dzina lachikhalidwe la ntchitoyi ya Q services), yomwe imatchedwa ndi kasitomala kuti awonjezere deta:

upd:{[tblName;data] // tblName нам не нужно, но обычно сервис обрабатывает несколько таблиц 
  tm:exec distinct time from data:() xkey preprocess data; // preprocess & calc time
  updMinute[data] each tm; // добавим данные для каждой минуты
};
updMinute:{[data;tm]
  if[tm<>currTime; roll tm; currTime::tm]; // поменяем минуту, если необходимо
  data:select from data where time=tm; // фильтрация
  if[count msyms:syms where not (syms:data`sym)in currSyms; addSyms msyms]; // новые символы
  updateAgg[`tradeAgg;offset+currSyms?syms;data]; // обновим агрегированную таблицу. Функция ? ищет индекс элементов списка справа в списке слева.
 };

Ndizomwezo. Nayi khodi yathunthu yautumiki wathu, monga momwe analonjezera, mizere yochepa chabe:

initWith:`sym`time`high`low`firstPrice`lastPrice`firstSize`lastSize`numTrades`volume`pvolume`turnover`avgPrice`avgSize`vwap`cumVolume!(`;00:00;0n;0n;0n;0n;0N;0N;0;0;0.0;0.0;0n;0n;0n;0);
aggCols:reverse key[initWith] except `sym`time;
rollColumns:`sym`cumVolume;

accumulatorCols:`numTrades`volume`pvolume`turnover;
specialCols:`high`low`firstPrice`firstSize;

selExpression:`high`low`firstPrice`lastPrice`firstSize`lastSize`numTrades`volume`pvolume`turnover!parse each ("max price";"min price";"first price";"last price";"first size";"last size";"count i";"sum size";"sum price";"sum price*size");
preprocess:?[;();`sym`time!`sym`time.minute;selExpression];

aggExpression:`high`low`firstPrice`lastPrice`firstSize`lastSize`avgPrice`avgSize`vwap`cumVolume!("row[`high]|inp`high";"row[`low]&inp`low";"row`firstPrice";"inp`lastPrice";"row`firstSize";"inp`lastSize";"pvolume%numTrades";"volume%numTrades";"turnover%volume";"row[`cumVolume]+inp`volume");
@[`aggExpression;specialCols;{"?[isFirst;inp`",y,";",x,"]"};string specialCols];
aggExpression[accumulatorCols]:{"row[`",x,"]+inp`",x } each string accumulatorCols;
updateAgg:value "{[aggTable;idx;inp] row:aggTable idx; isFirst_0=row`numTrades; .[aggTable;;:;]'[(idx;)each aggCols;(",(";"sv string[aggCols],'":",/:aggExpression aggCols),")]}"; / '

init:{
  tradeAgg::0#enlist[initWith];
  currTime::00:00;
  currSyms::`u#`symbol$();
  offset::0;
  rollCache:: `sym xkey update `u#sym from rollColumns#tradeAgg;
 };
roll:{[tm]
  if[currTime>tm; :init[]];
  rollCache,::offset _ rollColumns#tradeAgg;
  offset::count tradeAgg;
  currSyms::`u#`$();
 };
addSyms:{[syms]
  currSyms,::syms;
  `tradeAgg upsert @[count[syms]#enlist initWith;`sym`time,cols rc;:;(syms;currTime),(initWith cols rc)^value flip rc:rollCache ([] sym: syms)];
 };

upd:{[tblName;data] updMinute[data] each exec distinct time from data:() xkey preprocess data};
updMinute:{[data;tm]
  if[tm<>currTime; roll tm; currTime::tm];
  data:select from data where time=tm;
  if[count msyms:syms where not (syms:data`sym)in currSyms; addSyms msyms];
  updateAgg[`tradeAgg;offset+currSyms?syms;data];
 };

Kuyesa

Tiyeni tiwone momwe ntchito ikuyendera. Kuti tichite izi, tiyeni tiyiyendetse mwanjira ina (ikani kachidindo mu service.q file) ndikuyitanitsa ntchito ya init:

q service.q –p 5566

q)init[]

Mu console ina, yambani ndondomeko yachiwiri ya Q ndikugwirizanitsa yoyamba:

h:hopen `:host:5566
h:hopen 5566 // если оба на одном хосте

Choyamba, tiyeni tipange mndandanda wa zizindikiro - zidutswa 10000 ndikuwonjezera ntchito kuti tipange tebulo losasintha. Mu console yachiwiri:

syms:`IBM`AAPL`GOOG,-9997?`8
rnd:{[n;t] ([] sym:n?syms; time:t+asc n#til 25; price:n?10f; size:n?10)}

Ndinawonjezera zizindikiro zenizeni zitatu pamndandanda kuti zikhale zosavuta kuziyang'ana patebulo. Ntchito ya rnd imapanga tebulo lachisawawa lokhala ndi n mizere, pomwe nthawi imasiyana kuchokera pa t mpaka t + 25 milliseconds.

Tsopano mutha kuyesa kutumiza deta ku ntchito (onjezani maola khumi oyamba):

{h (`upd;`trade;rnd[10000;x])} each `time$00:00 + til 60*10

Mukhoza kuyang'ana mu utumiki kuti tebulo lasinthidwa:

c 25 200
select from tradeAgg where sym=`AAPL
-20#select from tradeAgg where sym=`AAPL

Zotsatira:

sym|time|high|low|firstPrice|lastPrice|firstSize|lastSize|numTrades|volume|pvolume|turnover|avgPrice|avgSize|vwap|cumVolume
--|--|--|--|--|--------------------------------
AAPL|09:27|9.258904|9.258904|9.258904|9.258904|8|8|1|8|9.258904|74.07123|9.258904|8|9.258904|2888
AAPL|09:28|9.068162|9.068162|9.068162|9.068162|7|7|1|7|9.068162|63.47713|9.068162|7|9.068162|2895
AAPL|09:31|4.680449|0.2011121|1.620827|0.2011121|1|5|4|14|9.569556|36.84342|2.392389|3.5|2.631673|2909
AAPL|09:33|2.812535|2.812535|2.812535|2.812535|6|6|1|6|2.812535|16.87521|2.812535|6|2.812535|2915
AAPL|09:34|5.099025|5.099025|5.099025|5.099025|4|4|1|4|5.099025|20.3961|5.099025|4|5.099025|2919

Tsopano tiyeni tiyesetse kuyesa kuti tidziwe kuchuluka kwa deta yomwe ntchitoyo ingagwire pamphindi. Ndiroleni ndikukumbutseni kuti timayika nthawi yosinthira kukhala 25 milliseconds. Chifukwa chake, ntchitoyo iyenera (pafupifupi) kukwanira ma milliseconds osachepera 20 pakusintha kulikonse kuti ipatse ogwiritsa ntchito nthawi yofunsira deta. Lowetsani zotsatirazi munjira yachiwiri:

tm:10:00:00.000
stressTest:{[n] 1 string[tm]," "; times,::h ({st:.z.T; upd[`trade;x]; .z.T-st};rnd[n;tm]); tm+:25}
start:{[n] times::(); do[4800;stressTest[n]]; -1 " "; `min`avg`med`max!(min times;avg times;med times;max times)}

4800 ndi mphindi ziwiri. Mutha kuyesa kuthamanga kaye pamizere 1000 pa mamilliseconds 25 aliwonse:

start 1000

Kwa ine, zotsatira zake zimakhala pafupifupi ma milliseconds angapo pakusintha. Chifukwa chake ndiwonjezera kuchuluka kwa mizere mpaka 10.000:

start 10000

Zotsatira:

min| 00:00:00.004
avg| 9.191458
med| 9f
max| 00:00:00.030

Apanso, palibe chapadera, koma izi ndi mizere 24 miliyoni pamphindi, 400 zikwi pamphindi. Kwa ma milliseconds opitilira 25, zosinthazo zidachepera nthawi 5 zokha, mwachiwonekere pomwe miniti idasintha. Tiyeni tiwonjezeke mpaka 100.000:

start 100000

Zotsatira:

min| 00:00:00.013
avg| 25.11083
med| 24f
max| 00:00:00.108
q)sum times
00:02:00.532

Monga mukuwonera, ntchitoyo simatha kupirira, komabe imatha kupitilirabe. Deta yotereyi (mizere 240 miliyoni pamphindi) ndi yayikulu kwambiri; Zikatero, ndizofala kuyambitsa ma clones angapo (kapena ma clones angapo) a ntchitoyo, iliyonse yomwe imagwira gawo limodzi la otchulidwa. Komabe, zotsatira zake zimakhala zochititsa chidwi chinenero chotanthauziridwa chomwe chimayang'ana kwambiri kusungirako deta.

Funso likhoza kubwera chifukwa chake nthawi imakula mopanda mzere ndi kukula kwa zosintha zilizonse. Chifukwa chake ndikuti shrink ntchito kwenikweni ndi ntchito ya C, yomwe ndiyothandiza kwambiri kuposa updateAgg. Kuyambira pakukula kwina kwake (kuzungulira 10.000), updateAgg imafika padenga lake ndiyeno nthawi yake yochitira sizitengera kukula kwake. Ndi chifukwa cha sitepe yoyamba Q kuti ntchitoyo imatha kukumba zambiri za data. Izi zikuwonetsa kufunikira kosankha algorithm yoyenera mukamagwira ntchito ndi data yayikulu. Mfundo ina ndikusungira kolondola kwa data mu kukumbukira. Zikadakhala kuti sizinasungidwe mokhazikika kapena sizinayimidwe ndi nthawi, ndiye kuti tikadadziwa zinthu monga kuphonya kwa TLB cache - kusowa kwa adilesi yatsamba lokumbukira mu cache ya processor. Kusaka adilesi kumatenga nthawi yayitali ka 30 ngati sikunapambane, ndipo ngati data imwazika, imatha kuchepetsa ntchitoyo kangapo.

Pomaliza

M'nkhaniyi, ndasonyeza kuti KDB + ndi Q Nawonso achichepere si oyenera kusunga deta yaikulu ndi mosavuta kupeza mwa kusankha, komanso popanga deta processing ntchito amene amatha kugaya mazana mamiliyoni mizere / gigabytes deta ngakhale mu. njira imodzi ya Q. Chilankhulo cha Q palokha chimalola kukhazikitsidwa kwachidule komanso koyenera kwa ma aligorivimu okhudzana ndi kusinthidwa kwa data chifukwa cha mawonekedwe ake vekitala, womasulira wa chilankhulo cha SQL wokhazikika komanso magwiridwe antchito a library.

Ndiwona kuti pamwambapa ndi gawo chabe la zomwe Q ingachite, ilinso ndi zina zapadera. Mwachitsanzo, njira yosavuta kwambiri ya IPC yomwe imachotsa malire apakati pa njira za Q ndikukulolani kuti muphatikize mazana ambiri mwa njirazi kukhala netiweki imodzi, yomwe imatha kupezeka pamaseva ambiri kumadera osiyanasiyana padziko lapansi.

Source: www.habr.com