问题描述
我们供应商的数据库具有所有数字的数字类型，包括整数和十进制数字.从字面上看，每个数字类型列都被创建为NUMBER，而没有精度和小数位数.
Our vendor's database has Number types for all numbers including whole numbers and decimal numbers. Literally, every numeric type column is created as NUMBER without precision and scale.
这是一个大问题，因为我们需要将这些列映射到目标系统上的适当数据类型，我们正在将这些表中的数据加载到其中.
This is a big problem as we need to map these columns to proper data types on our target system, we are loading data from these tables into.
我们需要知道数字是整数还是十进制.
We need to know if a number is an integer or decimal.
除了进行随机采样/数据分析外，还可以推断出正确的数据类型吗?
Other than doing a random sampling/data profiling, is it possible to infer proper data types?
更新:我接受了下面的答案和@Bohemian的建议.除此之外，由于源表很大(数十亿行)，因此我将使用SAMPLE子句对表进行随机采样.
UPDATE:I accepted the answer below and suggestion from @Bohemian. In addition to that, I will use SAMPLE clause that will do a random sampling of the table since my source tables are huge (many billions of rows).
SELECT
MAX(CASE WHEN col1 IS NOT NULL AND col1 <> round(col1, 0) then 1 else 0 end) as col1,
MAX(CASE WHEN col2 IS NOT NULL AND col2 <> round(col2, 0) then 1 else 0 end) as col2
FROM TABLE
SAMPLE(0.05)
如果我只想采样X行，请使用下面的公式进行SAMPLE(N):
If I want to sample only X rows, use formula below to SAMPLE(N):
Xrows * 100/table_rows_total
Xrows*100/table_rows_total
推荐答案
您可以尝试选择每个FIELD，并查看FIELD的所有值是否等于ROUND(FIELD，0).如果是，则该字段应为整数.如果不是，则为十进制.
You can try selecting each FIELD, and seeing if all values of FIELD are equal to ROUND(FIELD, 0). If they are, then that field should be integer. If not, decimal.
这篇关于Oracle Number类型不精确-如何知道它是否为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！