The query and formula form the entire table, with new columns, where the new columns are formulas involving other conditional column values
0
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)
; 2 answers
0
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 |
+----------+---------+-------+----------------+
Hot tools Tags
Hot Questions
Popular tool
vc9-vc14 (32+64 bit) runtime library collection (link below)
Download the collection of runtime libraries required for phpStudy installation
VC9 32-bit
VC9 32-bit phpstudy integrated installation environment runtime library
PHP programmer toolbox full version
Programmer Toolbox v1.0 PHP Integrated Environment
VC11 32-bit
VC11 32-bit phpstudy integrated installation environment runtime library
SublimeText3 Chinese version
Chinese version, very easy to use
Hot Topics
20522
7
13634
4