Databases
StackOverflow2013
Postsdbo
POHow to use subqueries in SQLAlchemy to produce a moving average?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to use subqueries in SQLAlchemy to produce a moving average?
    primarykey
    data
    text
    <p>My problem is that I want to retrieve both a list of measurements along with a moving average of those measurements. I can do that with this SQL statement (postgresql interval syntax):</p> <pre><code>SELECT time, value, ( SELECT AVG(t2.value) FROM measurements t2 WHERE t2.time BETWEEN t1.time - interval '5 days' AND t1.time ) moving_average FROM measurements t1 ORDER BY t1.time; </code></pre> <p>I want to have the SQLAlchemy code to produce a similar statement to this effect. I currently have this Python code:</p> <pre><code>moving_average_days = # configureable value, defaulting to 5 t1 = Measurements.alias('t1') t2 = Measurements.alias('t2') query = select([t1.c.time, t1.c.value, select([func.avg(t2.c.value)], t2.c.time.between(t1.c.time - datetime.timedelta(moving_average_days), t1.c.time))], t1.c.time &gt; (datetime.datetime.utcnow() - datetime.timedelta(ndays))). \ order_by(Measurements.c.time) </code></pre> <p>That however, generates this SQL:</p> <pre><code>SELECT t1.time, t1.value, avg_1 FROM measurements AS t1, ( SELECT avg(t2.value) AS avg_1 FROM measurements AS t2 WHERE t2.time BETWEEN t1.time - %(time_1)s AND t1.time ) WHERE t1.time &gt; %(time_2)s ORDER BY t1.time; </code></pre> <p>That SQL has the subquery as part of the FROM clause where it cannot have scalar access to the column values of the top-level values, i.e. it causes PostgreSQL to spit out this error:</p> <pre><code>ERROR: subquery in FROM cannot refer to other relations of same query level LINE 6: WHERE t2.time BETWEEN t1.time - interval '5 days' AN... </code></pre> <p>What I would thus like to know is: how do I get SQLAlchemy to move the subquery to the SELECT clause?</p> <p>Alternatively another way to get a moving average (without performing a query for each (time,value) pair) would be an option.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGiel
    1. USGiel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to use subqueries in SQLAlchemy to produce a moving average?
      1. USmikerobi
      1. VTFavorite
    2. VO
      singulars
      1. POHow to use subqueries in SQLAlchemy to produce a moving average?
      1. USGreg Case
      1. VTFavorite
    3. VO
      singulars
      1. POHow to use subqueries in SQLAlchemy to produce a moving average?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload