How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows
<p>I have a table from MySQL called References with 20 columns and 20 rows:</p>

<pre><code>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).
</code></pre>

<p>The data is updated daily.</p>

<p>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.  </p>

<p>Each row corresponds to one day's data.
I am using MySQL Workbench.</p>

<p>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.</p>