初学数据科学常犯的三个SQL错误
在之前的文章中,我们曾经讨论过一些帮助你提升数据分析工作能力的SQL技巧。今天,我想换一个角度,讨论一下初学数据科学常犯的3个SQL错误。要先了解这些错误,才能有效避免,才能写出性能最高的SQL查询语句,这对每个数据科学家/分析师至关重要。如果你想了解更多数据分析相关内容,可以阅读以下这些文章:
如何在Pandas里写SQL查询语句?
数据科学家面试必须掌握的十个SQL概念
SQL面试常考题有哪些?
提到SQL错误,主要有两种类型。第一种类型称为语法错误 (Syntax Errors),顾名思义,是指SQL语句不符合编程规则。SQL平台会显示存在错误,并停止运行程序。对于语法错误,我们无法通过谷歌搜索或Stack Overflow来解决!
另一类错误是逻辑错误 (Logic Errors),这是最难发现的。虽然代码看起来正确,并且可以成功运行,但是它在做错误的事。今天这篇文章要讲的也就是SQL中的逻辑错误,它们不会触发任何错误消息,但会让你的分析结果严重脱离预期目标。
我们会使用以下玩具相关的数据表来进行示范:
这是来自一个网站的数据集,用于追踪用户的登录信息。它包含用户ID、用户名、登录日期和登录位置,然后按照时间顺序对于每一个用户排序。出于演示目的,所有代码都会在MS SQL Server 2017中实现。
错误#1:误用IN来匹配多个条件
IN可用于检查一个特定的值是否与列表中的任何值匹配。它是多个OR条件的精简版本。尽管简单明了,但有时IN会被误用于实际为AND条件的问题。
我们的第一个任务是找到既在NYC又在Illinois登陆过的用户。一些分析师会立即写下以下查询语句:
输出结果:
IN的输出
这个结果是不对的,因为我们不应该看到ID 0180 /User DEF,该用户仅在NYC登陆过一次,并没有在伊利诺伊州登录过。
如何避免这个错误?
实际上,仅使用IN来简化这个问题是不对的,因为这个问题的条件逻辑是AND(即NYC登录和 Illinois登录名)而不是OR。可以采取以下措施解决这个错误:
首先,用一个计数器(即使用SUM,每经过一行,SUM递增1)来单独检查每个条件;然后,选择出两个计数器都大于0的情况。这样,我们就能够实现AND的条件逻辑,确保得到既在NYC又在Illinois登陆过的用户:
错误#2:OR和AND逻辑使用不当
当要使用多个OR和AND时,缺少逻辑顺序会改变应有的结果。让我们看一下下面这个查询语句:
我们的任务是获得(1)在NYC发生;并且(2)在2018年发生或者总的Num_Var≥1000的用户登录。下面是输出结果:
为什么这里会有一条Illinois的登陆记录(它违反了第一个条件,在NYC发生)?这是因为它符合Num_Var≥1000!
OR和AND的使用是要讲究顺序的;因此,上面的WHERE子句实际上标识了(1)在NYC并且在2018年发生;或者(2)总的Num_Var≥1000的所有用户登录。这显然不是我们的任务要求的。
如何避免这个错误?
我们可以用括号来修正这个错误:
这样就对了:
错误#3:无法考虑NULL值
最后,我们来探讨一下单个用户的案例,例如ID 0155 /User ABC。我们要计算这个用户的California登录和非-California登录:
查询结果:
同样,结果有些奇怪——California登录次数和非-California登录次数加起来与总登录次数并不相等!在检查原始数据表时,我们注意到ID 0155缺少一个值(编码为NULL)。
如何避免这个错误?
将NULL与实际值进行比较将返回缺失的值,缺失的值既不是= ‘California’也不是<> ‘California’。因此,我们还需要一个CASE WHEN:
结果符合预期:
使用CASE WHEN NULL得到的输出结果
结论
在数据分析工作中,避免出现上述3种SQL错误,才能写出更可靠、更有效的代码。希望这篇文章对你有用。感谢你的阅读!你还可以订阅我们的YouTube频道,观看大量数据科学相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/
原文作者:Yi Li
翻译作者:Haoran Qiu
美工编辑:过儿
校对审稿:Jiawei Tong
原文链接:https://towardsdatascience.com/3-common-sql-errors-to-spot-an-inexperienced-data-scientist-310b3e98842f