On Mon, 25 Aug 2008, ivo welch wrote:
Dear R wizards:
I decided to take the advice in the R data import/export manual and
want to learn how to work with SQL for large data sets. I am trying
SQLite with the DBI and RSQLite database interfaces. Speed is nice.
Alas, I am struggling to find a tutorial that is geared for the kind
of standard operations that I would want in R. Simple things:
* how to determine the number of rows in a table. (Of course, I
could select a row of data and then use this.)
To get number of rows
SELECT COUNT(*) FROM table_name
For number of columns the quickest reasonably portable way I know is
SELECT * FROM table_name LIMIT 1
and then count the columns of the result.
* how to insert a new column into my existing SQL table---say, the
rank of another variable---and save it back. Am I supposed to create
a new data frame, then save it as a new table, then delete the old SQL
table?
ALTER TABLE table_name ADD column_name
(you may have to/want to specify a data type as well)
If you are adding a bunch of columns you might also want to put the new
columns in a separate table and JOIN the tables, especially if the need
for these extra columns is sporadic or temporary.
* how to save a revised version of my table in a different sort order
(with or without deleting the original table). <-- I guess this is
not appropriate, as I should think of SQL tables as unordered.
As you note, SQL tables are conceptually unordered. You can order results
of a query as you read them:
SELECT foo, bar FROM table_name ORDER BY baz
-thomas
Thomas Lumley Assoc. Professor, Biostatistics
[EMAIL PROTECTED] University of Washington, Seattle
______________________________________________
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.
