数据科学中10个常用的高级SQL查询方法

介绍10个适合数据科学家和数据分析师的高级SQL查询。

长按关注《Python学研大本营》，加入读者群，分享更多精彩

作为一名数据分析师或数据科学家，熟练掌握SQL对于操作数据库和提取数据库中的见解至关重要。虽然基本的SQL查询是必要的，但掌握高级SQL查询可以提升你的数据分析技能。在这里，我们将探讨每个数据分析师和数据科学家都应该熟悉的10个高级SQL查询，接下来开始深入了解吧！

1. 子查询

子查询允许将一个查询嵌套在另一个查询中。它们对于根据另一个查询的结果执行复杂计算或过滤数据非常有用。例如：

-- 显示所有订单的订单编号和平均订单金额。
SELECT customer_id, (SELECT AVG(order_amount) FROM orders) AS average_order_amount
FROM customers;

-- 查找交易金额大于1000的客户。
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM transactions WHERE amount > 1000);

-- 显示销售数量大于50的店铺编号和其销售数量。
SELECT stor_id, qty
FROM (SELECT stor_id, qty FROM sales WHERE qty > 50) AS temp_table;

2. UNION

UNION运算符从两个表中选择唯一（不同）的行。在两个表中，列的数量、列的顺序和列的数据类型都应相同。

-- 显示employees和departments表中UNIQUE的部门编号。
SELECT department_id
FROM employees
UNION
SELECT department_id
FROM departments;


-- 使用INNER JOIN显示employees和departments表中MATCHING的部门编号。
SELECT department_id
FROM employees
INNER JOIN departments
USING (department_id);

3. 连接（Join）

连接（Join）用于根据相关列将多个表的行合并在一起。通过不同类型的连接（内连接、左连接、右连接、全外连接），可以检索特定的数据关系。例如：

-- 选择员工的ID、全名和部门名称。显示employees和departments表中的共同记录。
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

-- 或者，为简洁起见使用USING写法：
SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments
USING (department_id);

-- 选择员工的ID、全名和部门名称。显示employees表中的所有记录和departments表中匹配的记录。
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

-- 或者，为简洁起见使用USING写法：
SELECT employee_id, first_name, last_name, department_name
FROM employees
LEFT JOIN departments
USING (department_id);

4. 窗口函数（Window Function）

窗口函数在与当前行相关的一组行上执行计算。它们对于计算累计总数、排名和其他汇总值非常有用。例如：

SELECT CustomerName, OrderDate, TotalAmount,
       SUM(TotalAmount) OVER (PARTITION BY CustomerName ORDER BY OrderDate) AS RunningTotal
FROM Orders;

5. 临时表（Temporary Table）

临时表是用于在会话中创建和使用存储中间结果的表。当你需要对数据子集执行多个操作时，它们非常有用。例如：

-- 创建一个名为'titles_publishers'的临时表。
CREATE TEMPORARY TABLE titles_publishers
SELECT title, country
FROM titles
LEFT JOIN publishers
USING (pub_id);

-- 从临时表'titles_publishers'中检索所有数据。
SELECT *
FROM titles_publishers;

6. 条件聚合（Conditional Aggregation）

条件聚合允许你根据特定条件有选择地应用聚合函数。当你想根据特定条件计算不同的聚合时，它非常有用。例如：

SELECT Category, COUNT(*) AS TotalProducts,
       SUM(CASE WHEN Price > 100 THEN 1 ELSE 0 END) AS ExpensiveProducts
FROM Products
GROUP BY Category;

7. 透视查询

数据透视查询将行转换为列，使你能够旋转数据以得到更好的分析和报告。当你想要在不同维度之间比较数据时，它们尤其有用。例如：

SELECT *
FROM (
    SELECT CustomerID, ProductID, Quantity
    FROM Orders
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR ProductID IN ([1], [2], [3])
) AS PivotTable;

8. 字符串操作（String Manipulation）

SQL提供了各种字符串函数，用于操作和转换文本数据。像CONCAT、SUBSTRING、REPLACE和UPPER/LOWER这样的函数可以用于对字符串执行操作。例如：

-- 将所有员工的全名选择为"employee_fullname"。
SELECT CONCAT(fname, " ", lname) AS employee_fullname
FROM employee;

-- 在地址字段中将St.更改为Street。
SELECT address, REPLACE(address, "St.", "Street") AS updated_address
FROM authors;

-- 选择所有书名的前10个字符。
SELECT SUBSTRING(title, 1, 10) AS short_title
FROM titles;

9. 通用表表达式（CTE）

通用表表达式（CTE）允许创建命名的临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。它们对于将复杂查询拆分为更易管理的部分非常有用。例如：

-- 创建一个CTE来计算每个店铺的总销售额。
WITH StoreSales AS (
    SELECT stor_id, SUM(qty) AS total_sales
    FROM sales
    GROUP BY stor_id
)

-- 使用CTE来检索店铺信息和总销售额。
SELECT s.stor_name, ss.total_sales
FROM stores s
JOIN StoreSales ss
ON s.stor_id = ss.stor_id;

10. Case语句

通过Case语句，可以在SQL查询中执行条件逻辑。它们对于根据指定条件自定义查询结果非常有用。例如：

-- 根据客户的总购买额将其分类为Gold、Silver或Bronze。-- 根据客户的总购买量将其分为、银卡或铜卡。

-- 根据客户的总购买额将其分类为Gold（金卡）、Silver（银卡）或Bronze（铜卡）。
SELECT customer_name,
       CASE
           WHEN total_purchases >= 10000 THEN 'Gold'
           WHEN total_purchases >= 5000 THEN 'Silver'
           ELSE 'Bronze'
       END AS customer_category
FROM customers;

结论

掌握高级SQL查询对于数据分析师和数据科学家处理复杂数据场景和提取有价值的见解至关重要。在上述内容中，我们介绍了10个必备的高级SQL查询，包括子查询、连接、窗口函数、CTE等。通过将这些技术纳入你的SQL技能库，你将能够很好地应对具有挑战性的数据分析任务，并做出数据驱动的决策。