高级SQL技巧与窍门：数据分析师必备！
基础SQL非常简单，但是把SQL用得正确而高级并不是那么容易。
下面是一些更高级的SQL技巧，它们将帮助你执行更复杂的查询，或者只是更好地执行基础查询。如果你想了解更多关于求职的相关内容，可以阅读以下这些文章：
帮我得到第一份数据分析师工作的9个SQL核心概念
只会Pandas？来学习这25种Pandas变SQL的方法，让你的数据分析更得心应手！
使用SQL总结A/B实验结果
数据科学面试中你应该知道的10个高级SQL概念
公用表表达式（CTE）
CTE允许你通过创建可在单个查询中多次重用的中间结果，将复杂的查询分解为更小、更易于管理的部分。
你可以使用它们来替换嵌套查询，
不是——
SELECT *
FROM
(
SELECT *
FROM users
WHERE sex = 'M'
) men
INNER JOIN
(
SELECT *
FROM orders
WHERE total>100
) big_orders
ON men.id = big_orders.user_id而是——
WITH men AS (
SELECT *
FROM users
WHERE sex = 'M'
)
,
big_orders AS (
SELECT *
FROM orders
WHERE total>100
)
SELECT *
FROM men
INNER JOIN big_orders ON men.id
= big_orders.user_id这样做有两个目的：使查询运行得更快，使代码更易于阅读。
窗口函数
窗口函数允许你跨查询结果中的行，执行计算，不需要自联接或子查询。
SELECT customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders在上面的示例中，每个订单都有一行，并且对于每个订单，我们都有各自客户到目前为止所花费的金额。
数据透视表
数据透视表允许你将行转换为列，从而在某些情况下更容易分析数据。
假设你有以下表格：
+--------+----------+-------+
| region | year | sales |
+--------+----------+-------+
| North | 2010 | 100 |
| North | 2011 | 200 |
| South | 2010 | 150 |
| South | 2011 | 75 |
| East | 2010 | 50 |
+--------+----------+-------+你想将其转换为下表格：
+--------+----------+----------+
| region | 2010 | 2011 |
+--------+----------+----------+
| East | 50 | NULL |
| North | 100 | 200 |
| South | 150 | 75 |
+--------+----------+----------+你可以使用以下查询执行此操作：
WITH pivot_data AS (
SELECT region, year, sales
FROM sales_data
)
SELECT *
FROM pivot_data
PIVOT (SUM(sales) FOR year IN ([2010], [2011])) AS pivot_table它们的工作方式有点像Excel数据透视表，但是，正如你所看到的，如果列中有多个不同的值（本例中为“year”），则该过程可能需要一些人工操作。
递归查询
递归查询允许你查询层次结构或树状数据结构，如组织结构图或文件系统。
设想一个显示层次结构的员工表：
+-----------+------------+
| employee_id | manager_id |
+-----------+------------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
+-----------+------------+如果需要知道每个员工的层次级别，可以使用以下代码：
WITH RECURSIVE employee_hierarchy (employee_id, manager_id, level) AS (
SELECT employee_id, manager_id, 1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy这将返回以下结果：
+-----------+------------+-------+
| employee_id | manager_id | level |
+-----------+------------+-------+
| 1 | NULL | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 3 |
| 5 | 3 | 4 |
+-----------+------------+-------+你可以从中建立对应关系，例如1：CEO，2：总监，3：经理等。
动态SQL
动态SQL允许你在运行时根据用户输入或其他条件动态生成和执行SQL语句。
DECLARE @sql NVARCHAR(MAX);
DECLARE @table_name NVARCHAR(100) = ‘employees’;
DECLARE @column_name NVARCHAR(100) = ‘salary’;
SET @sql = 'SELECT AVG(' + @column_name + ') FROM ' + @table_name + ';'
EXEC sp_executesql @sql特别是如果你需要尝试查询的多个值，并且此值在查询中多次重复，这种方法会特别有用。此功能的可用性和语法将取决于你使用的SQL版本。
实例化视图
实例化视图允许你预先计算和存储查询结果，以便后续查询可以更快地访问结果。在处理大型或执行速度较慢的数据集时，这对于提高性能非常有用。
CREATE MATERIALIZED VIEW sales_summary
AS
SELECT product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product;
SELECT product, total_sales
FROM sales_summary实例化视图和“标准”视图之间的区别是什么呢？它们是在数据库中封装和重用复杂查询逻辑的两种不同方式。它们之间的关键区别在于，实例化视图是预先计算和存储的结果集，而标准视图只是存储的查询定义。
查询标准视图时，数据库管理系统（DBMS）将在你每次运行视图时执行基础查询，因此结果集将始终反映数据的当前状态。
另一方面，当你查询实例化视图时，DBMS将返回存储的结果集，而不是重新运行基础查询。因为结果集是预先计算和存储的，所以查询实例化视图比查询标准视图要快得多——尤其是在基础数据很大或查询速度很慢的情况下。
通常，如果需要频繁执行大型查询或执行速度较慢的查询，并且可以容忍一定程度的陈旧数据，则应使用实例化视图。另一方面，如果你需要最新的数据，并且不太关心查询性能，则可以使用标准视图。
Unnest + Generate_array
假设你希望以5%为步长生成10%到50%之间的所有可能折扣的列表。请看以下步骤：
SELECT ROUND(discount / 100, 2) AS discount
FROM UNNEST( GENERATE_ARRAY(10, 50, 5) ) AS discount这对于插入到与Products表的交叉联接中，为给定产品创建所有可能的折扣非常有用。
我不确定这个方法是否有名字，所以我只称它为“unnest+generate_array”（如果你碰巧知道它的正确名称，请在评论中告诉我）。此外，我现在Google BigQuery中使用它，但它在每个环境中的工作方式可能不同，因此你需要测试你使用的工具是否接受数组。
感谢阅读。你还可以订阅我们的YouTube频道，观看大量大数据行业相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/
原文作者：Arthur Mello
翻译作者：高佑兮
美工编辑：过儿
校对审稿：Chuang
原文链接：https://medium.com/geekculture/advanced-sql-tips-and-tricks-for-data-analysts-2f5079c8ba7b
