Mysql – How to assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

MySQL

I have a table from MySQL called References with 20 columns and 20 rows:

CREATE TABLE bop_ppy 
( start_time DATETIME (6)
, end_time DATETIME (6)
, high DECIMAL (5,2)
, low DECIMAL  (5,2)
, close DECIMAL (5,2)
, vol DECIMAL (5,2)
, vol_avg DECIMAL (5,2)
, range_ DECIMAL (5,2)
, poc_ DECIMAL (5,2)
, va_h DECIMAL (5,2)
, va_l DECIMAL (5,2)
, va_range DECIMAL (5,2)
, tpot INT
, tpo_ab INT
, tpo_bl INT
, sf DECIMAL (5,2)
, tff DECIMAL (5,2)
, rf INT
, vty DECIMAL (5,2)
, dists INT);

The data is updated daily.

I need to compute the difference between consecutive rows, for each column, (except the start_time and end_time columns, which are incremented by 1 day) such that if the value on day 2 is higher than the value on day 1, we assign +1 to the difference; if the value on day 2 is less than the value on day 1, we assign -1 to that difference; if the value on day 2 is equal to the value on day 1, we assign 0 to that difference. The idea is to generate a new table/view that shows only the tallies, instead of the original values.

Each row corresponds to one day's data. I am using MySQL Workbench.

I have tried to first do a self join of the table and then assign dummy variables to the difference between consecutive rows, but so far this has not worked.I don't know how to make this work anymore. Please could someone help.

Best Answer

You can use user defined variables for this. You can store the value of the current row's column in a variable, when the next row is processed, the variable still holds the value of the "previous" row.

Here's how it works.
You need to initialize the variables you need first. You can do this in a subquery.

SELECT
* 
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery

Like I already said, the rows are processed one after another. In a relational database there is no order, unless you specify it. This is very important here, when you don't specify an order, you might not get the previous row, but a random or simply wrong row.

SELECT
* 
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

In the SELECT clause the order is also very important. When you assign first and then do your calculations, the variables actually hold the value of the current row, not the previous one.

SELECT 
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

Okay, now we have a problem. The @prev_value value is NULL, when the very first row is processed. There are two ways to prevent this.

First, use the COALESCE() function to replace the NULL value with something else or the column you're comparing to.

CASE WHEN COALESCE(@prev_value, your_column) < your_column THEN 1
     WHEN COALESCE(@prev_value, your_column) > your_column THEN -1
     ELSE 0 END AS your_comparison

In your case that's actually not necessary, since it would result in the ELSE part anyway, which is probably what you want. I'm just mentioning it.

Second, assign the value of your choice in the initializing subquery.

SELECT 
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := your_column FROM your_table ORDER BY the_column_that_defines_the_order LIMIT 1) var_init_subquery
ORDER BY the_column_that_defines_the_order

When you want the columns to be in a different order, you can just put above query in a subquery.

Related Question