sqlite SQL中同集团城市的计算

yjghlzjz 于 2023-08-06 发布在 SQLite

关注(0)|答案(1)|浏览(102)

下面是SQLite表：

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

字符串
我需要的（最终结果）：

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

型
对于每个城市，我需要为S500，S10和汽油做一些数学计算。城市A的解释：

S500：S500值 * 88% +生物柴油值 * 12% = 13.6
S10：S10值 * 88% +生物柴油值 * 12% = 16.24
1.汽油：乙醇值 * 27% +汽油值 * 73% = 14.73
Lubs具有相同的值-不需要数学。
同样的事情也会发生在B市等等…

sqlite

来源：https://stackoverflow.com/questions/76766438/calculation-with-same-group-city-in-sql

1条答案

按热度按时间

eufgjt7s1#

使用CASE表达式和MAX()窗口函数获得每个结果：

WITH cte AS (
  SELECT City, Product,
         ROUND(
           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 Value * 0.73 + MAX(CASE WHEN Product = 'Ethanol' THEN Value END) OVER (PARTITION BY City) * 0.27  
           END,
           2
        ) Value_Pond  
  FROM tablename
)
SELECT * 
FROM cte
WHERE Value_Pond IS NOT NULL
ORDER BY City, 
         CASE Product
           WHEN 'Lubs' THEN 1
           WHEN 'S500' THEN 2
           WHEN 'S10' THEN 3
           WHEN 'Gasoline' THEN 4
         END;

字符串
参见demo。

赞(0）回复(0）举报 2023-08-06

我来回答

sqlite SQL中同集团城市的计算

1条答案

相关问题

热门标签

最新问答