So I had a query in raw PostgreSQL which was trying to find the start and
end of an event in
a history table:
select jh.job_id, jh.created as start_t, jh.status, min(jh.created)
over (PARTITION by jh.job_id
ORDER BY jh.created
ROWS BETWEEN 1 FOLLOWING and 3 FOLLOWING
) as end_t
from job_histories jh, jobs j, tasks t
where jh.status in ('running: copying files in', 'running','Running')
and jh.job_id = j.job_id
and j.task_id = t.task_id
and t.campaign_id = 704
When trying to render this in SQLAlchemy, as in
dbhandle.query(JobHistory.job_id.label('job_id'),
JobHistory.created.label('start_t'),
JobHistory.status.label('status'),
func.max(JobHistory.created).over(partition_by = JobHistory.job_id,
order_by=JobHistory.created,
rows=(1,3)
).label('end_t'))
.join(Job)
.join(Task)
.filter(JobHistory.status.in_([copy_start_status,'running','Running']))
.filter(JobHistory.job_id == Job.job_id)
.filter(Job.task_id == Task.task_id)
.filter(Task.campaign_id == campaign_id)
I find I cannot ask for "BETWEEN 1 FOLLOWING and 3 FOLLOWING", becuase the
code insists the first part of
the range must be none, zero or negative, and the second part must be
none, zero, or positive. -- thus
I seem to always have to give a range that spans the current item -- the
start of the range has to always be before
the current row (or equal to it) and the end of the range has to always be
at or after the current row.
Why the sign restrictions?!?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
