Note that, in general, you can speed up joins, even within sqldf, by adding indexes to your tables and ensuring that your select statement is written in such a way that the indexes are used. See example 4i on the sqldf home page.
On Wed, Mar 24, 2010 at 4:51 PM, Nick Switanek <nswita...@gmail.com> wrote: > I've recently stumbled across data.table, Matthew Dowle's package. I'm > impressed by the speed of the package in handling operations with large > data.frames, but am a bit overwhelmed with the syntax. I'd like to express > the SQL statement below using data.table operations rather than sqldf (which > was incredibly slow for a small subset of my financial data) or > import/export with a DBMS, but I haven't been able to figure out how to do > it. I would be grateful for your suggestions. > > nick > > > > My aim is to join events (trades) from two datasets ("edt" and "cdt") where, > for the same stock, the events in one dataset occur between 15 and 75 days > before the other, and within the same time window. I can only see how to > express the "WHERE e.SYMBOL = c.SYMBOL" part in data.table syntax. I'm also > at a loss at whether I can express the remainder using data.table's > %between% operator or not. > > ctqm <- sqldf("SELECT e.*, > c.DATE 'DATEctrl', > c.TIME 'TIMEctrl', > c.PRICE 'PRICEctrl', > c.SIZE 'SIZEctrl' > > FROM edt e, ctq c > > WHERE e.SYMBOL = c.SYMBOL AND > julianday(e.DATE) - julianday(c.DATE) BETWEEN 15 AND > 75 AND > strftime('%H:%M:%S',c.TIME) BETWEEN > strftime('%H:%M:%S',e.BEGTIME) AND strftime('%H:%M:%S',e.ENDTIME)") > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.