同城市分组SQL计算：SQLite表按城市实现特定产品值加权运算

原表数据 #

假设表名为product_values，数据如下：

ID  City Product    Value
1   A   Lubs        22
2   A   Ethanol     14
3   A   S500        10
4   A   S10         13
5   A   Biodiesel   40
6   A   Gasoline    15
7   B   Lubs        8
8   B   Ethanol     20
9   B   S500        12
10  B   S10         15
11  B   Biodiesel   50
12  B   Gasoline    8

计算规则 #

按城市分组，对指定产品执行以下计算：

• Lubs：保留原始Value值
• S500/S10：自身Value * 88% + 同城市Biodiesel的Value * 12%
• Gasoline：同城市Ethanol的Value * 27% + 自身Value * 73%

实现SQL #

方案1：返回计算后的数值结果 #

使用窗口函数获取同城市的关联产品值，简洁高效（SQLite 3.25+支持窗口函数）：

SELECT
    City,
    Product,
    CASE Product
        WHEN 'Lubs' THEN Value
        WHEN 'S500' THEN Value * 0.88 + MAX(CASE WHEN Product = 'Biodiesel' THEN Value END) OVER (PARTITION BY City) * 0.12
        WHEN 'S10' THEN Value * 0.88 + MAX(CASE WHEN Product = 'Biodiesel' THEN Value END) OVER (PARTITION BY City) * 0.12
        WHEN 'Gasoline' THEN MAX(CASE WHEN Product = 'Ethanol' THEN Value END) OVER (PARTITION BY City) * 0.27 + Value * 0.73
    END AS Value_Pond
FROM product_values
WHERE Product IN ('Lubs', 'S500', 'S10', 'Gasoline')
ORDER BY City, Product;

执行后得到的数值结果：

City    Product    Value_Pond
A       Lubs       22.0
A       Gasoline   14.73
A       S10        16.24
A       S500       13.6
B       Lubs       8.0
B       Gasoline   10.84
B       S10        18.7
B       S500       15.36

方案2：返回计算表达式字符串 #

如果需要和示例一致的表达式格式（而非计算结果），可以修改CASE语句拼接字符串：

SELECT
    City,
    Product,
    CASE Product
        WHEN 'Lubs' THEN CAST(Value AS TEXT)
        WHEN 'S500' THEN '=' || CAST(Value AS TEXT) || '*88% + ' || CAST(MAX(CASE WHEN Product = 'Biodiesel' THEN Value END) OVER (PARTITION BY City) AS TEXT) || '*12%'
        WHEN 'S10' THEN '=' || CAST(Value AS TEXT) || '*88% + ' || CAST(MAX(CASE WHEN Product = 'Biodiesel' THEN Value END) OVER (PARTITION BY City) AS TEXT) || '*12%'
        WHEN 'Gasoline' THEN '=' || CAST(MAX(CASE WHEN Product = 'Ethanol' THEN Value END) OVER (PARTITION BY City) AS TEXT) || '*27% + ' || CAST(Value AS TEXT) || '*73%'
    END AS Value_Pond
FROM product_values
WHERE Product IN ('Lubs', 'S500', 'S10', 'Gasoline')
ORDER BY City, Product;

执行后得到的表达式结果：

City    Product    Value_Pond
A       Lubs       22
A       Gasoline   =14*27% + 15*73%
A       S10        =13*88% + 40*12%
A       S500       =10*88% + 40*12%
B       Lubs       8
B       Gasoline   =20*27% + 8*73%
B       S10        =15*88% + 50*12%
B       S500       =12*88% + 50*12%

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