SQLite3 如何用单表两列乘积的滚动平均值更新指定列
SQLite 实现滚动平均值更新的正确方案
你遇到的两次更新失效、语法报错,本质是对SQLite的UPDATE语法规则和子查询关联逻辑不熟悉,完全可以用单条SQL实现需求,不需要拆分多步操作。
一、之前写法失效/报错的根因
- 第一种写法失效的原因:在没有建立外层表和子查询关联关系的前提下,把窗口函数写在
SET后的标量子查询中,SQLite会将窗口函数的上下文绑定到当前遍历的单行上,不会跨多行做窗口聚合,最终计算出的“滚动平均”其实就是当前行的dv本身,导致结果和预期完全不符。 - 第二种写法报错的原因:照搬PostgreSQL/MySQL的关联更新语法,触发了SQLite的语法限制:
- SQLite的
UPDATE语句中,SET后的待更新列不允许加表名/别名前缀,你写的SET tb.avg_dv = b.avg_dv里的tb.就是触发Error: near ".": syntax error的直接原因 - 派生表中的窗口计算列没有指定显式别名,后续关联时无法被正常引用
- SQLite的
二、可直接运行的单条SQL实现
该写法兼容SQLite 3.33.0及以上版本(绝大多数当前在用的SQLite发行版都满足版本要求):
UPDATE tb -- SET后只写待更新列名,不加任何表前缀 SET avg_dv = calc.avg_val FROM ( SELECT code, date, avg(dv) over ( partition by code order by date asc rows between 1 preceding and current row ) AS avg_val -- 窗口计算结果必须指定别名 FROM tb ) calc -- 通过联合唯一键做行级匹配,确保值和行一一对应 WHERE tb.code = calc.code AND tb.date = calc.date;
三、执行结果验证
执行完上述语句后查询全表,avg_dv列的结果和你之前单独跑SELECT窗口函数得到的滚动平均值完全一致:
BHP|2020-01-03|3.25|1000.0|3250.0|3250.0 BHP|2020-01-04|3.5|2000.0|7000.0|5125.0 BHP|2020-01-05|3.55|1000.0|3550.0|5275.0 CSR|2020-01-03|5.55|1500.0|8325.0|8325.0 CSR|2020-01-04|5.6|2000.0|11200.0|9762.5 CSR|2020-01-05|5.55|2000.0|11100.0|11150.0 DDG|2020-01-03|10.2|4000.0|40800.0|40800.0 DDG|2020-01-04|10.25|4500.0|46125.0|43462.5 DDG|2020-01-05|10.3|5000.0|51500.0|48812.5
四、注意事项
- 保证
code + date的组合在表内无重复值,否则更新时会出现值随机匹配的问题,建议提前给这两个字段建联合唯一索引 - 如果你使用的是3.33版本之前的旧版SQLite,不支持
UPDATE ... FROM语法,可以先通过计算结果创建临时映射表,再通过关联方式更新,本质逻辑和上述单条SQL完全一致
内容的提问来源于stack exchange,提问作者PlanetAtkinson
相关产品推荐
相关产品推荐

