0

I apologize if this question has been asked before. I tried to do searches but may not have been using the same wording as others so my searches came up with nothing.

I have a table of just over 6000 rows and I'm trying to do an average of the prior n number of rows of a particular column. In this case, the closing price of a FOREX currency pair. (I know I could use the PHP::Trader but I don't have access to install it on my server.)

I've tried :

SELECT *, (SELECT AVG(close) 
    FROM `2015_EURUSD_60min` 
    WHERE (id > (id - 20) AND id <= id)
) 
FROM `2015_EURUSD_60min`

This generated the same number for every row and I'm not sure why. Every value in the id column is sequential. I cleaned up the data to make sure of that.

What did I do wrong in my SELECT statement?

Drew
  • 24,851
  • 10
  • 43
  • 78
j_allen_morris
  • 559
  • 2
  • 11
  • 26
  • Thanks Ghost for cleaning up the formatting. I'll remember that for any future questions. – j_allen_morris Sep 06 '16 at 00:57
  • id is always > id-20 and id<=id – Drew Sep 06 '16 at 01:05
  • in other words, it just does an avg of close for the whole table – Drew Sep 06 '16 at 01:06
  • I'm basically trying to get the average of a group, but doing a GROUP BY FLOOR(id/20) , I want each row to be the reference point, so row 20 would get the prior 20 rows average (rows 1 through 20) and row 21 would get average of rows 2 through 21, and row 22 would show the average for rows 3 through 22, etc. – j_allen_morris Sep 06 '16 at 01:08
  • So how do i use the current row as a reference point and grab data relative to the current row? – j_allen_morris Sep 06 '16 at 01:08
  • Short of joining all 19 other rows. – j_allen_morris Sep 06 '16 at 01:09
  • I can do that for ya. But will wait and let someone else give it a crack – Drew Sep 06 '16 at 01:10
  • I can do the join as well, I know that it will not be efficient and there has to be a better way. I've been trying to figure out using variables as well. MySQL's way of using variables is a different way of thinking. – j_allen_morris Sep 06 '16 at 01:11
  • 1
    You also need to tell people the structure. And if you have id gaps. So they will know if they have to re-rank – Drew Sep 06 '16 at 01:12
  • I already addressed the issue of id gaps in the question itself. – j_allen_morris Sep 06 '16 at 01:14
  • Do you have a timestamp column which you could use to restrict to records you want to average? This would be much easier than trying to generate row numbers. – Tim Biegeleisen Sep 06 '16 at 01:16
  • I do have a unix_stamp column. – j_allen_morris Sep 06 '16 at 01:18
  • @Drew, I appreciate your help. If you need a break, go take a break. We appreciate your help, but don't do it to your detriment. – j_allen_morris Sep 06 '16 at 01:20
  • 1
    How many records are there? I saw your answer below stating it took 17 seconds. That's quite long. Are your tables properly indexed? There is also a way of doing direct indexing. Edit: ok 6000 that's not really a lot. Mysql can handle that. – Funk Forty Niner Sep 06 '16 at 02:00
  • For id 23, your query evaluates as `23 BETWEEN 3 AND 23`. For id 24, it evaluates as `24 BETWEEN 4 AND 24`. So that's what you did wrong; you didn't use a join ) – Strawberry Sep 06 '16 at 06:33

1 Answers1

0

I think I found an answer (although slow performance) to my question:

SELECT *, @s:=@s+1 serial_num, 
    (SELECT AVG(close) 
     FROM `2015_EURUSD_60min` 
     WHERE (id > (@s-20) AND id <= @s )) prior_id 
FROM `2015_EURUSD_60min`, (SELECT @s:=0) AS s
j_allen_morris
  • 559
  • 2
  • 11
  • 26
  • It took 17.0824 seconds to execute :( Not horrible. Most of the time in phpMyAdmin was rendering the text for displaying on the screen. – j_allen_morris Sep 06 '16 at 01:29