数据科学 | 数据科学中的SQL应用

数艺学苑 2020-10-28

300

本文作为SQL（结构化查询语言）操作的解释说明文档，将使用SQLite3库，它提供一个关系数据库管理系统。现如今，SQL已然成为了操作和查询数据的标准。SQL允许用户高效地从关系数据库中进行数据输入和信息检索。所谓关系数据库指的是一种数据库类型，采用关系模型来组织数据，其最大的特点是处理事务的一致性，简单来看关系型数据库的展现形式为二维表格，使用方便，易于维护,大大降低了数据冗余和数据不一致的概率。

常见的关系型数据库有SQLite、Oracle、Mysql等。可以将关系型数据库视为包含列和行的excel电子表格。他们可以由一个或多个表组成，每一行都由一个唯一的主键标识。这些数据库对象的集合称为模式。模式是一种行之有效的机制，基于不同的应用程序、不同的访问权限来有效隔离数据库对象。

在使用SQL语句时，最显著的优势是其可以良好的进行指定数据的查询。基于Python开发项目时，我们通常使用Pandas库进行大部分的数据操作和分析，语法会变得相当复杂。通过SQL语句的select、distinct、like等可以仅检索服务于目标对象的数据，这对之后的数据分析节省了大量的计算时间。

下图是一个关系数据库模式的一个示例。每个矩形框都是一个表，表明在最顶部。每个表名下面是每个关联表的列名列表。表的主键通过星号（*）唯一标识。一个表中的主键也可能在另一个表中（外键）。

SQLite库含有一个高效的关系数据库管理系统（RDBMS），并且SQLite3升级了更多的功能，值得一提的是SQLite3是自包含的、无服务器的、零配置的。

SQLite3操作

本文将演示安装并加载必要的库，连接到数据库，进行相应的查询操作。下面使用的一些示例来自LucasMcL/]5-sql querys 02 chinook。如果您使用的是Python3以上的版本，则可能不需要安装。

安装数据库：

pip install pysqlite3

数据库操作：

1#导入数据库
2import sqlite3
3#连接数据库
4conn = sqlite3.connect('data/Chinook_Sqlite.sqlite')
5#实例化对象
6cur = conn.cursor()

现在我们已经连接到数据库，我们可以查询其中的数据。使用cursor对象执行查询然后只返回cursor对象。为了查看结果，我们需要在之后使用fetchaii（）方法。使用select语句和where子句执行查询，以查看数据库中有多少表。where子句通常按某些条件筛选查询结果。在下面的示例中，我使用它返回数据库中“table”类型的对象的名称。每个SQLite数据库都有一个包含关于模式的信息的SQLITEHMART表。以分号结束查询表示语句结束。如果希望查询返回表中的所有记录，请使用星号（*）代替对象名称。

 1#代码块：
 2cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
 3print(cur.fetchall())
 4#输出结果
 5[('Album',), ('Artist',), ('Customer',), ('Employee',), ('Genre',), ('Invoice',), ('InvoiceLine',), ('MediaType',), ('Playlist',), ('PlaylistTrack',), ('Track',)]

此查询语句的输出返回数据库中的所有表。假设我们需要关于数据库中每个表的更详细的信息，pragma命令将会返回这些信息。

 1#代码块：
 2cur.execute("PRAGMA table_info(Employee)")
 3info = cur.fetchall()
 4print(*info, sep = "\n")
 5#输出：
 6(0, 'EmployeeId', 'INTEGER', 1, None, 1)
 7(1, 'LastName', 'NVARCHAR(20)', 1, None, 0) 
 8(2, 'FirstName', 'NVARCHAR(20)', 1, None, 0) 
 9(3, 'Title', 'NVARCHAR(30)', 0, None, 0) 
10(4, 'ReportsTo', 'INTEGER', 0, None, 0) 
11(5, 'BirthDate', 'DATETIME', 0, None, 0) 
12(6, 'HireDate', 'DATETIME', 0, None, 0) 
13(7, 'Address', 'NVARCHAR(70)', 0, None, 0) 
14(8, 'City', 'NVARCHAR(40)', 0, None, 0) 
15(9, 'State', 'NVARCHAR(40)', 0, None, 0) 
16(10, 'Country', 'NVARCHAR(40)', 0, None, 0) 
17(11, 'PostalCode', 'NVARCHAR(10)', 0, None, 0)
18(12, 'Phone', 'NVARCHAR(24)', 0, None, 0) 
19(13, 'Fax', 'NVARCHAR(24)', 0, None, 0) 
20(14, 'Email', 'NVARCHAR(60)', 0, None, 0)

此查询语句的输出返回Employee表的列ID、列名、列类型、非空值、默认值和主键。

如果希望输出的格式可以操作或处理数据，那么使用Pandas语句将查询结果包装到数据帧中从而提升效率。.description属性返回包含以下内容的列描述：

name 
type_code 
display_size 
internal_size 
precision 
scale 
null_ok

使用description属性，我们可以使用list comprehension获取每个列的名称。下面是一个查询语句，选择不在美国的客户、全名、id和国家/地区，结果用数据框包装起来。

1import pandas as pd
2cur.execute("""
3    SELECT FirstName, LastName, CustomerId, Country
4    FROM customer
5    WHERE country != 'USA'
6""")
7df = pd.DataFrame(cur.fetchall())
8df.columns = [x[0] for x in cur.description]
9df.head(10)

输出结果：

使用SQL进行筛选和排序

SQL查询只允许检索与任务相关的数据。我将介绍一些查询修饰符， order by是第一个。这个修饰符允许我们根据特定的特性对select语句的结果进行排序。

limit子句将输出限制为一定数量的结果。between运算符允许我们通过过滤设置值之间的结果来进一步选择特定数据。这在查询特定年龄组、时间范围等时非常有用。

下面我执行一个查询，从track表中选择数据，其中track的长度（毫秒）在205205和300000之间。结果按轨迹的毫秒数按降序排序，因此具有较长轨迹的轨迹将位于顶部。我还将此查询限制为仅输出10个结果。

 1#代码块
 2cur.execute("""
 3    SELECT Name, AlbumId, TrackId, Milliseconds
 4    FROM track
 5    WHERE Milliseconds BETWEEN 205205 AND 300000
 6    ORDER BY Milliseconds Desc
 7    LIMIT 10
 8""")
 9info = cur.fetchall()
10print(*info, sep = "\n")
11
12#输出：
13('Breathe', 212, 2613, 299781) 
14('Queixa', 23, 524, 299676) 
15('Getaway Car', 10, 97, 299598) 
16('Winterlong', 201, 2485, 299389) 
17('Cherub Rock', 202, 2491, 299389) 
18('Sonata for Solo Violin: IV: Presto', 325, 3480, 299350) 
19('Linha Do Equador', 21, 218, 299337) 
20('Who Are You (Single Edit Version)', 221, 2749, 299232) 
21('Garden', 181, 2201, 299154) 
22('The Spirit Of Radio', 196, 2406, 299154)

聚合函数

这些SQL函数在执行统计分析时非常有用。我们可以得到一列中的平均值、最小值和最大值，以及值的总和。使用count函数返回满足特定条件的记录数。 groupby函数将返回按集合列分组的结果。例如，按性别、品种或国籍对结果进行分组。

下面是每个国家的总销售额查询示例，结果按每个国家分组。我还将总数的总和称为“总销售额”，以便按每个国家的总销售额对结果进行分组。

 1#代码块：
 2cur.execute('''
 3    SELECT i.billingcountry, sum(total) as 'TotalSales'
 4    FROM invoice AS i
 5    GROUP BY billingcountry
 6    ORDER BY totalsales DESC
 7    '''
 8)
 9info = cur.fetchall()
10print(*info, sep = "\n")
11
12#输出：
13('USA', 523.0600000000003)
14('Canada', 303.9599999999999)
15('France', 195.09999999999994)
16('Brazil', 190.09999999999997)
17('Germany', 156.48)
18('United Kingdom', 112.85999999999999)
19('Czech Republic', 90.24000000000001)
20('Portugal', 77.23999999999998)
21('India', 75.25999999999999)
22('Chile', 46.62)
23('Hungary', 45.62)
24('Ireland', 45.62)
25('Austria', 42.62)
26('Finland', 41.620000000000005)
27('Netherlands', 40.62)
28('Norway', 39.62)
29('Sweden', 38.620000000000005)
30('Argentina', 37.620000000000005)
31('Australia', 37.620000000000005)
32('Denmark', 37.620000000000005)
33('Italy', 37.620000000000005)
34('Poland', 37.620000000000005)
35('Belgium', 37.62)
36('Spain', 37.62)