Hi AskTom Team,
I have been a big fan of this site since 1999 around the time it came up.
First of all, again a big Thank you for your support to Oracle Community since past two decades. I have immensely benefited from this. This time around I have a design and query issue where I need your advise.
The problem statement is to find the price change / movement in last 1 hour, 24 hour and 7 days for a given ticker based on price received at various intervals from exchange.
Ticker name table will store ticker details.
Quote table store price for the ticker and will be fetched via API from exchange say every 5 minutes.
The crux is PriceChange table which needs to be updated with percentage price change for the ticker in last 1 hour, 24 hour and 7 days. Will need your expertise in
Since this is a new development, I am flexible with design of PriceChange table and if need be it can be amended for better query performance.
Following is the structure of table for your review and suggestion.
---- Table to store ticker name and other details
----- To be fetched from API
Create table TickerName (
id Number(32) Not Null,
name Varchar2(32),
symbol Varchar2(32),
webSlung Varchar2(32),
rank Varchar2(32),
circulatingSupply Number(30, 10),
totalSupply Number(30, 10),
maxSupply Number(30, 10),
CONSTRAINT TickerName_PK PRIMARY KEY (id)
)
---- Table to store price from exchange based on id, currency and timestamp (lastupdate)
----- To be fetched from API
Create table Quotes (
id Number(32),
currency Varchar2(32) Not Null,
exchange Varchar2(32) Not Null,
price Number(30, 10),
volume_24h Number(30, 10),
marketCap Number(30, 10),
lastUpdate date Not Null,
CONSTRAINT Quotes_FK1 FOREIGN KEY (id) REFERENCES TickerName(id)
)
---- Table to update percentage of price change in last 1 hour, 24 hours and 7 days
----- To be calculated by using SQL
Create table PriceChange {
id Varchar2(32),
lasUpdate date,
exchange Varchar2(32),
pctChg1hr Varchar2(32),
pctChg24hr Varchar2(32),
pcdChg7d Varchar2(32),
CONSTRAINT Quotes_PK PRIMARK KEY (id, lasUpdate)
CONSTRAINT PriceChange_FK1 FOREIGN KEY (id) REFERENCES TickerName(id),
CONSTRAINT PriceChange_FK2 FOREIGN KEY (lastUpdate) REFERENCES Quotes(lastUpdate),
}
I will appreciate if you can advise me on :
1. Design of PriceChange table
2. Help me with a query to fetch the percent price change for given time period (1 hour, 24 hour and 7 days).
Thanking you in advance.
Regards
Bhavesh
1. It's hard to comment without more details of what your business requirements are and the type of queries you expect to run.
But to me it looks like quotes and price_change should be one table. Every time you get a new price, insert a row. Don't update the existing quote. This will make it much easier to track changes over time.
2. Assuming you have a single table recording your quote history, you can use the analytic first_value to find the value N minutes/hours/days ago.
Using the windowing clause, you can get it to consider the rows in a specified time range. It will then return the first of these according to the sort.
For example, the following sorts the rows by quote_datetime. Then returns the price for the first row in the 7 day period before the current:
first_value ( price ) over (
order by quote_datetime range between 7 preceding and current row
)
Which in a complete query is:
create table quotes (
symbol varchar2(10),
quote_datetime date,
price integer
);
var dt varchar2(10);
exec :dt := to_char(sysdate, 'dd/mm/yyyy');
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy') - 7, 100);
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy') - 6, 110);
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy') - 1, 95);
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy') - 0.5, 87);
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy') - (5/1440), 105);
insert into quotes values ('ACME', to_date ( :dt, 'dd/mm/yyyy'), 125);
commit;
select q.* ,
first_value ( price ) over (
partition by symbol
order by quote_datetime
range between 7 preceding and current row
) dy_7,
first_value ( price ) over (
partition by symbol
order by quote_datetime
range between 1 preceding and current row
) dy_1,
first_value ( price ) over (
partition by symbol
order by quote_datetime
range between (5/1440) preceding and current row
) min_5
from quotes q;
SYMBOL QUOTE_DATETIME PRICE DY_7 DY_1 MIN_5
ACME 13-JUN-2018 00:00:00 100 100 100 100
ACME 14-JUN-2018 00:00:00 110 100 100 110
ACME 19-JUN-2018 00:00:00 95 100 95 95
ACME 19-JUN-2018 12:00:00 87 100 95 87
ACME 19-JUN-2018 23:55:00 105 100 95 105
ACME 20-JUN-2018 00:00:00 125 100 95 105
You can then use this to compare against the current price to calculate the change.
Note that this includes the value for the current row if the previous one is outside the specified range. You can exclude the current value by making the upper bound just before the current. For example the following cuts off one second before the current row (86,400 is the number of seconds in one day):
first_value ( price ) over (
order by quote_datetime range between 7 preceding and (1/86400) preceding
)
Using this you get null for most of the five minute intervals, as the previous quote was earlier than that:
select q.* ,
first_value ( price ) over (
partition by symbol
order by quote_datetime
range between (5/1440) preceding and (1/86400) preceding
) min_5
from quotes q;
SYMBOL QUOTE_DATETIME PRICE MIN_5
ACME 13-JUN-2018 00:00:00 100 <null>
ACME 14-JUN-2018 00:00:00 110 <null>
ACME 19-JUN-2018 00:00:00 95 <null>
ACME 19-JUN-2018 12:00:00 87 <null>
ACME 19-JUN-2018 23:55:00 105 <null>
ACME 20-JUN-2018 00:00:00 125 105
Which leads to interesting questions:
* How rigidly do you apply the previous 7 day/24 hour/5 minute limit? If the previous quote was 5 minutes, 1 second ago, should you use that for the 5 minute change? What about if the gap was 4 minutes, 59 seconds? When is the gap too big/small?
* What do you show when there is no row at the specified interval (e.g. due to market closure)? Do you go back to the previous quote? Or move to the next?
These are questions you'll need to ask of the data consumers. If you store something other than quotes at the precise time interval you're inspecting, it's worth storing the time of the previous quote too.