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

一尘不染

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

sql

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

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)

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

如何做到这一点？而且，您能否建议一种更好的方法来执行此查询？

阅读 256

2021-03-17

共1个答案

一尘不染

windowed_contents.c.row_number反对alabel()是您如何做到的，它对我select_entity_from()有用（请注意，该方法是SQLA
0.8.2中的新方法，在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

2021-03-17