数据科学家要掌握的高级SQL

本文介绍了一些技巧，这些技巧一旦掌握，就可以为用户提供处理各种数据类型的工具。本文不涉及及与数据库管理有关的任何内容，例如表创建或架构。

如果您想继续，可以在此处使用SQLite设置本地SQL Server。

本文来自《数据黑客》，登录官网可阅读更多精彩资讯和文章。

内容导航：

1. 探索示例数据
2. JOIN充当过滤器
3. Self Joins
4. CASE WHEN
5. 子查询
6. 公用表表达式
7. 窗口函数

探索示例数据

在筛选之前，让我们快速浏览一下表格。

SELECT *  
FROM avocados  
LIMIT 50

此数据集中还有两个用于类型(type)和区域ID(regionid)的映射表，它们将数字类别映射到实际文本。

JOIN充当过滤器

通过将INNER JOIN与使用AND的过滤器结合使用，我们可以在联接表时对其进行过滤，与常规联接和WHERE过滤器相比，性能提高很小。对于大型数据集，这很方便，但是您需要很好地了解数据，因为您可能会丢弃不想要的行，因为此技术使用了INNER JOIN。

SELECT *  
FROM avocados a  
INNER JOIN region b ON a.regionid = b.regionid  
AND b.region = 'Denver'

Self Joins

当我们需要将不同行中的信息放在同一行中时，自联接（self joins）特别有用。我的数据集并没有完全设置为执行此操作，但是我们仍然可以说明这一点。

让我们尝试获取同一行在同一日期的2个地区的总交易量。

首先，请注意我们为每个avocados赋予的别名。它是同一张桌子，但我们分别将其称为a和b。

在联接内部，我们在日期和regionid上联接，但是我们使用了不等于运算符（ < > ）确保不会将相同类型联接在一起。

结果是一行上都有两种类型的结果，而不是每行都出现在单独的行中。

请注意，由于类型的每种组合，我们最终都会在这里得到重复的数据。这可以通过过滤来避免。

SELECT   
  a.date, a.avo_a, a.type,   
  b.avo_a, b.type, b.regionid  
FROM avocados a  
INNER JOIN avocados b   
  ON a.date = b.date   
  AND a.regionid=b.regionid  
  AND a.type <> b.type   
WHERE a.date = '2015-01-04'  
  AND a.regionid = 1

CASE WHEN

CASE WHEN本质上是 IF 函数，几乎在所有编码语言中都可以找到。与往常一样，在编写这些内容时，请记住以下“流程”：IF -> THEN ->
ELSE。

我们将使用它来将类型映射到实际类型（我们也可以通过联接我们的类型表来实现）。

请注意，公式以 CASE 开头，以 END 结尾。我们可以在 END 之后使用AS 为列指定别名。

我们可以根据需要提供多个WHEN / THEN类别。 在这里我们只需要一种，因为我们有2种类型， 第二种被ELSE覆盖

 SELECT date, avo_a,  
 CASE  
  WHEN type =1 THEN 'conventional'  
  ELSE 'organic'  
 END AS avo_type  
FROM avocados  
WHERE regionid = 1

子查询

子查询（Sub Queries）是嵌套查询，即查询中的查询。

假设我们需要完整的数据集，但只需要按总体积列出的前10个区域。

我们可以在下面的查询中找到前十名，然后可以使用此区域列表来过滤主表。但是，如果我们获得了新数据并且前十名发生了变化，该怎么办？我们必须再次运行两个查询。我们可以使用子查询来使其更加动态。

要获得前十名，我们将运行以下内容：

SELECT b.region, ROUND(SUM(a.totalvol))  
FROM avocados a  
LEFT JOIN region b ON a.regionid=b.regionid  
GROUP BY 1  
ORDER BY 2 DESC  
LIMIT 1

在下面的查询中，我们使用 INNER JOIN 将列表过滤到子查询中的区域。我们也可以从子查询中取回数据，因为我们已经加入了它。

请注意别名在这里的工作方式。子查询别名独立于主查询别名，这就是为什么我们有2个’a’和’b’别名的原因。要引用子查询列，我们必须使用子查询别名（在这种情况下为c）。

SELECT date, totalvol, avo_a, avo_b, b.region, c.total_totalvol  
FROM avocados a  
LEFT JOIN region b ON a.regionid=b.regionid  
INNER JOIN (  
  SELECT b.regionid, ROUND(SUM(a.totalvol)) as total_totalvol  
  FROM avocados a  
  LEFT JOIN region b ON a.regionid=b.regionid  
  GROUP BY 1  
  ORDER BY 2 DESC  
  LIMIT 10) c ON a.regionid = c.regionid

子查询也可以在 FROM 或 WHERE 子句之后使用。让我们用一个简单的例子来说明这些。

FROM 子查询

SELECT AVG(total_totalvol)  
FROM (  
  SELECT b.regionid, ROUND(SUM(a.totalvol)) as total_totalvol  
  FROM avocados a  
  LEFT JOIN region b ON a.regionid=b.regionid  
  GROUP BY 1  
  ORDER BY 2 DESC  
  LIMIT 10)

WHERE 子查询

SELECT *  
FROM avocados  
WHERE regionid IN (  
  SELECT regionid  
  FROM  region   
  WHERE region LIKE 'West%')

公用表表达式

公用表表达式（又名CTE ，即WITH 语句）是一个临时数据集，将用作查询的一部分。它仅在执行该查询期间存在；即使在同一会话中也不能在其他查询中使用它。

如果您打算在同一查询中重用子查询，则建议使用“公用表表达式”，因为该子查询被临时保存到内存中，这意味着它不需要多次运行。它们通常看起来也更干净，因此，如果您共享代码，则有助于提高可读性。

在此示例中，我们将以仅可通过子查询完成的方式使用CTE，并从上方开始使用INNER JOIN示例。

在这里，我们在主查询上方指定子查询。我们将其命名为“ top”并指定列名称，然后编写一个子查询。现在，我们可以在整个查询中调用“ top”来使用它。

如果我们将其称为多种类型，则SQL只需生成一次表。

WITH top (regionid, total_totalvol) AS (  
SELECT b.regionid, ROUND(SUM(a.totalvol))  
  FROM avocados a  
  LEFT JOIN region b ON a.regionid=b.regionid  
  GROUP BY 1  
  ORDER BY 2 DESC  
  LIMIT 10)  
    
SELECT date, totalvol, avo_a, avo_b, b.region, top.total_totalvol  
FROM avocados a  
LEFT JOIN region b ON a.regionid=b.regionid  
INNER JOIN top ON a.regionid = top.regionid

窗口函数（Window functions）

从PostgreSQL的文档：

窗口函数跨一组与当前行相关的表行执行计算。这相当于可以使用聚合函数完成的计算类型。但是，与常规聚合函数不同，使用窗口函数不会导致行被分组为单个输出行，而是保持行的独立标识。在后台，窗口功能不仅可以访问查询结果的当前行，还可以访问更多内容。
基本上我们可以使用Window函数查看每行上方和下方的行。

有关更多信息和挑战，请查看以下精彩资源： https://www.windowfunctions.com/

运行总计（Running Total）

OVER ORDER BY 序列是窗口函数。OVER几乎就像GROUP BY ，它指定了我们如何构造总和。ORDER BY使我们可以创建运行总计。

SELECT   
   date  
 , avo_a  
 , SUM(avo_a) OVER (ORDER BY date) AS running_total  
FROM avocados  
WHERE regionid =1 AND type =1

按日期总计（Total by date）

在这里，我们使用 PARTITION BY 将 SUM 按给定列分组（在这种情况下为Date）

SELECT   
   date  
 , avo_a  
 , type  
 , SUM(avo_a) OVER (PARTITION BY date) AS date_total  
FROM avocados  
WHERE regionid =1

移动平均（Moving Average）

这是事情开始变得有趣的地方。通过使用 AVG 和 ORDER BY date，我们可以指定 ROWS 作为移动平均值。3 PRECEDING
是3天移动平均线，但我们可以指定任何数字。

SELECT   
   date  
 , avo_a  
 , AVG(avo_a) OVER (ORDER BY date ASC  ROWS 3 PRECEDING) AS date_total  
FROM avocados  
WHERE regionid =1 AND type=1

获取第一行

假设每个地区的结束日期都不同。我们可以结合使用窗口函数和子查询来获取开始日期或最后日期。

首先，让我们定义我们的子查询。以下查询添加了一个行号，该行号按区域ID分组，并按从最新到最早的顺序排列。我们可以从每个分组中选择第一行以获取最新日期。

SELECT   
   date  
   , regionid  
   , ROW_NUMBER() OVER (PARTITION BY regionid ORDER BY date desc) AS row_number  
FROM avocados  
ORDER BY regionid

现在，我们对原始文档进行子查询，并仅获取第一行。

SELECT date, regionid  
FROM  
  (SELECT   
     date  
     , regionid  
     , ROW_NUMBER() OVER (PARTITION BY regionid   
     ORDER BY date desc) AS row_number  
  FROM avocados  
  ORDER BY regionid)  
WHERE row_number = 1

结论

精通这些技能将受益匪浅，其中许多功能有助于优化查询或汇总数据。当使用大型数据集时，这很重要，因为在分析管道的SQL层中汇总会加快处理速度。

来源：Medium
作者：Adam Shafi
翻译校对：数据黑客
原文标题：Advanced SQL for Data Scientists

数据黑客：专注金融大数据，聚合全网最好的资讯和教程，提供开源数据接口。

我们聚合全网最优秀的资讯和教程：

1. 金融大数据
2. 机器学习/深度学习
3. 量化交易
4. 数据工程
5. 编程语言，Python，R，Julia，Scala，SQL

我们提供开源数据接口：

1. 下载国内和国外海量金融数据
2. API接口，将数据整合到您的平台