SQL计算月度账户增长率结果恒为0的问题及解决方法

我有一张存储每月月末产品总账户数的表BASE_TABLE，表结构及数据如下：

我使用以下SQL语句计算账户增长率：

SELECT *
       ,  DIFF / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

但计算得到的GROWTH_PERCENTAGE结果始终为0，请问如何获取正确结果？

原因分析 #

问题出在整数除法的精度丢失：ACCOUNT和ACC_YSTR都是整数类型，数据库执行整数除法时会直接舍去小数部分，只保留整数商。比如(22848-22860)/22860的结果是接近0的小数，整数除法后直接取0。

修正后的SQL #

只需将其中一个数值转换为浮点型，强制数据库执行浮点除法即可，有两种常用实现方式：

方法1：通过乘以1.0隐式转换类型

SELECT *
       ,  DIFF * 1.0 / ACC_YSTR GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

方法2：使用CAST函数显式转换

SELECT *
       ,  DIFF / CAST(ACC_YSTR AS FLOAT) GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

额外优化（百分比格式输出） #

如果需要将结果转换为带百分比符号的格式（比如保留两位小数），可以用ROUND和字符串拼接实现：

SELECT *
       ,  CONCAT(ROUND(DIFF * 1.0 / ACC_YSTR * 100, 2), '%') GROWTH_PERCENTAGE
FROM(
SELECT *
       , (ACCOUNT - ACC_YSTR) DIFF
FROM(
SELECT PERIOD
       , GROUP_PRODUCT
       , ACCOUNT
       , COALESCE(LAG(ACCOUNT) OVER (PARTITION BY GROUP_PRODUCT ORDER BY PERIOD ASC), ACCOUNT) ACC_YSTR
FROM BASE_TABLE
))

内容的提问来源于stack exchange，提问作者H. D. U.