如何创建带整数参数的SQL自定义函数以动态调整查询中的时间偏移值

当然可以实现！作为SQL新手，你想要的这种参数化查询需求非常常见——尤其是在需要反复测试不同参数值的场景下，手动改代码确实太麻烦了。下面我会针对主流的关系型数据库（PostgreSQL、MySQL、SQL Server）分别给出具体的实现方法，完全满足你get_crashes(xxx)的调用需求：

PostgreSQL 实现方式 #

PostgreSQL支持返回表类型的函数，你可以这样创建：

CREATE OR REPLACE FUNCTION get_crashes(offset_val INTEGER)
RETURNS TABLE (
    deviceid TEXT, -- 请根据你的实际表字段类型调整
    kernel_time TIMESTAMP,
    crash_time TIMESTAMP,
    crash_process TEXT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    start_kernel_time TIMESTAMP,
    end_kernel_time TIMESTAMP,
    flag INTEGER,
    row_num INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        dc.deviceid, 
        dc.kernel_time, 
        dc.crash_time, 
        dc.crash_process, 
        dps.start_time, 
        dps.end_time, 
        dps.start_kernel_time, 
        dps.end_kernel_time, 
        CASE 
            WHEN dc.kernel_time BETWEEN dps.start_kernel_time AND dps.end_kernel_time THEN 1 
            WHEN dc.crash_time BETWEEN dps.start_time AND dps.end_time THEN 2 
            ELSE 3 
        END AS flag, 
        ROW_NUMBER() OVER (
            PARTITION BY dc.deviceid, dc.kernel_time, dc.crash_time, dc.crash_process 
            ORDER BY flag
        ) AS row_num 
    FROM dummy.dummy_crashes dc 
    LEFT OUTER JOIN dummy.dummy_power dps -- 简化原查询的子查询，提升性能
        ON dc.deviceid = dps.deviceid 
        AND (
            -- 如果kernel_time是整数时间戳，直接用dps.start_kernel_time + offset_val即可
            dc.kernel_time BETWEEN (dps.start_kernel_time + interval '1 millisecond' * offset_val) 
            AND (dps.end_kernel_time + interval '1 millisecond' * offset_val)
            OR dc.crash_time BETWEEN dps.start_time AND dps.end_time
        ) 
    ORDER BY dc.crash_time;
END;
$$ LANGUAGE plpgsql;

调用方式：SELECT * FROM get_crashes(10000);

MySQL 实现方式 #

MySQL用存储过程来实现这种需求更稳妥（8.0+版本也支持返回结果集的函数，但存储过程兼容性更好）：

DELIMITER //
CREATE PROCEDURE get_crashes(IN offset_val INT)
BEGIN
    SELECT 
        dc.deviceid, 
        dc.kernel_time, 
        dc.crash_time, 
        dc.crash_process, 
        dps.start_time, 
        dps.end_time, 
        dps.start_kernel_time, 
        dps.end_kernel_time, 
        CASE 
            WHEN dc.kernel_time BETWEEN dps.start_kernel_time AND dps.end_kernel_time THEN 1 
            WHEN dc.crash_time BETWEEN dps.start_time AND dps.end_time THEN 2 
            ELSE 3 
        END AS flag, 
        ROW_NUMBER() OVER (
            PARTITION BY dc.deviceid, dc.kernel_time, dc.crash_time, dc.crash_process 
            ORDER BY flag
        ) AS row_num 
    FROM dummy.dummy_crashes dc 
    LEFT OUTER JOIN dummy.dummy_power dps
        ON dc.deviceid = dps.deviceid 
        AND (
            dc.kernel_time BETWEEN (dps.start_kernel_time + offset_val) 
            AND (dps.end_kernel_time + offset_val)
            OR dc.crash_time BETWEEN dps.start_time AND dps.end_time
        ) 
    ORDER BY dc.crash_time;
END //
DELIMITER ;

调用方式：CALL get_crashes(10000);

SQL Server 实现方式 #

SQL Server可以创建表值函数，语法简洁直观：

CREATE FUNCTION get_crashes(@offset_val INT)
RETURNS TABLE
AS
RETURN (
    SELECT 
        dc.deviceid, 
        dc.kernel_time, 
        dc.crash_time, 
        dc.crash_process, 
        dps.start_time, 
        dps.end_time, 
        dps.start_kernel_time, 
        dps.end_kernel_time, 
        CASE 
            WHEN dc.kernel_time BETWEEN dps.start_kernel_time AND dps.end_kernel_time THEN 1 
            WHEN dc.crash_time BETWEEN dps.start_time AND dps.end_time THEN 2 
            ELSE 3 
        END AS flag, 
        ROW_NUMBER() OVER (
            PARTITION BY dc.deviceid, dc.kernel_time, dc.crash_time, dc.crash_process 
            ORDER BY flag
        ) AS row_num 
    FROM dummy.dummy_crashes dc 
    LEFT OUTER JOIN dummy.dummy_power dps
        ON dc.deviceid = dps.deviceid 
        AND (
            -- 若为日期类型，可改用DATEADD(ms, @offset_val, dps.start_kernel_time)
            dc.kernel_time BETWEEN (dps.start_kernel_time + @offset_val) 
            AND (dps.end_kernel_time + @offset_val)
            OR dc.crash_time BETWEEN dps.start_time AND dps.end_time
        ) 
    ORDER BY dc.crash_time
);

调用方式：SELECT * FROM get_crashes(10000);

关键注意事项 #

• 字段类型匹配：函数/存储过程返回的字段类型必须和原查询结果的字段类型完全一致，否则会触发报错。比如deviceid如果是INT类型，就不要定义成TEXT。
• 时间类型适配：如果你的时间字段是日期时间类型（而非整数时间戳），需要根据数据库语法把偏移量转换成对应的时间间隔（比如PostgreSQL的interval、SQL Server的DATEADD）。
• 性能优化：原查询里的LEFT OUTER JOIN (SELECT * FROM dummy.dummy_power)可以直接简化为LEFT OUTER JOIN dummy.dummy_power dps，去掉不必要的子查询能提升查询效率。

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