在SQLAlchemy中是否有一个LISTAGG WITHIN GROUP等效项?

问题描述

这是一个简单的Oracle表:

Here is a simple Oracle table:

+-----------+---------+
|   food    | person  |
+-----------+---------+
| pizza     | Adam    |
| pizza     | Bob     |
| pizza     | Charles |
| ice cream | Donald  |
| hamburger | Emma    |
| hamburger | Frank   |
+-----------+---------+

这是我想执行的汇总SELECT的结果:

And here are the results of an aggregated SELECT I'd like to do:

+-----------+------------------+
|   food    |      people      |
+-----------+------------------+
| hamburger | Emma,Frank       |
| ice cream | Donald           |
| pizza     | Adam,Bob,Charles |
+-----------+------------------+

使用Oracle 11g +，使用LISTAGG可以轻松实现:

With Oracle 11g+ this is easy enough with a LISTAGG:

SELECT food, LISTAGG (person, ',') WITHIN GROUP (ORDER BY person) AS people
FROM mytable
GROUP BY food;

但是我还无法在SQLAlchemy中找到一种方法来做到这一点.来自堆栈溢出的旧问题显示了有人试图在哪里实现一个自定义类来完成这项工作，但这真的是最好的选择吗?

But I haven't been able to find a way to do this within SQLAlchemy. An old question from Stack Overflow shows where someone was trying to implement a custom class to do the job, but is that really the best option there is?

MySQL具有group_concat功能，因此此提问者解决了他的问题使用func.group_concat(...).遗憾的是，该功能在Oracle中不可用.

MySQL has a group_concat feature, and thus this questioner solved his problem with func.group_concat(...). Sadly that function is not available within Oracle.

推荐答案

从版本1.1 您可以使用 FunctionElement.within_group(*order_by) :

Beginning from version 1.1 you can use FunctionElement.within_group(*order_by):

In [7]: func.listagg(column('person'), ',').within_group(column('person'))
Out[7]: <sqlalchemy.sql.elements.WithinGroup object at 0x7f2870c83080>

In [8]: print(_.compile(dialect=oracle.dialect()))
listagg(person, :listagg_1) WITHIN GROUP (ORDER BY person)

这篇关于在SQLAlchemy中是否有一个LISTAGG WITHIN GROUP等效项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！