SQL如何增强你的子查询？

本文概述

• TEMP表
• 嵌套的FROM语句
• WITH语句
• 总结

熟悉SQL之后, 你就会意识到所有真正出色的分析都需要多个步骤。例如, 假设你要创建每个用户在你的网站上花费的时间的直方图。首先, 你需要计算每个用户花费的时间。然后, 你需要统计在网站上花费了一定时间的用户数量。创建这些子查询有三个选项：

TEMP表

你可以创建一个临时表, 该表对于当前的SQL会话存在, 只能由你自己访问, 并且在断开连接时会消失。这对于你需要多次引用的长计算非常有用。语法很简单：

SELECT <query goes here>
INTO TEMP <name of the temporary table>
FROM <rest of query goes here>

TEMP表的最大问题是它们需要你运行多个命令。如果你正在使用Looker或Tableau等仪表板软件, 则TEMP表可能无法正常工作。

嵌套的FROM语句

通常, 我们使用FROM语句指示数据将来自哪个表。但是, 它们也可以从嵌套在某些括号内的查询中进行选择。考虑这个例子, 我们在构建每个用户的平均购买金额的直方图。首先, 我们需要计算每个user_id的平均购买金额：

SELECT user_id, ROUND(AVG(purchase)) as avg_purchase
FROM user_purchase
GROUP BY 1;

我们可以接受此查询并将其放在FROM语句中：

SELECT avg_purchase, COUNT(user_id) as num_customers
FROM (
    SELECT user_id, ROUND(AVG(purchase)) as avg_purchase
    FROM user_purchase
    GROUP BY 1 ) as subquery1
GROUP BY 1;

突出显示的查询将首先运行, 并且结果将由外部查询聚合。通过给子查询起别名(在本例中为subquery1), 你可以做复杂的事情, 例如在联接中引用它。

嵌套FROM语句的最大问题是它们很难阅读。特别是, 如果你稍后要回头参考代码, 或者尝试与他人共享代码, 则很难看到你的想法。一旦嵌套了多个FROM, 就几乎不可能弄清楚计算的作用。

WITH语句

WITH语句与FROM语句类似, 但有一个主要区别：它们是顺序编写的, 因此更容易理解创建最终查询的步骤。考虑我们之前的查询, 该查询使用WITH进行了重写：

WITH subquery1 AS (
    SELECT user_id, ROUND(AVG(purchase)) as avg_purchase
    FROM user_purchase
    GROUP BY 1 )
SELECT avg_purchase, COUNT(user_id) as num_customers
FROM subquery1
GROUP BY 1;

在主查询之前, 我们可以具有任意数量的WITH语句。我们只用逗号分隔每个：

WITH subquery1 AS (
... ), subquery2 AS (
... ), subquery3 AS (
... )
SELECT <final query>
FROM subquery3;

许多数据分析人员和数据科学家更喜欢WITH语句而不是嵌套FROM语句, 因为第三方可以更轻松地查看查询并遵循原始编码器的逻辑。

总结

无论你选择使用TEMP表, 嵌套FROM语句还是顺序WITH语句, 多步查询都将为你提供对大数据集的更丰富, 更可行的见解。

如果你想了解有关SQL的更多信息, 请参加srcmini的PostgreSQL中的联接数据课程。