Skip to Main Content
  • Questions
  • Need help in formulating query to fetch previous quote times

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Bhavesh.

Asked: June 19, 2018 - 3:59 am UTC

Last updated: June 20, 2018 - 2:34 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

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

and Chris said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Just superb. Tons of thanks

Bhavesh, June 21, 2018 - 12:54 am UTC

Many a thanks Chris for the most valuable insights. Let me adhere to what you have suggested. Will generate data & apply this solution. Will reach out to you again if there are further question. Tons of thanks again for such valuable insights.

using interval data types

Rajeshwaran, Jeyabal, June 21, 2018 - 2:46 pm UTC

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;


Just to the variation on the theme - the above sql can also be done using INTERVAL datatypes - something like this.

demo@ORA12C> select q.* ,
  2         first_value ( price ) over (
  3           partition by symbol
  4           order by quote_datetime
  5           range between (5/1440) preceding and (1/86400) preceding
  6         ) min_5 ,
  7        first_value (price) over(
  8             partition by symbol
  9             order by quote_datetime
 10             range between interval '5' minute preceding and
 11                     interval '1' second preceding ) as using_interval
 12  from   quotes q;

SYMBOL     QUOTE_DATETIME               PRICE      MIN_5 USING_INTERVAL
---------- ----------------------- ---------- ---------- --------------
ACME       12-JUN-2018 12:00:00 am        100
ACME       14-JUN-2018 12:00:00 am        110
ACME       19-JUN-2018 12:00:00 am         95
ACME       19-JUN-2018 12:00:00 pm         87
ACME       19-JUN-2018 11:55:00 pm        105
ACME       20-JUN-2018 12:00:00 am        125        105            105

6 rows selected.

demo@ORA12C>

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.