Select the latest recored in each 2-minute interval in MySQL

Question

I want to read about 8000 files, each containing the daily stock prices of a distinct stock, into a single table and select the latest price in each 2-minute interval and write a Null if no record available in an interval. My idea is add a column called bucketNumber to indicate which interval the record falls into, create another table containing one column of values 1, 2, ..., 195 repeating 8000 times and then joining the two tables. At last select the record with largest timestamps for records with the same bucketNumber.

Is this a good way to do the job? If it is, then how to efficiently generate a table with one column of values 1, 2, ..., 195 repeating 8000 times.

score 0 · Answer 1 · answered Dec 31 '12 at 04:12

Here's a query that will return you a column of integer values from 1 to 8000

SELECT thousands.d*1000 + hundreds.d*100 + tens.d*10 + ones.d + 1 AS num
  FROM ( SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
       ) ones
 CROSS      
  JOIN ( SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
       ) tens
 CROSS
  JOIN ( SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
       ) hundreds
 CROSS
  JOIN ( SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
       ) thousands
HAVING num <= 8000
ORDER BY num

score 0 · Answer 2 · answered Dec 31 '12 at 05:14

Seems like a stored procedure would be the easiest approach. Just loop through each 2 minute interval and select the price from the record having the maximum time in the interval. You could include arguments for the start and end time, which would provide a more general solution.

Select the latest recored in each 2-minute interval in MySQL

2 Answers2