python - SQLAlchemy group_by SQLite 与 PostgreSQL

Question

对于我们正在构建的网络应用程序，我们使用 SQLite 进行测试。最近我们想迁移到 PostgreSQL。这就是问题开始的地方:

我们有这个 SQLAlchemy 模型(简化)

class Entity(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    i_want_this = db.Column(db.String)
    some_value = db.Column(db.Integer)

我想按 some_value 对所有 Entity 进行分组，我这样做(简化):

db.session.query(Entity, db.func.count()).group_by(Entity.some_value)

在 SQLite 中这行得通。回想起来，我发现它没有意义，但 SQLite 确实理解了它。我不能确定返回了哪个实体。

现在在 PostgrSQL 中我们得到这个错误:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "entity.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT entity.id AS entity_id, entity.i_want_this AS entity_not...
^
[SQL: 'SELECT entity.id AS entity_id, entity.i_want_this AS entity_i_want_this, count(*) AS count_1 \nFROM entity GROUP BY entity.some_value']

这个错误完全有道理。

所以我的第一个问题是:为什么 SQLite 允许这样做以及它是如何做到的(使用了什么隐藏聚合)？

我的第二个问题很明显:我将如何使用 PostgreSQL 做到这一点？

我实际上只对计数和第一个 i_want_this 值感兴趣。所以我可以这样做:

groups = db.session.query(db.func.min(Entity.id), db.func.count()).group_by(Entity.some_value)

[(Entity.query.get(id_), count) for id_, count in groups]

但我不想要这些额外的 get 查询。

所以我想选择第一个实体(具有最小 id 的实体)和按 some_value 分组的实体数或第一个 i_want_this 和按以下分组的计数some_value

编辑以明确:

• 我想按 some_value 分组(完成)
• 我想获取每个组中的实体数量(完成)
• 我想获得每个组中 id 最低的实体(需要这方面的帮助)
• 或者我想获取每个组中具有最低 id 的实体的 i_want_this 值(需要这方面的帮助)

Answer 1

关于你的第一个问题，检查documentation :

Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.

关于第二个问题，考虑到您当前的查询也在 SQLite 中返回或多或少的随机结果，您可能必须解释您实际想要实现的目标。

编辑:要获取每组具有最小 ID 的实体，您可以使用 Query.select_from 构造:

import sqlalchemy.sql as sa_sql

# create the aggregate/grouped query
grouped = sa_sql.select([sa_sql.func.min(Entity.id).label('min_id')])\
                .group_by(Entity.some_value)\
                .alias('grouped')

# join it with the full entities table
joined = sa_sql.join(Entity, grouped, grouped.c.min_id == Entity.id)
# and let sqlalchemy pull the entities from this statement:
session.query(Entity).select_from(joined)

这将产生以下 SQL:

SELECT entities.id AS entities_id,
       entities.i_want_this AS entities_i_want_this,
       entities.some_value AS entities_some_value 
FROM entities JOIN (SELECT min(entities.id) AS min_id
                    FROM entities GROUP BY entities.some_value) AS grouped
ON grouped.min_id = entities.id