关于10个高级的 SQL 查询技巧

编写高效的 SQL 查询是确保数据库操作性能优化的关键。以下是十个高级 SQL 查询技巧及其详细说明和示例：

1. 公共表表达式（CTEs）

公共表表达式（Common Table Expressions, CTEs） 是一种 SQL 结构，提供了一种更简洁和更具可读性的方式来编写复杂的 SQL 查询。

示例：

WITH SalesCTE AS (
    SELECT employee_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM SalesCTE
WHERE total_sales > 10000;

2. 递归 CTEs

递归 CTEs 允许执行分层查询，适用于树形或层次结构的数据，比如组织结构图、物料清单等。

示例：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, employee_name, level 
FROM EmployeeHierarchy
ORDER BY level, employee_id;

3. 临时函数（Temporary Functions）

在支持的数据库中（如 PostgreSQL），可以定义临时函数（存储过程或函数）来 encapsulate 复杂的逻辑，增强代码重用性。

示例（PostgreSQL）：

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;

SELECT calculate_discount(100, 0.1);

4. 使用 CASE WHEN 枢转数据

CASE WHEN 语句可以用于数据枢轴（pivoting），即将行数据转为列数据。

示例：

SELECT
    employee_id,
    SUM(CASE WHEN month = 'January' THEN sales ELSE 0 END) AS January_Sales,
    SUM(CASE WHEN month = 'February' THEN sales ELSE 0 END) AS February_Sales,
    SUM(CASE WHEN month = 'March' THEN sales ELSE 0 END) AS March_Sales
FROM sales
GROUP BY employee_id;

5. EXCEPT vs NOT IN

EXCEPT 可用于从一个结果集中排除另一个结果集中的行。NOT IN 可用于在逻辑运算时避免某些值。注意：在某些数据库中，EXCEPT 唯一化结果而 NOT IN 不会。

示例：

-- 使用 EXCEPT
SELECT product_id
FROM products2022
EXCEPT
SELECT product_id
FROM products2023;

-- 使用 NOT IN
SELECT product_id
FROM products2022
WHERE product_id NOT IN (SELECT product_id FROM products2023);

6. 自联结（Self Join）

自联结 是指一个表与自身进行联结。常用来处理相对数据（例如，父子关系、前后项比较等）。

示例：

SELECT e1.employee_id AS employee, e2.employee_id AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

7. Rank vs Dense Rank vs Row Number

RANK()、DENSE_RANK() 和 ROW_NUMBER() 是窗口函数，分别用于排名。RANK 会在相同排名出现空档，DENSE_RANK 则不会，而 ROW_NUMBER 简单计数而不考虑相同值。

示例：

SELECT employee_id, sales,
       RANK() OVER (ORDER BY sales DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_number
FROM employee_sales;

8. 计算 Delta 值

Delta：即计算值的变化差。通常用于查看某个数据字段的逐期变动。

示例：

SELECT date, sales,
       LAG(sales, 1) OVER (ORDER BY date) AS previous_sales,
       (sales - LAG(sales, 1) OVER (ORDER BY date)) AS delta
FROM daily_sales;

9. 计算运行总数（Running Total）

运行总数 是一种累计计数，用来展示一段时间内不断变化的总和。

示例：

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;

10. 日期时间操作（Date and Time Manipulation）

日期时间操作是数据库操作中必不可少的一部分。常见操作包括日期加减、日期格式转换等。

示例：

-- 计算日期差
SELECT order_id, DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;

-- 增加日期
SELECT order_id, order_date, DATE_ADD(order_date, INTERVAL 10 DAY) AS delivery_date
FROM orders;

-- 提取日期部分
SELECT order_id, order_date, EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

总结

以上十个 SQL 查询技巧包括了高级查询在日常工作中的常用范式和实现方法。通过掌握这些技巧，你可以编写更高效和更具可读性的查询，从而提高数据操作的效率和准确性。
谢谢阅读！