CODEWITHSUNDEEP
mysqllagwindow-functionslead
Michael
Michael

Reputation: 111

Fill in the gaps between values in table - MySQL

I have a table (MySQL)

UserID | CreationTS | Type         | Value    | Bonus Value    
259275 | 2012-08-01 | Deposit      | -------- | NULL
259275 | 2012-08-02 | BonusApplied |      175 | 175
259275 | 2012-08-03 | TradeOrder   | -------- | 175
259275 | 2012-08-06 | TradeOrder   | -------- | 175
259275 | 2012-08-10 | BonusApplied |      180 | 180
259275 | 2012-08-11 | TradeOrder   | -------- | 180
259275 | 2012-08-12 | TradeOrder   | -------- | 180
259275 | 2012-08-15 | TradeOrder   | -------- | 180
259275 | 2012-08-17 | BonusApplied |      200 | 200
259275 | 2012-08-18 | TradeOrder   | -------- | 200
259681 | 2012-08-01 | Deposit      | -------- | NULL
259681 | 2012-08-02 | BonusApplied |      175 | 175
259681 | 2012-08-03 | TradeOrder   | -------- | 175
259681 | 2012-08-06 | TradeOrder   | -------- | 175
259681 | 2012-08-10 | BonusApplied |      180 | 180
259681 | 2012-08-11 | TradeOrder   | -------- | 180
259681 | 2012-08-12 | TradeOrder   | -------- | 180
259681 | 2012-08-15 | TradeOrder   | -------- | 180
259681 | 2012-08-17 | BonusApplied |      200 | 200
259681 | 2012-08-18 | TradeOrder   | -------- | 200

I need to fill in the gaps in the VALUE filled, between BonusApplied type, per UserID, based on the first Value per user and BonusApplied. The final value is in the Bonus Value column. That's what I need. If there is a solution based on @variables instead of the JOIN, it would be great.

Upvotes: 1

Views: 456

Answers (3)

Levi Haskell
Levi Haskell

Reputation: 853

This is similar to the answer by Strawberry but works for both, ascending and descending bonus values:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,seq INT NOT NULL
,value INT NULL
,PRIMARY KEY(id,seq)
);

INSERT INTO my_table VALUES
(101, 1,NULL),
(101, 2,175),
(101, 4,NULL),
(101, 7,NULL),
(101, 9,180),
(101,11,NULL),
(102, 2,NULL),
(102, 3,175),
(102, 4,NULL),
(102, 7,NULL),
(102, 9,150),
(102,12,NULL);

SELECT
    x.*,
    (SELECT value FROM my_table
        WHERE id = x.id
        AND seq = MAX(y.seq)) filled
FROM my_table x
LEFT JOIN my_table y
    ON y.id = x.id
    AND y.seq <= x.seq
    AND y.value IS NOT NULL
GROUP BY
    x.id, x.seq

The result:

+-----+-----+-------+------+
| id  | seq | value | i    |
+-----+-----+-------+------+
| 101 |   1 |  NULL | NULL |
| 101 |   2 |   175 |  175 |
| 101 |   4 |  NULL |  175 |
| 101 |   7 |  NULL |  175 |
| 101 |   9 |   180 |  180 |
| 101 |  11 |  NULL |  180 |
| 102 |   2 |  NULL | NULL |
| 102 |   3 |   175 |  175 |
| 102 |   4 |  NULL |  175 |
| 102 |   7 |  NULL |  175 |
| 102 |   9 |   150 |  150 |
| 102 |  12 |  NULL |  150 |
+-----+-----+-------+------+

How it works:

  1. The self join adds all non-empty preceding rows with the same id to each original row via y.seq <= x.seq and y.value IS NOT NULL expressions
  2. The GROUP BY x.id, x.seq clause allows us to select the highest preceding seq number via MAX(y.seq)
  3. The select list sub-query looks up the value of the most immediate non-empty preceding row within the same id group

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Here's a JOIN type solution:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,seq INT NOT NULL
,value INT NULL
,PRIMARY KEY(id,seq)
);

INSERT INTO my_table VALUES
(101, 1,NULL),
(101, 2,175),
(101, 4,NULL),
(101, 7,NULL),
(101, 9,180),
(101,11,NULL),
(102, 2,NULL),
(102, 3,175),
(102, 4,NULL),
(102, 7,NULL),
(102, 9,200),
(102,12,NULL);

SELECT x.*
     , MAX(y.value) i 
  FROM my_table x 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.seq <= x.seq 
 GROUP 
    BY x.id,x.seq;
+-----+-----+-------+------+
| id  | seq | value | i    |
+-----+-----+-------+------+
| 101 |   1 |  NULL | NULL |
| 101 |   2 |   175 |  175 |
| 101 |   4 |  NULL |  175 |
| 101 |   7 |  NULL |  175 |
| 101 |   9 |   180 |  180 |
| 101 |  11 |  NULL |  180 |
| 102 |   2 |  NULL | NULL |
| 102 |   3 |   175 |  175 |
| 102 |   4 |  NULL |  175 |
| 102 |   7 |  NULL |  175 |
| 102 |   9 |   200 |  200 |
| 102 |  12 |  NULL |  200 |
+-----+-----+-------+------+

Upvotes: 1

James Scott
James Scott

Reputation: 1084

Try this:

CREATE TABLE bonusTable (userID INT UNSIGNED, CreationTs DATE, `Type` CHAR(32), `Value` INT UNSIGNED, BonusValue INT);

INSERT INTO bonusTable VALUES
(259275, '2012-08-01', 'Deposit', NULL, NULL),
(259275, '2012-08-02', 'BonusApplied', 175, 175),
(259275, '2012-08-03', 'TradeOrder', NULL, 175),
(259275, '2012-08-06', 'TradeOrder', NULL, 175),
(259275, '2012-08-10', 'BonusApplied', 180, 180),
(259275, '2012-08-11', 'TradeOrder', NULL, 180),
(259275, '2012-08-12', 'TradeOrder', NULL, 180),
(259275, '2012-08-15', 'TradeOrder', NULL, 180),
(259275, '2012-08-17', 'BonusApplied', 200, 200),
(259275, '2012-08-18', 'TradeOrder', NULL, 200),
(259681, '2012-08-01', 'Deposit', NULL, NULL),
(259681, '2012-08-02', 'BonusApplied', 175, 175),
(259681, '2012-08-03', 'TradeOrder', NULL, 175),
(259681, '2012-08-06', 'TradeOrder', NULL, 175),
(259681, '2012-08-10', 'BonusApplied', 180, 180),
(259681, '2012-08-11', 'TradeOrder', NULL, 180),
(259681, '2012-08-12', 'TradeOrder', NULL, 180),
(259681, '2012-08-15', 'TradeOrder', NULL, 180),
(259681, '2012-08-17', 'BonusApplied', 200, 200),
(259681, '2012-08-18', 'TradeOrder', NULL, 200);


SET @VUserID := NULL;
SET @VValue := NULL;

SELECT CreationTs, `Type`, IF(@VUserID = userID, IF(`Value` IS NULL, @VValue, @VValue := `Value`), @VValue := `Value`) BonusValue, @VUserID := userID userID FROM bonusTable ORDER BY userID, CreationTs;

#Cols in original order:
SELECT userID, CreationTs, `Type`, BonusValue FROM (
SELECT CreationTs, `Type`, IF(@VUserID = userID, IF(`Value` IS NULL, @VValue, @VValue := `Value`), @VValue := `Value`) BonusValue, @VUserID := userID userID FROM bonusTable ORDER BY userID, CreationTs
) A;

Upvotes: 2

Related Questions