基于前一行计算值的SQLite视图创建与技术问询

首先，先给你的视图核心逻辑做个初步验证：

• bf_kg字段：计算round(a.bw * a.bf,2)完全正确，就是当前记录的体重乘以体脂率得到体脂重量，保留两位小数的处理也符合常规需求。
• d_bw和d_bf字段：你想要计算当前记录与上一条记录的体重差值、体脂重量差值，这个业务思路是对的，但实现方式存在一个关键的依赖条件需要注意。

当前实现的潜在隐患 #

你的自连接条件a.id = b.id + 1是基于id字段严格连续递增的假设：

• 如果你的data表中id是按日期顺序自动生成、从未有过删除操作（每条记录的id比前一条恰好大1），那这个逻辑能正常输出预期结果。
• 但如果中间有记录被删除（比如id=34被删掉，id=33之后直接是id=35），那么a.id=35会找不到b.id=34的记录，导致d_bw和d_bf变成NULL，而实际上你应该想关联的是id=33的上一条有效记录，这时候结果就不符合业务预期了。
• 从你的示例数据来看，记录是按日期每周生成的，业务上更合理的关联逻辑应该是按日期顺序取上一条记录，毕竟id只是主键，不一定和时间顺序严格绑定。

更健壮的优化方案 #

SQLite 3.25.0及以上版本支持窗口函数，用LAG()函数可以直接获取上一行的数据，不需要自连接，逻辑更清晰也更可靠：

create view eval as 
select 
    id,
    date,
    bw,
    bf,
    round(bw * bf, 2) as bf_kg,
    round(bw - lag(bw) over (order by date), 2) as d_bw,
    round((bw * bf) - lag(bw * bf) over (order by date), 2) as d_bf
from data;

这个方案的优势：

• 直接按date排序取上一条记录，完全贴合你的业务场景（按时间顺序计算差值）。
• 不依赖id的连续性，哪怕id有缺失或者乱序，只要date是正确的，就能得到正确的上一条数据。
• 语法更简洁，避免了自连接可能带来的性能问题（数据量大时差异更明显）。

关于结果的小疑问 #

你提到视图结果里出现了b_bf字段，但你的SQL语句里并没有选择这个字段，这应该是笔误或者输入错误——当前的select a.*, ...只会包含data表的所有字段（id、date、bw、bf）加上你定义的bf_kg、d_bw、d_bf，不会出现b_bf，可能是你粘贴结果时的失误。

总的来说，你的核心计算逻辑是正确的，但依赖id连续的实现方式有局限性，换成窗口函数的版本会更健壮。

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