使用SQLAlchemy实现PostgreSQL聚合函数的ORDER BY

Question

我有一个查询，使用PostgreSQL的语法在聚合函数中使用ORDER BY，类似于这样：

SELECT some_agg_func(a ORDER BY b DESC) FROM table;

有人知道如何使用SQLAlchemy表达式语言实现这一点吗？

- Alex jg

Answer 1

来自 SQLAlchemy文档：

from sqlalchemy.dialects.postgresql import aggregate_order_by

expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
stmt = select([expr])

- avyk37

Answer 2

我认为SqlAlchemy 1.1现在已经原生支持string_agg了: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by （在页面上搜索“func.string_agg”）

- Mar

---

你能否编辑一下，添加一个链接到http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by，并提供一个具体的使用示例？这样我就可以接受它作为正确答案。谢谢。 - Alex jg

Answer 3

您需要使用SQLAlchemy的编译器扩展来实现此功能。以下是Postgres string_agg函数的示例：

from sqlalchemy.sql.expression import ColumnElement, _literal_as_column
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.compiler import compiles

class string_agg(ColumnElement):
    def __init__(self, expr, separator, order_by=None):
        self.type = Text()
        self.expr = _literal_as_column(expr)
        self.separator = literal(separator)
        self.order_by = _literal_as_column(order_by)

    @property
    def _from_objects(self):
        return self.expr._from_objects

@compiles(string_agg, 'postgresql')
def compile_string_agg(element, compiler, **kwargs):
    head = 'string_agg(%s, %s' % (
        compiler.process(element.expr),
        compiler.process(element.separator)
    )
    if element.order_by is not None:
        tail = ' ORDER BY %s)' % compiler.process(element.order_by)
    else:
        tail = ')'
    return head + tail

query = session.query(string_agg(Foo.bar, ', ', order_by=Foo.bar.desc()))
# Print compiled SQL query.
print query.statement.compile(dialect=postgresql.dialect())
# Run the query and print result.
print query.scalar()

- Audrius Kažukauskas

---

sqlalchemy 0.8似乎没有_literal_as_column，我通过使用__cause_element__解决了这个问题，这就是_literal_as_column所能做的。除此之外一切正常。 - Alex jg

是的，我在0.7.9上测试过了。我认为_literal_as_column在0.8中被重命名为_literal_as_binds。 - Audrius Kažukauskas

或者并非如此，因为我看到 _literal_as_binds 在0.7代码库中也存在。 - Audrius Kažukauskas