Search code examples
sqlmysqlsubquerywindow-functions

Calculate Weekly Metrics of a Daily Array in mySQL

I couldn't find a solution to this anywhere. I have a table with daily data having four columns with floating values. See picture here

Example

I want to calculate a weekly price such that the Open price is the price from the start of the week ie, date_sub(running_date, Interval 5 workday) and the High and Low are the highs and lows over those last 5 workdays. The close being the last close (so nothing to calc).

This is the calculation of the weekly price data as shown here

Weekly Price Defintion

I imagine it works something like this here for a single stock

SELECT
    *,
    ( SELECT max( High ) FROM dat WHERE stock = "AAPL" AND `Date` >= date_sub( Curdate( ), INTERVAL 8 DAY ) ) AS High 
FROM
    dat 
WHERE
    stock = "AAPL" 
ORDER BY
    `Date` DESC

but where Curdate() is actually the Date of the table 'dat' and is changing in every row. This here only adds the weekly for the current date.

As a result I want the four columns wOpen, wHigh, wLow, wCLose as shown here

Result Table

Solution

  • This looks like window functions over weekly partitions :

    select d.*,
    first_value(open) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wOpen,
    max(high)         over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wHigh,
    min(low)          over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wLow,
    last_value(close) over(partition by stock, yearweek(tradeDate, 1) order by tradeDate) as wClose
from dat d

    The trick is to partition your data by stock and by trade week; yearweek(tradeDate, 1) gives you the year/week for the given date, based on weeks that start on Monday (as inferred from your sample data).

    The rest is just windowing: the first opening and last closing values can be accessed with first_value() and last_value, and the window min/max give you the running minium and maximum.

    If you want to restrict the results to a specific date range, you can just add a where clause to the query.

    If you are running MySQL 5.x, where window functions are not available, one alternative uses a couple of correlated subqueries. This will probably be far less efficient if you have a lot of rows to process:

    select d.*,
    (
        select open 
        from dat d1 
        where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
        order by d1.tradeDate limit 1
    ) as wOpen,
    (
        select max(high)
        from dat d1 
        where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
    ) as wHigh,
    (
        select min(low)
        from dat d1 
        where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
    ) as wLow,
    (
        select close
        from dat d1 
        where d1.stock = d.stock and d1.tradeDate <= d.tradeDate and d1.tradeDate >= d.tradeDate - interval weekday(d.tradeDate) day
        order by d1.tradeDate desc limit 1
    ) as wClose
from dat d