Fill in the gaps between values in table - MySQL

Question

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.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 20 '17 at 16:52

score 2 · Answer 1 · answered Apr 20 '17 at 17:04

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;

Glad to have been of help. Please consider accepting the answer (green tick) in the case it was the most helpful given and answered your question, or upvoting if it was useful. This way my value to the community is reflected and when I get stuck and ask a question the probability of a useful answer increases. — James Scott, Apr 23 '17 at 14:04

Strawberry · Answer 2 · 2017-04-20T18:29:08.223

1

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 |
+-----+-----+-------+------+

edited Apr 20 '17 at 18:29

answered Apr 20 '17 at 17:11

Strawberry

33,750
13
40
57

@Martin that's a big question. A useful trick is to consider what rows would be returned if we removed the aggregating functions and replaced the GROUP BY clause with an ORDER BY clause. – Strawberry Apr 20 '17 at 18:32
I was really just enquiring as to some form of explanation as to why the SQL you posted works, thanks `:-)` – Martin Apr 20 '17 at 21:28
Thanks a lot!! :) – Michael Apr 24 '17 at 14:11
@levihaskell but 3 years on, who cares? – Strawberry Feb 12 '20 at 00:37
@Strawberry I have a similar problem – Levi Haskell Feb 12 '20 at 00:43

score 0 · Answer 3 · answered Feb 12 '20 at 00:42

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:

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
The GROUP BY x.id, x.seq clause allows us to select the highest preceding seq number via MAX(y.seq)
The select list sub-query looks up the value of the most immediate non-empty preceding row within the same id group

Fill in the gaps between values in table - MySQL

3 Answers3