The query and formula form the entire table, with new columns, where the new columns are formulas involving other conditional column values

Question

EDIT: The formula should be the "value" column - the "value" column with the lowest user_id based on the category.

So row 2 will be 0.04 because the "value" column of the lowest "user_id" for category "A" is 0.01.

Feel sorry.

I want to retrieve everything from the table and add another column that involves a formula containing information based on other columns. This is the original form:

category	user_id	value
one	1	0.01
one	2	0.05
B	4	0.34
B	7	0.27

The new column must be the minimum value "user_id" of the "value" column minus the "category" of each row. So for the second row it will be (0.05 - 1) since the category is "A" and the lowest "user_id" of "A" is 1.

There are many more rows and columns, this is just an example.

What formula would I use?

Here is what I have so far, just to be able to demonstrate that I am able to create new columns, but without the correct formula:

CREATE TABLE new_table 
 AS (select * FROM table_1);
 
 ALTER TABLE new_table
 ADD COLUMN `new_column` DECIMAL(3,2)
 GENERATED ALWAYS AS (table_1.value-table_1.value) STORED;
 
 select * from new_table;

This obviously gives me the new column, but as 0 (because it subtracts itself).

What is the correct formula?

This is the architecture:

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),
('B', 4, 0.34),
('B', 7, 0.27)
;

Answer 1

In mysql 5.7 you can use subquery to achieve the goal, the view seems better than the new table, but since the select is equal, you can choose

db<>violinhere

Answer 2

mysql> create view new_table as 
  select category, user_id, value, 
    value - min(user_id) over (partition by category) as adjusted_value
  from table_1;

mysql> select * from new_table;
+----------+---------+-------+----------------+
| category | user_id | value | adjusted_value |
+----------+---------+-------+----------------+
| A        |       1 |  0.01 |          -0.99 |
| A        |       2 |  0.05 |          -0.95 |
| B        |       4 |  0.34 |          -3.66 |
| B        |       7 |  0.27 |          -3.73 |
+----------+---------+-------+----------------+

This uses window functions, meaning it requires MySQL 8.0, which is the current version of MySQL as of 2018.

---

Reply to your comment: Use the value column from the lowest user_id in the category:

mysql> create or replace view new_table as 
  select category, user_id, value, 
    value - first_value(value) over (partition by category order by user_id) as adjusted_value  
  from table_1;

mysql> select * from new_table;
+----------+---------+-------+----------------+
| category | user_id | value | adjusted_value |
+----------+---------+-------+----------------+
| A        |       1 |  0.01 |           0.00 |
| A        |       2 |  0.05 |           0.04 |
| B        |       4 |  0.34 |           0.00 |
| B        |       7 |  0.27 |          -0.07 |
+----------+---------+-------+----------------+