本文介绍了一些技巧,这些技巧一旦掌握,就可以为用户提供处理各种数据类型的工具。本文不涉及及与数据库管理有关的任何内容,例如表创建或架构。
如果您想继续,可以在此处使用SQLite设置本地SQL Server。
本文来自《数据黑客》,登录官网可阅读更多精彩资讯和文章。
内容导航:
- 探索示例数据
- JOIN充当过滤器
- Self Joins
- CASE WHEN
- 子查询
- 公用表表达式
- 窗口函数
探索示例数据
在筛选之前,让我们快速浏览一下表格。
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
数据黑客:专注金融大数据,聚合全网最好的资讯和教程,提供开源数据接口。
我们聚合全网最优秀的资讯和教程:
- 金融大数据
- 机器学习/深度学习
- 量化交易
- 数据工程
- 编程语言,Python,R,Julia,Scala,SQL
我们提供开源数据接口:
- 下载国内和国外海量金融数据
- API接口,将数据整合到您的平台