[R-sig-DB] Vector Operations

[Sean Davis]

On Wed, Dec 1, 2010 at 8:27 AM, Kasper Daniel Hansen <
kasperdanielhansen using gmail.com> wrote:

> This is a pretty basic R question.  For you own sake, I would
> recommend looking into lapply, sapply, mapply
>
> In this case you could do
>
> allocatedSpace <- lapply(sql, dbGetQuery, con = con)
>
> This code assumes that the relevant position of the argument is the
> second one in dbGetQuery.  A more future proof version would be
> something like
>
> allocatedSpace <- lapply(sql, function(state) dbGetQuery(con = con,
> statement = state))
>
> Kasper
>
> On Mon, Nov 29, 2010 at 9:34 PM, Nick Torenvliet
> <nick.torenvliet using gmail.com> wrote:
> > This isn't strictly an DB question, but I'll venture out anyways...
> >
> > I've got a vector of sql statements called sql
> >
> >> sql
> > [1] "select date, open, high, low, close, volume from endOfDayData where
> > date>'2009-09-01' and market like 'TSX' and symbol like 'AAB' order by
> date"
> > .
> > .
> > .
> > [2464] "select date, open, high, low, close, volume from endOfDayData
> where
> > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUH' order by
> date"
> >
> > [2465] "select date, open, high, low, close, volume from endOfDayData
> where
> > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUT' order by
> > date"
> >
> > For each element in sql I'd like to run the queries in sql and have them
> in
> > another vector of some sort as in the following for loop...
> >
> > for(x in sql){
> > allocatedSpace <- dbGetQuery(con, statement=x)
> > }
> >
> > Is there a way to do this in a more R-esque vector like fashion/single
> line?
>
>
Hi, Nick.

Are you sure that you can't do this on the database side with a single query
with something like:

select symbol, date, open, high, low, close, volume from endOfDayData
where date>'2009-09-01' and market like 'TSX' and symbol in
('ZUH','ZUT','AAB') order by symbol,date

Typically (but not always), letting the database do this once will be more
performant than several thousand separate queries.  On the R side, you can
use split() if you need separate data frames per symbol.

Also, just a note that using "like" instead of "=" will often disable the
use of indexes in the query, perhaps significantly impacting performance.

Sean


> > Regards,
> >
> > Nick
> >
> >        [[alternative HTML version deleted]]
> >
> > _______________________________________________
> > R-sig-DB mailing list -- R Special Interest Group
> > R-sig-DB using r-project.org
> > https://stat.ethz.ch/mailman/listinfo/r-sig-db
> >
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]