时间戳

首页 > 解决方案 > Azure 数据仓库 - 修复 Float 数据类型列中的 bigint 值

sql - Azure 数据仓库 - 修复 Float 数据类型列中的 bigint 值

问题描述

我有一个float数据类型的列。有一个带有bigint值的坏数据。值为-4.66606E+22。

我尝试了以下逻辑，但它不起作用。

  1. 使用 case 语句检查列是否落在浮动最小和最大范围之间，如果不是，则将其更改为 0。

  2. ISNULL(TRY_CAST(Column1 AS FLOAT), 0) 但这不起作用，因为我们的是 Azure 数据仓库。

Select 
    case 
        when Column1 > '1.79E+32' then 0
        when Column1 < '-1.79E+32' then 0
        Else Column1 
    End as Column1 
From Table1

我也试过

Select 
    case 
        when Column1 between '1.79E+32' and '-1.79E+32' then Column1 
        Else 0
    End as Column1 
From Table1

预期是用-4.66606E+22替换0。

标签： sqlsql-servertsqlazure-sql-database

解决方案

让我们从BigInt范围开始：-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)。这大约是±9E+18，所以-4.66606E+22 不是一个可能的BigInt值。

继续比较不同数据类型的值：

declare @Foo as BigInt = 8765432109876543210;
declare @FloatFoo as Float(24) = @Foo, @DoubleFoo as Float(53) = @Foo;

-- According to the rules for data type preedence a BigInt value will be converted to a Float
--   when the two data types are compared. Hence all of the values are "equal".
select @Foo as Foo,
  @FloatFoo as FloatFoo, case when @Foo = @FloatFoo then 1 else 0 end as FloatMatch,
  @DoubleFoo as DoubleFoo, case when @Foo = @DoubleFoo then 1 else 0 end as DoubleMatch;

-- Since a Float(53) has a precision of 15 digits that means some precision will be lost.
set @Foo += 1; -- Bump the BigInt value, but leave the Floats unchanged.
-- And ... the values are all still "equal"!
select @Foo as Foo,
  @FloatFoo as FloatFoo, case when @Foo = @FloatFoo then 1 else 0 end as FloatMatch,
  @DoubleFoo as DoubleFoo, case when @Foo = @DoubleFoo then 1 else 0 end as DoubleMatch;

-- Once more, with feeling.
set @Foo += 1000; -- A bigger bump makes the Float(53) value different, but not the Float(24).
select @Foo as Foo,
  @FloatFoo as FloatFoo, case when @Foo = @FloatFoo then 1 else 0 end as FloatMatch,
  @DoubleFoo as DoubleFoo, case when @Foo = @DoubleFoo then 1 else 0 end as DoubleMatch;

Ref's:数据类型优先级, Float.

执行摘要：“有一个带有bigint值的坏数据。该值为-4.66606E+22。” 这真是糟糕的数据。比较BigInt和Float价值是一种不精确的活动，可能会导致失望。

推荐阅读