python - 使用 SQLAlchemy，如何对 PGSQL 进行范围窗口子查询？

Question

我有一个类似于以下内容的查询:

  SELECT id, max(subq.foo), sum(subq.bar), etc...
  FROM (
      SELECT id,
           first_value(foo) over w as foo,
           etc...
      FROM my_table
      WHERE <where information>
      WINDOW w AS (PARTITION BY id 
                   ORDER BY time_stamp ASC
                   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) as subq

我将如何使用 sqlalchemy 完成此操作？特别是，我的问题是关于在内部查询上完成该窗口。 SQLalchemy 似乎只有 partition_by 和 order_by 的参数，并且该范围参数是必需的。

谢谢!

Answer 1

最后这样做:

# hack to get window supported
subq = subq.filter(text("TRUE WINDOW w AS (PARTITION BY id "
                        "ORDER BY time_stamp ASC "
                        "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)"))

这将是应用于查询的最后一个过滤器，因此您最终得到这样的 sql:

WHERE .... AND TRUE WINDOW w as (PARTITION BY id ORDER BY ...)