אחסון נתונים מספריים עם דיוק שרירותי

‫Spanner מספק את הסוג NUMERIC שיכול לאחסן מספרים עם דיוק עשרוני באופן מדויק. הסמנטיקה של סוג הנתונים NUMERIC ב-Spanner משתנה בין שני הדיאלקטים של SQL (GoogleSQL ו-PostgreSQL), במיוחד בכל הנוגע למגבלות על הקנה מידה והדיוק:

  • ‫NUMERIC בניב PostgreSQL הוא סוג מספרי עם דיוק עשרוני שרירותי (הקנה מידה או הדיוק יכולים להיות כל מספר בטווח הנתמך), ולכן הוא בחירה אידיאלית לאחסון נתונים מספריים עם דיוק שרירותי.

  • ‫NUMERIC ב-GoogleSQL הוא טיפוס נתונים מספרי בדיוק קבוע (precision=38 ו-scale=9) ואי אפשר להשתמש בו כדי לאחסן נתונים מספריים בדיוק שרירותי. כשצריך לאחסן מספרים עם דיוק שרירותי במסדי נתונים של ניב GoogleSQL, מומלץ לאחסן אותם כמחרוזות.

רמת הדיוק של סוגים מספריים ב-Spanner

הדיוק הוא מספר הספרות במספר. הקנה מידה הוא מספר הספרות שמימין לנקודה העשרונית במספר. לדוגמה, למספר 123.456 יש דיוק של 6 וקנה מידה של 3. ב-Spanner יש שלושה סוגים מספריים:

  • סוג מספר שלם עם סימן באורך 64 ביט שנקרא INT64 בניב GoogleSQL ו-INT8 בניב PostgreSQL.
  • סוג נקודה צפה בינארית (double) בדיוק של 64 ביט לפי תקן IEEE, שנקרא FLOAT64 בניב GoogleSQL ו-FLOAT8 בניב PostgreSQL.
  • סוג הדיוק העשרוני NUMERIC.

בואו נבחן כל אחד מהם מבחינת דיוק וקנה מידה.

‫INT64 / INT8 מייצג ערכים מספריים שלא כוללים רכיב של שבר. סוג הנתונים הזה מספק 18 ספרות של דיוק, עם קנה מידה של אפס.

הפונקציה FLOAT64 / FLOAT8 יכולה לייצג רק ערכים מספריים דצימליים משוערים עם רכיבים חלקיים, ומספקת 15 עד 17 ספרות משמעותיות (מספר הספרות במספר אחרי הסרת כל האפסים בסוף) של דיוק דצימלי. הסוג הזה מייצג ערכים מספריים עשרוניים משוערים, כי הייצוג הבינארי של נקודה צפה של 64 ביט בתקן IEEE שבו Spanner משתמש לא יכול לייצג במדויק שברים עשרוניים (בסיס 10) (הוא יכול לייצג רק שברים בבסיס 2 במדויק). האובדן הזה של דיוק מוביל לשגיאות עיגול בחלק מהשברים העשרוניים.

לדוגמה, כשמאחסנים את הערך העשרוני 0.2 באמצעות סוג הנתונים FLOAT64 / FLOAT8, הייצוג הבינארי מומר בחזרה לערך עשרוני של 0.20000000000000001 (עד 18 ספרות של דיוק). באופן דומה, (‎1.4 * 165) מומר בחזרה ל-230.999999999999971 ו-‎ (0.1 + 0.2) מומר בחזרה ל-0.30000000000000004. לכן, מספרים ממשיים בפורמט 64 ביט מתוארים כמספרים עם 15 עד 17 ספרות משמעותיות של דיוק (רק חלק מהמספרים עם יותר מ-15 ספרות עשרוניות יכולים להיות מיוצגים כמספרים ממשיים בפורמט 64 ביט ללא עיגול). למידע נוסף על אופן החישוב של דיוק נקודה צפה, אפשר לעיין במאמר בנושא פורמט נקודה צפה עם דיוק כפול.

ל-INT64 / INT8 ול-FLOAT64 / FLOAT8 אין את הדיוק האידיאלי לחישובים פיננסיים, מדעיים או הנדסיים, שבהם נדרש בדרך כלל דיוק של 30 ספרות או יותר.

סוג הנתונים NUMERIC מתאים לאפליקציות האלה, כי הוא יכול לייצג ערכים מספריים מדויקים עם דיוק של יותר מ-30 ספרות אחרי הנקודה העשרונית.

סוג הנתונים NUMERIC ב-GoogleSQL יכול לייצג מספרים עם דיוק עשרוני קבוע של 38 וקנה מידה קבוע של 9. הטווח של GoogleSQL NUMERIC הוא ‎-99999999999999999999999999999.999999999 עד ‎99999999999999999999999999999.999999999.

הסוג NUMERIC ב-PostgreSQL יכול לייצג מספרים עם דיוק עשרוני מקסימלי של 147,455 וקנה מידה מקסימלי של 16,383.

אם אתם צריכים לאחסן מספרים גדולים יותר מהדיוק ומהקנה מידה שמוצעים על ידי NUMERIC, בסעיפים הבאים מתוארים כמה פתרונות מומלצים.

המלצה: אחסון מספרים עם דיוק שרירותי כמחרוזות

אם אתם צריכים לאחסן מספר עם דיוק שרירותי במסד נתונים של Spanner, ואתם צריכים דיוק גבוה יותר ממה ש-NUMERIC מספק, מומלץ לאחסן את הערך כייצוג העשרוני שלו בעמודה STRING / VARCHAR. לדוגמה, המספר 123.4 מאוחסן כמחרוזת "123.4".

בגישה הזו, האפליקציה צריכה לבצע המרה ללא אובדן מידע בין הייצוג הפנימי של המספר באפליקציה לבין ערך העמודה STRING /VARCHAR לקריאה וכתיבה של מסד הנתונים.

לרוב הספריות של חישובים עם דיוק שרירותי יש שיטות מובנות לביצוע ההמרה הזו ללא אובדן נתונים. לדוגמה, ב-Java אפשר להשתמש בשיטה BigDecimal.toPlainString() ובבונה BigDecimal(String).

היתרון בשמירת המספר כמחרוזת הוא שהערך נשמר עם דיוק מוחלט (עד למגבלת אורך העמודה STRING / VARCHAR), והערך נשאר קריא.

ביצוע צבירות וחישובים מדויקים

כדי לבצע צבירות וחישובים exact על ייצוגים של מחרוזות של מספרים עם דיוק שרירותי, האפליקציה צריכה לבצע את החישובים האלה. אי אפשר להשתמש בפונקציות מצטברות של SQL.

לדוגמה, כדי לבצע פעולה ששווה ל-SQL SUM(value) על טווח של שורות, האפליקציה צריכה לשלוח שאילתה לגבי ערכי המחרוזות של השורות, ואז להמיר ולסכם אותם באופן פנימי באפליקציה.

ביצוע צבירות, מיון וחישובים משוערים

אפשר להשתמש בשאילתות SQL כדי לבצע חישובים מצטברים משוערים על ידי המרת הערכים ל-FLOAT64 או ל-FLOAT8.

GoogleSQL 

SELECT SUM(CAST(value AS FLOAT64)) FROM my_table

PostgreSQL 

SELECT SUM(value::FLOAT8) FROM my_table

באופן דומה, אפשר למיין לפי ערך מספרי או להגביל ערכים לפי טווח באמצעות המרה:

GoogleSQL 

SELECT value FROM my_table ORDER BY CAST(value AS FLOAT64);
SELECT value FROM my_table WHERE CAST(value AS FLOAT64) > 100.0;

PostgreSQL 

SELECT value FROM my_table ORDER BY value::FLOAT8;
SELECT value FROM my_table WHERE value::FLOAT8 > 100.0;

החישובים האלה הם קירוב למגבלות של סוג הנתונים FLOAT64 / FLOAT8.

חלופות

יש עוד דרכים לאחסן מספרים עם דיוק שרירותי ב-Spanner. אם אחסון מספרים עם דיוק שרירותי כמחרוזות לא מתאים לאפליקציה שלכם, כדאי לשקול את החלופות הבאות:

אחסון ערכים של מספרים שלמים שמותאמים לאפליקציה

כדי לאחסן מספרים עם דיוק שרירותי, אפשר לשנות את קנה המידה של הערכים לפני הכתיבה, כך שהמספרים תמיד יאוחסנו כמספרים שלמים, ואז לשנות את קנה המידה של הערכים אחרי הקריאה. האפליקציה שלכם שומרת גורם קבוע של קנה מידה, והדיוק מוגבל ל-18 הספרות שסופקו על ידי סוג הנתונים INT64 / INT8.

לדוגמה, מספר שצריך לאחסן עם דיוק של 5 ספרות אחרי הנקודה העשרונית. האפליקציה ממירה את הערך למספר שלם על ידי הכפלה ב-100,000 (הזזת הנקודה העשרונית 5 מקומות ימינה), כך שהערך 12.54321 נשמר כ-1254321.

מבחינה כספית, הגישה הזו דומה לאחסון ערכים בדולרים ככפולות של אלפיות סנט, בדומה לאחסון יחידות זמן כאלפיות השנייה.

האפליקציה קובעת את גורם קביעת קנה המידה הקבוע. אם משנים את מקדם ההתאמה, צריך להמיר את כל הערכים הקודמים שהותאמו במסד הנתונים.

בגישה הזו, הערכים שמאוחסנים הם ערכים שקריאים לבני אדם (בהנחה שאתם יודעים את גורם לקביעת קנה מידה). בנוסף, אפשר להשתמש בשאילתות SQL כדי לבצע חישובים ישירות על ערכים שמאוחסנים במסד הנתונים, כל עוד התוצאה מותאמת בצורה נכונה ולא חורגת מהקיבולת.

שמירת הערך השלם הלא מותאם והקנה מידה בעמודות נפרדות

אפשר גם לאחסן ב-Spanner מספרים עם דיוק שרירותי באמצעות שני רכיבים:

  • הערך השלם הלא מותאם שמאוחסן במערך בייטים.
  • מספר שלם שמציין את גורם לקביעת קנה מידה.

קודם, האפליקציה ממירה את המספר העשרוני עם הדיוק השרירותי לערך של מספר שלם לא מוקטן. לדוגמה, האפליקציה ממירה את 12.54321 ל-1254321. הקנה מידה בדוגמה הזו הוא 5.

לאחר מכן, האפליקציה ממירה את ערך המספר השלם הלא מותאם למערך בייטים באמצעות ייצוג בינארי נייד רגיל (לדוגמה, משלים ל-2 מסוג big-endian).

לאחר מכן, מסד הנתונים מאחסן את מערך הבייטים (BYTES / BYTEA) ואת קנה המידה של המספר השלם (INT64 / INT8) בשתי עמודות נפרדות, וממיר אותם בחזרה בזמן הקריאה.

ב-Java, אפשר להשתמש ב-BigDecimal וב-BigInteger כדי לבצע את החישובים האלה:

byte[] storedUnscaledBytes = bigDecimal.unscaledValue().toByteArray();
int storedScale = bigDecimal.scale();

אפשר לקרוא בחזרה ל-Java BigDecimal באמצעות הקוד הבא:

BigDecimal bigDecimal = new BigDecimal(
    new BigInteger(storedUnscaledBytes),
    storedScale);

בגישה הזו, הערכים מאוחסנים עם דיוק שרירותי וייצוג נייד, אבל הערכים לא קריאים לאנשים במסד הנתונים, וכל החישובים צריכים להתבצע על ידי האפליקציה.

אחסון הייצוג הפנימי של האפליקציה כבייטים

אפשרות נוספת היא לבצע סריאליזציה של ערכי עשרוניים עם דיוק שרירותי למערכי בייטים באמצעות הייצוג הפנימי של האפליקציה, ואז לאחסן אותם ישירות במסד הנתונים.

הערכים במסד הנתונים המאוחסן לא קריאים לאנשים, והאפליקציה צריכה לבצע את כל החישובים.

יש בעיות ניידות בגישה הזו. אם תנסו לקרוא את הערכים באמצעות שפת תכנות או ספרייה ששונות מאלה ששימשו לכתיבתם, יכול להיות שהפעולה לא תצליח. יכול להיות שקריאת הערכים בחזרה לא תפעל כי לספריות שונות של דיוק שרירותי יכולות להיות ייצוגים שונים של סדרות למערכי בייטים.

המאמרים הבאים