使用窗口函数在 Postgres 上使用 SqlAlchemy 限制查询

本文介绍了使用窗口函数在 Postgres 上使用 SqlAlchemy 限制查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用 sqlalchemy ORM 编写以下 sql 查询:

I'm trying to write the following sql query with sqlalchemy ORM:

SELECT * FROM (SELECT *, row_number() OVER(w) FROM (select distinct on (grandma_id, author_id) * from contents) as c WINDOW w AS (PARTITION BY grandma_id ORDER BY RANDOM())) AS v1 WHERE row_number <= 4;

这是我目前所做的:

s = Session() unique_users_contents = (s.query(Content).distinct(Content.grandma_id, Content.author_id) .subquery()) windowed_contents = (s.query(Content, func.row_number() .over(partition_by=Content.grandma_id, order_by=func.random())) .select_from(unique_users_contents)).subquery() contents = (s.query(Content).select_from(windowed_contents) .filter(row_number >= 4)) ## how can I reference the row_number() value? result = contents for content in result: print "%s\t%s\t%s" % (content.id, content.grandma_id, content.author_id)

如您所见，它几乎是建模的，但我不知道如何从外部查询 where 引用子查询的 row_number() 结果.我尝试了类似 windowed_contents.c.row_number 并在 window func 上添加一个 label() 调用但它不起作用，在官方文档中找不到任何类似的例子或在 stackoverflow 中.

As you can see it's pretty much modeled, but I have no idea how to reference the row_number() result of the subquery from the outer query where. I tried something like windowed_contents.c.row_number and adding a label() call on the window func but it's not working, couldn't find any similar example in the official docs or in stackoverflow.

如何实现?还有，你能提出一个更好的方法来做这个查询吗?

How can this be accomplished? And also, could you suggest a better way to do this query?

推荐答案

windowed_contents.c.row_number 针对 label() 是你如何做，有效对我来说(注意 select_entity_from() 方法是 SQLA 0.8.2 中的新方法，在 0.9 与 select_from() 中将需要这里):

windowed_contents.c.row_number against a label() is how you'd do it, works for me (note the select_entity_from() method is new in SQLA 0.8.2 and will be needed here in 0.9 vs. select_from()):

from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Content(Base): __tablename__ = 'contents' grandma_id = Column(Integer, primary_key=True) author_id = Column(Integer, primary_key=True) s = Session() unique_users_contents = s.query(Content).distinct( Content.grandma_id, Content.author_id).\ subquery('c') q = s.query( Content, func.row_number().over( partition_by=Content.grandma_id, order_by=func.random()).label("row_number") ).select_entity_from(unique_users_contents).subquery() q = s.query(Content).select_entity_from(q).filter(q.c.row_number <= 4) print q