SQL使用子查询和CASE WHEN计算商品相对基准价的变动率

解决方案 #

你可以用窗口函数实现全商品的价格变动率计算，无需硬编码指定单个商品，性能也优于逐行关联子查询，适配所有支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等）：

SELECT 
    `Unique ID` AS Unique_id,
    Product,
    Price,
    Date,
    `Index`,
    CONCAT(
        ROUND(
            (REPLACE(Price, '$', '') / base_price - 1) * 100,
            1
        ),
        '%'
    ) AS Price_change
FROM (
    SELECT 
        *,
        -- 按商品分组，取对应商品基准行的价格
        MAX(CASE WHEN `Index` = 'Index' THEN REPLACE(Price, '$', '') END) 
            OVER (PARTITION BY Product) AS base_price
    FROM my_table
) AS t

如果你使用的是不支持窗口函数的低版本数据库，也可以用子查询关联实现：

SELECT 
    a.`Unique ID` AS Unique_id,
    a.Product,
    a.Price,
    a.Date,
    a.`Index`,
    CONCAT(
        ROUND(
            (REPLACE(a.Price, '$', '') / REPLACE(b.base_price, '$', '') - 1) * 100,
            1
        ),
        '%'
    ) AS Price_change
FROM my_table a
LEFT JOIN (
    -- 提取所有商品的基准价格
    SELECT Product, Price AS base_price
    FROM my_table
    WHERE `Index` = 'Index'
) b ON a.Product = b.Product

说明 #

1. 以上SQL默认你的Price字段是带$符号的字符串类型，若已经是纯数值类型，删除REPLACE(Price, '$', '')的转换逻辑即可
2. 不同数据库的数值转换、字符串拼接语法略有差异，可根据你使用的数据库类型调整对应语法
3. 前提是每个商品仅存在1行标记为Index的基准行，符合你给出的表结构设计

内容的提问来源于stack exchange，提问作者Sluna