SQLite函数与聚合操作详解

SQLite 函数概述

SQLite 提供了丰富的函数，这些函数可以帮助我们在查询数据时进行各种数据处理和计算。SQLite 的函数分为不同类型，包括文本函数、数值函数、日期和时间函数以及聚合函数等。

文本函数

1. UPPER 和 LOWER 函数
   • UPPER 函数用于将文本字符串转换为大写形式，LOWER 函数则将文本字符串转换为小写形式。
   • 示例： 假设我们有一个 employees 表，其中有 name 列存储员工姓名。

-- 创建表
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- 插入数据
INSERT INTO employees (name) VALUES ('John Doe'), ('Jane Smith');
-- 使用 UPPER 函数
SELECT UPPER(name) FROM employees;
-- 使用 LOWER 函数
SELECT LOWER(name) FROM employees;

- 在上述代码中，`UPPER(name)` 会将 `name` 列中的值转换为大写形式输出，`LOWER(name)` 则转换为小写形式输出。

2. SUBSTR 函数 - SUBSTR 函数用于提取字符串的子串。它接受三个参数：源字符串、起始位置（从 1 开始计数）和要提取的字符数（可选，如果不提供则提取到字符串末尾）。 - 示例：

SELECT SUBSTR('Hello, World!', 7, 5);

- 上述查询将返回 `World`，因为从第 7 个字符开始，提取 5 个字符。

3. LENGTH 函数 - LENGTH 函数返回字符串的长度，即字符串中字符的个数。 - 示例：

SELECT LENGTH('Hello, World!');

- 此查询将返回 `13`，即字符串 `Hello, World!` 的字符个数。

数值函数

1. ABS 函数
   • ABS 函数返回一个数的绝对值。
   • 示例：

SELECT ABS(-5);

- 该查询将返回 `5`，即 `-5` 的绝对值。

2. ROUND 函数 - ROUND 函数用于对数字进行四舍五入。它可以接受一个或两个参数。如果只提供一个参数，它将把该数字四舍五入到最接近的整数；如果提供两个参数，第二个参数指定要保留的小数位数。 - 示例：

SELECT ROUND(3.14159);
SELECT ROUND(3.14159, 2);

- 第一个查询将返回 `3`，第二个查询将返回 `3.14`。

3. CEIL 和 FLOOR 函数 - CEIL 函数返回大于或等于给定数字的最小整数，FLOOR 函数返回小于或等于给定数字的最大整数。 - 示例：

SELECT CEIL(3.14);
SELECT FLOOR(3.14);

- `CEIL(3.14)` 将返回 `4`，`FLOOR(3.14)` 将返回 `3`。

日期和时间函数

1. DATE 函数
   • DATE 函数用于从日期和时间值中提取日期部分。它可以接受一个日期时间字符串作为参数，并按照指定的格式返回日期。
   • 示例：

SELECT DATE('2023 - 10 - 15 12:30:00');

- 上述查询将返回 `2023 - 10 - 15`。

2. TIME 函数 - TIME 函数用于从日期和时间值中提取时间部分。 - 示例：

SELECT TIME('2023 - 10 - 15 12:30:00');

- 此查询将返回 `12:30:00`。

3. STRFTIME 函数 - STRFTIME 函数用于根据指定的格式字符串格式化日期和时间值。它接受两个参数：格式字符串和日期时间值。 - 示例：

SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');

- 这里 `%Y` 表示四位数的年份，`%m` 表示两位数的月份，`%d` 表示两位数的日期，`%H` 表示 24 小时制的小时，`%M` 表示分钟，`%S` 表示秒。`now` 是 SQLite 中的一个特殊关键字，表示当前日期和时间。

SQLite 聚合操作

聚合操作是对一组数据进行计算，并返回单个结果值。SQLite 提供了多种聚合函数，用于执行诸如求和、求平均值、计数等操作。

COUNT 函数

1. 基本用法
   • COUNT 函数用于计算表中的行数或满足特定条件的行数。
   • 示例： 假设我们有一个 products 表，包含 id、name 和 price 列。

-- 创建表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);
-- 插入数据
INSERT INTO products (name, price) VALUES ('Product A', 10.0), ('Product B', 20.0), ('Product C', 30.0);
-- 计算表中的总行数
SELECT COUNT(*) FROM products;
-- 计算价格大于 15 的产品数量
SELECT COUNT(*) FROM products WHERE price > 15;

- `COUNT(*)` 会统计表中的所有行，而 `COUNT(*)` 结合 `WHERE` 子句可以统计满足特定条件的行。

2. COUNT 与特定列 - 我们也可以使用 COUNT 函数对特定列进行计数，此时它会忽略 NULL 值。 - 示例：

SELECT COUNT(price) FROM products;

- 此查询将统计 `price` 列中非 `NULL` 值的数量。

SUM 函数

1. 对数值列求和
   • SUM 函数用于计算数值列的总和。
   • 示例：

SELECT SUM(price) FROM products;

- 在 `products` 表中，该查询将计算所有产品价格的总和。

2. 结合条件使用 SUM - 可以结合 WHERE 子句来计算满足特定条件的数值列总和。 - 示例：

SELECT SUM(price) FROM products WHERE price > 15;

- 此查询将计算价格大于 15 的产品的价格总和。

AVG 函数

1. 计算平均值
   • AVG 函数用于计算数值列的平均值。
   • 示例：

SELECT AVG(price) FROM products;

- 该查询将返回 `products` 表中 `price` 列的平均值。

2. 处理 NULL 值 - 与 SUM 和 COUNT 类似，AVG 函数在计算时也会忽略 NULL 值。如果整列都是 NULL，AVG 函数将返回 NULL。

MIN 和 MAX 函数

1. MIN 函数
   • MIN 函数用于返回数值列或文本列中的最小值。对于文本列，它会按照字典序比较。
   • 示例：

SELECT MIN(price) FROM products;
SELECT MIN(name) FROM products;

- 第一个查询返回 `products` 表中价格的最小值，第二个查询返回按字典序排列的最小的产品名称。

2. MAX 函数 - MAX 函数与 MIN 函数相反，用于返回数值列或文本列中的最大值。 - 示例：

SELECT MAX(price) FROM products;
SELECT MAX(name) FROM products;

- 第一个查询返回价格的最大值，第二个查询返回按字典序排列的最大的产品名称。

分组与聚合

在 SQLite 中，我们常常需要结合分组操作与聚合函数来获取更有意义的统计数据。通过 GROUP BY 子句，可以将数据按照一个或多个列进行分组，然后对每个分组应用聚合函数。

GROUP BY 基本用法

1. 按单列分组
   • 假设我们有一个 orders 表，包含 order_id、customer_id、order_date 和 order_amount 列。我们想按 customer_id 分组，统计每个客户的订单总金额。
   • 示例：

-- 创建表
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    order_amount REAL
);
-- 插入数据
INSERT INTO orders (customer_id, order_date, order_amount) VALUES 
(1, '2023 - 10 - 10', 100.0), 
(1, '2023 - 10 - 11', 200.0), 
(2, '2023 - 10 - 10', 150.0);
-- 按 customer_id 分组并计算每个客户的订单总金额
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

- 在上述代码中，`GROUP BY customer_id` 将数据按 `customer_id` 分组，然后 `SUM(order_amount)` 计算每个分组中的订单总金额，并使用 `AS` 关键字给聚合结果取别名 `total_amount`。

2. 按多列分组 - 我们也可以按多个列进行分组。例如，在 orders 表中，我们想按 customer_id 和 order_date 分组，统计每天每个客户的订单总金额。 - 示例：

SELECT customer_id, order_date, SUM(order_amount) AS daily_total
FROM orders
GROUP BY customer_id, order_date;

- 这里按 `customer_id` 和 `order_date` 进行分组，然后计算每个分组的订单总金额。

HAVING 子句

1. 与 GROUP BY 结合使用
   • HAVING 子句用于对分组后的结果进行筛选，它与 WHERE 子句类似，但 WHERE 子句用于筛选行，而 HAVING 子句用于筛选分组。
   • 继续以 orders 表为例，我们想找出订单总金额大于 200 的客户。
   • 示例：

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 200;

- 在这个查询中，先按 `customer_id` 分组并计算每个客户的订单总金额，然后 `HAVING` 子句筛选出总金额大于 200 的分组。

2. HAVING 中的表达式 - HAVING 子句中可以使用聚合函数和其他表达式。例如，我们想找出平均订单金额大于 150 的客户。 - 示例：

SELECT customer_id, AVG(order_amount) AS avg_amount
FROM orders
GROUP BY customer_id
HAVING AVG(order_amount) > 150;

- 此查询先按 `customer_id` 分组并计算每个客户的平均订单金额，然后 `HAVING` 子句筛选出平均金额大于 150 的分组。

嵌套聚合

在 SQLite 中，我们可以进行嵌套聚合操作，即在一个聚合函数的结果上再应用另一个聚合函数。

简单嵌套聚合示例

1. 计算分组后的聚合值的总和
   • 假设我们有一个 sales 表，包含 department、product 和 sales_amount 列。我们想先按 department 分组计算每个部门的销售总额，然后计算所有部门销售总额的总和。
   • 示例：

-- 创建表
CREATE TABLE sales (
    department TEXT,
    product TEXT,
    sales_amount REAL
);
-- 插入数据
INSERT INTO sales (department, product, sales_amount) VALUES 
('Electronics', 'TV', 1000.0), 
('Electronics', 'Laptop', 1500.0), 
('Clothing', 'T - Shirt', 50.0), 
('Clothing', 'Jeans', 100.0);
-- 嵌套聚合查询
SELECT SUM(department_total) AS grand_total
FROM (
    SELECT SUM(sales_amount) AS department_total
    FROM sales
    GROUP BY department
);

- 内部查询先按 `department` 分组计算每个部门的销售总额，并将结果命名为 `department_total`。外部查询再对这些部门的销售总额进行求和，得到所有部门销售总额的总和 `grand_total`。

2. 计算分组后的聚合值的平均值 - 同样以 sales 表为例，我们想计算每个部门销售总额的平均值。 - 示例：

SELECT AVG(department_total) AS avg_department_total
FROM (
    SELECT SUM(sales_amount) AS department_total
    FROM sales
    GROUP BY department
);

- 内部查询按 `department` 分组计算每个部门的销售总额，外部查询计算这些部门销售总额的平均值。

自定义函数与聚合函数

除了 SQLite 内置的函数和聚合函数，我们还可以根据自己的需求定义自定义函数和聚合函数。

自定义函数

1. 使用 SQLite 扩展库创建自定义函数
   • 在 C 语言中，可以使用 SQLite 的扩展库来创建自定义函数。下面是一个简单的示例，创建一个将两个整数相加的自定义函数。
   • C 代码示例：

#include <sqlite3.h>
#include <stdio.h>

static void add(sqlite3_context *context, int argc, sqlite3_value **argv) {
    if (argc != 2) {
        sqlite3_result_error(context, "add() requires exactly 2 arguments", -1);
        return;
    }
    int a = sqlite3_value_int(argv[0]);
    int b = sqlite3_value_int(argv[1]);
    sqlite3_result_int(context, a + b);
}

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    sqlite3_create_function(db, "add", 2, SQLITE_UTF8, 0, add, 0, 0);
    sqlite3_stmt *stmt;
    const char *sql = "SELECT add(2, 3)";
    sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        int result = sqlite3_column_int(stmt, 0);
        printf("Result: %d\n", result);
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

- 在上述代码中，首先定义了 `add` 函数，它接受两个参数并返回它们的和。然后在 `main` 函数中，打开一个内存数据库，使用 `sqlite3_create_function` 注册自定义函数 `add`，最后执行查询 `SELECT add(2, 3)` 并输出结果。

2. 在 Python 中使用 SQLite3 模块创建自定义函数 - 在 Python 中，可以使用 sqlite3 模块创建自定义函数。以下是一个将字符串重复指定次数的自定义函数示例。 - Python 代码示例：

import sqlite3


def repeat_string(text, times):
    return text * times


conn = sqlite3.connect(":memory:")
conn.create_function("repeat", 2, repeat_string)
cursor = conn.cursor()
cursor.execute("SELECT repeat('Hello, ', 3)")
result = cursor.fetchone()[0]
print(result)
conn.close()

- 在这个 Python 代码中，定义了 `repeat_string` 函数，然后使用 `conn.create_function` 注册为 SQLite 的自定义函数 `repeat`，最后执行查询并输出结果。

自定义聚合函数

1. C 语言实现自定义聚合函数
   • 以下是一个在 C 语言中实现自定义聚合函数，用于计算一组数字的平方和。
   • C 代码示例：

#include <sqlite3.h>
#include <stdio.h>

typedef struct {
    double sum_of_squares;
} SumOfSquaresContext;

static void sos_step(sqlite3_context *context, int argc, sqlite3_value **argv) {
    if (argc != 1) {
        sqlite3_result_error(context, "sos() requires exactly 1 argument", -1);
        return;
    }
    double value = sqlite3_value_double(argv[0]);
    SumOfSquaresContext *ctx = (SumOfSquaresContext *) sqlite3_aggregate_context(context, sizeof(SumOfSquaresContext));
    ctx->sum_of_squares += value * value;
}

static void sos_finalize(sqlite3_context *context) {
    SumOfSquaresContext *ctx = (SumOfSquaresContext *) sqlite3_aggregate_context(context, 0);
    sqlite3_result_double(context, ctx->sum_of_squares);
}

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    sqlite3_create_aggregate(db, "sos", 1, SQLITE_UTF8, 0, sos_step, 0, sos_finalize);
    sqlite3_stmt *stmt;
    const char *sql = "SELECT sos(1), sos(2), sos(3)";
    sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        double result1 = sqlite3_column_double(stmt, 0);
        double result2 = sqlite3_column_double(stmt, 1);
        double result3 = sqlite3_column_double(stmt, 2);
        printf("Result 1: %f\nResult 2: %f\nResult 3: %f\n", result1, result2, result3);
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

- 在这段代码中，定义了 `SumOfSquaresContext` 结构体来存储聚合过程中的中间结果（平方和）。`sos_step` 函数在每一步处理输入值并更新平方和，`sos_finalize` 函数在聚合结束时返回最终的平方和。通过 `sqlite3_create_aggregate` 注册自定义聚合函数 `sos`。

2. Python 中实现自定义聚合函数 - 在 Python 中，可以使用 sqlite3 模块实现自定义聚合函数。以下是一个计算一组字符串长度总和的自定义聚合函数示例。 - Python 代码示例：

import sqlite3


class StringLengthSum:
    def __init__(self):
        self.total_length = 0

    def step(self, value):
        if value is not None:
            self.total_length += len(value)

    def finalize(self):
        return self.total_length


conn = sqlite3.connect(":memory:")
conn.create_aggregate("str_len_sum", 1, StringLengthSum)
cursor = conn.cursor()
cursor.execute("SELECT str_len_sum('Hello'), str_len_sum('World')")
results = cursor.fetchall()
for result in results:
    print(result[0])
conn.close()

- 这里定义了 `StringLengthSum` 类，它有 `__init__` 方法初始化总和为 0，`step` 方法在每一步处理字符串并更新长度总和，`finalize` 方法在聚合结束时返回最终的长度总和。通过 `conn.create_aggregate` 注册自定义聚合函数 `str_len_sum`。

通过深入理解 SQLite 的函数与聚合操作，无论是简单的数据处理还是复杂的数据分析和统计，我们都能更高效地利用 SQLite 数据库来满足各种需求。无论是内置函数和聚合函数，还是自定义的函数和聚合函数，都为我们提供了强大的数据处理能力。在实际应用中，根据具体的业务场景选择合适的函数和聚合操作，可以极大地提高数据处理的效率和准确性。同时，掌握如何创建自定义函数和聚合函数，能够让我们在面对特殊需求时，灵活扩展 SQLite 的功能。