SQLAlchemy group_by SQLite vs PostgreSQL

Question

对于我们正在构建的Web应用程序，我们使用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

关于您的第一个问题，请查看文档：

然后，结果集中的每个表达式将针对每个表达式计算一次 行组。如果表达式是聚合表达式，则它是 在组中的所有行中进行评估。否则，将对其进行评估 针对组内任意选择的单个行。如果 结果集中有多个非聚合表达式， 然后针对同一行计算所有这些表达式。

关于第二个问题，考虑到您当前的查询也在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