Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

extract a one minute forex tick into a new table

Posted on 2011-09-18
4
Medium Priority
?
368 Views
Last Modified: 2012-06-27
Hi,

I have a table with a raw set of forex data. I am trying to change the granularity to 1 minute ticks, instead of variable ticks, sometimes multiple ticks per second. I want this data in a new table. I want the query to be robust enough that it will work (or fail to work gracefully) so it can be cronjobbed or similiar, and reliably produce a clean/no result even if the source table returns null values on a particular select statement (when markets are closed, there is no source data, but the cronjob will probably still run)

I have made some headway into reducing the granularity but am finding that as this is my very first go at sql, reading the manual and searching for examples takes too long. My complete statement is below, after my question. I am using Navicat.

This is the first of a bunch of probably simple questions, with 500 points each, so it would be great - if you like this stuff keep an eye for more posts from me over the next few hours.

Question: This sql query works (unreliably), but takes time to run, probably because first extracts a data set to a temp table, then sorts the entire set using orderby to just extract a few items of data for each minute period. Is there a better overall approach that is any faster getting the 'open' 'high' 'low' 'close' (or o,h,l,c) for each minute, than the multiple select statements I have used?

Example results data showing table structure:-

tickid      ticker      tickdt      bidprice      askprice      lastprice      o      h      l      c      status      volume
8764058      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.95      1825.27      1825.27      1824.95      1824.95      2      100000
8764059      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764060      XAUUSD      9/19/2011 10:08:01      1824.9      1825.98      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764061      XAUUSD      9/19/2011 10:08:02      1825      1825.85      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764062      XAUUSD      9/19/2011 10:08:02      1825      1825.85      1825      1825.27      1825.27      1824.9      1825      2      100000
8764063      XAUUSD      9/19/2011 10:08:03      1825.03      1825.85      1825.03      1825.27      1825.27      1824.9      1825.03      2      100000
8764064      XAUUSD      9/19/2011 10:08:03      1824.98      1825.85      1824.98      1825.27      1825.27      1824.9      1824.98      2      100000
8764065      XAUUSD      9/19/2011 10:08:06      1824.98      1825.93      1824.98      1825.27      1825.27      1824.9      1824.98      2      100000

SQL Query:-

CREATE TEMPORARY TABLE tempticks (

    tickid INT(11) NOT NULL
    , ticker VARCHAR (10)
    , tickdt DATETIME
    , bidprice FLOAT
    , askprice FLOAT
    , lastprice FLOAT
    , o FLOAT
    , h FLOAT
    , l FLOAT
    , c FLOAT
    , status TINYINT (4)
    , volume VARCHAR (20)
) ENGINE=MEMORY;

/* xxx Select statements to create the data */

INSERT INTO tempticks
select * from xauusd
where tickdt <date_format(date_add(now()-120,INTERVAL 1 minute),'%y%m%d%h%i00')
and
tickdt >=date_format(now()-120,'%y%m%d%h%i00');

/* Just output the full 1minute table for */
SELECT * FROM tempticks;

/* XXX output just the data we want */

SELECT o As "open"
from tempticks
ORDER BY tickid ASC
limit 1;

SELECT c AS "close"
from tempticks
ORDER BY tickid DESC
limit 1;

SELECT h AS "maximum"
FROM tempticks
ORDER BY h DESC
LIMIT 1;

select l as "minimum"
from tempticks
ORDER BY l ASC
LIMIT 1;

select volume
from tempticks
ORDER BY tickid DESC
LIMIT 1;

/* Explicitly destroy the table */

DROP TABLE tempticks;
0
Comment
Question by:XenekStoehr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 36916788
you want to use an aggregate function but, I am not 100% sure what you are looking for. Does this do what you want?

SELECT tickid, ticker, tickdt, bidprice,      askprice,      lastprice,      o, h, l, c, status, volume
FROM table
WHERE <whatever time constraints you want>
GROUP BY ticker,tickdt


if this is not what you are looking for, can you give us the description of the original table?
0
 
LVL 1

Author Comment

by:XenekStoehr
ID: 37097641


tickid      ticker      tickdt      bidprice      askprice      lastprice      o      h      l      c      status      volume
8764058      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.95      1825.27      1825.27      1824.95      1824.95      2      100000

We are trying to reduce the volume of data from as many as a few ticks a second (variable), to one tick a minute (consistent), with an accurate open High Low Close calcuated from parts of the orginal data within the first minute period.

We want to feed the data into a new table.

And - we want to do it all in SQL :) :) :)

I think I need to become familar with using subquery statements, and perhaps do away with the temporary table?  
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 total points
ID: 37118885
you could use something like this:

SELECT ticker, 
       Date_format(tickdt, '%m/%d/%y %h:%i:00') tick_minute, 
       bidprice, 
       askprice, 
       lastprice, 
       (SELECT o 
        FROM   ticks 
        WHERE  Date_format(tickdt, '%m/%d/%y %h:%i:00') = 
               Date_format(t1.tickdt, '%m/%d/%y %h:%i:00') 
        ORDER  BY tickid ASC 
        LIMIT  1)                               o, 
       Max(h)                                   h, 
       Min(l)                                   l, 
       (SELECT c 
        FROM   ticks 
        WHERE  Date_format(tickdt, '%m/%d/%y %h:%i:00') = 
               Date_format(t1.tickdt, '%m/%d/%y %h:%i:00') 
        ORDER  BY tickid DESC 
        LIMIT  1)                               o, 
       status, 
       SUM(volume) 
FROM   ticks t1 
GROUP  BY tick_minute 

Open in new window

0
 
LVL 1

Author Closing Comment

by:XenekStoehr
ID: 37142396
This gives me substantial amounts of assistance, and will almost certainly help resolve the issue. I will reopen another question regarding specific parts of your script if I have problems or fail to understand how it works.

Thanks Mankowitz!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question