I am using mysql to receive stock market rate data, and I have a rate
feed which tells me when the rate has changed. I input the data as it
comes in, into a mysql database that has both a unix_timestamp( ) field
and the rate field. Neither field is indexed.
What I need to do is calculate the Moving Average by taking the rate
from 30 seconds, 60, 90, etc seconds ago. Currently I am using the
humungous sub query select statement below. Are there any alternatives?
Any help would be greatly appreciated.
Matt Fong
select ((select bid from eurusd WHERE timestamp<=unix _timestamp() ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-30 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-60 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-90 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-120 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-150 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-180 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-210 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-240 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-270 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-300 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-330 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-360 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-390 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-420 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-450 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-480 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-510 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-540 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-570 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-600 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-630 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-660 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-690 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-720 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-750 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-780 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-810 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-840 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix _timestamp()-870 ORDER
BY timestamp DESC LIMIT 1)) / 30 as a;
2  2609 
>I am using mysql to receive stock market rate data, and I have a rate feed which tells me when the rate has changed. I input the data as it
comes in, into a mysql database that has both a unix_timestamp( ) field
and the rate field. Neither field is indexed.
It seems to me you could get a big performance boost indexing the
timestamp field.
What I need to do is calculate the Moving Average by taking the rate
from 30 seconds, 60, 90, etc seconds ago. Currently I am using the
humungous sub query select statement below. Are there any alternatives?
Any help would be greatly appreciated.
Depending on how you need to handle missing or duplicate reports, you
might be able to do:
select avg(bid) from eurusd where timestamp between unix_timestamp( ) -870
and unix_timestamp( );
This presumes you've got a report coming in about every 30 seconds, and
from your description, I'm not sure that's your setup.
Gordon L. Burditt
Gordon,
You are correct, there is no guarantee I get a quote every 30 seconds,
it can come in 3 times a second or only once every 60 seconds, I just
need a way to get the bid at any second of the day.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Will |
last post by:
On the subject of Data Warehouses, Data Cubes & OLAP….
I would like to speak frankly about Data Warehouses, Data Cubes and
OLAP (on-line analytical processing). Has it dawned on anyone else
that these buzz words were created by some geek who decided to take a
stab at marketing? Knowing that to the backwoods manager who knows
little of technology that new innovative names for old concepts would
help to sale their products.
I mean...
|
by: Sturnoff Megantic |
last post by:
I have columns called repeat_date and repeat_month and I would like to
use the info within them in my WHERE clause as part of a
UNIX_TIMESTAMP().
Essentially, I would like to say something like:
SELECT * FROM table WHERE
UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) > $date_start
AND UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) <
$date_end
|
by: meltedown |
last post by:
Why does this return 0 ?
SELECT UNIX_TIMESTAMP('20051001');
It should be the unix timestamp of that date.
|
by: rottytooth |
last post by:
A general data design question:
We have data which changes every week. We had considered seperating
historical records and current records into two different tables with
the same columns, but thought it might be simpler to have them all
together in one table and just add a WeekID int column to indicate
which week it represents (and perhaps an isCurrent bit column to make
querying easier). We have a number of tables like this, holding...
|
by: Erwin |
last post by:
I have a work assignment in which I have to put a historical archive
within access which can be used for trendlines etc. It contains data
about month, service percentages and numbers.
Within a period these data are no longer used by the access database,
but still have to be stored within this historical archive so that the
information can be recalled each time a manager asks about the
historical data and the trendline to compare it with...
| | |
by: Teemu Keiski |
last post by:
Hi,
I have following type of scenario (also explained here
http://blogs.aspadvice.com/joteke/archive/2005/01/10/2196.aspx )
We have problematic web server (wink2 Standard, 1.5GB of physical memory,
SQL 2000 in same box, framework 1.0 and 1.1 installed, apps use mainly 1.0)
whose aspnet_wp.exe's memory consumption increases slowly but surely, leading
to process restart eventually and then again recollecting memory etc etc
|
by: igendreau |
last post by:
I need to have a form where my users can enter job info. One thing
they need to enter is "Regular Hours". What I need to do is then
calculate "Regular Cost" which =Regular Hours x Regular Rate which is
maintained in a "Rates" table.
The tricky part is rates can change. But when they do, I need my old
entries to maintain old pricing. So I can't just setup a query that
calculates Regular Cost when the report opens. Otherwise it'll...
|
by: Martin Hughes |
last post by:
Hi guys,
I was wondering if anyone could give me some advice.
I am looking to develop an ASP.NET application that will enable several workstations to access real time telemetry data received from a vehicle on a track.
The data flow would be as follows:
|-- Workstation 1
|
by: rds80 |
last post by:
In the xml document below, I would like to retrieve the distinct
attributes for the element '<Bal>'. However, I haven't had any
success. Here is what I have so far:
<TRANS>
<TRAN TRAN_DESC_CD="ACRT" TRAN_DESC="Actual Rate">
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
