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)
; 
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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
dbviolinhere p>
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
valuecolumn 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 | +----------+---------+-------+----------------+