CODEWITHSUNDEEP
pythonpostgresqlsqlalchemywindowrange
JamesHutchison
JamesHutchison

Reputation: 923

Using SQLAlchemy, how do to PGSQL ranged window subquery?

I have a query that resembles the following:

  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

How would I accomplish this this using sqlalchemy? In particular, my question is about accomplishing that window on the inner query. Sqlalchemy only seems to have arguments for partition_by and order_by and that range argument is required.

Thanks!

Upvotes: 1

Views: 270

Answers (1)

JamesHutchison
JamesHutchison

Reputation: 923

Ended up doing this:

# 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)"))

This would be the last filter applied to the query, so you end up with sql like this:

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

Upvotes: 1

Related Questions