SQLite GROUP BY ו-HAVING: סינון תוצאות מצטברות

GROUP BY מקבץ שורות לקבוצות

פונקציות צבירה כמו COUNT, SUM ו-AVG מצמצמות הרבה שורות למספר אחד. GROUP BY מאפשר לעשות את זה לכל קטגוריה: מספר אחד לכל לקוח, לכל חודש, לכל סטטוס. כל ערך ייחודי (או צירוף ערכים) הופך לשורה אחת בתוצאה.

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

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

שלושה לקוחות, שלוש שורות בפלט. שש השורות המקוריות נעלמו: הן כווצו לקבוצות לפי לקוח, ו-COUNT(*) ו-SUM(amount) חושבו בתוך כל אחת.

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

מה אפשר לשים ברשימת ה-SELECT

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

CREATE TABLE sales (
    region TEXT,
    rep TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Ada', 100),
    ('North', 'Boris', 200),
    ('South', 'Cleo', 150);

-- זה עובד: region מקובצת, ו-amount עוברת צבירה.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

אם הייתם כותבים SELECT region, rep, SUM(amount) עם GROUP BY region, SQLite הייתה מריצה את זה בשמחה (היא מתירנית במקום שבו מסדי נתונים אחרים דוחים את זה), אבל rep הייתה נבחרת שרירותית מתוך הקבוצה. הייתם מקבלים שם של נציג אחד לכל אזור בלי שום הבטחה איזה. אל תסמכו על זה: קבצו לפי כל עמודה שאינה עוברת צבירה ושאתם מציגים.

HAVING מסנן קבוצות אחרי הצבירה

WHERE מסנן שורות לפני הקיבוץ. HAVING מסנן קבוצות אחרי הקיבוץ. זה כל ההבדל, וזו הסיבה שאי אפשר לשים COUNT(*) > 1 בפסוקית WHERE: בזמן ש-WHERE רץ, הספירה עוד לא קיימת.

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

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 50), ('Ada', 30),
    ('Boris', 80), ('Boris', 20), ('Boris', 15),
    ('Cleo', 200);

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

Cleo ביצעה רק הזמנה אחת, ולכן הקבוצה שלה מסוננת החוצה. Ada ו-Boris נשארים. התנאי רץ מול הערך המצטבר של כל קבוצה, לא מול שורות בודדות.

אפשר להפנות לכינויי עמודות מרשימת ה-SELECT ישירות ב-HAVING, ו-SQLite מאפשרת את זה:

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

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total >= 100;

זה לרוב קריא יותר מחזרה על SUM(amount) בפסוקית HAVING.

WHERE מול HAVING: השתמשו בשניהם יחד

שתי הפסוקיות לא באות אחת במקום השנייה. WHERE מצמצם אילו שורות משתתפות בקיבוץ; HAVING מצמצם אילו קבוצות מגיעות לפלט. רוב השאילתות האמיתיות משתמשות בשתיהן.

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

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 50, 'paid'),
    ('Ada', 30, 'refunded'),
    ('Boris', 80, 'paid'),
    ('Boris', 20, 'paid'),
    ('Cleo', 200, 'paid'),
    ('Cleo', 50, 'refunded');

SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 75;

קראו אותה מלמעלה למטה לפי סדר הביצוע:

1. WHERE status = 'paid': משמיטים לגמרי שורות שקיבלו החזר.
2. GROUP BY customer: מקבצים את מה שנשאר לפי לקוח.
3. SUM(amount) רץ לכל קבוצה.
4. HAVING SUM(amount) > 75: משאירים רק קבוצות שעוברות את הסף.

Boris (80 + 20 = 100) ו-Cleo (200) שורדים. ההזמנה המשולמת היחידה של Ada הייתה 50, וזה לא עומד בסף.

כמה תנאים וכמה עמודות קיבוץ

HAVING מקבל את אותם אופרטורים בוליאניים כמו WHERE, AND, OR, NOT, ואפשר לקבץ לפי יותר מעמודה אחת כדי לקבל תת-קבוצות:

CREATE TABLE sales (
    region TEXT,
    quarter TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Q1', 100), ('North', 'Q1', 50),
    ('North', 'Q2', 300),
    ('South', 'Q1', 80),
    ('South', 'Q2', 120), ('South', 'Q2', 60);

SELECT region, quarter, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region, quarter
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

כל זוג (region, quarter) הוא קבוצה נפרדת. פסוקית ה-HAVING דורשת גם סכום מעל 100 וגם לפחות שתי עסקאות. רק ('North', 'Q1') ו-('South', 'Q2') עומדים בתנאים.

תבנית מעשית: מציאת כפילויות

שאילתת GROUP BY ... HAVING COUNT(*) > 1 היא הדרך המקובלת למצוא ערכים כפולים בעמודה:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO users (email) VALUES
    ('ada@example.com'),
    ('boris@example.com'),
    ('ada@example.com'),
    ('cleo@example.com'),
    ('boris@example.com');

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

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

HAVING בלי GROUP BY

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (50), (30), (80);

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
HAVING SUM(amount) > 100;

שורת התוצאה היחידה מופיעה כי הסכום הוא 160. שנו את הסף ל-> 200 והשאילתה לא תחזיר שום שורה. בפועל כמעט תמיד תצמידו HAVING ל-GROUP BY, אבל טוב לדעת שהשפה לא דורשת את זה.

סיכום מהיר

• GROUP BY מכווץ שורות לקבוצות לפי מפתח; פונקציות הצבירה רצות בתוך כל קבוצה.
• כל עמודה שאינה עוברת צבירה ב-SELECT צריכה להופיע ב-GROUP BY.
• WHERE מסנן שורות לפני הקיבוץ; HAVING מסנן קבוצות אחריו.
• פונקציות צבירה כמו COUNT(*) ו-SUM(...) שייכות ל-HAVING, אף פעם לא ל-WHERE.
• HAVING מקבל תנאים מורכבים ויכול להפנות לכינויים מה-SELECT.

הצעד הבא: מפתחות זרים

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