面试题：SQLite中如何创建一个简单的用户自定义聚合函数

Question

假设在SQLite中，你需要创建一个自定义聚合函数来计算某列数据的平方和。请描述实现该功能的步骤，并给出主要代码示例（使用C语言扩展API）。

Answer 1

实现步骤

1. 包含必要的头文件：包含SQLite的头文件sqlite3.h。
2. 定义聚合函数的结构体：用于存储聚合计算过程中的中间值。
3. 实现聚合函数的初始化函数：初始化结构体中的变量。
4. 实现聚合函数的迭代函数：每次有新的数据行时调用，更新聚合结果。
5. 实现聚合函数的最终函数：计算并返回最终的聚合结果。
6. 注册聚合函数：将自定义聚合函数注册到SQLite中。

主要代码示例

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

// 定义用于存储中间结果的结构体
typedef struct {
    double sumOfSquares;
} SumOfSquaresContext;

// 初始化聚合函数上下文
static void sumOfSquaresInit(sqlite3_context *context, int argc, const sqlite3_value **argv) {
    SumOfSquaresContext *ctx = sqlite3_aggregate_context(context, sizeof(SumOfSquaresContext), 0);
    if (ctx) {
        ctx->sumOfSquares = 0;
    }
}

// 迭代函数，处理每一行数据
static void sumOfSquaresStep(sqlite3_context *context, int argc, const sqlite3_value **argv) {
    SumOfSquaresContext *ctx = (SumOfSquaresContext *)sqlite3_aggregate_context(context, 0, 0);
    if (ctx && argc == 1 && sqlite3_value_type(argv[0]) == SQLITE_FLOAT) {
        double value = sqlite3_value_double(argv[0]);
        ctx->sumOfSquares += value * value;
    }
}

// 最终函数，返回聚合结果
static void sumOfSquaresFinal(sqlite3_context *context) {
    SumOfSquaresContext *ctx = (SumOfSquaresContext *)sqlite3_aggregate_context(context, 0, 0);
    if (ctx) {
        sqlite3_result_double(context, ctx->sumOfSquares);
    } else {
        sqlite3_result_null(context);
    }
}

int main() {
    sqlite3 *db;
    int rc = sqlite3_open(":memory:", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return rc;
    }

    // 注册自定义聚合函数
    sqlite3_create_function(db, "sum_of_squares", 1, SQLITE_UTF8, 0, sumOfSquaresInit, sumOfSquaresStep, sumOfSquaresFinal);

    // 使用自定义聚合函数的SQL示例
    const char *sql = "CREATE TABLE data (num REAL); "
                      "INSERT INTO data (num) VALUES (2.0), (3.0), (4.0); "
                      "SELECT sum_of_squares(num) FROM data;";
    sqlite3_stmt *stmt;
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc == SQLITE_OK) {
        while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
            double result = sqlite3_column_double(stmt, 0);
            printf("Sum of squares: %f\n", result);
        }
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        }
    } else {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}