在SQL的窗口函数计算场景中，浮点数精度问题是很常见的痛点，尤其是当计算涉及除法、乘法等运算时，使用FLOAT、DOUBLE类型存储的数值很容易出现精度丢失，导致最终计算结果和预期存在偏差，影响业务逻辑的正确性。
浮点数精度问题的产生原因
计算机存储浮点数时遵循IEEE 754标准，很多十进制的小数无法用二进制精确表示，因此会存在近似值存储的情况。当窗口函数对这类近似值做多次运算时，误差会不断累积，最终得到的结果就会出现偏差。
比如我们有一张销售记录表，需要计算每个商品的销售额占分类总销售额的比例，如果使用浮点数类型做除法，就很可能得到不准确的占比结果。
DECIMAL转换的解决方案
DECIMAL是定点数类型，存储时会精确表示十进制的小数，不会出现近似存储的问题，因此可以在窗口函数计算前，把参与计算的浮点数先转换为DECIMAL类型，就能避免精度丢失。
转换的核心逻辑
- 先确定需要的精度，比如保留4位小数，就指定DECIMAL的长度为总位数和小数位数
- 在窗口函数的计算逻辑中，对参与运算的字段先做CAST转换，再进行后续计算
- 如果最终需要浮点数结果，可以在计算完成后按需转换回对应类型，此时精度误差已经降到最低
不同数据库的实现示例
MySQL示例
假设我们有销售表sales，包含字段category分类、product商品、amount销售额，需要计算每个商品销售额占所属分类总销售额的比例，保留4位小数：
-- 创建测试表
CREATE TABLE sales (
category VARCHAR(20),
product VARCHAR(20),
amount FLOAT
);
-- 插入测试数据
INSERT INTO sales VALUES
('电子', '手机', 1999.99),
('电子', '平板', 2999.99),
('服装', 'T恤', 99.99),
('服装', '外套', 399.99);
-- 窗口函数计算占比，先做DECIMAL转换
SELECT
category,
product,
amount,
-- 将amount转换为DECIMAL(10,2)后做窗口求和，再计算占比，保留4位小数
CAST(
CAST(amount AS DECIMAL(10,2)) /
SUM(CAST(amount AS DECIMAL(10,2))) OVER (PARTITION BY category)
AS DECIMAL(10,4)
) AS category_ratio
FROM sales;
PostgreSQL示例
PostgreSQL的语法和MySQL类似，转换时同样使用CAST函数，示例代码如下：
-- 创建测试表
CREATE TABLE sales (
category VARCHAR(20),
product VARCHAR(20),
amount FLOAT
);
-- 插入测试数据
INSERT INTO sales VALUES
('电子', '手机', 1999.99),
('电子', '平板', 2999.99),
('服装', 'T恤', 99.99),
('服装', '外套', 399.99);
-- 窗口函数计算占比
SELECT
category,
product,
amount,
-- 转换为NUMERIC类型，PostgreSQL中NUMERIC等价于DECIMAL
CAST(
CAST(amount AS NUMERIC(10,2)) /
SUM(CAST(amount AS NUMERIC(10,2))) OVER (PARTITION BY category)
AS NUMERIC(10,4)
) AS category_ratio
FROM sales;
SQL Server示例
SQL Server同样支持DECIMAL类型，转换语法一致，示例代码如下：
-- 创建测试表
CREATE TABLE sales (
category VARCHAR(20),
product VARCHAR(20),
amount FLOAT
);
-- 插入测试数据
INSERT INTO sales VALUES
('电子', '手机', 1999.99),
('电子', '平板', 2999.99),
('服装', 'T恤', 99.99),
('服装', '外套', 399.99);
-- 窗口函数计算占比
SELECT
category,
product,
amount,
-- 转换为DECIMAL类型计算
CAST(
CAST(amount AS DECIMAL(10,2)) /
SUM(CAST(amount AS DECIMAL(10,2))) OVER (PARTITION BY category)
AS DECIMAL(10,4)
) AS category_ratio
FROM sales;
注意事项
- DECIMAL的精度设置要合理，总位数和小数位数要能覆盖计算过程中的最大值，避免出现溢出
- 如果参与计算的字段本身已经是DECIMAL类型，不需要重复转换，只需要确认精度是否满足需求即可
- 窗口函数的ORDER BY、PARTITION BY子句中的字段如果涉及数值比较，也建议统一类型，避免隐式转换带来的额外误差
通过DECIMAL转换的方式，基本可以解决绝大多数窗口函数计算中的浮点数精度问题，开发者可以根据实际业务场景调整DECIMAL的精度参数，适配不同的计算需求。