Stripe数据库BIGINT金额转换问题：4.99异常显示为499

问题原因说明 #

Stripe的金额字段默认以对应货币的最小单位存储（如美元对应美分、人民币对应分），存储的整数值除以100才是常规展示的元级金额单位，你两次尝试的错误点分别是：

1. 仅做类型转换未做单位换算：CAST(amount AS DECIMAL(19,2))只是把整数499转成了499.00，没有做除以100的单位转换，得到的结果自然是正确值的100倍。
2. 直接执行整数除法：大多数关系型数据库中，两个整数做除法默认执行整数除法，会直接截断小数部分，所以499/100得到的是整数4，会导致求和结果错误。

正确实现方案 #

该需求完全可以实现，并非数据库设置问题，只要先将整数金额转换为DECIMAL类型，再做除以100的单位换算，即可得到保留两位小数的正确金额，SQL示例如下：

SELECT 
  COALESCE(card_address_country, card_country) AS "Country", 
  SUM(CAST(amount AS DECIMAL(19,2)) / 100) AS "Total Revenue" 
FROM charges 
WHERE amount_refunded = 0 AND paid = true 
GROUP BY COALESCE(card_address_country, card_country) 
ORDER BY SUM(amount) DESC

如果数据量较大，也可以先求和整数金额再做统一转换，性能更优：

SELECT 
  COALESCE(card_address_country, card_country) AS "Country", 
  CAST(SUM(amount) AS DECIMAL(19,2)) / 100 AS "Total Revenue" 
FROM charges 
WHERE amount_refunded = 0 AND paid = true 
GROUP BY COALESCE(card_address_country, card_country) 
ORDER BY SUM(amount) DESC

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