布客说
主页 归档 分类 专栏-加密 专栏-Java编程指南 关于
布客说

目录

统计查询中常用的 SQL 技巧

 收录于 IT-理论研究
   约 3885 字   预计阅读 8 分钟 

在 SQL 查询中有许多查询技巧，这些技巧可以帮助我们更有效地处理数据，本文主要总结一些常用的统计。

将持续更新…

1. SQL 中常见的查询技巧

  1. 使用窗口函数进行分析查询：窗口函数可以在一组相关的行（称为"窗口"）上执行计算，这些行与当前行有某种关系。窗口函数可以用于执行各种复杂的计算，如排名、累计和移动平均等。

  2. 使用联接（Join）操作：联接操作可以从多个表中获取数据。有多种类型的联接，包括内联接、左联接、右联接和全联接，每种联接都有其特定的使用场景。

  3. 使用子查询：子查询是嵌入在其他查询中的查询。子查询可以在 WHERE、FROM 或 SELECT 子句中使用，它们可以解决一些复杂的查询问题。

  4. 使用分组和聚合函数：分组和聚合函数可以对数据进行汇总。常见的聚合函数有 COUNT、SUM、AVG、MIN 和 MAX 等。

  5. 使用 CASE 表达式：CASE 表达式可以在查询中添加逻辑。它可以用于实现多种功能，如条件格式化、数据转换和复杂的业务逻辑。

  6. 使用索引优化查询：理解如何使用索引可以显著提高查询效率。索引可以帮助数据库快速定位到所需的数据，从而避免全表扫描。

  7. 使用 CTE (Common Table Expressions)：CTE 提供了一种将复杂的查询分解为更简单、更易于理解的部分的方法。我们可以在 CTE 中定义一个临时的、只在当前查询中可见的视图，然后在查询的其他部分中引用这个视图。

2. 窗口函数

窗口函数（Window Function）是一种特殊类型的函数，它会在一组相关的行（称为窗口）上执行计算，这些行与当前行有某种关系。窗口函数非常适合用于执行各种复杂的计算，如排名、累计和移动平均等。

2.1 窗口函数案例

  1. 排名：可以使用窗口函数来为数据集中的每一行分配一个排名。例如，以下的查询会为每个部门的员工按照薪水进行排名：

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

    在这个查询中，RANK() 是一个窗口函数，它会为每个部门的员工按照薪水进行排名。PARTITION BY department_id 指定了窗口的划分方式，即每个部门是一个单独的窗口。ORDER BY salary DESC 指定了窗口内的排序方式，即按照薪水的降序排列。

  2. 累计：可以使用窗口函数来计算累计值。例如，以下的查询会计算每个部门的累计薪水：

    SELECT department_id, employee_id, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) as running_total
FROM employees;

    在这个查询中，SUM(salary) 是一个窗口函数，它会计算每个部门的累计薪水。窗口的划分方式和排序方式与上一个例子相同。

  3. 移动平均：可以使用窗口函数来计算移动平均。例如，以下的查询会计算每个员工的薪水的 3 个月移动平均：

    SELECT employee_id, salary_date, salary,
       AVG(salary) OVER (ORDER BY salary_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average
FROM salaries;

    在这个查询中，AVG(salary) 是一个窗口函数，它会计算每个员工的薪水的 3 个月移动平均。ORDER BY salary_date 指定了窗口内的排序方式，即按照日期的顺序排列。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 指定了窗口的范围，即当前行及其前面的 2 行。

2.2 MySQL 支持哪些窗口函数

MySQL 支持多种窗口函数，这些函数可用于在数据集的特定"窗口"内执行各种计算。以下是一些常见的 MySQL 窗口函数：

  1. 排名函数：

    • RANK(): 计算窗口内每行的排名，如果两行的值相同，则它们的排名也相同，下一个值的排名将跳过。
    • DENSE_RANK(): 计算窗口内每行的排名，如果两行的值相同，则它们的排名也相同，下一个值的排名不会跳过。
    • ROW_NUMBER(): 计算窗口内每行的排名，如果两行的值相同，它们的排名也会不同。

  2. 聚合函数：在窗口内执行常见的聚合操作，如SUM(), AVG(), MIN(), MAX(), COUNT()等。

  3. 导航函数：

    • FIRST_VALUE(): 返回窗口中的第一行。
    • LAST_VALUE(): 返回窗口中的最后一行。
    • LEAD(): 返回窗口中当前行之后的第 N 行。
    • LAG(): 返回窗口中当前行之前的第 N 行。

  4. 其他函数：

    • NTILE(): 将窗口内的行分配到指定数量的组中，每组包含的行数相同（或尽可能相同）。
    • CUME_DIST(): 计算窗口中当前行的累积分布。
    • PERCENT_RANK(): 计算窗口内当前行的百分比排名。

使用窗口函数时，需要使用OVER子句来定义窗口，窗口可以通过PARTITION BY和ORDER BY来划分和排序，还可以通过ROWS或RANGE来限定范围。

2.3 如何定义窗口

在 SQL 中，窗口函数的应用需要定义一个"窗口"，这个窗口可以被视为一组相关的行，窗口函数会在这组行上进行计算。定义窗口的方法通常使用OVER子句，以下是一些常用的定义窗口的方法：

  1. PARTITION BY：这个子句用于将数据分割成多个窗口。例如，可以使用PARTITION BY department_id来为每个部门创建一个窗口，然后在每个部门内部进行计算。

    SELECT department_id, SUM(salary) OVER (PARTITION BY department_id)
FROM employees;

    在这个例子中，SUM(salary) OVER (PARTITION BY department_id)会计算每个部门的总薪水。

  2. ORDER BY：这个子句用于定义窗口内的排序。例如，可以使用ORDER BY salary来按照薪水的顺序排序窗口内的行。

    SELECT employee_id, salary, RANK() OVER (ORDER BY salary)
FROM employees;

    在这个例子中，RANK() OVER (ORDER BY salary)会按照薪水的顺序为每个员工分配一个排名。

  3. ROWS/RANGE：这些子句用于定义窗口的范围。例如，可以使用ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING来创建一个包含当前行及其前后各一行的窗口。

    SELECT employee_id, salary, AVG(salary) OVER (ORDER BY salary_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM salaries;

    在这个例子中，AVG(salary) OVER (ORDER BY salary_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)会计算每个员工薪水的移动平均值，这个平均值包括当前月及其前后各一个月的薪水。

3. 联接（Join）操作

3.1 联接（Join）操作案例

  1. 获取员工及其部门信息：假设有两个表，一个是"employees"，包含员工的信息，如 employee_id、name 和 department_id，另一个是"departments"，包含部门的信息，如 department_id 和 department_name。可以使用内连接（INNER JOIN）来获取每个员工及其部门的信息：

    SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

  2. 获取所有的部门以及各部门的员工信息：在上述的例子中，如果有些部门没有员工，那么这些部门不会出现在结果中。为了获取所有的部门以及各部门的员工信息（即使某些部门没有员工），可以使用左外连接（LEFT JOIN）：

    SELECT departments.department_name, employees.name
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id;

  3. 获取所有的员工以及他们的经理信息：假设在"employees"表中，还有一个 manager_id 列，表示每个员工的经理。可以使用自联接（自己与自己联接）来获取所有的员工以及他们的经理信息：

    SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

“ON” 子句用于指定联接条件，即哪些列应该在两个表之间进行匹配。

4.1 联接（Join）操作有哪些类型

在 SQL 中，JOIN 操作主要有四种类型，包括内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL JOIN）。

  1. 内联接（INNER JOIN）：只返回两个表中匹配的行。如果在一个表中存在行，但在另一个表中没有匹配的行，则这些行不会出现在结果集中。示例：

    SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

  2. 左外联接（LEFT JOIN）：返回左表中的所有行，即使在右表中没有匹配的行。如果在左表中存在行，但在右表中没有匹配的行，结果集中的右表列将包含 NULL。示例：

    SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

  3. 右外联接（RIGHT JOIN）：返回右表中的所有行，即使在左表中没有匹配的行。如果在右表中存在行，但在左表中没有匹配的行，结果集中的左表列将包含 NULL。示例：

    SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

  4. 全外联接（FULL JOIN）：返回左表和右表中的所有行。如果在一个表中存在行，但在另一个表中没有匹配的行，结果集中的那个表的列将包含 NULL。注意 MySQL 免费版不支持该操作，示例：

    SELECT orders.order_id, customers.customer_name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

3.3 联接（Join）操作其他问题

  1. 支持 FULL JOIN 的数据库：

    • PostgreSQL
    • SQL Server
    • Oracle
    • IBM DB2

  2. 不支持 FULL JOIN 的数据库：

    MySQL：尽管 MySQL 不直接支持 FULL JOIN，但可以通过结合使用 LEFT JOIN 和 UNION 来模拟 FULL JOIN 的功能。例如：

    SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id

    SQLite：与 MySQL 类似，SQLite 不直接支持 FULL JOIN，但可以通过结合使用 LEFT JOIN 和 UNION 来模拟 FULL JOIN 的功能。

  3. 交叉连接（CROSS JOIN）

交叉连接（CROSS JOIN）也是 SQL 的一种基本连接类型。它返回两个表的笛卡尔积，也就是说，它会返回左表中的每一行与右表中的每一行的所有可能组合。如果左表有 M 行，右表有 N 行，那么结果集将有 M*N 行。

下面是一个交叉连接的例子：

SELECT employee.name, department.name
FROM employee
CROSS JOIN department;

这个查询将返回员工表中的每一个员工与部门表中的每一个部门的所有可能组合。

请注意，如果省略掉连接类型（INNER, LEFT, RIGHT, FULL, CROSS），并且没有提供连接条件（ON 或 USING），那么 SQL 将默认使用交叉连接。例如：

SELECT employee.name, department.name
FROM employee, department;

这个查询和上面的交叉连接查询是等效的。

  1. 连接条件（ON 或 USING）

在 SQL 中，连接条件是用于指定如何将两个表的行匹配起来的。这些条件通常出现在 JOIN 语句的后面，并使用 ON 或 USING 关键字来指定。示例：

ON: ON 关键字允许指定任意的连接条件。可以使用等于（=），不等于（<>），小于（<），大于（>），小于等于（<=），大于等于（>=）等比较运算符来比较两个表的列。例如：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

在这个例子中，ON 关键字后面的条件是 “orders.customer_id = customers.customer_id”。这意味着只有当 orders 表的 customer_id 列的值等于 customers 表的 customer_id 列的值时，两个表的行才会被匹配起来。

USING: USING 关键字是一种更简洁的方式来指定连接条件，但它只能用于两个表有相同名称的列，并且这些列具有相同的值。例如：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
USING (customer_id);

在这个例子中，USING 关键字后面的条件是 “(customer_id)"。这意味着只有当 orders 表的 customer_id 列的值等于 customers 表的 customer_id 列的值时，两个表的行才会被匹配起来。

请注意，Microsoft SQL Server 不支持 USING 关键字，只支持 ON 关键字。

更新于 2023-10-04
 SQL, 统计查询
返回 | 主页