Databases
StackOverflow2013
Postsdbo
POMySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery
    primarykey
    data
    text
    <p>I have a table that looks something like this:</p> <pre><code>DataTable +------------+------------+------------+ | Date | DailyData1 | DailyData2 | +------------+------------+------------+ | 2012-01-23 | 146.30 | 212.45 | | 2012-01-20 | 554.62 | 539.11 | | 2012-01-19 | 710.69 | 536.35 | +------------+------------+------------+ </code></pre> <p>I'm trying to create a view (call it <code>AggregateView</code>) that will, for each date and for each data column, show a few different aggregates. For example, <code>select * from AggregateView where Date = '2012-01-23'</code> might give:</p> <pre><code>+------------+--------------+----------------+--------------+----------------+ | Date | Data1_MTDAvg | Data1_20DayAvg | Data2_MTDAvg | Data2_20DayAvg | +------------+--------------+----------------+--------------+----------------+ | 2012-01-23 | 697.71 | 566.34 | 601.37 | 192.13 | +------------+--------------+----------------+--------------+----------------+ </code></pre> <p>where <code>Data1_MTDAvg</code> shows <code>avg(DailyData1)</code> for each date in January prior to Jan 23, and <code>Data1_20DayAvg</code> shows the same but for the prior 20 dates in the table. I'm no SQL ninja, but I was thinking that the best way to do this would be via subqueries. The MTD average is easy:</p> <pre><code>select t1.Date, (select avg(t2.DailyData1) from DataTable t2 where t2.Date &lt;= t1.Date and month(t2.Date) = month(t1.Date) and year(t2.Date) = year(t1.Date)) Data1_MTDAvg from DataTable t1; </code></pre> <p>But I'm getting hung up on the 20-day average due to the need to limit the number of results returned. Note that the dates in the table are irregular, so I can't use a date interval; I need the last twenty records in the table, rather than just all records over the last twenty days. The only solution I've found is to use a nested subquery to first limit the records selected, and then take the average.</p> <p>Alone, the subquery works for individual hardcoded dates:</p> <pre><code>select avg(t2.DailyData1) Data1_20DayAvg from (select DailyData1 from DataTable where Date &lt;= '2012-01-23' order by Date desc limit 0,20) t2; </code></pre> <p>But trying to embed this as part of the greater query blows up:</p> <pre><code>select t1.Date, (select avg(t2.DailyData1) Data1_20DayAvg from (select DailyData1 from DataTable where Date &lt;= t1.Date order by Date desc limit 0,20) t2) from DataTable t1; ERROR 1054 (42S22): Unknown column 't1.Date' in 'where clause' </code></pre> <p>From searching around I get the impression that you can't use correlated subqueries as part of a <code>from</code> clause, which I think is where the problem is here. The other issue is that I'm not sure if MySQL will accept a view definition containing a <code>from</code> clause in a subquery. Is there a way to limit the data in my aggregate selection without resorting to subqueries, in order to work around these two issues?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMike Sherrill 'Cat Recall'
    1. USB. Striegel
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery
      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