第6章 函数、谓词、CASE表达式

在SQL的广阔世界中，函数、谓词和CASE表达式是构建复杂查询、进行数据转换与条件逻辑处理不可或缺的工具。它们能够极大地增强SQL语句的灵活性和表达能力，使得数据分析和处理变得更加高效和直观。本章将深入探讨这三种强大的SQL特性，并通过实例展示其在实际应用中的价值。

6.1 函数基础

函数是SQL中预定义的或用户自定义的、用于执行特定任务（如计算、数据转换、格式化等）的代码块。根据功能和作用范围的不同，SQL函数大致可以分为以下几类：

• 聚合函数：用于对一组值执行计算并返回单个值，如SUM()、AVG()、COUNT()、MAX()、MIN()等。
• 标量函数：对单个值或表达式进行操作，并返回单个值，如字符串处理函数LENGTH()、UPPER()、SUBSTRING()，日期时间函数YEAR()、MONTH()，以及数学函数ROUND()、FLOOR()等。
• 窗口函数（或称为分析函数）：在结果集的每一行上执行计算，但能够访问多行数据，如ROW_NUMBER()、RANK()、DENSE_RANK()以及SUM()、AVG()等聚合函数在OVER子句中的使用。

示例：使用聚合函数

假设有一个销售数据表sales，包含字段sale_date（销售日期）、product_id（产品ID）和amount（销售额）。要计算某个产品的总销售额，可以使用如下SQL语句：

SELECT product_id, SUM(amount) AS total_sales
FROM sales
WHERE product_id = 'P001'
GROUP BY product_id;

示例：使用标量函数

要将sales表中的销售日期格式化为“年-月”形式，可以使用YEAR()和MONTH()函数结合字符串拼接：

SELECT CONCAT(YEAR(sale_date), '-', LPAD(MONTH(sale_date), 2, '0')) AS formatted_date, SUM(amount) AS total_sales
FROM sales
GROUP BY formatted_date;

6.2 谓词详解

谓词是SQL语句中用于条件判断的部分，它决定了哪些行会被包括在查询结果中。谓词通常与WHERE子句一起使用，但也可以在其他需要条件判断的地方（如HAVING子句）出现。SQL中的常见谓词包括比较谓词（如=、<>、<、>、<=、>=）、逻辑谓词（如AND、OR、NOT）、范围谓词（如BETWEEN...AND）、空值谓词（如IS NULL、IS NOT NULL）、存在性谓词（如EXISTS）等。

示例：使用比较谓词和逻辑谓词

查询sales表中销售额大于1000且销售日期在2023年1月1日至2023年3月31日之间的记录：

SELECT *
FROM sales
WHERE amount > 1000 AND sale_date BETWEEN '2023-01-01' AND '2023-03-31';

示例：使用存在性谓词

查询至少有一次销售额超过5000的产品的ID：

SELECT DISTINCT product_id
FROM sales s1
WHERE EXISTS (
    SELECT 1
    FROM sales s2
    WHERE s2.product_id = s1.product_id AND s2.amount > 5000
);

6.3 CASE表达式

CASE表达式是SQL中用于实现条件逻辑的强大工具，它可以在查询的SELECT列表、WHERE子句、ORDER BY子句以及更新或删除语句中使用。CASE表达式通过一系列的条件判断来返回不同的结果，其基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

示例：在SELECT列表中使用CASE表达式

假设sales表还包含一个region（地区）字段，我们想要根据销售额给每个销售记录打上等级标签（’Low’, ‘Medium’, ‘High’）：

SELECT
    sale_date,
    product_id,
    amount,
    CASE
        WHEN amount <= 1000 THEN 'Low'
        WHEN amount BETWEEN 1001 AND 5000 THEN 'Medium'
        ELSE 'High'
    END AS sales_level
FROM sales;

示例：在ORDER BY子句中使用CASE表达式

如果希望根据销售额的不同范围对产品进行排序，可以先使用CASE表达式为每个销售额范围分配一个排序值：

SELECT
    product_id,
    SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
ORDER BY CASE
    WHEN SUM(amount) <= 1000 THEN 1
    WHEN SUM(amount) BETWEEN 1001 AND 5000 THEN 2
    ELSE 3
END;

总结

函数、谓词和CASE表达式是SQL编程中不可或缺的组成部分，它们不仅简化了复杂的数据处理任务，还使得SQL查询更加灵活和强大。通过合理使用这些工具，开发人员可以编写出既高效又易于维护的SQL代码，满足各种复杂的数据分析和处理需求。在实际应用中，掌握这些基础知识并灵活运用，将对提升数据处理效率和准确性起到至关重要的作用。