《机器学习与数据科学（基于R的统计学习方法）》——2.10 SQL数据库

本节书摘来异步社区《机器学习与数据科学（基于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