使用SQLAlchemy对单个表进行多个group_by计数

Question

我正在使用SQLAlchemy(1.4/2.0过渡风格)访问PostgreSQL数据库。我使用group_by来计算按计数排序的唯一名称的数量。然后我需要做的是应用额外的group_by来计算相同的东西，但条件不同。例如，如果我有这个User对象

class User(Base):
__tablename__ = "user"
user_id = Column(Integer, primary_key=True)
name = Column(String)
city = Column(String)

我可以得到前100名最流行的用户名和那些使用该名称的计数:

stmt = select(User.name, func.count(User.user_id).label("total")) 
.group_by(User.name) 
.order_by(desc('total')) 
.limit(100)

我要做的是在这100个最受欢迎的名字中，找出有多少人的名字是city == "New York"， "Chicago","洛Angeles"我希望得到的数据像这样:

name: "Charles Smith", total:526, NY:28, Chi:17, LA:23

你知道怎么做吗?

Answer 1

Is straight SQL可以在count上使用FILTER子句来获取单个城市的计数，并使用标准计数来表示总数:参见下面的示例

select  name, total, ny, chi, la 
from ( select name 
, count(*) total 
, COUNT(*) FILTER (WHERE city='Chicago')     chi
, COUNT(*) FILTER (WHERE city='Los Angeles') la
, COUNT(*) FILTER (WHERE city='New York')    ny  
from users 
group by   name            
) sq;

我把它留给你来转换为SQLAlchemy;因为我可以通过阅读来理解它，但我不愿意尝试写出来。