CODEWITHSUNDEEP
mysql
mmdemirbas
mmdemirbas

Reputation: 9158

MySql - Insert rows for skipped numbers (interpolation)

I have a table like this (simplified):

+------+--------+
| tick | value  |
+------+--------+
|    1 |     10 |
|    2 |     20 |
|    5 |     50 |
|    7 |     10 |
|   10 |     85 |
+------+--------+

I want to generate another table which includes all numbers from min(tick) to max(tick) as tick values:

+------+--------+
| tick | value  |
+------+--------+
|    1 |     10 |           * already exists
|    2 |     20 |           * already exists
|    3 |     30 | = 20 + 10
|    4 |     40 | = 30 + 10
|    5 |     50 | = 40 + 10 * already exists
|    6 |     30 | = 50 - 20
|    7 |     10 | = 30 - 10 * already exists
|    8 |     35 | = 10 + 25
|    9 |     60 | = 35 + 25
|   10 |     85 | = 60 + 25 * already exists
+------+--------+

To be clear,

  1. I have non-consecutive tick values and want to insert missing ticks.
  2. I want to do this to another table, so original table won't be modified.
  3. values of missing ticks increases/decreases as equal steps from previous existing tick to next existing tick. As a psudo-formula:
    increment = (next_value - current_value) / (next_tick - current_tick)

How to manage this?

Upvotes: 3

Views: 226

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65314

This stored procedure will do what you want, populating the table allticks

CREATE PROCEDURE `tickster`()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE oldticks INT DEFAULT 0; -- initial ticks here
    DECLARE oldvalue INT DEFAULT 0; -- initial value here
    DECLARE newticks INT DEFAULT 0;
    DECLARE newvalue INT DEFAULT 0;
    DECLARE t INT DEFAULT 0;
    DECLARE v INT DEFAULT 0;
    DECLARE delta INT DEFAULT 0;

    DECLARE existing CURSOR FOR SELECT ticks,value FROM ticks ORDER BY ticks;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;

    OPEN existing;

    nexttick: LOOP
        -- Get next value or break
        FETCH existing INTO newticks,newvalue;
        IF finished>0 THEN
            LEAVE nexttick;
        END IF;

        -- initialize a loop to cover the inexistant ticks
        SET t=oldticks+1;
        SET v=oldvalue;
        SET delta=(newvalue-oldvalue)/(newticks-oldticks);

        -- run the loop
        nextstep: LOOP
            IF t>=newticks THEN
                LEAVE nextstep;
            END IF;
            -- calculate new interpolated value and write it to the table
            SET v=v+delta;
            INSERT INTO allticks SET ticks=t, value=v;
            SET t=t+1;
        END LOOP;

        -- write the existing value to the table
        INSERT INTO allticks SET ticks=newticks, value=newvalue;

        -- make new dataset the old dataset
        SET oldticks=newticks;
        SET oldvalue=newvalue;

    END LOOP;
END

Add the initial ticks and values where indicated, they are needed, if there is no row with ticks=1

Upvotes: 4

srini.venigalla
srini.venigalla

Reputation: 5145

First you create a Number table 

Create Table ALL_NUMBERS (
   N Integer
)

Alter table ALL_NUMBERS add primary key (n);

Then fill the number table from 1 to a large number, like 1 Million

Then Left Join your data table with this number table and fill the gaps..

Upvotes: 0

Related Questions