使用窗口函数在 Postgres 上使用 SqlAlchemy 限制查询 [英] Using window functions to LIMIT a query with SqlAlchemy on Postgres

问题描述

我正在尝试使用 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

这篇关于使用窗口函数在 Postgres 上使用 SqlAlchemy 限制查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！