# HIDDEN
# Clear previously defined variables
%reset -f
# Set directory for data loading to work properly
import os
os.chdir(os.path.expanduser('~/notebooks/09'))
# HIDDEN
import warnings
# Ignore numpy dtype warnings. These warnings are caused by an interaction
# between numpy and Cython and can be safely ignored.
# Reference: https://stackoverflow.com/a/40846742
warnings.filterwarnings("ignore", message="numpy.dtype size changed")
warnings.filterwarnings("ignore", message="numpy.ufunc size changed")
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
%matplotlib inline
import ipywidgets as widgets
from ipywidgets import interact, interactive, fixed, interact_manual
import nbinteract as nbi
sns.set()
sns.set_context('talk')
np.set_printoptions(threshold=20, precision=2, suppress=True)
pd.options.display.max_rows = 7
pd.options.display.max_columns = 8
pd.set_option('precision', 2)
# This option stops scientific notation for pandas
# pd.set_option('display.float_format', '{:.2f}'.format)
# HIDDEN
# Creating a table
sql_expr = """
CREATE TABLE prices(
retailer TEXT,
product TEXT,
price FLOAT);
"""
result = sqlite_engine.execute(sql_expr)
# HIDDEN
# Inserting records into the table
sql_expr = """
INSERT INTO prices VALUES
('Best Buy', 'Galaxy S9', 719.00),
('Best Buy', 'iPod', 200.00),
('Amazon', 'iPad', 450.00),
('Amazon', 'Battery pack', 24.87),
('Amazon', 'Chromebook', 249.99),
('Target', 'iPod', 215.00),
('Target', 'Surface Pro', 799.00),
('Target', 'Google Pixel 2', 659.00),
('Walmart', 'Chromebook', 238.79);
"""
result = sqlite_engine.execute(sql_expr)
# HIDDEN
import pandas as pd
prices = pd.DataFrame([['Best Buy', 'Galaxy S9', 719.00],
['Best Buy', 'iPod', 200.00],
['Amazon', 'iPad', 450.00],
['Amazon', 'Battery pack', 24.87],
['Amazon', 'Chromebook', 249.99],
['Target', 'iPod', 215.00],
['Target', 'Surface Pro', 799.00],
['Target', 'Google Pixel 2', 659.00],
['Walmart', 'Chromebook', 238.79]],
columns=['retailer', 'product', 'price'])
sql（结构化查询语言）是一种编程语言，它具有对关系数据库管理系统（RDBMS）中存储的数据进行定义、逻辑组织、操作和执行计算的操作。
SQL 是一种声明性语言。这意味着用户只需要指定他们想要的 _ 数据类型，而不需要指定 _ 如何获取它。下面是一个示例，其中一个必要的示例用于比较：__
- 声明性：从表“a”计算列“x”和“y”，其中“y”中的值大于 100.00。
- 命令式：对于表“a”中的每个记录，检查记录是否包含大于 100 的“y”值。如果是，则将记录的“x”和“y”属性存储在新表中。返回新表。
在本章中，我们将以 python 字符串的形式编写 SQL 查询，然后使用pandas执行 SQL 查询并将结果读取到pandas数据帧中。在我们介绍 SQL 语法的基础知识时，为了便于比较，我们偶尔还会显示pandas等价物。
通过pandas¶执行 SQL 查询
要从 python 执行 SQL 查询，我们将使用sqlachemy库连接到数据库。然后我们可以使用pandas函数pd.read_sql通过这个连接执行 SQL 查询。
import sqlalchemy
# pd.read_sql takes in a parameter for a SQLite engine, which we create below
sqlite_uri = "sqlite:///sql_basics.db"
sqlite_engine = sqlalchemy.create_engine(sqlite_uri)
此数据库包含一个关系：prices。为了显示关系，我们运行一个 SQL 查询。调用read_sql将在 RDBMS 上执行 SQL 查询，然后在pandas数据帧中返回结果。
sql_expr = """
SELECT *
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| 零售商 | 产品 | 价格 | |
|---|---|---|---|
| 零 | 百思买 | 银河 S9 | 七百一十九 |
| --- | --- | --- | --- |
| 1 个 | Best Buy | iPod | 二百 |
| --- | --- | --- | --- |
| 二 | 亚马逊 | iPad | 四百五十 |
| --- | --- | --- | --- |
| 三 | Amazon | 电池组 | 二十四点八七 |
| --- | --- | --- | --- |
| 四 | Amazon | 彩色电子书 | 二百四十九点九九 |
| --- | --- | --- | --- |
| 5 个 | 目标 | iPod | 二百一十五 |
| --- | --- | --- | --- |
| 六 | Target | 微软 Surface Pro 平板电脑 | 七百九十九 |
| --- | --- | --- | --- |
| 七 | Target | 谷歌像素 2 | 六百五十九 |
| --- | --- | --- | --- |
| 8 个 | 沃尔玛 | Chromebook | 二百三十八点七九 |
| --- | --- | --- | --- |
在本节后面，我们将比较 SQL 查询和pandas方法调用，因此我们在pandas中创建了相同的数据帧。
prices
| retailer | product | price | |
|---|---|---|---|
| 0 | Best Buy | Galaxy S9 | 719.00 |
| --- | --- | --- | --- |
| 1 | Best Buy | iPod | 200.00 |
| --- | --- | --- | --- |
| 2 | Amazon | iPad | 450.00 |
| --- | --- | --- | --- |
| 3 | Amazon | Battery pack | 24.87 |
| --- | --- | --- | --- |
| 4 | Amazon | Chromebook | 249.99 |
| --- | --- | --- | --- |
| 5 | Target | iPod | 215.00 |
| --- | --- | --- | --- |
| 6 | Target | Surface Pro | 799.00 |
| --- | --- | --- | --- |
| 7 | Target | Google Pixel 2 | 659.00 |
| --- | --- | --- | --- |
| 8 | Walmart | Chromebook | 238.79 |
| --- | --- | --- | --- |
SQL 句法¶
所有 SQL 查询都采用以下常规形式：
SELECT [DISTINCT] <column expression list>
FROM <relation>
[WHERE <predicate>]
[GROUP BY <column list>]
[HAVING <predicate>]
[ORDER BY <column list>]
[LIMIT <number>]
注意：
- **方括号中的所有内容都是可选的。**有效的 SQL 查询只需要
SELECT和FROM语句。 - **SQL 语法通常用大写字母编写。**虽然不需要大写，但通常使用大写字母编写 SQL 语法。它还可以帮助您直观地构造查询，以供其他人阅读。
FROM查询块可以引用一个或多个表，尽管在本节中，为了简单起见，我们一次只查看一个表。
从¶中选择和
SQL 查询中的两个强制语句是：
SELECT指示要查看的列。FROM指示从中选择这些列的表。
要显示整个prices表，我们运行：
sql_expr = """
SELECT *
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | product | price | |
|---|---|---|---|
| 0 | Best Buy | Galaxy S9 | 719.00 |
| --- | --- | --- | --- |
| 1 | Best Buy | iPod | 200.00 |
| --- | --- | --- | --- |
| 2 | Amazon | iPad | 450.00 |
| --- | --- | --- | --- |
| 3 | Amazon | Battery pack | 24.87 |
| --- | --- | --- | --- |
| 4 | Amazon | Chromebook | 249.99 |
| --- | --- | --- | --- |
| 5 | Target | iPod | 215.00 |
| --- | --- | --- | --- |
| 6 | Target | Surface Pro | 799.00 |
| --- | --- | --- | --- |
| 7 | Target | Google Pixel 2 | 659.00 |
| --- | --- | --- | --- |
| 8 | Walmart | Chromebook | 238.79 |
| --- | --- | --- | --- |
SELECT *返回原始关系中的每一列。为了只显示prices中表示的零售商，我们将retailer列添加到SELECT语句中。
sql_expr = """
SELECT retailer
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | |
|---|---|
| 0 | Best Buy |
| --- | --- |
| 1 | Best Buy |
| --- | --- |
| 2 | Amazon |
| --- | --- |
| 3 | Amazon |
| --- | --- |
| 4 | Amazon |
| --- | --- |
| 5 | Target |
| --- | --- |
| 6 | Target |
| --- | --- |
| 7 | Target |
| --- | --- |
| 8 | Walmart |
| --- | --- |
如果我们想要一个唯一零售商的列表，我们可以调用DISTINCT函数来省略重复的值。
sql_expr = """
SELECT DISTINCT(retailer)
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | |
|---|---|
| 0 | Best Buy |
| --- | --- |
| 1 | Amazon |
| --- | --- |
| 2 | Target |
| --- | --- |
| 3 | Walmart |
| --- | --- |
这相当于以下pandas代码的功能：
prices['retailer'].unique()
array(['Best Buy', 'Amazon', 'Target', 'Walmart'], dtype=object)每个 RDBMS 都有自己的函数集，可以应用于SELECT列表中的属性，例如比较运算符、数学函数和运算符，以及字符串函数和运算符。在数据 100 中，我们使用 PostgreSQL，这是一种成熟的 RDBMS，有数百个这样的函数。完整列表可在此处找到。请记住，每个 RDBMS 都有一组不同的函数可用于SELECT。
以下代码将所有零售商名称转换为大写，并将产品价格减半。
sql_expr = """
SELECT
UPPER(retailer) AS retailer_caps,
product,
price / 2 AS half_price
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| 零售商上限 | product | 半价 | |
|---|---|---|---|
| 0 | 百思买 | Galaxy S9 | 359.500 美元 |
| --- | --- | --- | --- |
| 1 | BEST BUY | iPod | 10 万 |
| --- | --- | --- | --- |
| 2 | 亚马逊 | iPad | 225.000 美元 |
| --- | --- | --- | --- |
| 3 | AMAZON | Battery pack | 十二点四三五 |
| --- | --- | --- | --- |
| 4 | AMAZON | Chromebook | 124.995 年 |
| --- | --- | --- | --- |
| 5 | 目标 | iPod | 107.500 美元 |
| --- | --- | --- | --- |
| 6 | TARGET | Surface Pro | 399.500 美元 |
| --- | --- | --- | --- |
| 7 | TARGET | Google Pixel 2 | 329.500 美元 |
| --- | --- | --- | --- |
| 8 | 沃尔玛 | Chromebook | 119.395 条 |
| --- | --- | --- | --- |
请注意，我们可以用别名将列（指定另一个名称）与AS一起使用，以便这些列在输出表中以这个新名称出现。这不会修改源关系中列的名称。
式中¶
WHERE子句允许我们为返回的数据指定某些约束；这些约束通常被称为谓词。例如，只检索低于 500 美元的小工具：
sql_expr = """
SELECT *
FROM prices
WHERE price < 500
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | product | price | |
|---|---|---|---|
| 0 | Best Buy | iPod | 200.00 |
| --- | --- | --- | --- |
| 1 | Amazon | iPad | 450.00 |
| --- | --- | --- | --- |
| 2 | Amazon | Battery pack | 24.87 |
| --- | --- | --- | --- |
| 3 | Amazon | Chromebook | 249.99 |
| --- | --- | --- | --- |
| 4 | Target | iPod | 215.00 |
| --- | --- | --- | --- |
| 5 | Walmart | Chromebook | 238.79 |
| --- | --- | --- | --- |
我们还可以使用操作符AND、OR和NOT进一步约束我们的 SQL 查询。为了在亚马逊上找到一个不带电池包的商品，价格低于 300 美元，我们写下：
sql_expr = """
SELECT *
FROM prices
WHERE retailer = 'Amazon'
AND NOT product = 'Battery pack'
AND price < 300
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | product | price | |
|---|---|---|---|
| 0 | Amazon | Chromebook | 249.99 |
| --- | --- | --- | --- |
pandas中的等效操作为：
prices[(prices['retailer'] == 'Amazon')
& ~(prices['product'] == 'Battery pack')
& (prices['price'] <= 300)]
| retailer | product | price | |
|---|---|---|---|
| 4 | Amazon | Chromebook | 249.99 |
| --- | --- | --- | --- |
有一个细微的区别值得注意：SQL 查询中 Chromebook 的索引是 0，而数据帧中相应的索引是 4。这是因为 SQL 查询总是返回一个索引从 0 开始计数的新表，而pandas将数据帧prices的一部分子集并返回原始索引。我们可以使用pd.dataframe.reset_index重置pandas中的索引。
聚合函数¶
到目前为止，我们只处理表中现有行的数据；也就是说，我们返回的所有表都是表中找到的条目的一个子集。但是为了进行数据分析，我们需要计算数据的聚合值。在 SQL 中，这些函数称为聚合函数。
如果我们想找到prices关系中所有小工具的平均价格：
sql_expr = """
SELECT AVG(price) AS avg_price
FROM prices
"""
pd.read_sql(sql_expr, sqlite_engine)
| 平均价格 | |
|---|---|
| 0 | 395.072222 个 |
| --- | --- |
相当于，inpandas：
prices['price'].mean()
395.0722222222222postgresql 聚合函数的完整列表可以在这里找到。尽管我们在数据 100 中使用 PostgreSQL 作为 SQL 的主要版本，但请记住，SQL 还有许多其他变体（MySQL、SQLite 等），它们使用不同的函数名，并具有不同的可用功能。
分组依据并具有¶
使用聚合函数，我们可以执行更复杂的 SQL 查询。为了对更细粒度的聚合数据进行操作，我们可以使用以下两个子句：
GROUP BY获取列列表，并将表分组，如pandas中的pd.dataframe.groupby函数。HAVING在功能上与WHERE相似，但仅用于将谓词应用于聚合数据。（请注意，为了使用HAVING，它前面必须有一个GROUP BY子句。）
重要：使用GROUP BY时，SELECT子句中的所有列都必须在GROUP BY子句中列出或应用聚合函数。
我们可以使用这些声明来找到每个零售商的最高价格。
sql_expr = """
SELECT retailer, MAX(price) as max_price
FROM prices
GROUP BY retailer
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | 最高价格 | |
|---|---|---|
| 0 | Amazon | 450.00 |
| --- | --- | --- |
| 1 | Best Buy | 719.00 |
| --- | --- | --- |
| 2 | Target | 799.00 |
| --- | --- | --- |
| 3 | Walmart | 238.79 |
| --- | --- | --- |
比如说，我们有一个品味很高的客户，只想找到售价超过 700 美元的零售商。注意，我们必须使用HAVING定义聚合列上的谓词；我们不能使用WHERE筛选聚合列。为了计算满足我们需求的零售商列表和附带价格，我们运行：
sql_expr = """
SELECT retailer, MAX(price) as max_price
FROM prices
GROUP BY retailer
HAVING max_price > 700
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | max_price | |
|---|---|---|
| 0 | Best Buy | 七百一十九 |
| --- | --- | --- |
| 1 | Target | 七百九十九 |
| --- | --- | --- |
为了进行比较，我们在pandas中重新创建了相同的表：
max_prices = prices.groupby('retailer').max()
max_prices.loc[max_prices['price'] > 700, ['price']]
| price | |
|---|---|
| retailer | |
| --- | --- |
| 百思买 | 719.0 |
| --- | --- |
| 目标 | 799.0 |
| --- | --- |
订货人和限额¶
这些条款允许我们控制数据的显示：
ORDER BY让我们按照列值的字典顺序显示数据。默认情况下，order by 使用升序（ASC），但我们可以使用DESC指定降序。LIMIT控制显示多少元组。
让我们在prices表中显示三个最便宜的项目：
sql_expr = """
SELECT *
FROM prices
ORDER BY price ASC
LIMIT 3
"""
pd.read_sql(sql_expr, sqlite_engine)
| retailer | product | price | |
|---|---|---|---|
| 0 | Amazon | Battery pack | 24.87 |
| --- | --- | --- | --- |
| 1 | Best Buy | iPod | 200.00 |
| --- | --- | --- | --- |
| 2 | Target | iPod | 215.00 |
| --- | --- | --- | --- |
注意，我们不必包含ASC关键字，因为默认情况下ORDER BY按升序返回数据。用于比较，在pandas中：
prices.sort_values('price').head(3)
| retailer | product | price | |
|---|---|---|---|
| 3 | Amazon | Battery pack | 24.87 |
| --- | --- | --- | --- |
| 1 | Best Buy | iPod | 200.00 |
| --- | --- | --- | --- |
| 5 | Target | iPod | 215.00 |
| --- | --- | --- | --- |
（我们再次看到，索引在pandas数据帧中不正常。和以前一样，pandas在数据帧prices上返回一个视图，而 SQL 在每次执行查询时都显示一个新表。）
SQL 查询中的子句按特定顺序执行。不幸的是，此顺序与在 SQL 查询中写入子句的顺序不同。从最初执行到最后：
FROM：一个或多个源表- 【HTG0】：申请选择资格（删除行）
- 【HTG0】：分组和汇总
HAVING：消除组SELECT：选择列
**关于WHERE对HAVING**的说明：由于在应用GROUP BY之前处理了WHERE子句，因此WHERE子句不能使用聚合值。要基于聚合值定义谓词，必须使用HAVING子句。
摘要¶
我们介绍了 SQL 语法和使用关系数据库管理系统进行数据分析所需的最重要的 SQL 语句。