פונקציות מספריות ב-SQLite: ROUND, ABS, CEIL, FLOOR ומתמטיקה

ל-SQLite יש יותר מתמטיקה ממה שהייתם מצפים

SQLite ידועה במינימליזם שלה, אבל היא מגיעה עם סט מלא של פונקציות מספריות: עיגול, ערך מוחלט, עיגול כלפי מעלה וכלפי מטה, חזקות, שורשים, לוגריתמים, טריגונומטריה ומספרים אקראיים. רוב הפונקציות המתמטיות נוספו ב-SQLite 3.35 (2021), כך שכל התקנה מודרנית במידה סבירה, זו שמגיעה עם Python, עם Node, עם האב הקדמון WebSQL של הדפדפן שלכם או עם ה-CLI הרשמי, כוללת אותן ומוכנה לעבודה.

הנה טעימה מהירה לפני שנצלול פנימה:

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

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

ROUND: זו שתשתמשו בה הכי הרבה

ROUND(value, digits) מעגל למספר נתון של ספרות אחרי הנקודה. הארגומנט השני אופציונלי: השמיטו אותו ותקבלו עיגול למספר השלם הקרוב (אבל עדיין כערך נקודה צפה):

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

כמה דברים לשים לב אליהם:

• ROUND(3.14159) מחזיר 3.0, לא 3. אם אתם רוצים מספר שלם, השתמשו ב-CAST(ROUND(x) AS INTEGER), או פשוט ב-CAST(x AS INTEGER) לקטיעה.
• SQLite משתמשת ב"עיגול חצי הרחק מאפס": 2.5 מתעגל ל-3, ו--2.5 מתעגל ל--3. יש מסדי נתונים שמשתמשים בעיגול בנקאי (חצי לזוגי הקרוב); SQLite לא.
• הארגומנט digits יכול להיות שלילי: ROUND(1234.5, -2) מעגל למאה הקרובה ונותן 1200.

בפועל תכתבו ROUND(price, 2) להצגת סכומי כסף יותר מכל דבר אחר.

ROUND מול CAST: הם שונים

אנשים משתמשים ב-CAST(x AS INTEGER) כשהם מתכוונים לעגל, ונכווים:

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

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

ABS, SIGN והסימן של מספר

ABS(x) מחזיר את הערך המוחלט. SIGN(x) מחזיר -1, 0 או 1 לפי הסימן:

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS הוא סוס העבודה, שימושי לשאילתות של "כמה רחוקים שני הערכים האלה זה מזה". SIGN פחות נפוץ, אבל שימושי כשרוצים לקבץ שורות לפי כיוון (חובה מול זכות, רווח מול הפסד) בלי CASE מפורש.

CEIL, FLOOR ו-TRUNC

אלה נותנים ערכים שלמים בלי עיגול לקרוב ביותר. CEIL תמיד עולה, FLOOR תמיד יורד, TRUNC תמיד הולך לכיוון אפס:

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

שימו לב למקרים השליליים. FLOOR(-2.9) הוא -3 (רחוק יותר מאפס), אבל TRUNC(-2.9) הוא -2 (לכיוון אפס). במספרים שליליים FLOOR ו-TRUNC לא מסכימים, ובחירה בלא נכון היא באג off-by-one קלאסי.

CEILING הוא שם חלופי ל-CEIL. השתמשו בכתיב שנראה לכם קריא יותר.

חילוק בשלמים הוא המלכודת האמיתית

זו לא פונקציה, זה האופרטור /, אבל הוא מכשיל מתחילים יותר מכל אחת מהפונקציות המתמטיות עצמן:

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

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

זה עוקץ הכי חזק עם הפניות לעמודות: total_cents / 100 מחזיר מספר שלם. total_cents / 100.0 מחזיר את הסכום בדולרים שבאמת רציתם.

MOD והאופרטור %

MOD(x, y) מחזיר את השארית של x / y. האופרטור % עושה את אותו דבר:

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) ו-17 % 5 מחזירים שניהם 2. מודולו באפס מחזיר NULL ב-SQLite: הוא לא זורק שגיאה, וזה חריג בהשוואה לרוב השפות. אם זה חשוב לכם, בדקו קודם את המחלק או עטפו את הקריאה ב-CASE WHEN y = 0 THEN ... END.

צורת הפונקציה וצורת האופרטור ניתנות להחלפה. רוב האנשים משתמשים ב-% כי הוא קצר יותר.

POWER, SQRT, EXP, LOG

לחזקות ולשורשים:

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

כמה הערות שתופסות אנשים:

• POW הוא שם חלופי ל-POWER.
• LOG(x) ב-SQLite הוא בבסיס 10. LN(x) הוא הלוגריתם הטבעי. LOG(b, x) עם שני ארגומנטים הוא לוגריתם בבסיס b. (זה שונה מהרבה שפות שבהן log הוא הלוגריתם הטבעי; מוסכמת ה-SQL ניצחה.)
• SQRT של מספר שלילי מחזיר NULL, לא שגיאה.
• POWER(0, 0) מחזיר 1 לפי מוסכמה.

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

RANDOM ו-RANDOMBLOB

RANDOM() מחזירה מספר שלם של 64 ביט עם סימן, בכל מקום בטווח המלא שלו:

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

כדי לקבל מספר בטווח מסוים, עטפו עם ABS (כי RANDOM() כולל סימן) והשתמשו ב-%. כדי לקבל מספר ממשי בין 0 ל-1, חלקו במספר השלם המקסימלי של 64 ביט. ל-SQLite אין RAND() מובנית שמחזירה ערך בין 0 ל-1: בונים אותה בעצמכם.

RANDOMBLOB(n) מחזירה n בתים של נתונים אקראיים, שימושי לייצור טוקנים של סשן או נתוני בדיקה. שלבו עם HEX() כדי לקבל מחרוזת שאפשר להדפיס:

SELECT HEX(RANDOMBLOB(8)) AS token;

כל קריאה מייצרת ערך חדש. אל תצפו ש-RANDOM() תחזיר את אותו מספר פעמיים באותה שורה: גם בתוך ביטוי אחד, כל הפעלה עצמאית.

לחבר את הכול יחד

דוגמה מעשית קטנה: חישוב מרחקים ועיגול מחירים בטבלת מוצרים.

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Mug',     1299, 350.5),
    (2, 'Notebook', 850, 180.2),
    (3, 'Pen',      199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

ה-price_cents / 100.0 הוא החלק החשוב: ה-.0 הזה הופך את החילוק לממשי, ואז ROUND מעצב אותו לשתי ספרות אחרי הנקודה. בלעדיו, 1299 / 100 היה נותן 12, לא 12.99.

הצעד הבא: תאריך ושעה

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