如何在MariaDB中基于实例规格实现按小时成本计算？

优先选择在SQL中直接实现计算 #

不需要额外用Python/Bash脚本处理，直接在SQL里完成成本计算更高效，减少数据导出和二次处理的步骤，逻辑也更集中。下面提供两种可行的SQL实现方式：

方式1：用CASE WHEN直接映射单价 #

适合规格少、单价不常变动的场景，写法简洁：

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.050
    WHEN '"gen.large"' THEN 0.060
    WHEN '"sriov.medium"' THEN 0.070
    ELSE 0 -- 处理未定义的规格，避免计算错误
  END AS 'Total Cost'
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;

方式2：用临时表存储单价并关联查询 #

你考虑的临时表方案确实简便，尤其适合规格较多、单价可能变动的场景，维护起来更灵活：

1. 先创建临时表存储规格单价：

CREATE TEMPORARY TABLE flavor_prices (
  flavor_name VARCHAR(50) PRIMARY KEY,
  hourly_price DECIMAL(10,3) NOT NULL
);
-- 插入单价数据
INSERT INTO flavor_prices VALUES
  ('"gen.medium"', 0.050),
  ('"gen.large"', 0.060),
  ('"sriov.medium"', 0.070);

2. 关联原查询完成计算：

SELECT 
  t.hostname,
  t.created_at,
  t.`Total Hours`,
  t.FLAVOR,
  t.`Total Hours` * fp.hourly_price AS 'Total Cost'
FROM (
  -- 原查询作为子查询
  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
) t
JOIN flavor_prices fp ON t.FLAVOR = fp.flavor_name;

脚本处理的适用场景 #

如果后续需要对计算结果做复杂的统计分析、可视化，或者需要集成到其他自动化工作流中，再考虑用Python/Bash脚本处理。但仅针对当前的成本计算需求，SQL实现更直接高效。

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