Query and formula to form entire table, with new column, where new column is a formula involving oth-CodePudding

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

So row 2 would be 0.04, since the 'value' column of the lowest 'user_id' for category 'A' is 0.01.

Sorry about that.

I would like to retrieve everything from a table, with another column added, that involves a formula with info based on other columns. Here is the original table:

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

The new column has to be the 'value' column, minus the lowest value 'user_id' for the 'category', for each row. So for the second row, it would be (0.05 - 1), since the category is 'A', and the lowest 'user_id' for 'A' is 1.

There are far 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 show that I am able to create the new column, 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 with 0 (since it subtracts itself).

What is the correct formula?

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

CodePudding user response：

Inb mysql 5.7 you can use a subquery for the purpose, the view seems top be a better coice than a new table, but as the selects are eqal, you can choose

Create view new_tableview as
SELECT t1.`category`, t1.`user_id`, t1.`value`, t2.minid - t1.`value` as new_value FROM table_1 t1 JOIN 
( SELECT `category`,MIN(`user_id`) minid FROM table_1 GROUP BY `category`) t2 ON t1.`category` = t2.`category`

SELECT * FROM new_tableview

category | user_id | value | new_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

db<>fiddle here

CodePudding user response：

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 a window function, which means it requires MySQL 8.0, which is the current version of MySQL since 2018.

Re your comment: using 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 |
 ---------- --------- ------- ----------------