PostgreSQL时间戳15分钟差校验及KPI计算最优方案咨询

问题1：校验逻辑封装为函数是否属于过度设计，是否是规范做法？ #

要分场景判断：

• 从当前数据规模（每月6500行，5-10%增速）来看，直接在SQL中写CASE逻辑完全够用，封装函数属于过度设计——函数调用会带来微小的额外开销，而且简单逻辑封装后反而会增加阅读成本（其他开发者需要跳转查看函数定义）。
• 但如果存在未来扩展需求（比如偏差阈值需要动态配置、多个SQL语句复用该校验规则、对接实时仪表盘时要统一规则口径），封装函数就是规范且合理的做法：它能保证校验逻辑的一致性，避免重复编写CASE语句出现错误，后续修改规则也只需要调整函数即可，无需修改所有引用的SQL。

问题2：是否存在更优、更高效的实现方式？ #

首先明确现有两种写法的问题：

• 写法一逻辑错误：用TO_CHAR提取分钟数的方式，只计算了时间差的分钟部分，忽略了小时/天级别的差值。比如计划开始时间是10:00，实际开始时间是11:10，时间差为70分钟，但TO_CHAR会返回10，会误判为PASS，完全不符合需求。
• 写法二逻辑正确，但可以优化：

最优SQL写法（简洁且高效） #

利用PostgreSQL对timestamp和interval的原生支持，用更简洁的方式实现偏差判断，性能和写法二一致，但可读性更强：

SELECT
    a.job_no,
    -- KPI1：实际开始与计划开始偏差±15分钟内
    CASE WHEN ABS(b.actual_start - a.rostered_start) <= '15 minutes'::INTERVAL THEN 'PASS' ELSE 'FAIL' END AS start_time_check,
    -- KPI2：实际结束与计划结束偏差±15分钟内
    CASE WHEN ABS(b.actual_end - a.rostered_end) <= '15 minutes'::INTERVAL THEN 'PASS' ELSE 'FAIL' END AS end_time_check,
    -- KPI3：开始和结束均满足偏差要求
    CASE 
        WHEN ABS(b.actual_start - a.rostered_start) <= '15 minutes'::INTERVAL
             AND ABS(b.actual_end - a.rostered_end) <= '15 minutes'::INTERVAL
        THEN 'PASS' ELSE 'FAIL' END AS start_end_both_check
FROM table_a a
JOIN table_b b ON a.job_no = b.job_no;

性能优化建议（适配实时仪表盘需求） #

1. 索引优化：确保table_a和table_b的job_no字段为主键或建有索引，这样两表JOIN时能快速匹配关联数据，避免全表扫描。
2. 预计算汇总：因为要对接实时仪表盘，每次查询都JOIN计算会增加数据库压力。可以创建物化视图或专门的汇总表，定时（比如每分钟/每5分钟）刷新计算结果，仪表盘直接查询汇总表即可，既能保证实时性，又能提升查询效率。

可选的函数封装方案（针对有扩展需求的场景） #

如果确实需要统一规则或复用逻辑，可以封装成以下函数：

CREATE OR REPLACE FUNCTION check_time_deviation(planned_time TIMESTAMP, actual_time TIMESTAMP, allowed_deviation INTERVAL DEFAULT '15 minutes')
RETURNS TEXT AS $$
BEGIN
    IF ABS(actual_time - planned_time) <= allowed_deviation THEN
        RETURN 'PASS';
    ELSE
        RETURN 'FAIL';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用函数的SQL示例：

SELECT
    a.job_no,
    check_time_deviation(a.rostered_start, b.actual_start) AS start_time_check,
    check_time_deviation(a.rostered_end, b.actual_end) AS end_time_check,
    CASE 
        WHEN check_time_deviation(a.rostered_start, b.actual_start) = 'PASS'
             AND check_time_deviation(a.rostered_end, b.actual_end) = 'PASS'
        THEN 'PASS' ELSE 'FAIL' END AS start_end_both_check
FROM table_a a
JOIN table_b b ON a.job_no = b.job_no;

标记IMMUTABLE可以让PostgreSQL对函数调用进行优化，因为输入相同参数时输出固定。

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