excel写sql(从Excel到SQL效率增倍错误减半)

如果你经常用到Excel,下面这个令人沮丧的对话框可能对你来说并不陌生。

excel写sql(从Excel到SQL效率增倍错误减半)(1)

也可能遇到过这种情况:编辑电子表格时运转非常缓慢,每点击一次鼠标电脑就会卡10秒。

出现这种情况通常是由于文件太大或工作簿中的公式过多。Excel最多可以处理100万行数据,但当你处理大型数据集或进行繁重的分析时,例如,将公式应用于一组单元格、链接多个电子表格或连接到其他工作簿时,在未超出Excel处理的最大行数之前,其操作速度会大大降低。

Excel还有一个可能导致其效率低下的弱点: 结构过于灵活。这听起来有点匪夷所思——灵活性是人们钟爱Excel的原因之一。因为每个单元格都是一个独立的实体,所以可以自由地添加脚注、合并单元格或绘制“刺绣图案”。

excel写sql(从Excel到SQL效率增倍错误减半)(2)

但是,如果一个单元格的操作很简单,那么就很难信任整个电子表格的完整性。Excel的灵活性使得在大型数据集中几乎不可能实现一致性和准确性。不管你有多警惕,不管你在电子表格中梳理了多少次拼写错误和错误的公式,你可能仍会有所遗漏。

不过完成这项工作通常有更好的工具。有些小窍门可以帮助你利用Excel知识来学习SQL。

excel写sql(从Excel到SQL效率增倍错误减半)(3)

嗨,SQL

在Excel中使用的数据肯定来源于某处。而这个来源就是数据库。即使你感觉数据的来源缺乏技术性(比如Google Analytics、Stripe或Salesforce),但你查询的就是数据库。

我们的网站1月份的访问量是多少? 我们刚刚推出的产品的支付渠道放弃率是多少?哪些销售代表拥有更多的销售渠道?这些都是人类的疑问,而不是计算机语言。作为一个Excel的高级用户,如果拥有正确的数据集,你可能会考虑如何将这些问题写成公式。

那么,如何直接查询数据库呢?在大多数情况下,人们使用SQL( Structured Query Language 结构化查询语言)。SQL会告诉数据库要对哪些数据进行查看和运算操作。

将一些初始运算导入SQL中,可以减少导出的数据量。若使用的数据集较小,那么你可能不会遇到Excel的性能问题。

随着你对SQL的操作熟练度的提高,你可以将越来越多的分析转移到SQL中,直到Excel成为例外,而不是规则。SQL数据库可以处理大量数据而无需担心性能问题,并且具有保护数据完整性的有序结构。

学习一门新语言可能听起来令人望而生畏,就像使用感觉技术性更高的工具一样。但是作为一个Excel用户,你对SQL的了解已经超出自己的预期。

excel写sql(从Excel到SQL效率增倍错误减半)(4)

电子表格,满足关系数据

数据库是一个有序数据集合。数据库有很多不同的类型,但是有一种数据库可以与SQL建立连接,即关系数据库(relation database)。

正如Excel工作簿由电子表格组成一样,关系数据库也由表组成,如下所示。

excel写sql(从Excel到SQL效率增倍错误减半)(5)

与电子表格一样,表也有行和列。但是在表中,单元格(或数据库术语中的“值”)之间不能建立联系。若想将Ralph Abernathy的家乡从上图所示表格的第一行中排除,你不能直接将其删除,而必须排除整个行或者整个“家乡”列。

不能动态更改单元格的原因是数据库具有严格的结构。作为独立的单元,每行中的所有值绑定在一起。每个列必须有唯一的名称,并且只能包含特定类型的数据(“Integer”、“Text”、“Date”等)。

Excel的灵活结构现在听起来不错,但请稍等。因为数据库的结构非常严格,所以保护数据的完整性较容易。换句话说,你所得结果中出现不一致和错误的可能性要小得多。这意味着你的数据的可信度更高。

excel写sql(从Excel到SQL效率增倍错误减半)(6)

从公式转为查询

在Excel中操作数据最常用的方法是使用公式。公式由一个或多个函数组成,这些函数告诉Excel如何处理单元格中的数据。例如,你可以使用SUM(A1:A5)进行数值求和,或者使用AVERAGE(A1:A5).求其平均值。

公式所对应的SQL语句是查询。返回上表的查询,如下所示

SELECT player_name,

hometown,

state,

weight

FROM benn.college_football_players

SELECT 和 FROM 是任何SQL查询的两个基本组成部分。SELECT 指定所需数据的列, FROM指示该数据列属于哪个表。你可以通过在SELECT 后添加星号(*)来表示所有的列,如下所示。

SELECT *

FROM benn.college_football_players

该查询将会显示 benn.college_football_players 表中的所有列,这样你就可以对整个数据集有所了解。一旦知道需要什么,你就可以快速地删除列以减小数据集规模。

与公式一样,查询由指定数据操作的函数组成。查询还可以包含子句、运算符、表达式和其他一些组件,但是我们不打算在这里细讲。你需要知道的是,你可以使用SQL操作数据,且操作方式与excel的几乎完全一样。

以 IF 函数为例。使用 IF 创建条件语句,根据定义的规则过滤数据或添加新数据。当你把一个 IF 函数应用到一个单元格上时,所得结果如下:

=IF(logical_test, value_if_true, [value_if_false])

也可表示为IF <some condition is met> THEN <display this value> OTHERWISE <display a different value>. 其中,OTHERWISE部分(显示为)是可选的。相当于 IF 的SQL语句是 CASE 。两者的语法非常相似。

CASE WHEN <condition 1 is met> THEN <display value 1>

ELSE <display a different value>

END

CASE语句比IF 语句更容易阅读,因为SQL查询有多行,是具有多个条件的IF 语句的理想化的结构。例如,若想在基于Excel中的现有数据中添加两个类别,则必须将一个IF语句嵌入另一个IF语句中。当添加的条件很多时,事情很快就会变得很糟糕。但是在SQL中,你可以添加一个新条件作为另一行。

在SQL中,让我们用上面的大学足球队的数据来添加多个条件。我们要添加一列,把足球运动员分成四组。其查询如下:

SELECT player_name,

weight,

CASE WHEN weight > 250 THEN 'over 250'

WHEN weight > 200 THEN '201-250'

WHEN weight > 175 THEN '176-200'

ELSE '175 or under' END AS weight_group

FROM benn.college_football_players

所得表格如下:

excel写sql(从Excel到SQL效率增倍错误减半)(7)

也没有很难,对不对?不过这对于IF语句将是一场噩梦。

你可能会想,那么图表呢?哪些让我的报告称得上是报告的图形呢? 一种选择是在SQL中操作数据、导出数据并在Excel中构建图表。

但是,如果你想跳过导出步骤,一些SQL程序(比如Mode)允许你在查询结果之上构建图表。这些图表是直接绑定到数据库的,因此每当重新运行查询时,结果和可视化都会自动刷新。

excel写sql(从Excel到SQL效率增倍错误减半)(8)

学习SQL的下一步

当你对SQL有所了解时,知道什么是学习重点以及如何处理公司的数据是很有帮助的。

选择针对数据分析的教程

SQL的资源有很多,但不是所有的SQL资源都专注于数据分析。

工程师和数据库管理员使用SQL在数据库中创建、更新和删除表。他们可以上传一个全新的表,也可以从数据库中永久删除一个表。这些任务与你将如何使用SQL有很大的不同(至少在你对数据感兴趣并因此转为从事数据分析工作之前)。

不要陷入针对数据库管理的SQL教程中。专注于查询相关的教程。下面是一些SQL教程:

· 数据检索

· 数据过滤及一些简单的运算

· 同时使用多个过滤器

· 对结果进行排序

· 数据聚合

· 计算列中的唯一值

· 条件逻辑

· 数据集连接

如果你发现自己在教程中讨论以下内容:

· CREATE TABLE

· DROP TABLE

· CREATE DATABASE

· DROP DATABASE

那你的关注点已经出错了。

利用公司的数据进行练习

如果你办公时需要进行数据分析,那么没有什么比利用公司的数据学习SQL更合适了。你可以探索公司的数据结构,同时学习技术概念。你的任何选择都将立即适用于你的工作。

要做到这一点,你需要了解公司的数据是如何构成的:产品和营销数据存储在哪里? 如果你想查看上个月出现问题的帐户,应该查询哪个表?

大多数企业都有一个分析团队,每天处理公司的数据。这些人将能够回答你的问题或为你指出有用的文档。这里有一种互惠关系: 如果你自己查询数据,分析团队积压的数据请求就会减少。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页