I have a table that looks like this:

The data needs to be filled up to the max value of user_id for both categories (A and B). So 1-7 for A and for B. The data must use the value for the previous/most recent user_id for its category. If there isn't one, then it must use the first available value for its category.

Here's what I'm trying to get it to look like:

So for 'category' B 'user_id' 1, since it was the most recent/lowest value, it used the next available from B2.

Schema:

CREATE TABLE table_1 (
  `category` VARCHAR(2),
  `user_id` INT(2), 
  `value` DECIMAL(3,2)
 );
INSERT INTO table_1
(`category`, `user_id`, `value`)
VALUES
('A', 1, 0.01),
('A', 2, 0.05),
('A', 5, 0.09),
('A', 7, 0.12),
('B', 2, 0.34),
('B', 3, 0.27),
('B', 6, 0.08),
('B', 7, 0.12)
;

CodePudding user response：

you may use a query like below.

Also here's a db fiddle

Explanation:

1. first we need a numbers table to get all numbers to a sufficient range here 5000
2. then we get max values of table_1 per category
3. then we cross above two get all numbers per category to the max value
4. next we get reference user_id index of values to be pushed in for all missing user_id
5. finally we join to get actual values for all user_ids. Special case of B-1 which does not exists is handled via window function

Query:

SET @@cte_max_recursion_depth  = 5000;
WITH RECURSIVE cte AS
(
   SELECT 1 AS i
   UNION ALL
   SELECT i 1
   FROM cte
   WHERE i < 3000
), maxuserids as 
(
select category, max(user_id) as m from table_1 group by category
), leftsidetable as (
SELECT *
FROM cte cross join maxuserids
where m>=i
), refvalues as (
select 
l.category, l.i, max(user_id) m 
from leftsidetable l left join table_1 t1
on l.category=t1.category and l.i>=t1.user_id 
group by l.category, l.i
)
select r.category, r.i as user_id, 
ifnull(value, 
lead(value) over (partition by r.category order by r.i)) value  from refvalues r 
left join table_1 t 
on t.category=r.category and t.user_id=r.m