title: How to best get daily SUM for minute-level data?
body: <p>I have a data set consisting of minute-by-minute data. My goal is to return minute-by-minute records, and add calculations that create sums of a certain field for the past 24 hours, counting back from each minute record.</p>
  
  <p>The query I have is the following:</p>
  
  <pre><code>SELECT main.recorded_at AS x, (SELECT SUM(precipitation) FROM data AS sub WHERE sub.host = main.host sub.recorded_at BETWEEN SUBTIME(main.recorded_at, '24:00:00') AND main.recorded_at) AS y FROM data AS main WHERE host = 'xxxx' ORDER BY x ASC;
  </code></pre>
  
  <p>Is there a more efficient way to write this query? I have tried, but failed, so far, using LEFT JOINS and different GROUP BYs.</p>
  
  <p>When I explain this query, I get the following:</p>
  
  <pre><code>1   PRIMARY main    ref host    host    767 const   4038    100.00  Using where; Using filesort
  2   DEPENDENT SUBQUERY  sub ref host,recorded_at    host    767 const   4038    100.00  Using where
  </code></pre>
  
  <p>In total, the query takes about 200 seconds to run with 8000 records, getting slower all the time. My goal is to get the aggregate 24-hour precipitation for each result, and somehow in under 2 seconds.</p>
  
  <p>Maybe I'm going about this the wrong way? I'm open to suggestions for other avenues to get the same result. :)</p>
  
  <p>Thanks!
  ~Mike</p>