若已经掌握了SQL的基础知识并想要进一步提升技能,探索更多高级功能和技巧是非常有用的。高级查询技巧可以帮助更灵活、更高效地从数据库中获取所需的数据。高级查询技巧可以帮助处理更复杂的数据需求,提高查询的灵活性和效率。

1、子查询

子查询是一个嵌套在其他查询中的查询,用于提供额外的数据过滤、计算或子集。子查询可以出现在SELECT, FROM, WHEREHAVING子句中。子查询是一个非常强大的工具,它可以让在单个查询中执行多级数据计算。正确使用子查询能大大增强SQL的功能,使复杂数据关系的查询变得可能。

1)在WHERE子句中使用子查询

有一个名为employees的表格,其中包含员工的信息,包括他们的部门和工资。要找出工资高于其部门平均工资的员工,如下,

SELECT 
    employee_id,
    name,
    salary
FROM 
    employees
WHERE 
    salary > (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id);

2) 在SELECT子句中使用子查询

需要在查询结果中显示每个员工的工资与其部门平均工资的差异,如下,

SELECT 
    employee_id,
    name,
    salary,
    (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id) AS department_avg,
    salary - (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id) AS diff_from_avg
FROM 
    employees;

3)在FROM子句中使用子查询

可以使用子查询创建一个临时表格,然后从这个表格中进行选择。如找出每个部门工资最高的员工,如下,

SELECT 
    e.*
FROM 
    employees e
INNER JOIN 
    (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) AS max_salaries
ON 
    e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

4)在HAVING子句中使用子查询

要找出平均工资高于公司整体平均工资的部门,如下,

SELECT 
    department_id,
    AVG(salary) AS average_salary
FROM 
    employees
GROUP BY 
    department_id
HAVING 
    AVG(salary) > (SELECT AVG(salary) FROM employees);

2、公用表表达式 (CTE)

公用表表达式(Common Table Expressions,简称CTE)是SQL查询中的一个强大工具,可以将查询结果暂时命名并在后续的查询中重复使用。CTE的使用可以使查询变得更加模块化、易于理解和维护。CTE尤其有用于复杂查询的构建,可以将复杂的逻辑分解成简单、模块化的部分。

1)基础CTE用法

有一个员工表employees,其中包含员工的ID、姓名和部门ID。要找出每个部门的员工数量,如下,

WITH DepartmentCounts AS (
    SELECT department_id, COUNT(*) AS num_employees
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM DepartmentCounts;

2)多层CTE

公用表表达式可以嵌套使用,使得处理多步骤的数据分析变得更简单。比如,要从员工表中找到每个部门平均工资最高的三个部门,如下,

WITH DepartmentSalary AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
),
TopDepartments AS (
    SELECT department_id
    FROM DepartmentSalary
    ORDER BY average_salary DESC
    LIMIT 3
)
SELECT e.department_id, e.name, e.salary
FROM employees e
JOIN TopDepartments td ON e.department_id = td.department_id;

3)递归CTE

递归CTE可以用来处理层级或递归关系,如组织结构或树形结构。假设我们有一个组织结构表organization,其中包含员工ID和上级ID,要为每个员工创建一个层级列表,如下 ,

WITH RECURSIVE OrgChart AS (
    SELECT employee_id, supervisor_id, 1 AS depth
    FROM organization
    WHERE supervisor_id IS NULL
    UNION ALL
    SELECT o.employee_id, o.supervisor_id, oc.depth + 1
    FROM organization o
    INNER JOIN OrgChart oc ON o.supervisor_id = oc.employee_id
)
SELECT *
FROM OrgChart;

3、 窗口函数

窗口函数(Window Functions)是SQL的强大功能之一,可以在数据集的"窗口"上执行计算,这些窗口根据指定的条件对行进行分组。窗口函数对于执行如排名、累计、移动平均等操作非常有用,而且不需要将数据实际分组到单独的输出行中,因此可以保留详细的行级数据。窗口函数是一种特殊的函数,可以对查询结果集中的一组行进行计算,并返回一个与每行关联的值。常见的窗口函数包括排名函数(ROW_NUMBER(), RANK(), DENSE_RANK())、聚合函数(SUM(), AVG(), COUNT())以及累积函数(SUM()OVER())等。

1)排名函数

ROW_NUMBER():为每一行分配一个唯一的连续整数。

RANK():为相同值的行分配相同的排名,但会在下一个值上留出空白(例如 1, 2, 2, 4)。

DENSE_RANK():与RANK()类似,但排名之间没有间隔(例如 1, 2, 2, 3)。

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

2)聚合窗口函数

SUM()AVG()MAX()MIN():在窗口中计算总和、平均值、最大值和最小值。计算每个员工在每个部门的累计销售总额,如下,

SELECT 
  employee_id,
  department_id,
  sales,
  SUM(sales) OVER (PARTITION BY department_id ORDER BY sales_date) AS cumulative_sales
FROM sales_records;

3) 移动平均和累积

LEAD()、LAG():获取窗口中当前行的前一行或后一行的数据。

移动平均:使用窗口函数计算移动平均值。

计算每个产品的当前月销售与前两月销售的平均值,如下,

SELECT 
  product_id,
  sales_month,
  sales,
  AVG(sales) OVER (PARTITION BY product_id ORDER BY sales_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM monthly_sales;

4)首值与尾值

窗口函数通过指定OVER()子句来定义窗口的分组方式和排序规则。PARTITION BY子句用于在不同的分区中独立计算每个窗口,而ORDER BY子句则用于数据排序。FIRST_VALUE()LAST_VALUE()是在窗口内获取一组值的第一个或最后一个。显示每个部门中薪资最高的员工的薪资。如下,

SELECT 
  employee_id,
  department_id,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;

推荐文档