数字时代基本功:使用简单的查询语言帮您更好地掌握数据

  • 这是公民记者和活动家组织必备的基本功。本文将尽可能说得简单,因此即便您没有任何基础,也一样不难掌握本文中介绍的知识

在现代工作场所中,处理数据已成为越来越重要的技能。

数据不再是分析师和软件工程师的领域。借助当今的技术,任何人都可以使用数据来分析趋势并提供决策依据。

处理数据时的基本概念是“查询”数据集。就是字面上的意思:询问有关一组数据的问题。查询语言是一种软件语言,提供用于询问此类问题的语法。

如果您没有编写查询的经验,它们可能会显得有些吓人。但是,不要怕!只需少量练习,您就可以掌握基础知识。

本文就是您开始的地方。

Google Visualization API查询语言

您可能已经在大部分日常工作中使用过 Google 表格。也许您已经熟悉使用它来生成图表的方法。

Google Visualization API查询语言是在幕后起作用的魔力,是它使之成为可能。

但是您是否知道您可以通过 QUERY()函数访问此语言吗?它是处理大量数据的强大工具

查询语言和SQL之间有很多相似之处。

在这两种情况下,您都将定义列和行的数据集,并通过指定各种标准和条件来选择不同的列和行。

在本演示中,示例数据将来自一个大型CSV文件,其中包含1872年至2019年之间的国际足球比赛结果。您可以从 Kaggle 下载该数据

在新的Google表格中,上传 CSV 文件。您可以使用 Ctrl + A(在Mac上为 Cmd + A)选择所有数据。

从功能区菜单中,选择“数据”>“命名范围…”,然后将所选范围称为“数据”。这将使其更易于使用。

现在,您可以开始查询数据了。在电子表格中创建一个新选项卡,并在单元格 A1 中创建一个新的 QUERY() 公式。

获取所有英格兰比赛

第一个查询查找数据集中所有英格兰为主队或客队的比赛。

QUERY()至少接受两个参数。第一个是命名范围,它是需要查询的数据集;第二个是包含实际查询的字符串。

=QUERY(data, "SELECT * WHERE B = 'England' OR C = 'England'")

下面分析一下。

SELECT * 要求返回数据集中的所有列。如果只需要A、B和C列,则可以编写 SELECT A,B,C。

接下来需要包含一个过滤器,以便仅查找B列或C列中包含团队 ’England’ 的行。

确保查询中的字符串使用单引号。双引号用于打开和关闭查询本身。

此公式将返回英格兰踢过的所有比赛。如果要搜索另一个团队,只需在过滤器中更改条件即可。

统计所有友谊赛

接下来,我们计算一下数据集中有多少个友谊赛匹配项。

=QUERY(data, "SELECT COUNT(A) WHERE F = 'Friendly'")

利用查询语言 COUNT()函数。这是一个聚合函数的示例。聚合函数将许多行汇总起来。

例如,在此数据集中,有16,716行,其中列 F 等于 “Friendly”。查询不返回所有这些行,而是返回单个行 —— 对其进行计数。

聚合函数的其他示例包括 MAX(),MIN()和 AVG()。不是返回与查询匹配的所有行,而是查找它们的最大值、最小值和平均值。

按比赛分组

如果在聚合函数旁边使用 GROUP BY ,则汇总函数可以做更多的事。该查询会找出每种锦标赛类型进行了多少场比赛。

=QUERY(data, "SELECT F, COUNT(A) GROUP BY F")

该查询按F列中的每个值对数据集进行分组。然后,它计算每组中有多少行。

您可以在多个列上使用 GROUP BY。例如,要查找每个国家在锦标赛中进行了多少场比赛,请使用以下查询:

=QUERY(data, "SELECT H, F, COUNT(A) GROUP BY H, F")

下面来尝试一些更高级的过滤。

获取所有英格兰vs德国的比赛

您可以使用AND和OR指定更复杂的过滤器逻辑。为了便于阅读,可以在过滤器的每个部分周围使用括号。

例如,要查找英格兰和德国之间的所有比赛,就是这样:

=QUERY(data, "SELECT * WHERE (B = 'England' AND C = 'Germany') OR (C = 'England' AND B ='Germany')")

此筛选有两个条件:一个条件是英格兰是主场队,而德国则不是;另一个条件是相反的。

使用数据验证可轻松选择数据集中的任何两个球队。

然后,您可以编写一个查询,使用其过滤器中不同单元格的值。请记住,使用单引号标识查询中的字符串,并使用双引号打开和关闭查询的不同部分。

=QUERY(data, "SELECT * WHERE (B = '"&B1&"' AND C = '"&B2&"') OR (C = '"&B1&"' AND B ='"&B2&"')")

寻找趋势

聚合函数和过滤器结合使用时将成为功能强大的工具。一旦熟悉了它们的工作方式,就可以开始在数据集中搜索各种有趣的趋势。

例如下面的例子,查找自1900年以来每年的每场比赛平均进球。

=QUERY(data, "SELECT YEAR(A), AVG(D) + AVG(E) WHERE YEAR(A) >= 1900 GROUP BY YEAR(A)")

如果将查询结果绘制为线形图,则可以立即开始查看一段时间内的趋势。

排序结果

有时,您对查找数据集中的全部匹配行不感兴趣。通常,您将需要根据一些条件对它们进行排序。也许您只希望找到前十个记录。

该查询查找数据集中得分最高的十个比赛的匹配项。

=QUERY(data, "SELECT * ORDER BY (D+E) DESC LIMIT 10")

注意 ORDER BY 语句。这将根据指定的列对行进行排序。在这里,查询按比赛中的得分数对输出进行排序。

DESC 关键字指示按降序排序(ASC 则是按升序对它们进行排序)。

最后,LIMIT 将输出限制为给定的行数(在本例中为10行)。

哪些城市举办了最多的世界杯比赛?

现在,作为最后一个例子,将所有内容整合在一起,激发您的想象力。

此查询查找举办最多FIFA世界杯比赛的前十个城市。

=QUERY(data, "SELECT G, COUNT(A) WHERE F = 'FIFA World Cup' GROUP BY G ORDER BY COUNT(A) DESC LIMIT 10")

现在轮到你了

希望本文对您的调查工作有用。如果您熟悉每个示例中的逻辑,那么您就可以尝试使用真正的 SQL。

这将介绍诸如JOINS、嵌套查询和窗口函数的概念。当您掌握了这些内容后,您处理数据的能力就会大幅度提升。

有很多地方可以开始学习 SQL。比如这里。加油哦!⚪️

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据