SQLite3查询指定日期区间A0-A3最值及对应时间的问题

原代码的核心错误 #

1. SQL语法错误：缺失WHERE关键字，原语句FROM fivmin_tbl time >= ...不符合SQL规范，必须添加WHERE来指定过滤条件。
2. 字段误用：要过滤日期区间，应该使用表中的date字段，而非time字段。
3. 格式化与参数问题：用%d格式化日期会导致SQLite DATE类型解析错误，且字符串拼接存在SQL注入风险，应该用参数绑定。
4. 变量拼写错误：FinishtDate应为FinishDate。
5. 需求匹配失败：原查询返回的MIN(time)/MAX(time)是整个区间的时间极值，并非每个A字段最值对应的具体时间，无法满足需求。

修正后的Lua实现代码 #

以下代码使用CTE（公共表表达式）+窗口函数，精准获取每个A字段最值对应的时间戳，同时遵循SQLite最佳实践：

-- 构造查询语句：用窗口函数标记每个字段最值的行位置
local query = [[
WITH ranked_data AS (
    SELECT 
        date, time,
        A0, A1, A2, A3,
        -- 标记A0最值的行
        ROW_NUMBER() OVER (ORDER BY A0) AS rn_A0_min,
        ROW_NUMBER() OVER (ORDER BY A0 DESC) AS rn_A0_max,
        -- 标记A1最值的行
        ROW_NUMBER() OVER (ORDER BY A1) AS rn_A1_min,
        ROW_NUMBER() OVER (ORDER BY A1 DESC) AS rn_A1_max,
        -- 标记A2最值的行
        ROW_NUMBER() OVER (ORDER BY A2) AS rn_A2_min,
        ROW_NUMBER() OVER (ORDER BY A2 DESC) AS rn_A2_max,
        -- 标记A3最值的行
        ROW_NUMBER() OVER (ORDER BY A3) AS rn_A3_min,
        ROW_NUMBER() OVER (ORDER BY A3 DESC) AS rn_A3_max
    FROM fivmin_tbl
    WHERE date >= ? AND date <= ?
)
SELECT
    -- A0的最值及对应时间
    (SELECT A0 FROM ranked_data WHERE rn_A0_min = 1) AS min_A0,
    (SELECT time FROM ranked_data WHERE rn_A0_min = 1) AS min_A0_time,
    (SELECT A0 FROM ranked_data WHERE rn_A0_max = 1) AS max_A0,
    (SELECT time FROM ranked_data WHERE rn_A0_max = 1) AS max_A0_time,
    -- A1的最值及对应时间
    (SELECT A1 FROM ranked_data WHERE rn_A1_min = 1) AS min_A1,
    (SELECT time FROM ranked_data WHERE rn_A1_min = 1) AS min_A1_time,
    (SELECT A1 FROM ranked_data WHERE rn_A1_max = 1) AS max_A1,
    (SELECT time FROM ranked_data WHERE rn_A1_max = 1) AS max_A1_time,
    -- A2的最值及对应时间
    (SELECT A2 FROM ranked_data WHERE rn_A2_min = 1) AS min_A2,
    (SELECT time FROM ranked_data WHERE rn_A2_min = 1) AS min_A2_time,
    (SELECT A2 FROM ranked_data WHERE rn_A2_max = 1) AS max_A2,
    (SELECT time FROM ranked_data WHERE rn_A2_max = 1) AS max_A2_time,
    -- A3的最值及对应时间
    (SELECT A3 FROM ranked_data WHERE rn_A3_min = 1) AS min_A3,
    (SELECT time FROM ranked_data WHERE rn_A3_min = 1) AS min_A3_time,
    (SELECT A3 FROM ranked_data WHERE rn_A3_max = 1) AS max_A3,
    (SELECT time FROM ranked_data WHERE rn_A3_max = 1) AS max_A3_time
]]

-- 准备语句并绑定参数（StartDate/FinishDate需为'YYYY-MM-DD'格式字符串）
local stmt = db:prepare(query)
stmt:bind_values(StartDate, FinishDate)

-- 执行查询并读取结果
local result_code = stmt:step()
if result_code == sqlite3.ROW then
    -- 读取A0相关结果
    local min_A0 = stmt:get_value(0)
    local min_A0_time = stmt:get_value(1)
    local max_A0 = stmt:get_value(2)
    local max_A0_time = stmt:get_value(3)
    -- 读取A1相关结果
    local min_A1 = stmt:get_value(4)
    local min_A1_time = stmt:get_value(5)
    local max_A1 = stmt:get_value(6)
    local max_A1_time = stmt:get_value(7)
    -- 读取A2相关结果
    local min_A2 = stmt:get_value(8)
    local min_A2_time = stmt:get_value(9)
    local max_A2 = stmt:get_value(10)
    local max_A2_time = stmt:get_value(11)
    -- 读取A3相关结果
    local min_A3 = stmt:get_value(12)
    local min_A3_time = stmt:get_value(13)
    local max_A3 = stmt:get_value(14)
    local max_A3_time = stmt:get_value(15)

    -- 示例：打印结果
    print("A0 最小值: " .. min_A0 .. ", 对应时间: " .. min_A0_time)
    print("A0 最大值: " .. max_A0 .. ", 对应时间: " .. max_A0_time)
    -- 可自行添加A1/A2/A3的打印逻辑
end

-- 释放语句资源
stmt:finalize()

兼容低版本SQLite的替代方案 #

如果你的SQLite版本低于3.25.0（不支持窗口函数），可以用多次子查询的方式获取结果：

-- 获取A0最小值及对应时间
local query_a0_min = [[SELECT A0, time FROM fivmin_tbl WHERE date >= ? AND date <= ? ORDER BY A0 LIMIT 1]]
local stmt_a0_min = db:prepare(query_a0_min)
stmt_a0_min:bind_values(StartDate, FinishDate)
if stmt_a0_min:step() == sqlite3.ROW then
    local min_A0 = stmt_a0_min:get_value(0)
    local min_A0_time = stmt_a0_min:get_value(1)
    print("A0 最小值: " .. min_A0 .. ", 对应时间: " .. min_A0_time)
end
stmt_a0_min:finalize()

-- 获取A0最大值及对应时间
local query_a0_max = [[SELECT A0, time FROM fivmin_tbl WHERE date >= ? AND date <= ? ORDER BY A0 DESC LIMIT 1]]
-- 同理可编写A1/A2/A3的最值查询语句

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