Generic stored procedure to lag a table column

Question

I need to calculate returns at different frequencies. In order to do so, I would like to be able to lag the values in a column by k units. While I have found different specific solutions, I have not been able to make a general stored procedure (most likely due to my inexperience with mysql). How could I best do this?

I have a table with multiple columns, amongst which columns containing info on:

• ID
• Date
• Price

The end result should be a table with all the original columns, plus a column containing the lagged values of Price.

To keep the procedure general, I could imagine the procedure would take the table name, necessary column names (e.g. ID, Date, Price), and number of lags k as input, and append a column to the table.

Answer 1

You can do what you want with a correlated subquery. Here is an example:

select t.*,
       (select t2.price
        from <tablename> t2
        where t2.date < t.date
        order by date
        limit 1 offset 1  -- change the offset for a bigger lag
       ) as price_lag_1
from <tablename> t;

Your desire to create a generic stored procedure is not very SQL-y. MySQL doesn't support table-valued functions, so you wouldn't be able to use the resulting table as an actual table.

If you want to put this in a stored procedure that is generic, you will need dynamic SQL to construct the SQL statement, using the particular table and columns that you pass in.

Instead, I would suggest that you simply learn how to express what you want as a query. If you have multiple tables with the same structure, then you may want to revisit your data model. Have multiple similar tables is often an example of an entity being inappropriately spread across too many tables.