查询和公式形成整个表，带有新列，其中新列是涉及其他条件列值的公式

Question

编辑：公式应该是“值”列 - 根据类别具有最低 user_id 的“值”列。

因此第 2 行将为 0.04，因为类别“A”的最低“user_id”的“value”列是 0.01。

抱歉。

我想从表中检索所有内容，并添加另一列，其中涉及包含基于其他列的信息的公式。这是原始表格：

类别	user_id	值
一个	1	0.01
一个	2	0.05
B	4	0.34
B	7	0.27

新列必须是每行的“值”列减去“类别”的最小值“user_id”。因此，对于第二行，它将是 (0.05 - 1)，因为类别是“A”，而“A”的最低“user_id”是 1。

还有更多的行和列，这只是一个示例。

我会使用什么公式？

这是我到目前为止所拥有的，只是为了能够证明我能够创建新列，但没有正确的公式：

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;

这显然给了我新的列，但为 0（因为它减去了自身）。

正确的公式是什么？

这是架构：

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

在 mysql 5.7 中，您可以使用子查询来达到目的，视图似乎比新表更好，但由于选择是相等的，您可以选择

db<>小提琴此处

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 |
+----------+---------+-------+----------------+

这使用了窗口函数，意味着它需要 MySQL 8.0，这是自 2018 年以来 MySQL 的当前版本。

---

回复您的评论：使用类别中最低 user_id 中的 value 列：

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 |
+----------+---------+-------+----------------+