How to use SQL statements in R language?

There is also an interesting sqldf package in r, which allows you to operate dataframe with SQL. This function enables people who know R to practice SQL, and people who know SQL can also practice R. They have to sigh the powerful skills and magical charm of R language.

Of course, it is also feasible to connect R with an external database, directly operate the database in R and generate the final result. Connecting to the database in R requires installing other expansion packages. According to the different connection methods, we have two choices: one is ODBC, and ROODBC package and ODBC driver need to be installed. The other is DBI, which can install the corresponding driver according to the installed database type. Because the latter retains the original characteristics of each database, I prefer to use DBI connection mode. There are several major packages that provide DBI connectivity: RMySQL, RSQLite, ROracle, and RPostgreSQL. As can be seen from the name, they correspond to several mainstream databases respectively.

Note: the above text comes from R-blogger- SQL using r language.

It is worth mentioning that when using the sqldf package, you may need to upgrade the R software to version 3. 1.0, otherwise the installation will not succeed. This paper briefly introduces a method of automatically upgrading R software in Windows environment.

Upgrading r language on Windows:

install . packages(" installr ")；

Library (Installer) # Load Package

Update program ()

Then select OK to update automatically.

Although there are many excellent functions in R language, such as aggregate () and daply (), which can count data frames, sql is powerful, which can not only clean, count and calculate data, but also store, control, define and call data. More and more companies require data analysts not only to master the theoretical methods and programming ability of statistical modeling and data mining, but also to have the ability to use sql when recruiting. The landlord is also actively learning sql.

Here are some programs that I completed with sqldf (). Simple as it is, it is the first step in learning.

& gt name & lt-c(rep ('Zhang San', 1, 3), rep ('Li Si', 3))

& gt theme & lt-c ('math',' Chinese',' English',' math',' Chinese',' English')

> score & gt-c (89, 80, 70, 90, 70, 80)

& gtstuid & lt-c( 1， 1， 1，2，2，2)

& gtstuscore & lt-data.frame (name, subject, score, stuid)

& gt library (sqldf)

1. Calculate and rank the total score of each person (required fields: name, total score).

& gtsqldf('select name，sum(score)as all score from stu score group by name order by all score ')

Name allscore

1 Zhang San 239

Lisi 240

2. Calculate and rank everyone's total score (required fields: student number, name and total score).

& gtsqldf('select name，stuid，sum(score)as all score from stu score group by name order by all score ')

Name stuid allscore

1 3 1 239

Lisi 2 240

3. Calculate the highest score of each individual subject (required fields: student number, name, course and highest score).

& gtsqldf('select stuid, name, subject, max(score) as maxscore of stuscore group by stuid')

Stuid name theme maxscore

1 1 Zhang San Mathematics 89

2 2 Lisi Mathematics 90

4. Calculate everyone's average score (required fields: student number, name, average score).

& gtsqldf('select stuid，name，subject，avg(score)as avg score from stu score group by stuid ')

Stuid name theme avgscore

Three English papers1

2 2 Lisi English 80.00000

5. List the best students in each course (required fields: student number, name, subject, score).

& gtsqldf('select stuid, name, subject, max(score) as maxscore of stuscore group by subject order by stuid')

Stuid name theme maxscore

1 1 Trilingual 80

2 2 Lisi Mathematics 90

3 2 Lisi English 80

8. List the average score of each course (required: course, average score).

& gtsqldf('select subject，avg(score)as avg score from stu score group by subject ')

Theme avgscore

1 Math 89.5

2 English 75.0

3 language 75.0