基于类型的MariaDB SELECT算术运算

z31licg0 于 2023-01-09 发布在其他

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

我在MariaDB中有以下查询

MariaDB [nova]> SELECT hostname, instances.created_at, 
  SUM(TIMESTAMPDIFF(hour,instances.created_at, NOW())) AS 'Total Hours',  
  JSON_EXTRACT(flavor, '$.cur."nova_object.data".name') AS FLAVOR  
 FROM instances  
 JOIN instance_extra ON instances.uuid = instance_extra.instance_uuid  
 WHERE (vm_state='active' OR vm_state='stopped') 
  AND hostname LIKE '%bfbxp%' 
 Group by FLAVOR;
+------------------+---------------------+-------------+----------------+
| hostname         | created_at          | Total Hours | FLAVOR         |
+------------------+---------------------+-------------+----------------+
| www5             | 2022-11-17 15:44:32 |       14703 | "gen.medium"   |
| www6             | 2022-11-17 15:44:26 |        3678 | "gen.large"    |
| www7             | 2022-11-17 15:44:46 |        7350 | "sriov.medium" |
+------------------+---------------------+-------------+----------------+
3 rows in set, 1 warning (0.007 sec)

我想应用每小时成本计算Total Hour * Cost和以下成本，我为每个风味。我如何应用SQL查询，或者我应该使用脚本，如Python或bash做复杂的查询？
"gen.medium"成本$0.050成本3578*0.50
"gen.large"成本$0.060成本14703*0.60
"sriov.medium"成本$0.070成本也是7350*0.70
我在想我可以创建#temp表，在表中添加每种口味的成本，然后连接两个表并运行计算。听起来容易吗？

mariadb

来源：https://stackoverflow.com/questions/75042699/mariadb-select-arithmetic-operation-based-on-type

1条答案

按热度按时间

wfsdck301#

SELECT hostname, 
       instances.created_at, 
       SUM(TIMESTAMPDIFF(hour,instances.created_at, NOW())) AS `Total Hours`,  
       JSON_EXTRACT(flavor, '$.cur."nova_object.data".name') AS flavor  

,      SUM(TIMESTAMPDIFF(hour,instances.created_at, NOW())) 
           * CASE JSON_EXTRACT(flavor, '$.cur."nova_object.data".name')
                 WHEN '"gen.medium"'   THEN 0.5
                 WHEN '"gen.large"'    THEN 0.6
                 WHEN '"sriov.medium"' THEN 0.7
                 END AS `Total Cost`

FROM instances  
JOIN instance_extra ON instances.uuid = instance_extra.instance_uuid  
WHERE vm_state IN ('active', 'stopped') 
  AND hostname LIKE '%bfbxp%' 
GROUP BY flavor;

如果2024年成本将发生变化，你想更新你的应用程序而不是数据吗？-乔治·里希特
x一个一个一个一个x一个一个二个x

赞(0）回复(0）举报 2023-01-09

我来回答

基于类型的MariaDB SELECT算术运算

1条答案

相关问题

热门标签

最新问答