高级SQL

1、join和union

介绍

在SQL micro简介课程中，您学习了如何使用内部联接合并来自两个不同表的信息。现在，您将了解更多类型的联接，以及如何使用联合从多个表中提取信息。

在此过程中，我们将使用两个虚构的表，称为所有者和宠物。

两张桌子

owners表的每一行标识不同的宠物主人，其中ID列是唯一标识符。“宠物ID”列（在“所有者”表中）包含属于所有者的宠物的ID（此数字与“宠物”表中宠物的ID匹配）。

例如

宠物表显示Harris Bonkers博士是ID为1的宠物。

所有者表显示Aubrey Little是ID为1的宠物的所有者。

把这两个事实放在一起，哈里斯·邦克斯博士归奥布里·利特尔所有。同样，由于Veronica Dunn没有相应的宠物ID，因此她没有宠物。而且，由于5没有出现在宠物ID列中，Maisie没有主人。

加入

回想一下，我们可以使用内部联接从两个表中提取行，其中所有者表的Pet_ID列中的值与pets表的ID列中的值匹配。

在这种情况下，Veronica Dunn和Maisie不包括在结果中。但是，如果我们想创建一个包含所有宠物的表，而不管它们是否有主人，该怎么办？或者，如果我们要合并两个表中的所有行，该怎么办？在这些情况下，我们只需要使用不同类型的联接。

例如，要创建包含所有者表中所有行的表，我们使用左联接。在本例中，“left”是指查询中联接之前出现的表。（）右”表示联接后的表。）

将上面查询中的内部联接替换为左联接将返回两个表中具有匹配项的所有行，以及左表中的所有行（无论是否存在匹配项）。

如果改为使用右连接，则会得到匹配的行，以及右表中的所有行（无论是否存在匹配）。

最后，完全联接返回两个表中的所有行。请注意，通常情况下，两个表中不匹配的任何行都会有空条目来表示缺少的值。您可以在下图中看到这一点。

工会

如您所见，连接水平地组合来自不同表的结果。如果您想垂直连接列，可以使用并集。下面的示例查询组合了两个表中的年龄列。

请注意，对于UNION，两列的数据类型必须相同，但列名可以不同(因此，例如，我们不能从owners表中获取Age列的并集，而从pets表中获取Pet_Name列的并集。）

我们使用UNION ALL来包含重复的值—您会注意到9同时出现在owners表和pets表中，并在连接的结果中显示两次。如果要删除重复的值，只需将查询中的UNION ALL更改为UNION DISTINCT。

例子

我们将使用黑客新闻数据集。我们首先回顾comments表的前几行(相应的代码已隐藏，但您可以通过单击下面的“代码”按钮取消隐藏。）

from google.cloud import bigquery

# 创建一个“客户端”对象
client = bigquery.Client()

# 构造对“黑客新闻”数据集的引用
dataset_ref = client.dataset("hacker_news", project="bigquery-public-data")

# API请求-获取数据集
dataset = client.get_dataset(dataset_ref)

# 构造对“注释”表的引用
table_ref = dataset_ref.table("comments")

# API请求-获取表
table = client.get_table(table_ref)

# 预览表格的前五行
client.list_rows(table, max_results=5).to_dataframe()

您还将使用stories表。

# 构造对“stories”表的引用
table_ref = dataset_ref.table("stories")

# API请求-获取表
table = client.get_table(table_ref)

# 预览表格的前五行
client.list_rows(table, max_results=5).to_dataframe()

由于您已经熟悉从Intro到SQL micro课程的连接，我们将使用一个使用公共表表达式（CTE）的连接的相对复杂的示例。

下面的查询从stories and comments表中提取信息，创建一个表，显示2012年1月1日发布的所有故事以及相应的评论数量。我们使用左连接，以便结果包括没有收到任何评论的故事。

# 查询以选择2012年1月1日发布的所有故事，以及评论数
join_query = """
             WITH c AS
             (
             SELECT parent, COUNT(*) as num_comments
             FROM `bigquery-public-data.hacker_news.comments` 
             GROUP BY parent
             )
             SELECT s.id as story_id, s.by, s.title, c.num_comments
             FROM `bigquery-public-data.hacker_news.stories` AS s
             LEFT JOIN c
             ON s.id = c.parent
             WHERE EXTRACT(DATE FROM s.time_ts) = '2012-01-01'
             ORDER BY c.num_comments DESC
             """

# 运行查询，并返回一个数据帧
join_result = client.query(join_query).result().to_dataframe()
join_result.head()

由于结果是按num_comments列排序的，因此没有注释的故事将显示在数据帧的末尾(记住，NaN代表“不是数字”。）

# 这些报道都没有收到任何评论

join_result.tail（）

接下来，我们编写一个查询，以选择与2014年1月1日撰写故事或评论的用户相对应的所有用户名。我们使用UNION DISTINCT（而不是UNION all）确保每个用户最多在表中出现一次。

# 查询以选择在2014年1月1日发布故事或评论的所有用户
union_query = """
              SELECT c.by
              FROM `bigquery-public-data.hacker_news.comments` AS c
              WHERE EXTRACT(DATE FROM c.time_ts) = '2014-01-01'
              UNION DISTINCT
              SELECT s.by
              FROM `bigquery-public-data.hacker_news.stories` AS s
              WHERE EXTRACT(DATE FROM s.time_ts) = '2014-01-01'
              """

# 运行查询，并返回一个数据帧
union_result = client.query(union_query).result().to_dataframe()
union_result.head()

要获得2014年1月1日发布的用户数量，我们只需要获取数据帧的长度。

# 2014年1月1日发布故事或评论的用户数量
len(union_result)

2、分析函数

介绍

在SQL micro简介课程中，您学习了如何使用聚合函数，它根据行集执行计算。在本教程中，您将学习如何定义分析函数，这些函数也对一组行进行操作。但是，与聚合函数不同，分析函数为原始表中的每一行返回一个（可能不同的）值。

分析函数允许我们使用相对简单的语法执行复杂的计算。例如，我们可以快速计算移动平均数和运行总数，以及其他数量。

语法

为了理解如何编写分析函数，我们将使用一个小表，其中包含来自两个正在为比赛进行训练的不同人员的数据。id列标识每个跑步者，日期列保存训练课程的日期，时间显示跑步者投入训练的时间（以分钟为单位）。假设我们要计算每个跑步者训练时间的移动平均值，我们总是取当前和以前训练课程的平均值。我们可以通过以下查询执行此操作：

所有分析函数都有一个OVER子句，用于定义每次计算中使用的行集。OVER条款有三个（可选）部分：

PARTITION BY子句将表中的行划分为不同的组。在上面的查询中，我们用id除以，这样计算就用runner隔开了。

ORDERBY子句定义每个分区内的顺序。在示例查询中，按日期列排序可确保较早的培训课程最先出现。

最后一个子句（前一行和当前行之间的行）称为窗口框架子句。它标识每次计算中使用的行集。我们可以将这组行称为窗口(实际上，分析函数有时被称为分析窗口函数，或者简称为窗口函数！）

（更多关于）窗框条款

有许多方法可以编写窗口框架子句：

前一行和当前行之间的行-前一行和当前行。

前3行和后1行之间的行-前3行、当前行和下一行。

无界前向和无界后向之间的行-分区中的所有行。

当然，这不是一个详尽的列表，您可以想象还有更多的选项！在下面的代码中，您将看到这些子句中的一些正在起作用。

三类解析函数

上面的例子只使用了许多分析函数中的一个。BigQuery支持各种各样的分析函数，我们将在这里探讨一些。要获得完整的列表，您可以查看文档。

1） 解析聚合函数

您可能还记得，AVG（）（来自上面的示例）是一个聚合函数。OVER子句确保它被视为分析（聚合）函数。聚合函数将窗口中的所有值作为输入并返回单个值。

MIN（）（或MAX（））-返回输入值的最小值（或最大值）

AVG（）（或SUM（））-返回输入值的平均值（或总和）

COUNT（）-返回输入中的行数

2） 解析导航函数

导航函数根据（通常）与当前行不同的行中的值分配值。

FIRST_VALUE（）（或LAST_VALUE（））-返回输入中的第一个（或最后一个）值

LEAD（）（和LAG（））-返回后续（或前一）行上的值

3） 解析编号函数

编号函数根据顺序为每行指定整数值。

ROW_NUMBER（）-返回行在输入中的显示顺序（从1开始）

RANK（）-排序列中具有相同值的所有行接收相同的秩值，其中下一行接收一个秩值，该秩值按具有上一个秩值的行数递增。

例子

我们将使用旧金山OpenDataDataSet。我们首先回顾bikeshare_trips表的前几行(相应的代码已隐藏，但您可以通过单击下面的“代码”按钮取消隐藏。）

from google.cloud import bigquery

# 创建一个“客户端”对象
client = bigquery.Client()

# 构建对“旧金山”数据集的引用
dataset_ref = client.dataset("san_francisco", project="bigquery-public-data")

# API请求-获取数据集
dataset = client.get_dataset(dataset_ref)

# 构建“bikeshare_trips”表的参考
table_ref = dataset_ref.table("bikeshare_trips")

# API请求-获取表
table = client.get_table(table_ref)

# 预览表格的前五行
client.list_rows(table, max_results=5).to_dataframe()

表中的每一行对应不同的自行车出行，我们可以使用分析函数计算2015年每个日期的累计出行次数。

# 查询以计算每天的（累计）旅行次数
num_trips_query = """
                  WITH trips_by_day AS
                  (
                  SELECT DATE(start_date) AS trip_date,
                      COUNT(*) as num_trips
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE EXTRACT(YEAR FROM start_date) = 2015
                  GROUP BY trip_date
                  )
                  SELECT *,
                      SUM(num_trips) 
                          OVER (
                               ORDER BY trip_date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                               ) AS cumulative_trips
                      FROM trips_by_day
                  """

# 运行查询，并返回一个数据帧
num_trips_result = client.query(num_trips_query).result().to_dataframe()
num_trips_result.head()

该查询使用公共表表达式（CTE）首先计算每日行程数。然后，我们使用SUM（）作为聚合函数。

因为没有PARTITION BY子句，所以整个表被视为单个分区。

ORDER BY子句按日期对行进行排序，其中较早的日期最先出现。

通过将window frame子句设置为无界的前一行和当前行之间的行，我们确保使用当前日期之前（包括当前日期）的所有行来计算（累计）总和(注意：如果您阅读文档，您将看到这是默认行为，因此如果我们忽略这个window-frame子句，查询将返回相同的结果。）

下一个查询跟踪2015年10月25日当天每辆自行车开始（在start_station_id中）和结束（在end_station_id中）的站点。

# 查询2015年10月25日每辆自行车的起点站和终点站
start_end_query = """
                  SELECT bike_number,
                      TIME(start_date) AS trip_time,
                      FIRST_VALUE(start_station_id)
                          OVER (
                               PARTITION BY bike_number
                               ORDER BY start_date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                               ) AS first_station_id,
                      LAST_VALUE(end_station_id)
                          OVER (
                               PARTITION BY bike_number
                               ORDER BY start_date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                               ) AS last_station_id,
                      start_station_id,
                      end_station_id
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE DATE(start_date) = '2015-10-25' 
                  """

# 运行查询，并返回一个数据帧
start_end_result = client.query(start_end_query).result().to_dataframe()
start_end_result.head()

查询同时使用FIRST_VALUE（）和LAST_VALUE（）作为分析函数。

PARTITION BY子句根据bike_number列将数据划分为多个分区。由于此列包含自行车的唯一标识符，因此可确保对每辆自行车分别执行计算。

ORDERBY子句按时间顺序放置每个分区中的行。

由于window frame子句是介于无界前向和无界后向之间的行，因此对于每一行，都使用其整个分区来执行计算(这将确保同一分区中的行的计算值相同。）

3、嵌套和重复数据

介绍

到目前为止，您已经使用了许多类型的数据，包括数字类型（整数、浮点值）、字符串和日期时间类型。在本教程中，您将学习如何查询嵌套和重复的数据。这些是在BigQuery数据集中可以找到的最复杂的数据类型！

嵌套数据

假设一个假设的数据集包含有关宠物和他们的玩具的信息。我们可以将这些信息组织在两个不同的表中（宠物表和玩具表）。玩具表可以包含一个“宠物ID”列，用于将每个玩具与拥有它的宠物进行匹配。

BigQuery中的另一个选项是将所有信息组织在一个表中，类似于下面的pets_和_toys表。

在这种情况下，toys表中的所有信息都被折叠成一列（pets_和_toys表中的“Toy”列）。我们将pets_和_toys表中的“Toy”列称为嵌套列，并将“Name”和“Type”字段嵌套在其中。

嵌套列具有类型STRUCT（或类型RECORD）。这反映在下面的表模式中。

回想一下，我们将表的结构称为其模式。如果您需要回顾如何解释表模式，请随意查看SQL微教程简介中的这一课。

要查询包含嵌套数据的列，我们需要在包含该列的列的上下文中标识每个字段：

Toy.Name是指“Toy”列中的“Name”字段，以及

类型是指“玩具”列中的“类型”字段。

否则，我们的常规规则将保持不变——我们不需要更改有关查询的任何其他内容。

重复数据

现在考虑一下（更现实！）每个宠物可以有多个玩具的情况。在这种情况下，要将这些信息折叠到一个表中，我们需要利用不同的数据类型。

我们说“Toys”列包含重复的数据，因为它允许每行有多个值。这反映在下面的表模式中，“玩具”列的模式显示为“重复”。

重复字段中的每个条目都是一个数组，或具有相同数据类型的（零个或多个）值的有序列表。例如，Moon the Dog的“玩具”列中的条目是[Frisbee，Bone，Rope]，这是一个具有三个值的数组。

查询重复数据时，我们需要将包含重复数据的列的名称放入UNNEST（）函数中。

这实际上是将重复的数据展平（然后将其附加到表的右侧），以便每行有一个元素。有关这方面的说明，请查看下图。

嵌套和重复数据

现在，如果宠物可以有多个玩具，并且我们想记录每个玩具的名称和类型，该怎么办？在这种情况下，我们可以使“Toys”列嵌套并重复。

在上面的more_pets_和_toys表中，“Name”和“Type”都是包含在“toys”结构中的字段，“toys.Name”和“toys.Type”中的每个条目都是一个数组。

让我们看一个示例查询。

由于“玩具”列重复出现，因此我们使用UNNEST（）函数将其展平。而且，由于我们为展平的列指定了别名t，因此可以将“Toys”列中的“Name”和“Type”字段分别称为t.Name和t.Type。

为了巩固您所学到的知识，我们将在下面一节中将这些想法应用到一个真实的数据集。

例子

我们将使用Google Analytics示例数据集。它包含跟踪谷歌商品商店访问者行为的信息，这是一个销售谷歌品牌商品的电子商务网站。

我们首先打印ga_sessions_20170801表的前几行(我们已经隐藏了相应的代码。要查看，请单击下面的“代码”按钮。）此表跟踪2017年8月1日网站的访问量。

from google.cloud import bigquery

# 创建一个“客户端”对象
client = bigquery.Client()

# 构建对“google_analytics_sample”数据集的引用
dataset_ref = client.dataset("google_analytics_sample", project="bigquery-public-data")

# 构建对“ga_sessions_20170801”表的参考
table_ref = dataset_ref.table("ga_sessions_20170801")

# API请求-获取表
table = client.get_table(table_ref)

# 预览表格的前五行
client.list_rows(table, max_results=5).to_dataframe()

有关每个字段的说明，请参阅此数据字典。

该表有许多嵌套字段，您可以通过查看数据字典（提示：搜索“记录”在页面上的外观）或上面的表预览来验证这些字段。

在对该表的第一次查询中，我们将使用“总计”和“设备”列。

print("SCHEMA field for the 'totals' column:\n")
print(table.schema[5])

print("\nSCHEMA field for the 'device' column:\n")
print(table.schema[7])

在下面的查询中，我们将“浏览器”字段（嵌套在“设备”列中）和“交易”字段（嵌套在“总计”列中）称为device.browser和totals.transactions：

# 查询以统计每个浏览器的事务数
query = """
        SELECT device.browser AS device_browser,
            SUM(totals.transactions) as total_transactions
        FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
        GROUP BY device_browser
        ORDER BY total_transactions DESC
        """

# 运行查询，并返回一个数据帧
result = client.query(query).result().to_dataframe()
result.head()

通过将信息作为结构存储在“设备”和“总计”列中（与单独的表相反），我们避免了昂贵的联接。这提高了性能，使我们不必担心连接键（以及哪些表具有我们需要的确切数据）。

现在，我们将使用“hits”列作为嵌套和重复数据的示例。自：

“hits”是一个结构（包含嵌套数据）并重复，

“hitNumber”、“page”和“type”都嵌套在“hits”列中，并且

“pagePath”嵌套在“page”字段中，

我们可以使用以下语法查询这些字段：

# 查询以确定网站上最流行的登录点
query = """
        SELECT hits.page.pagePath as path,
            COUNT(hits.page.pagePath) as counts
        FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, 
            UNNEST(hits) as hits
        WHERE hits.type="PAGE" and hits.hitNumber=1
        GROUP BY path
        ORDER BY counts DESC
        """

# 运行查询，并返回一个数据帧
result = client.query(query).result().to_dataframe()
result.head()

在这种情况下，大多数用户通过“/主页”登录网站。

4、编写高效的查询

介绍

有时，查询是否有效并不重要。例如，您可能编写一个只运行一次的查询，并且它可能在一个小数据集上工作。在这种情况下，任何能给你所需答案的东西都可以。

但对于将要运行多次的查询，比如向网站提供数据的查询，又如何呢？这些必须是有效的，这样你就不会让用户等待你的网站加载。

或者对大型数据集的查询呢？如果它们写得不好，那么它们可能会很慢，并且会花费很多钱。

大多数数据库系统都有一个查询优化器，它试图以最有效的方式解释/执行查询。但在许多情况下，几种策略仍能带来巨大的节约。

一些有用的函数

我们将使用两个函数来比较不同查询的效率：

show_amount_of_data_scanned（）显示查询使用的数据量。

show_time_to_run（）打印查询执行所需的时间。

from google.cloud import bigquery
from time import time

client = bigquery.Client()

def show_amount_of_data_scanned(query):
    # dry_run lets us see how much data the query uses without running it
    dry_run_config = bigquery.QueryJobConfig(dry_run=True)
    query_job = client.query(query, job_config=dry_run_config)
    print('Data processed: {} GB'.format(round(query_job.total_bytes_processed / 10**9, 3)))
    
def show_time_to_run(query):
    time_config = bigquery.QueryJobConfig(use_query_cache=False)
    start = time()
    query_result = client.query(query, job_config=time_config).result()
    end = time()
    print('Time to run: {} seconds'.format(round(end-start, 3)))

策略

1） 仅选择所需的列。

使用SELECT*FROM…开始查询很有诱惑力。。。。这很方便，因为您不需要考虑需要哪些列。但它可能非常低效。

如果存在不需要的文本字段，这一点尤其重要，因为文本字段往往比其他字段大。

star_query = "SELECT * FROM `bigquery-public-data.github_repos.contents`"
show_amount_of_data_scanned(star_query)

basic_query = "SELECT size, binary FROM `bigquery-public-data.github_repos.contents`"
show_amount_of_data_scanned(basic_query)

在本例中，我们看到为完成查询而扫描的数据减少了1000倍，因为原始数据包含的文本字段比我们可能需要的字段大1000倍。

2） 读取更少的数据。

以下两个查询都计算了旧金山市单程自行车旅行的平均持续时间（秒）。

more_data_query = """
                  SELECT MIN(start_station_name) AS start_station_name,
                      MIN(end_station_name) AS end_station_name,
                      AVG(duration_sec) AS avg_duration_sec
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE start_station_id != end_station_id 
                  GROUP BY start_station_id, end_station_id
                  LIMIT 10
                  """
show_amount_of_data_scanned(more_data_query)

less_data_query = """
                  SELECT start_station_name,
                      end_station_name,
                      AVG(duration_sec) AS avg_duration_sec                  
                  FROM `bigquery-public-data.san_francisco.bikeshare_trips`
                  WHERE start_station_name != end_station_name
                  GROUP BY start_station_name, end_station_name
                  LIMIT 10
                  """
show_amount_of_data_scanned(less_data_query)

由于站点ID和站点名称之间存在1:1的关系，因此我们不需要在查询中使用start\u station\u ID和end\u station\u ID列。通过只使用带有站点ID的列，我们可以扫描更少的数据。

3） 避免N:N连接。

您在本课程中执行的大多数联接都是1:1联接。在这种情况下，每个表中的每一行在另一个表中最多有一个匹配项。

另一种类型的联接是N:1联接。在这里，一个表中的每一行都可能与另一个表中的许多行相匹配。

最后，N:N联接是一个表中的一组行可以与另一个表中的一组行匹配的联接。请注意，一般来说，在所有其他条件相同的情况下，这种类型的联接生成的表的行数比正在联接的两个（原始）表中的任何一个都要多。

现在，我们将使用一个来自真实数据集的示例。下面的两个示例都计算了不同提交者的数量以及几个GitHub存储库中的文件数量。

big_join_query = """
                 SELECT repo,
                     COUNT(DISTINCT c.committer.name) as num_committers,
                     COUNT(DISTINCT f.id) AS num_files
                 FROM `bigquery-public-data.github_repos.commits` AS c,
                     UNNEST(c.repo_name) AS repo
                 INNER JOIN `bigquery-public-data.github_repos.files` AS f
                     ON f.repo_name = repo
                 WHERE f.repo_name IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                 GROUP BY repo
                 ORDER BY repo
                 """
show_time_to_run(big_join_query)

small_join_query = """
                   WITH commits AS
                   (
                   SELECT COUNT(DISTINCT committer.name) AS num_committers, repo
                   FROM `bigquery-public-data.github_repos.commits`,
                       UNNEST(repo_name) as repo
                   WHERE repo IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                   GROUP BY repo
                   ),
                   files AS 
                   (
                   SELECT COUNT(DISTINCT id) AS num_files, repo_name as repo
                   FROM `bigquery-public-data.github_repos.files`
                   WHERE repo_name IN ( 'tensorflow/tensorflow', 'facebook/react', 'twbs/bootstrap', 'apple/swift', 'Microsoft/vscode', 'torvalds/linux')
                   GROUP BY repo
                   )
                   SELECT commits.repo, commits.num_committers, files.num_files
                   FROM commits 
                   INNER JOIN files
                       ON commits.repo = files.repo
                   ORDER BY repo
                   """

show_time_to_run(small_join_query)

第一个查询有一个大的N:N联接。通过重写查询以减小联接的大小，我们可以看到它运行得更快。

了解更多

这些策略以及更多内容将在本Google BigQuery全面指南中讨论。如果您想了解更多关于如何编写更高效的查询（或加深对BigQuery的所有知识），我们鼓励您查看！