如何选取最精确的数值列类型实现多列无数据丢失的联合合并？

I need to determine which column type has the highest precision among multiple columns with different numeric types (float, long, double, decimals with varying precision and scale) to merge them into a single unified column. My goal is to avoid data loss from truncation during conversion. For example, I have these column types: decimal(10,0), decimal(18,3), decimal(10,6), long, double, float — what's the right target type to retain all data?

Great question — this is a common scenario when unifying heterogeneous numeric columns, and getting the target type right is critical to avoid silent data loss. Let's break down the solution step by step:

Step 1: Rank Numeric Types by Precision & Accuracy #

First, we need to prioritize types based on their ability to represent values exactly (no rounding errors):

• Decimal: This is an exact numeric type — it stores every digit precisely, making it the top choice for avoiding data loss. Unlike floating-point types, it doesn't suffer from binary representation gaps for decimal values (e.g., 0.1 can be stored perfectly).
• Double/Float: These are approximate floating-point types. Double has more precision (64-bit) than Float (32-bit), but both use binary storage that can't exactly represent some decimal fractions, leading to tiny (but sometimes critical) precision losses.
• Long: This is an exact integer type, but it's limited to whole numbers — it can't handle fractional values at all, so it's less flexible than decimal.

So your initial priority order is spot-on: decimal > double > float > long. Choosing a decimal type as the target is the correct foundation.

Step 2: Calculate the Correct Precision & Scale for the Target Decimal #

To cover all existing data without truncation, we need to account for two key metrics:

1. Maximum decimal places (scale): Look at all decimal columns and pick the largest scale. In your example, that's 6 (from decimal(10,6)). This ensures we retain all fractional values from the column with the most decimal digits.
2. Maximum integer digits: We need to accommodate the largest integer value across all columns:
   • For decimal columns: integer digits = precision - scale. Your decimals give us 10 (10-0), 15 (18-3), and 4 (10-6) — the largest here is 15.
   • For the long type: The maximum value of a long is 9223372036854775807, which is a 19-digit integer. This is larger than the 15-digit max from your decimal columns, so we need to use 19 as our integer digit count.

Combine these two values to get the total precision: 19 (integer digits) + 6 (scale) = 25. So the final target type should be decimal(25,6).

Note: Your initial calculation of decimal(21,6) missed the long type's larger integer range — using that would truncate the largest long values, leading to data loss. We have to factor in all input types, not just the decimals.

Step 3: Verify Compatibility with All Input Types #

Let's confirm this target works for every type in your example:

• decimal(10,0): Fits easily (10 total digits ≤25, 0 decimals ≤6)
• decimal(18,3): 18 total digits ≤25, 3 decimals ≤6 — we'll pad with 3 trailing zeros (no data loss)
• decimal(10,6): Exact match for scale, total digits well under 25
• long: All integer values fit in the 19-digit integer portion of decimal(25,6)
• double/float: While converting approximate floating-point types to decimal can't fix their inherent precision issues, it will preserve the closest exact decimal representation of their stored values — this is the best we can do to avoid additional data loss during merging.

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