小试牛刀：用SQL玩转R数据框

让SQL飞一会儿

技多不压身，跨界融合更是对十八般武艺的一种考验，SQL是一种技能，一样可以在R的舞台上展示独特的魅力，一起看看SQL处理数据的长袖有怎么样的舞艺。

1，介绍主角

SQL 是结构化查询语句，是一种数据库查询和程序设计语言。数据分析从业者的主要技能之一，普及程度不低于R语言。当我们用R语言处理数据：检索，排序，筛选的时候怎觉得力不从心。怎样用SQL来处理R中的数据呢？用SQL的优势来武装R的数据处理更能锦上添花，鱼和熊掌兼得。当然我们可以引用R中的sqldf包，让SQL在R飞起，处理数据就这么简单。

2，打造一个舞台

从简单的数据来探索有趣的知识，用R组合出关于学生的数据框（student_dframe），比如学号-sid，性别-sex，年龄-age，班级-class，成绩等级-level：

code---

sid<-c(1,2,3,4,5,6) sex<-c('男', '女','男', '女','男', '女')

age<-c(18,24,45,18,19,22)

class<-c('class1', 'class1', 'class2','class2', 'class3', 'class3')

level<-c('Poor', 'Improved', 'Excellent', 'Poor', 'Improved', 'Excellent')

student_dframe <- data.frame(sid, sex, age, class,level)

3，SQL VS R的常用舞艺

#SQL：表示SQL语法，#R：表示R语法。对比二者同结果操作的不同之处，在比较中提升学习的效率。time is money！！！

一定要安装的包

library(dplyr)
library(sqldf)
library(data.table)
library(ggplot2)
library(compare)
library(plotrix)

3.1，COUNT计数

#SQL：sqldf(" SELECT COUNT(sid) as 'Number of rows' FROM student_dframe; ")

#R：nrow(student_dframe)

输出结果为：6

3.2，LIMIT 取部分行，前三行

#SQL ：sqldf("SELECT * FROM student_dframe LIMIT 3;")

#R：head(student_dframe, 3)

输出结果：

3.3，WHERE 过滤数据

SQL2=sqldf("SELECT * FROM student_dframe WHERE sex ='男';")

R2 = filter(student_dframe, sex=="男") identical(SQL2, R2)

输出结果：

3.4，GROUP BY and ORDER BY 分组与排序

SQL3 = sqldf("SELECT sex, COUNT(sid) as Total FROM student_dframe WHERE sex IN ('男','女') GROUP BY sex ORDER BY Total DESC ;")

R3=student_dframe%>%filter(sex %in%c('F','M','NS','UNK'))%>%group_by(sex) %>% summarise(Total = n())%>%arrange(desc(Total))

输出结果：

3.5，Inner Join 集合合并--添加列

我们需要在创建一个学生姓名的数据框：

code---

sid<-c(1,2,3,4,5,6) # 学号一样

sname<-c('张三', '李四', '王二', '狗蛋', '毛线', '乔布斯')

student_name <- data.frame(sid, sname)

SQL4=sqldf("SELECT sd.*, sn.sname as sname FROM student_dframe sd INNER JOIN student_name sn ON sd.sid=sn.sid ORDER BY sd.sid, sn.sname")

R4 = merge(student_dframe, student_name,by=intersect(names(student_dframe), names(student_name))) compare(R4,SQL4,allowAll = TRUE)

输出结果：

3.6，UNION ALL 取两个集合的交集

student_dframe1<-student_dframe[1:3,] #重复利用数据框 前3行

R5 <- rbind(student_dframe, student_dframe1)

SQL5 <- sqldf("SELECT * FROM student_dframe UNION ALL SELECT * FROM student_dframe1;") compare(R5,SQL5, allowAll = TRUE)

输出结果：

3.7，INTERSECT 取两个集合的交集

student_dframe1<-student_dframe[1:3,]

R6 <- semi_join(student_dframe, student_dframe1)

SQL6 <- sqldf("SELECT * FROM student_dframe INTERSECT SELECT * FROM student_dframe1;") compare(R6,SQL6, allowAll = TRUE)

输出结果：

3.8，EXCEPT 取两个集合的补集

R7 <- anti_join(student_dframe, student_dframe1)

SQL7 <- sqldf("SELECT * FROM student_dframe EXCEPT SELECT * FROM student_dframe1;") compare(R7,SQL7, allowAll = TRUE)

输出结果：

4，SQL数据可视化

SQL对R数据框检索，排序，筛选后的数据结果依然是一个数据框，这么我们也可以直接作图，让数据可视化。

SQL = sqldf("SELECT sex, COUNT(sid) as Total FROM student_dframe WHERE sex IN ('男','女') GROUP BY sex ORDER BY Total DESC ;")

SQL$Total=as.numeric(SQL$Total)

pie(SQL$Total, labels =SQL$sex,explode=0.1,col=rainbow(4), main="性别分布饼图",cex.lab=0.5, cex.axis=0.5, cex.main=1,labelcex=1, family='SimSun')

ggplot(sqldf('SELECT age, sex FROM student_dframe WHERE age between 0 AND 100 ;'),aes(x=age, fill = sex), family='SimSun')+ geom_density(alpha = 0.6)

ggplot(sqldf('SELECT age, sex FROM student_dframe

union all SELECT age, sex FROM student_dframe1

union all SELECT age, sex FROM student_dframe1

union all SELECT age, sex FROM student_dframe1 '), aes(x=age, fill = sex), family='SimSun')+geom_bar(alpha=0.6)