Início / user-29516307

threedom's questions

Martin Hope
threedom
Asked: 2025-03-29 20:51:09 +0800 CST
  • 5

Colunas:

date of transaction
uid is the trader id
sym is the trade id

Subconjunto da tabela:

q) select date, uid, sym from tb

date       uid    sym   
------------------------
2011.08.12 171196 537876
2012.09.08 171196 562161
2012.12.28 171196 570391
2014.04.29 171196 599420
2014.04.29 171196 601520
2014.05.11 171196 602286
2014.06.24 171196 605785
2014.07.19 171196 605686
2011.03.15 160872 524982
2011.07.11 168153 536311
2011.07.25 168153 535616
2011.08.25 30746  537340
2011.01.27 122083 523350
2011.03.05 122083 525676
2011.05.06 122083 531523
2011.01.07 181372 521088
2011.02.07 181372 522780
2011.03.02 181372 523984
2011.03.15 181372 524980
2011.03.21 181372 525448
2011.04.09 181372 529164
2011.04.19 181372 527627
2011.04.28 181372 528302
2011.05.16 181372 530337
2011.06.14 181372 532987

Mirar

Estou tentando obter as 6 negociações anteriores para cada uidum sym.

Existem vários uids para cada sym; para cada um deles, preciso dos 6 syms anteriores para cada uid. Preciso retornar todas as colunas, não apenas o subconjunto que você pode ver acima.

Por exemplo, para esta linha:

2011.04.19 181372 527627

retornaria + colunas adicionais linha por linha - então retornaria cada informação anterior para cada data anterior onde syme uidcorresponderia na mesma linha na nova tabela - então é a tabela original com colunas adicionais contendo dados anteriores para cada linha.

date       uid    sym     prevdate1    prevuid1   prevsym1  prevdate2   prevuid2  etc.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2011.04.19 181372 527627 2011.01.07 181372 521088 2011.03.02 181372 523984 2011.03.15 181372 524980 2011.03.21 181372 525448

Se não houver datas anteriores ou apenas 3 em vez de 6, etc., ele ainda retornará as colunas, mas serão nulas.

Minha tentativa:

getHHData:{[tb;syms;colnames;numtrades]
dict:exec i by uid from select uid from tb where sym=syms;
tradedate:first exec date from tb where sym=syms;
histdates:?[?[tb;enlist (in;`uid;(raze;(inv;`dict)));(enlist`uid)!enlist`uid;colnames!colnames];();`uid;`date];
histdatesro:raze each value (asc dict),histdates;
ind:til count histdatesro;
preind:{[prevtrades;ind;tradedate;histdatesro]
{[prevtrades;ind;tradedate;histdatesro]
prevtrades+{where y[z] in x}[tradedate;histdatesro;]each ind
}[;ind;tradedate;histdatesro]
}[;ind;tradedate;histdatesro]each neg 1_til numtrades;
uids:exec uid from tb where sym=syms;
histdata:reverse each key asc(value ?[tb;enlist (in;`uid;`uids);`uid;last colnames])!(value dict);
histCols:{[x;data]
{$[x<count y;y[x];0N]}[x] each data}[;histdata] each til numtrades-1;
flip(`sym`uid,`$string[last colnames],/:string 1+til numtrades-1)!(enlist[count[histdata]#syms],enlist[uids],histCols)
}

Função wrapper:

getHHDataMerged:{[tb;syms;colnames;numtrades]
raze{[tb;sym;colnames;numtrades](lj/){`sym`uid xkey x
}each {[tb;sym;c;numtrades]getHHData[tb;sym;c;numtrades]
}[tb;sym;;numtrades]each colnames
}[tb;;colnames;numtrades]each syms
}

Estou procurando uma abordagem mais concisa para resolver esse problema.

EDITAR:

q)updCols:{`$string[x],\:string[y]}
q)prevCols:{if[x=0;:`date`uid`sym]; updCols[;x] `prevdate`prevuid`prevsym}
q)7#f/[tb1;1+til 6]
'type
  [0]  7#f/[tb1;1+til 6]
          ^
q)tb1
date       uid    sym   
------------------------
2011.08.12 171196 537876
2012.09.08 171196 562161
2012.12.28 171196 570391
2014.04.29 171196 599420
2014.04.29 171196 601520
2014.05.11 171196 602286
2014.06.24 171196 605785
2014.07.19 171196 605686
2011.03.15 160872 524982
kdb+
Martin Hope
threedom
Asked: 2025-02-27 20:05:45 +0800 CST
  • 5

Estou tendo problemas para traduzir a saída do parse para seleção funcional. Estou certo em pensar que você precisa converter cada um ,paraenlist

Problema completo:

q)dict:exec i by uid from select uid from tb where sym=syms

Um:

q)hd:exec date by uid from select date,ht by uid from tb where uid in raze key dict
q)parse"exec date by uid from select date,ht by uid from tb where uid in raze key dict"
?
(?;`tb;,,(in;`uid;(,/;(!:;`dict)));(,`uid)!,`uid;`date`ht!`date`ht)
()
,`uid
,`date

Dois:

q)ht:key asc(value exec ht by uid from tb where uid in uids)!(value dict)
q)parse"exec ht by uid from tb where uid in uids"
?
`tb
,,(in;`uid;`uids)
,`uid
,`ht
q)

Minhas tentativas:

Um (não está funcionando):

q)?[(?;`tb;enlist enlist (in;`uid;(,/;(!:;`dict)));(enlist `uid)!enlist `uid;`date`ht!`date`ht);();enlist `uid;enlist `date]
'type
  [0]  ?[(?;`tb;enlist enlist (in;`uid;(,/;(!:;`dict)));(enlist `uid)!enlist `uid;`date`ht!`date`ht);();(enlist`date)!enlist`date]

Dois (trabalhando):

q) ?[`tb;enlist(in;`uid;`uids);`uid;(enlist`ht)!enlist`ht]
kdb+