פונקציות צבירה ב-SQL ב-SQLite: COUNT, SUM, AVG, MIN, MAX

מה פונקציית צבירה עושה בפועל

רוב הפונקציות ב-SQL שפגשתם עד עכשיו פועלות שורה אחר שורה: UPPER(name) רצה פעם אחת לכל שורה, ROUND(price, 2) רצה פעם אחת לכל שורה. פונקציות צבירה שונות. הן מסתכלות על קבוצה שלמה של שורות ומכווצות אותה לערך יחיד.

בנו טבלה קטנה להתנסות בה:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

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

COUNT: שורות מול ערכים

ל-COUNT יש שלוש צורות, וההבדל ביניהן חשוב:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) סופר שורות. NULL נכללים. תמיד מחזיר מספר.
• COUNT(column) סופר ערכים שאינם NULL בעמודה הזו.
• COUNT(DISTINCT column) סופר ערכים ייחודיים שאינם NULL.

חמש שורות, בשלוש מהן יש amount, שלושה לקוחות שונים. אם אי פעם תראו ש-COUNT(amount) קטן מ-COUNT(*) ותתהו למה, זו הסיבה: NULL לא נספרים.

SUM, AVG, MIN, MAX

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG היא (10 + 20 + 30) / 3 = 20.0, לא 60 / 4 = 15.0. המכנה הוא מספר הערכים שאינם NULL. אם זה לא מה שאתם רוצים, למשל אם אתם מעדיפים להתייחס לנתונים חסרים כאפס, כתבו את זה במפורש:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

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

SUM מול TOTAL

ל-SQLite יש פונקציית צבירה נוספת שדומה לסכום, TOTAL, שפותרת שני מטרדים של SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM על אפס שורות מחזירה NULL. TOTAL מחזירה 0.0.
• SUM כשכל הערכים הם NULL מחזירה NULL. TOTAL מחזירה 0.0.
• TOTAL מחזירה תמיד מספר נקודה צפה, ולכן היא אף פעם לא גולשת כמו חשבון שלמים.

המחיר: TOTAL אינה חלק מהתקן, והתוצאה שהיא תמיד REAL עלולה להפתיע אם ציפיתם למספר שלם. השתמשו בה כש"אין שורות פירושו אפס" היא התשובה הנכונה לאפליקציה שלכם, והישארו עם SUM כשאתם רוצים את ההתנהגות של תקן SQL.

DISTINCT בתוך פונקציות צבירה

אפשר לשים DISTINCT בתוך כל פונקציית צבירה, לא רק ב-COUNT. הוא מסיר ערכים כפולים לפני שהצבירה רצה:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) מחברת את הסכום של כל שורה. SUM(DISTINCT amount) מחברת כל סכום ייחודי פעם אחת בלבד: שימושי למשהו כמו "סך סכומי החשבוניות הייחודיים", אבל לרוב זה לא מה שאתם רוצים. COUNT(DISTINCT customer) היא הנפוצה.

FILTER: צבירה של תת קבוצה

כשרוצים לצבור רק חלק מהשורות, הצעד המתבקש הוא WHERE. אבל WHERE מסנן את הכול, כך שאי אפשר לשלב באותה שאילתה "ספירת הזמנות ששולמו" ו"ספירת החזרים". FILTER פותר את זה:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

כל פסוקית FILTER (WHERE ...) חלה רק על פונקציית הצבירה הזו. מעבר אחד על הטבלה, כמה פרוסות מסוכמות. לפני ש-FILTER הייתה קיימת, כתבו SUM(CASE WHEN status = 'paid' THEN amount END): אותו רעיון, יותר הקלדה.

GROUP_CONCAT: חיבור מחרוזות

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

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

המפריד ברירת המחדל הוא פסיק. העבירו ארגומנט שני כדי להשתמש במשהו אחר. הסדר לא מובטח, אלא אם כותבים את הקריאה כ-GROUP_CONCAT(tag ORDER BY tag), וזה שימושי כשהפלט מוצג בממשק ואתם רוצים שיהיה יציב.

צבירה בלי GROUP BY

כל דוגמה עד עכשיו שהשתמשה בפונקציות צבירה בלי GROUP BY הפיקה בדיוק שורה אחת. זה הכלל: SELECT עם פונקציות צבירה ובלי GROUP BY הוא סיכום של שורה אחת לכל הטבלה (אחרי WHERE).

אפשר לשלב פונקציות צבירה בחופשיות:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

מה ש_אי אפשר_ לעשות זה לשלב עמודות שאינן מצורפות עם פונקציות צבירה ולצפות לתוצאות הגיוניות:

-- מותר ב-SQLite, אבל הערך של `customer` שרירותי.
SELECT customer, SUM(amount) FROM orders;

SQLite לא תזרוק כאן שגיאה (מסדי נתונים אחרים כן), אבל היא תבחר שם של לקוח אקראי כלשהו להציג לצד הסכום. אם אתם רוצים סכום לכל לקוח, צריך GROUP BY, וזה הנושא של העמוד הבא.

הבא בתור: GROUP BY ו-HAVING

פונקציות צבירה על כל הטבלה עונות על "כמה בסך הכול". פונקציות צבירה לכל קבוצה, לכל לקוח, לכל חודש, לכל סטטוס, עונות על השאלות המעניינות יותר. GROUP BY היא הדרך לפצל את השורות לדליים לפני הצבירה, ו-HAVING היא הדרך לסנן לפי התוצאה המצורפת. זה מה שמחכה בעמוד הבא.