Hi everyone, I have just loaded two large splayed q databases with264361663 and 187823972 rows.The DB called trade has a DATE column and I run the querriesexec DATE from select distinct DATE from tradeexec distinct DATE from nbbowhich both give “wsfull” errors and crashes.The querry should not take a lot of memory (about 1 years worth ofdates) but I don’t know how to fix this. Could someone help?
Possibly break it up?
q)(union)over{exec distinct DATE from trade where DATE.month=3Dx}each m
where m is an exhaustive list of months in the db (e.g. 2010.01
2010.02 2010.03m)
I’m assuming DATE is an actual column and not a virtual column… or
did you remove your DATE column and splay it daily using .Q.dpft with
the partition being the distinct value of DATEs for that block?
I’m curious, does this work:
{count x;.Q.PV where not 0=3D.Q.pn x}`trade
I get the following complaint:".Q.pn"On Feb 9, 7:13?pm, Tim Rieder <trie…> wrote:> I’m curious, does this work:>> {count x;.Q.PV where not 0=.Q.pn x}`trade>>>> On Wed, Feb 9, 2011 at 7:04 PM, Tim Rieder <trie…> wrote:> > I’m assuming DATE is an actual column and not a virtual column… or> > did you remove your DATE column and splay it daily using .Q.dpft with> > the partition being the distinct value of DATEs for that block?>> > On Wed, Feb 9, 2011 at 5:20 PM, Michael <princetonbac…> wrote:> >> Hi everyone, I have just loaded two large splayed q databases with> >> 264361663 and 187823972 rows.> >> The DB called trade has a DATE column and I run the querries>> >> exec DATE from select distinct DATE from trade> >> exec distinct DATE from nbbo>> >> which both give “wsfull” errors and crashes.> >> The querry should not take a lot of memory (about 1 years worth of> >> dates) but I don’t know how to fix this. ?Could someone help?>> >> –> >>
Submitted via Google Groups</princetonbac…></trie…></trie…>
I’m not sure. The table was create this way:colnames:SYMBOL,DATE,TIME,PRICE,SIZE,EX;.Q.fs[{ .[:q/data/db/trade/; (); ,; .Q.en[:q/data/db] flip colnames!(“SDTFIS”;“,”)0:x]}]`:q/data/taqtrade.csvLet me know if that helps.On Feb 9, 7:04?pm, Tim Rieder <trie…> wrote:> I’m assuming DATE is an actual column and not a virtual column… or> did you remove your DATE column and splay it daily using .Q.dpft with> the partition being the distinct value of DATEs for that block?>>>> On Wed, Feb 9, 2011 at 5:20 PM, Michael <princetonbac…> wrote:> > Hi everyone, I have just loaded two large splayed q databases with> > 264361663 and 187823972 rows.> > The DB called trade has a DATE column and I run the querries>> > exec DATE from select distinct DATE from trade> > exec distinct DATE from nbbo>> > which both give “wsfull” errors and crashes.> > The querry should not take a lot of memory (about 1 years worth of> > dates) but I don’t know how to fix this. ?Could someone help?>> > –> >
Submitted via Google Groups</princetonbac…></trie…>
pn is only present when a partitioned hdb has been loaded. If your
tables are plain allayed tables, that code won’t work.
Yeah, that’s a plain splayed table, not a partitioned table.
Did my earlier suggestion work?
Hi, the following code seems to work. Thanks!months:2000.04m + til 12;dates:() ,/ {select distinct DATE from trade where x=DATE.month} eachmonths;On Feb 10, 4:17?pm, Aaron Davies <aaron.dav…> wrote:> Yeah, that’s a plain splayed table, not a partitioned table.>> Did my earlier suggestion work?>>>>>> On Thursday, February 10, 2011, Michael <princetonbac…> wrote:> > I’m not sure. ?The table was create this way:> > colnames:SYMBOL,DATE,TIME,PRICE,SIZE,EX;> > .Q.fs[{ .[:q/data/db/trade/; (); ,; .Q.en[:q/data/db] flip colnames!> > (“SDTFIS”;“,”)0:x]}]`:q/data/taqtrade.csv> > Let me know if that helps.>> > On Feb 9, 7:04?pm, Tim Rieder <trie…> wrote:> >> I’m assuming DATE is an actual column and not a virtual column… or> >> did you remove your DATE column and splay it daily using .Q.dpft with> >> the partition being the distinct value of DATEs for that block?>> >> On Wed, Feb 9, 2011 at 5:20 PM, Michael <princetonbac…> wrote:> >> > Hi everyone, I have just loaded two large splayed q databases with> >> > 264361663 and 187823972 rows.> >> > The DB called trade has a DATE column and I run the querries>> >> > exec DATE from select distinct DATE from trade> >> > exec distinct DATE from nbbo>> >> > which both give “wsfull” errors and crashes.> >> > The querry should not take a lot of memory (about 1 years worth of> >> > dates) but I don’t know how to fix this. ?Could someone help?>> >> > –> >> >
Submitted via Google Groups</princetonbac…></trie…></princetonbac…></aaron.dav…>
great. if you want a little further abstraction, you could automatethe range:q)until:{x+til 1+y-x}q)dates:(union)over{select distinct DATE from trade where x=DATE.month}each(until). exec(min DATE;max DATE)from trade(or (first DATE;last DATE) if your data is sorted)On Feb 10, 5:03?pm, Michael <princetonbac…> wrote:> Hi, the following code seems to work. ?Thanks!>> months:2000.04m + til 12;> dates:() ,/ {select distinct DATE from trade where x=DATE.month} each> months;>> On Feb 10, 4:17?pm, Aaron Davies <aaron.dav…> wrote:>> > Yeah, that’s a plain splayed table, not a partitioned table.>> > Did my earlier suggestion work?>> > On Thursday, February 10, 2011, Michael <princetonbac…> wrote:> > > I’m not sure. ?The table was create this way:> > > colnames:SYMBOL,DATE,TIME,PRICE,SIZE,EX;> > > .Q.fs[{ .[:q/data/db/trade/; (); ,; .Q.en[:q/data/db] flip colnames!> > > (“SDTFIS”;“,”)0:x]}]`:q/data/taqtrade.csv> > > Let me know if that helps.>> > > On Feb 9, 7:04?pm, Tim Rieder <trie…> wrote:> > >> I’m assuming DATE is an actual column and not a virtual column… or> > >> did you remove your DATE column and splay it daily using .Q.dpft with> > >> the partition being the distinct value of DATEs for that block?>> > >> On Wed, Feb 9, 2011 at 5:20 PM, Michael <princetonbac…> wrote:> > >> > Hi everyone, I have just loaded two large splayed q databases with> > >> > 264361663 and 187823972 rows.> > >> > The DB called trade has a DATE column and I run the querries>> > >> > exec DATE from select distinct DATE from trade> > >> > exec distinct DATE from nbbo>> > >> > which both give “wsfull” errors and crashes.> > >> > The querry should not take a lot of memory (about 1 years worth of> > >> > dates) but I don’t know how to fix this. ?Could someone help?>> > >> > –> > >> >
Submitted via Google Groups</princetonbac…></trie…></princetonbac…></aaron.dav…></princetonbac…>