Mike,
Thanks for the patch! I won't get a chance to try it 'till probably Monday, but it looks good. I'll send a note when I've had a chance to try it. -- Marc W. Mengel Computer Services Senior Developer Data Management and Applications Fermi National Accelerator Laboratory 630 840 8256 office www.fnal.gov <https://email.fnal.gov/owa/redir.aspx?C=JkVsWjSyHEuWV2kFJWNPyC9OyMKf7dEIp_yxPk9yfhfadE3mdLfI6T8Ztf8BVaJjzoIe8oJ5H0M.&URL=http%3a%2f%2fwww.fnal.gov> Connect with us! Newsletter<https://email.fnal.gov/owa/redir.aspx?C=-29AtpIe40SAvGF4aiNr86KvVjap7dEIuxIdg8Q5bYa9sT7QTFVhxhVnl8zwXkEuBSuAQLRkEfg.&URL=http%3a%2f%2fwww.fnal.gov%2fpub%2ftoday> | Facebook<https://email.fnal.gov/owa/UrlBlockedError.aspx> | Twitter<https://email.fnal.gov/owa/redir.aspx?C=-29AtpIe40SAvGF4aiNr86KvVjap7dEIuxIdg8Q5bYa9sT7QTFVhxhVnl8zwXkEuBSuAQLRkEfg.&URL=https%3a%2f%2ftwitter.com%2fFermilabToday> ________________________________ From: sqlalchemy@googlegroups.com <sqlalchemy@googlegroups.com> on behalf of Mike Bayer <mike...@zzzcomputing.com> Sent: Friday, August 25, 2017 8:44:50 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] window function madness... On Thu, Aug 24, 2017 at 4:22 PM, Marc Mengel <marc.men...@gmail.com> wrote: > > 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?!? well, I read through https://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS over and over and over trying to get my head around some syntax that could capture everything I could possibly imagine this would do without too much verbosity, and that's what we came up with. issue is at https://bitbucket.org/zzzeek/sqlalchemy/issues/4053/support-range-between-x-following-and-y and you can try out the patch at https://gerrit.sqlalchemy.org/changes/487/revisions/fb55270835b18e6ba942b74ee72719ce0fc4d062/archive?format=tgz please verify it does everything you need thanks! > > > > -- > 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. -- 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. -- 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.
