Connecting R to SQL Server is easy on Windows but is kinda challenge on Mac, so I figured out that doing time series analysis on SQL Server itself is also an option. There is a wide variety of so-called window functions in T-SQL which allow one to calculate things on a row-per-row basis without using cursors.

For example, say you have a stock history with the usual closing prices, trading date and such. How to calculate a moving average? Without window functions you’d have to inner-join or cursors things but in the more recent versions of SQL Server it’s just;

select id, tickersymbol, TradeDate,
    Avg(Volume) over(partition by tickersymbol order by tradeDate ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) as MA
    from StockHistory
    order by TickerSymbol, TradeDate

with a moving average over five rows. You can peek at data in the past or in the future like so

select id, tickersymbol, TradeDate,
    Lag(Volume, 2) over(partition by tickersymbol order by tradeDate) as Past,
        Lead(Volume, 2) over(partition by tickersymbol order by tradeDate) as Past,
    from StockHistory
    order by TickerSymbol, TradeDate

which looks 2 days ahead or two days behind. Sure, this is nowhere near the statistical power of R (no ARIMA simulation, no plotting either) but we’ll have to wait for the Revolution Analytics integration in SQLServer for that.