Solved

in MySQL, how can I reference a row that is prior to another one based on the primary index?

Posted on 2013-05-31
7
253 Views
Last Modified: 2013-06-21
I have a table, full_daily_data, that has as it's PRIMARY index the columns (symbol,trade_date).

I would like to be able to reference one row that immediately precedes another

Right now, I'm looking at something like this:

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY);

Open in new window


But that only works if there are no gaps in the dates, and there are.

instead of
    on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY);

Open in new window


Is there a way to join based on the sequence of the primary index being in date order?
0
Comment
Question by:cashonly
[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
  • 3
  • 3
7 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39211710
Well, you want the date from this query, don't you (note the DESC)

SELECT Trade_Date
FROM full_daily_data
ORDER BY Trade_DATE DESC
LIMIT 1
;

Open in new window


Would it be safe to do something like this:

SELECT @LATEST_TRADE_DATE:=Trade_Date
FROM full_daily_data
ORDER BY Trade_DATE DESC
LIMIT 1
;

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = @LATEST_TRADE_DATE
;

Open in new window



Looking at your scenario, I'm guessing this is pretty safe.  Only only have to worry if the select statement grabs a date and somehow a new date is inserted in the microsecond before the UPDATE statement runs.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39211726
It would be nice to combine these into one statement, like this, but MySQL doesn't let you do that. ;-(

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = (SELECT Trade_Date
          FROM full_daily_data
          ORDER BY Trade_DATE DESC
          LIMIT 1
    )
;

Open in new window

0
 

Author Comment

by:cashonly
ID: 39211907
nemws1,

I was thinking about trying the select in the update... sorry to hear it doesn't work.

I'm not sure if your first solution will work. I was looking to do an update of all the data in one shot and it looks like the update would be based on a fixed trade date, not a relative one.  It has to be based on the trade_date of the row that is receiving the "Prior_" data.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 250 total points
ID: 39211950
Ah I gotcha.  Well, there's still a solution.  You could create a FUNCTION that figures that out.  It won't be lightening fast, but it should work.  Something like this, I'm thinking:

DELIMITER $$

DROP FUNCTION IF EXISTS previous_trading_day$$

CREATE FUNCTION previous_trading_day (input_date DATE)
    RETURNS DATE
BEGIN
    DECLARE work_date DATE;
    SET work_date = NULL;
    
    SET work_date =
        (
         SELECT Trade_Date
         FROM full_daily_data
         WHERE Trade_Date < input_date
         ORDER BY Trade_DATE DESC
         LIMIT 1
         )
    ;

    RETURN work_date;
END $$

Open in new window


Then your update (try this out with a SELECT statement first, of course) would look something like this:

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = previous_trade_date(fdd2.Trade_Date)

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39212209
aside from the issue of aligning the correct dates, please notice that your joins require symbol in them... (it was an issue I attempted to point out in an earlier question but was too late)
update full_daily_data fdd1
inner join full_daily_data fdd2
on fdd1.symbol = fdd2.symbol
and fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY)
set
  fdd1.Prior_Open  = fdd2.Day_Open
, fdd1.Prior_High  = fdd2.Day_High
, fdd1.Prior_Low   = fdd2.Day_Low
, fdd1.Prior_Close = fdd2.Day_Close
;

Open in new window

see: http://sqlfiddle.com/#!9/cc5f0/1
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39212254
Perhaps that function also need to consider symbol? (in addition to the absolute necessity for symbol in the joins), to allow for exceptions to trading cycles in certain symbols if these occur. Like this:
update full_daily_data fdd1
inner join full_daily_data fdd2
on fdd1.symbol = fdd2.symbol
and fdd2.Trade_Date = previous_trade_date(fdd2.symbol, fdd2.Trade_Date)
set
  fdd1.Prior_Open  = fdd2.Day_Open
, fdd1.Prior_High  = fdd2.Day_High
, fdd1.Prior_Low   = fdd2.Day_Low
, fdd1.Prior_Close = fdd2.Day_Close
;

Open in new window

with an extra parameter in the function proposed earlier
DELIMITER $$

DROP FUNCTION IF EXISTS previous_trading_day$$

CREATE FUNCTION previous_trading_day (input_symbol VARCHAR, input_date DATE)
    RETURNS DATE
BEGIN
    DECLARE work_date DATE;
    SET work_date = NULL;
    
    SET work_date =
        (
         SELECT Trade_Date
         FROM full_daily_data
         WHERE symbol = input_symbol 
         AND Trade_Date < input_date
         ORDER BY Trade_DATE DESC
         LIMIT 1
         )
    ;

    RETURN work_date;
END $$

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258966
cashonly, is this question finished now?
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 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