SQLite按ID计算交易均价并更新表的低效查询优化求助

问题分析 #

你当前的UPDATE语句使用了相关子查询，会对Table_A的每一行单独执行一次AVG()计算——每处理一行就全表扫描一次Table_B筛选对应公司的数据。当Table_B数据量较大时，这种重复扫描会导致性能急剧下降，出现超时情况。

同时注意：你的price字段带有$符号，直接用AVG("price")会因为字符串无法正确计算数值均值，必须先转换为数值类型。

优化方案 #

方案1：使用UPDATE FROM语法（SQLite 3.33.0+推荐） #

先一次性预计算所有公司的均价，再通过关联更新Table_A，仅扫描Table_B一次，效率大幅提升：

-- 1. 给Table_B的公司ID字段创建索引（首次执行后可省略）
CREATE INDEX IF NOT EXISTS idx_b_company_id ON "Table_B"("company id");

-- 2. 高效更新语句，支持按季度过滤
UPDATE "Table_A" AS A
SET "avg_price" = '$' || ROUND(C.avg_price, 2) -- 保留$符号和两位小数
FROM (
    SELECT 
        "company id", 
        AVG(REPLACE("price", '$', '') + 0) AS avg_price
    FROM "Table_B"
    -- 如需按季度筛选，添加WHERE条件（示例：2023年第一季度）
    -- WHERE strftime('%Y-%m', "date") BETWEEN '2023-01' AND '2023-03'
    GROUP BY "company id"
) AS C
WHERE A."company id" = C."company id";

方案2：兼容低版本SQLite（3.33.0以下） #

如果你的SQLite版本不支持UPDATE FROM，可以先将预计算的均价存入临时表，再执行更新：

-- 1. 创建临时表存储所有公司的均价
CREATE TEMP TABLE IF NOT EXISTS CompanyAvg AS
SELECT 
    "company id", 
    AVG(REPLACE("price", '$', '') + 0) AS avg_price
FROM "Table_B"
-- 按季度筛选的话添加WHERE条件
GROUP BY "company id";

-- 2. 给临时表创建索引提升查询速度
CREATE INDEX IF NOT EXISTS idx_temp_company_id ON CompanyAvg("company id");

-- 3. 更新Table_A
UPDATE "Table_A" AS A
SET "avg_price" = '$' || ROUND((SELECT avg_price FROM CompanyAvg WHERE "company id" = A."company id"), 2);

-- 可选：更新完成后删除临时表
DROP TABLE CompanyAvg;

关键说明 #

1. 索引优化：给Table_B的company id字段创建索引，能大幅加速分组聚合的速度，尤其是数据量大时效果显著。
2. 数值转换：通过REPLACE("price", '$', '') + 0将带$的字符串转换为数值，确保AVG()计算正确。
3. 结果格式化：用'$' || ROUND(..., 2)将计算后的均值还原为带$的格式，符合你的预期结果。

验证结果 #

执行优化后的语句后，Table_A会得到预期结果：

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