本节书摘来异步社区《机器学习与数据科学(基于R的统计学习方法)》一书中的第2章,第2.10节,作者:【美】Daniel D. Gutierrez(古铁雷斯),更多章节内容可以访问云栖社区“异步社区”公众号查看。
2.10 SQL数据库
企业数据的常见来源是SQL数据库。SQL数据库是大大小小各种企业的生命线。很多情况下,数据存储在企业级的数据仓库或是部门级的数据集市中。虽然SQL数据库的应用相当广泛,但是最常用的思路是将数据存储在由行、列组成的“表格”中。事实上,大多数数据库应用软件将数据存储在多个表中。机器学习利用SQL数据的目的是写新的SQL查询语句(或者使用现有的)来得到一个平面文件,其中包含你在分析中想使用的数据。鉴于大多数SQL数据库处理工具将数据导成CSV格式,使用上面章节提到的工具来读取SQL数据库产生的CSV文件是一个有效的解决方案。但是,你可能会认为直接从SQL数据库中读取数据更吸引人。
在连接保存在SQL数据库中的数据内容时,有一个好消息是:R拥有几乎每一种数据库的驱动。这多亏了有各种各样的R包。即使你现在使用的是一个没有单独驱动的数据库,也可以使用一个通用的ODBC(开放数据库互联,Open Database Connectivity)进行连接。下面是一些比较流行的SQL数据库R包的列表:
RMySQL;
RMongo;
Roracle;
RPostgresSQL;
RSQLite;
RODBC。```
为了展示从SQL数据库中读取数据的过程,我将使用容易获取的工具。早在2012年,我就用Kaggle.com网站中著名的Heritage Health Network机器学习比赛的数据集做过实验,当时用的是Microsoft SQL Server 2012 Express,这是Microsoft企业关系型数据库的一个免费版本。我选择将所有的原始数据文件读入SQL表格中,然后在SQL Server中做大部分的数据处理(这部分内容在第3章会进行讨论)。我使用了大量存储过程来管理这个过程,这是我建议大家在执行复杂连接时使用的方法,尽管复杂的数据转换最好是在R环境中进行。
这里概括了如何建立一个到SQL数据库的连接,执行了一个简单的SELECT查询来把数据从表格中提取出来,然后将数据保存在R数据框中。这些步骤应用到我使用的SQL数据库和开发环境中,也就是在Windows 7专业版笔记本电脑上运行的SQL Server 2012 Express。你必须仔细研究所用的SQL数据库和开发环境的细节,不过上面这些步骤至少能让你熟悉处理过程。鉴于SQL Server没有自己的R包,我使用了RODBC包进行处理。
1.在Administrative Tools下的Window Control Panel中,使用ODBC Data Source Administrator工具来创建一个用户DSN(数据源名字)。我将“Heritage”这个名字赋给用户DSN。
2.加载RODBC库。
3.使用odbcConnect()函数建立一个到DSN是“Heritage”的数据库的连接。
4.向数据库传递一个SQL SELECT查询语句,用sqlQuery()函数进行连接,然后将结果集保存在数据框中。
5.关闭连接。
下面是完成这些操作的所有R代码。最后,我们在结果数据框中展示所有的变量名,并计算选中变量PayDelayI的平均值,来展示从SQL数据库中传递过来的数据是完整的。
library(RODBC)
con <- odbcConnect("Heritage", uid="dan")
df <- sqlQuery(con, "SELECT TOP 1000 [MemberID]
,[ProviderID]
,[Vendor]
,[PCP]
,[Year]
,[Specialty]
,[PlaceSvc]
,[PayDelay]
,[LengthOfStay]
,[DSFS]
,[PrimaryConditionGroup]
,[CharlsonIndex]
,[ProcedureGroup]
,[SupLOS]
,[dsfsI]
,[CharlsonIndexI]
,[LengthOfStayI]
,[PayDelayI]
FROM [Heritage].[dbo].[Claims]")
odbcClose(con)
names(df)
[1] "MemberID" "ProviderID" "Vendor"
[4] "PCP" "Year" "Specialty"
[7] "PlaceSvc" "PayDelay" "LengthOfStay"
[10] "DSFS" "PrimaryConditionGroup" "CharlsonIndex"
[13] "ProcedureGroup" "SupLOS" "dsfsI"
[16] "CharlsonIndexI" "LengthOfStayI" "PayDelayI"
mean(df$PayDelayI)
[1] 42.944`
当我们谈及SQL数据库和R时,值得在这里绕一个“小远路”来演示如何在R数据框中执行SQL查询操作。如果你能熟练使用SQL,你可能会发现,在R中使用SQL来连接数据内容比使用R中一些更简洁的数据处理工具(我们会在第3章看到很多这部分内容)要更容易操作。在R中使用SQL的关键是sqldf包,它能帮助你像处理SQL表一样处理数据框,并能用标准SQL语法引用它们。即使你从来没有使用过SQL,你也可能对这个功能感兴趣,因为使用SQL能使一些高难度的R语言结构有更简单的用法。
让我们看一个在R中使用SQL的案例。这是一个典型的商业中关于订单和产品的应用。不引用现存的数据集,我们将通过两个数据框orders和product马上创建一些测试数据。orders数据框有3个变量:order_no、prod_id和qty,分别代表了订单编号、产品ID和订购数量。product数据框也有三个变量:product_id、desc和price,分别代表了产品ID、描述和产品单价。我们将使用SQL连接语句将两个数据框通过共同的关键字prod_id关联起来,然后创建一个新的数据框,其中是包含order_no、prod_id、qty和price的结果集。
> orders <- data.frame(order_no=c("10021","10022","10023", "10024","10025"), prod_id=c("AC-01","AC-01","AD-11","AE-21","AM-19"), qty=c(1,1,2,3,1))
> product <- data.frame(prod_id=c("AC-01","AD-11","AE-21", "AM-19","AG-40"), desc=c("Widget A","Widget B","Widget C","Widget D",
"Widget E"), price=c(123.50,25,55,17.95,45.33))
> sqldf("SELECT o.*, p.price FROM orders o INNER JOIN product p ON o.prod_id = p.prod_id;")
order_no prod_id qty price
1 10021 AC-01 1 123.50
2 10022 AC-01 1 123.50
3 10023 AD-11 2 25.00
4 10024 AE-21 3 55.00