如何用SQLite3函数或自定义函数获取接口重置前的累计数据？

首先，你完全可以通过SQLite的内置函数（窗口函数、条件聚合等）来实现这个需求，不需要编写自定义函数。核心思路是按0的出现位置分组，提取每个分组中最后一个非0值，再对这些值求和。

实现SQL语句 #

WITH grouped_rows AS (
    -- 为每行标记分组ID：每遇到一个0，分组号加1，确保同一"累计周期"的行在同一组
    SELECT 
        value,
        SUM(CASE WHEN value = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM SIM_STATS
),
group_final_values AS (
    -- 提取每个分组中最后一个非0值（用MAX取非0值，因为每个周期的累计值是递增的，最大值就是最后一个非0值）
    SELECT 
        group_id,
        MAX(CASE WHEN value != 0 THEN value ELSE NULL END) AS cycle_total
    FROM grouped_rows
    GROUP BY group_id
)
-- 对所有有效周期的累计值求和
SELECT SUM(cycle_total) AS total_accumulated FROM group_final_values;

逻辑解释 #

1. 分组标记：使用SUM() OVER (ORDER BY id)窗口函数，统计当前行及之前出现的0的数量，以此作为分组ID。这样每个"从0重置到下一次0"的周期会被分到同一组。
2. 提取周期峰值：对每个分组，用MAX(CASE...)筛选出该组内的最后一个非0值（因为每个周期的累计值是递增的，最大值就是最后一个非0值）。
3. 求和：对所有有效周期的峰值求和，得到最终的真实累计数据。

针对你提供的测试数据，执行后会返回21（5+6+10），完全符合需求。

如果你确实需要通过自定义函数来实现，下面是具体的代码示例，包括xStep和xFinal函数的编写：

1. 定义状态结构体 #

我们需要在函数执行过程中维护两个状态：当前累计总和、当前周期的最后一个非0值：

#include <sqlite3.h>
#include <stdlib.h>

typedef struct {
    int total;          // 最终要返回的总和
    int last_non_zero;  // 当前周期的最后一个非0值
} SumCycleState;

2. 实现xStep函数 #

xStep负责逐行处理数据，更新状态：

static void sum_cycle_step(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    SumCycleState *state = (SumCycleState *)sqlite3_context_user_data(ctx);
    int current_val = sqlite3_value_int(argv[0]);

    if (current_val == 0) {
        // 遇到0，将当前周期的非0值计入总和，然后重置周期
        if (state->last_non_zero != 0) {
            state->total += state->last_non_zero;
            state->last_non_zero = 0;
        }
    } else {
        // 非0值，更新当前周期的最后一个非0值
        state->last_non_zero = current_val;
    }
}

3. 实现xFinal函数 #

xFinal负责在所有行处理完成后，处理最后一个未结束的周期，并返回结果：

static void sum_cycle_final(sqlite3_context *ctx) {
    SumCycleState *state = (SumCycleState *)sqlite3_context_user_data(ctx);
    
    // 如果最后一行不是0，将最后一个周期的非0值计入总和
    if (state->last_non_zero != 0) {
        state->total += state->last_non_zero;
    }

    // 返回最终结果
    sqlite3_result_int(ctx, state->total);
    // 释放状态内存
    free(state);
}

4. 注册自定义函数 #

在你的SQLite连接初始化后，注册这个自定义聚合函数：

int register_sum_cycle_function(sqlite3 *db) {
    SumCycleState *initial_state = malloc(sizeof(SumCycleState));
    if (!initial_state) {
        return SQLITE_NOMEM;
    }
    initial_state->total = 0;
    initial_state->last_non_zero = 0;

    return sqlite3_create_function(
        db,                  // SQLite连接句柄
        "sum_cycle_total",   // 函数名
        1,                   // 参数个数（仅接收value列）
        SQLITE_UTF8,         // 编码
        initial_state,       // 初始状态数据
        NULL,                // 不需要xFunc（聚合函数用xStep/xFinal）
        sum_cycle_step,      // 逐行处理函数
        sum_cycle_final      // 最终计算函数
    );
}

使用自定义函数 #

注册完成后，就可以像使用内置函数一样调用：

SELECT sum_cycle_total(value) FROM SIM_STATS;

内容的提问来源于stack exchange，提问作者Guilherme Nascimento