一、窗口函数（Window Functions）

1. 基本概念
   • 窗口函数是一种特殊的函数，它可以在不改变结果集行数的情况下，对每一行进行计算。与聚合函数不同，聚合函数会将多行数据聚合成一行，而窗口函数会为每一行计算一个值。
   • 例如，计算每个员工的工资在部门内的排名。假设我们有一个员工表employees，包含字段employee_id、department_id和salary。

SELECT employee_id,department_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

• 在这个例子中，RANK()函数就是一个窗口函数。PARTITION BY department_id表示按照部门进行分组，在每个部门内部进行计算。ORDER BY salary DESC是对每个分组内的数据按照工资降序排序，然后计算排名。

1. 常用窗口函数类型
   • 排名函数
     • 除了RANK()函数外，还有DENSE_RANK()和ROW_NUMBER()。RANK()函数在排名时，如果有相同的值，会占用相同的名次，下一个名次会跳过相应的数量。例如，有两个并列第一，那么下一个名次是第三。DENSE_RANK()函数在遇到相同值排名时，下一个名次不会跳过，例如，有两个并列第一，下一个名次是第二。ROW_NUMBER()函数则是按照顺序依次排名，不考虑是否有相同的值。
   • 聚合函数作为窗口函数
     • 可以将聚合函数（如SUM、AVG、MAX、MIN）作为窗口函数使用。例如，计算每个员工的工资与部门平均工资的差值。

SELECT employee_id,department_id,salary,salary - AVG(salary) OVER (PARTITION BY department_id) as salary_diff_from_avg
FROM employees;

• 这里AVG(salary) OVER (PARTITION BY department_id)计算了每个部门的平均工资，然后用每个员工的工资减去部门平均工资得到差值。

二、公共表表达式（CTE - Common Table Expressions）

1. 定义和语法
   • CTE 是一个临时结果集，它可以在一个 SQL 语句中定义，并且可以在后续的查询中引用。语法如下：

WITH cte_name (column1, column2,...) AS (-- 子查询，用于定义CTE的内容SELECT column1, column2,...FROM some_tableWHERE some_condition
)
SELECT *
FROM cte_name;

• 例如，我们要查找购买了价格最高的产品的客户。首先，我们可以使用 CTE 找到价格最高的产品价格。

WITH max_price_cte AS (SELECT MAX(price) as max_priceFROM products
)
SELECT customers.customer_name,orders.order_date
FROM customersJOIN orders ON customers.customer_id = orders.customer_idJOIN order_items ON orders.order_id = order_items.order_idJOIN products ON order_items.product_id = products.product_id,max_price_cte
WHERE products.price = max_price_cte.max_price;

1. 递归 CTE
   • 递归 CTE 是一种特殊的 CTE，它可以引用自身。常用于处理具有层次结构的数据，如组织结构树、文件系统目录结构等。
   • 例如，假设有一个员工表employees，包含employee_id、manager_id和employee_name字段，我们要查询某个经理及其所有下属的员工姓名。

WITH RECURSIVE subordinates_cte (employee_id, employee_name, manager_id) AS (-- 递归的初始查询，找到经理SELECT employee_id, employee_name, manager_idFROM employeesWHERE employee_id = 1 -- 假设经理的ID为1UNION ALL-- 递归部分，找到下属SELECT e.employee_id, e.employee_name, e.manager_idFROM employees eJOIN subordinates_cte s ON e.manager_id = s.employee_id
)
SELECT *
FROM subordinates_cte;

• 在这个例子中，首先通过初始查询找到了经理的记录。然后在递归部分，通过将员工表和 CTE 自身进行连接，找到经理下属的员工记录，不断重复这个过程，直到没有下属为止。

三、动态 SQL

1. 概念和应用场景
   • 动态 SQL 是指在 SQL 语句中可以动态地构建和执行 SQL 代码。这在需要根据不同的条件生成不同的查询语句时非常有用。例如，在一个报表系统中，用户可以选择不同的筛选条件（如日期范围、产品类别等），动态 SQL 可以根据用户的选择构建相应的查询。
2. 实现方式（以存储过程为例）
   • 在许多数据库管理系统中，可以使用存储过程来实现动态 SQL。下面是一个简单的示例，假设我们有一个表sales，包含sales_date、product_id和sales_amount字段，我们要根据用户输入的日期范围查询销售数据。

CREATE PROCEDURE get_sales_in_date_range (start_date DATE, end_date DATE)
LANGUAGE SQL
BEGINDECLARE dynamic_sql VARCHAR(1000);SET dynamic_sql = 'SELECT * FROM sales WHERE sales_date BETWEEN '' ' || start_date || ' '' AND '' ' || end_date || ' '';';PREPARE stmt FROM dynamic_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END;

• 在这个存储过程中，首先定义了一个变量dynamic_sql来存储动态构建的 SQL 语句。然后通过拼接字符串的方式将日期范围条件添加到查询语句中。接着使用PREPARE语句准备要执行的 SQL 语句，EXECUTE语句执行它，最后使用DEALLOCATE PREPARE释放资源。

四、索引优化技巧

1. 选择合适的索引类型
   • B - 树索引（B - Tree Index）：这是最常用的索引类型。它适用于大多数情况，特别是在查询条件涉及范围查询（如>、<、BETWEEN）、等值查询（如=）和排序操作（ORDER BY）时非常有效。例如，在一个包含客户订单信息的表orders中，对customer_id字段创建 B - 树索引，可以快速查询某个客户的所有订单。
   • 哈希索引（Hash Index）：哈希索引主要用于等值查询，它通过哈希函数将索引键转换为哈希值来快速定位数据。但是它不支持范围查询和排序操作。例如，在一个用于存储用户登录信息的表中，对username字段创建哈希索引，可以快速验证用户登录时输入的用户名是否存在。
2. 复合索引（Composite Index）的使用
   • 复合索引是包含多个列的索引。在创建复合索引时，要考虑列的顺序。一般来说，应该将最常用于查询条件的列放在最左边。例如，在一个employees表中，经常按照department_id和job_title查询员工信息，那么可以创建一个复合索引(department_id, job_title)。当查询条件是department_id = 1 AND job_title = 'Manager'时，这个复合索引可以有效地提高查询速度。但是如果查询条件只有job_title，这个复合索引可能不会被充分利用。
3. 避免过度索引
   • 索引虽然可以提高查询速度，但也会增加数据插入、更新和删除的成本。因为每次对数据进行这些操作时，数据库都需要更新相关的索引。所以要避免为很少使用的列或者已经有其他高效索引覆盖的列创建索引。例如，如果已经有一个(column1, column2)的复合索引，并且大多数查询可以通过这个复合索引满足，就不需要再为column1单独创建索引。