SQLAlchemy + SQLite 统计标准差

需求

有两个数据表，主要字段如下：

• Food: id, name
• Weigh: id, food_id, weight

需要统计每个 Food 的 weight 的数量、平均数、标准差。

一个比较简单的思路是，先单用 Weigh 这张表，group by 得到每个 food_id 下的统计数据，然后再将这张表跟 Food join 一下得到最终的统计数据。

有个麻烦的问题是 SQLite 不支持求标准差，这里只能用定义来求了。

$$\sigma = \sqrt{\frac{1}{N}\sum_{i=1}^N(x_i - \mu)} = \sqrt{\frac{1}{N}\sum_{i=1}^N x_i^2 - \mu^2}$$

代码如下：

async def get_with_weight(db: AsyncSession):
    subq = (
        select(
            Weigh.food_id,
            func.count().label("weight_cnt"),
            func.avg(Weigh.weight).label("weight_avg"),
            func.sqrt(
                func.avg(Weigh.weight * Weigh.weight) - func.avg(Weigh.weight) * func.avg(Weigh.weight)
            ).label("weight_std"),
        )
        .group_by(Weigh.food_id)
        .subquery()
    )
    stmt = select(Food, subq.c.weight_cnt, subq.c.weight_avg, subq.c.weight_std).join_from(Food, subq)
    return (await db.execute(stmt)).all()

这里 select 语句中含有一堆列统计，不知道性能咋样。

尝试拆成两个 subquery：

subq = (
    select(
        Weigh.food_id,
        func.count().label("weight_cnt"),
        func.avg(Weigh.weight).label("weight_avg"),
        func.avg(Weigh.weight * Weigh.weight).label("weight_sqr"),
    )
    .group_by(Weigh.food_id)
    .cte()
)
subq2 = select(
    subq.c.food_id,
    subq.c.weight_cnt,
    subq.c.weight_avg,
    func.sqrt(subq.c.weight_sqr - subq.c.weight_avg * subq.c.weight_avg).label("weight_std"),
).subquery()
stmt = select(Food, subq2.c.weight_cnt, subq2.c.weight_avg, subq2.c.weight_std).join_from(Food, subq)

这样能运行，但是会卡死，可能是语句写得有点问题。

所以还是放弃 SQLite 拥抱 PG 吧。

性能问题

完整的模型是这样的：

class Food(TimeMixin, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("food.id"), default=None)
    name: Mapped[str]
    price: Mapped[int]
    desc: Mapped[str]

    parent: Mapped[Optional["Food"]] = relationship("Food", back_populates="variants", remote_side=[id])
    variants: Mapped[list["Food"]] = relationship("Food", back_populates="parent")
    tags: Mapped[list["Tag"]] = relationship(secondary=FoodTagLink, back_populates="foods")

在获取所有 food 的时候，返回的 Pydantic 模型需要顺带获取 variants、tags 信息。

如果逐一做 model_validate，上述代码运行会出现严重的性能问题。
在 food 表 100+ 数据，weigh 表 600+ 数据的情况下，获取所有 weigh 并返回大约要 0.1s，但获取所有 food 需要将近 2s。尝试去掉统计数据，发现没有明显改善，得出结论是多次查询开销太大。每个模型获取每个关系属性，都会发起一个新的异步数据库查询。

SQLAlchemy 默认是 lazy load，不会获取 relationship 的数据，除非对其访问。
在这里，既然我们最终是要获取 relationship 值的，那么为何不在 CRUD 层直接获取呢？

根据官方文档，

对查询语句修改如下：

stmt = (
    select(Food, subq.c.weight_cnt, subq.c.weight_avg, subq.c.weight_std)
    .join_from(Food, subq)
    .options(subqueryload(Food.tags), subqueryload(Food.variants))
)

这里用 subqueryload 和 selectinload 皆可，性能上没显著差异，但后者比较蠢，会产生 in (1, 2, 3, ...) 这样的巨长 SQL。用 joinedload 会报错。

经过简单的性能测试，调用一次接口大约 0.15s，时间显著减少。

这样一来，原本需要包在异步块里的过程也可以解脱了。

@router.get("/", response_model=list[FoodReadWithStats])
async def get_foods(
    db: AsyncSession = Depends(deps.get_db),
):
    rows = await crud.food.get_with_weight(db)
    return [
        FoodReadWithStats(
            **FoodReadWithVariants.model_validate(row[0]).model_dump(),
            weight_cnt=row[1],
            weight_avg=row[2],
            weight_std=row[3],
        )
        for row in rows
    ]