这六个SQL小技巧，让你的分析效率突飞猛进！

数据科学家/分析师应该了解 SQL，事实上，所有从事数据和分析工作的专业人员都应该了解 SQL。在某种程度上，SQL 是一项被低估的数据科学技能，因为很多人认为它是一种从数据库中提取数据，以供Pandas和{tidyverse}这两个更花哨的方式来整理数据，虽然很有必要，但却不一点都不流行。

然而，随着该行业每天都在收集和生产大量数据，只要数据位于符合 SQL 标准的数据库中，SQL 仍然是帮助你调查、过滤和聚合数据来彻底了解业务的最有效工具。通过使用 SQL 对数据进行交叉分析，分析人员可以识别值得进一步研究的模式，通常会重新定义分析人群和变量，使其大大小于初始范围。

因此，与其将庞大的数据集传输到 Python 或 R 中，分析的第一步应该是使用 SQL ，从我们的数据中获取信息见解。

在现实世界的关系数据库中，SQL 不仅仅是 SELECT、JOIN、ORDER BY 语句。在这篇文章中，我将讨论 6 个技巧（和一个额外技巧），使你的分析更高效地使用 SQL 及其与其他编程语言（如 Python 和 R）的集成。

在本次实际操作中，我们将使用 Oracle SQL 来处理下面的玩具数据表，该表由多种类型的数据元素组成，如果你想了解更多数据分析相关内容，可以阅读以下这些文章：
Facebook和Microsoft数据科学家面试,他们会问这些SQL问题
如何在Jupyter Notebook里运行SQL？
面试常见5大SQL题目：培养SQL技能，助你完胜任何面试！
四条SQL准则：提升你的数据科学技能

1. 用COALESCE() 处理 NULL/缺失数据

处理缺失值时，COALESCE() 函数是我们的独家秘诀，在这种情况下，它将 NULL 重新编写为第二个参数中指定的值。在本实例中，我们可以将 NULL_VAR 重新编为字符值“MISSING”

此代码片段会返回

然而，有一点需要注意，在数据库中，除了 NULL 之外，还可以通过多种方式对缺失值进行处理。例如，它们可以是空字符串/空格（例如，表中的 EMPTY_STR_VAR），或字符串“NA”（例如，表中的 NA_STR_VAR）。在这些情况下， COALESCE( ) 就不能用了，但可以使用 CASE WHEN 语句进行处理，

用CASE WHEN 重新编写空格或NA

2. 计算运行总次数和累计次数

当我们对总数（而不是单个值）感兴趣，从而进行潜在的分析、群体细分和异常值识别时，运行总次数可能很有用。

下面展示了如何计算变量 NUM_VAR 的运行总数和累积频率

这是我们的输出结果。

这里有两个技巧，(1) 对无边界的行进行求和，可以计算出所有先前值的总和；(2)创建JOIN_ID，计算总和。

我们使用窗口函数进行此计算，从累积频率来看不难发现，最后一条记录为异常值。

3. 找到没有self join的极端值的记录

这里，我们的任务是为每个独有 ID 返回具有最大 NUM_VAR 值的行。直观的查询首先是使用 group by 找到每个 ID 的最大值，然后对 ID 和最大值进行self join。但以下是更简单的方法：

此查询应为我们提供以下输出结果，显示按 ID 分组的具有最大 NUM_VAR 值的行

4. 条件 WHERE 从句

每个人都知道 SQL 中的 WHERE 从句。事实上，我发现自己使用条件 WHERE 从句的次数更加频繁。例如，对于玩具表，我们只想保留满足以下逻辑的行，

— if SEQ_VAR in (1, 2, 3) & diff(DATE_VAR2, DATE_VAR1)≥ 0

— elif SEQ_VAR in (4, 5, 6) & diff(DATE_VAR2, DATE_VAR1) ≥1

— else diff（DATE_VAR2，DATE_VAR1）≥2

现在条件 WHERE 从句派上用场了，

上述逻辑应该消除ID = 19064的序列4、5，因为date2和date1之间的差值为 0，这正是上面查询返回的内容。

5. Lag() 和 Lead() 来处理连续行

Lag（查看前一行）和 Lead（查看下一行）可能是我日常工作中最常用的两个分析函数。简而言之，用户通过这两个函数一次查询多个行，而无需自连接。

假设，我们要计算两个连续行（按序列排序）之间的 NUM_VAR 差异：

LAG() 函数会返回前一行，如果没有（即每个 ID 的第一行），则 PREV_NUM 编码为 0 ，计算如下 NUM_DIFF所示的差值，

6. 将SQL查询集成到Python和R语言

将 SQL 查询集成到 Python 和 R 语言中的前提，是通过 ODBC 或 JDBC 建立数据库连接。由于这超出了本文的范围，我不会在这里详细讨论它。

现在，我们先假设已经将 Python 和 R 语言连接到了我们的数据库，在 Python 中使用查询的最直接方法，是将其作为字符串复制粘贴，然后调用 pandas.read_sql()，

只要我们的查询很短，并且无需进一步更改，就可以证明这种方法很有用。但是，如果我们的查询有 1000 行，或者我们需要不断更改呢？在这样的情形下，我们希望将 .sql 文件直接读入 Python 或 R语言中。下面将演示如何在 Python 中操作 getSQL 函数，其思路同样适用于R语言，

这里，第一个 arg sql_query 接受一个独立的 .sql 文件，这个文件就能更易于维护：

“ID_LIST”是我们将要放入的值的占位符字符串，可以使用以下代码调用 getSQL()，

额外提示，SQL 中的正则表达式

尽管在 SQL 中，我并不常用正则表达式，但它有时可以便于文本提取。例如，以下代码显示了如何使用 REGEXP_INSTR( ) 来查找和提取数字。

希望这篇文章对你有所帮助！你还可以订阅我们的YouTube频道，观看大量数据科学相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Yi Li
翻译作者：Lia
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://towardsdatascience.com/6-sql-tricks-every-data-scientist-should-know-f84be499aea5