CTE ב-SQLite: הסבר על פסוקית WITH

CTE היא תת שאילתה עם שם

Common Table Expression (CTE) היא תת שאילתה שהוצאתם החוצה ונתתם לה שם. במקום לקנן SELECT בתוך SELECT אחר, מגדירים אותה למעלה עם WITH, נותנים לה שם, ואז משתמשים בשם הזה בשאילתה הראשית כאילו היה טבלה.

המבנה תמיד זהה:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

קראו את זה מלמעלה למטה: קודם בונים תוצאה עם שם, customer_totals, ואז שואלים את התוצאה הזו. ה-CTE מתנהגת כמו view זמני שקיים רק לאורך הפקודה הזו.

אותה שאילתה בלי CTE

הנה אותה לוגיקה כתובה כתת שאילתה, כדי שתוכלו לראות מה ה-CTE מחליפה:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

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

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

כמה CTE בשאילתה אחת

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

WITH אחד, ואחריו הגדרות CTE מופרדות בפסיקים. ה-CTE השנייה (big_spenders) קוראת מהראשונה (customer_totals) בדיוק כמו שקוראים מטבלה. ה-SELECT הראשי בא אחרי הגדרת ה-CTE האחרונה.

טעות נפוצה: לכתוב שוב WITH לפני ה-CTE השנייה. אל תעשו את זה: זו שגיאת תחביר. WITH אחד מכסה את כולן.

פנייה ל-CTE יותר מפעם אחת

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

פונים ל-CTE פעמיים: פעם אחת כדי לחשב את הממוצע, ופעם כמקור הראשי. בלי ה-CTE, הייתם משכפלים את שאילתת ה-GROUP BY, וכל שינוי בה היה צריך להיעשות בשני מקומות.

CTE עם INSERT, UPDATE ו-DELETE

CTE הן לא רק ל-SELECT. אפשר לשים פסוקית WITH לפני INSERT, UPDATE או DELETE כדי להשתמש בתת שאילתה עם שם בפעולת כתיבה:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

ה-CTE מתארת אילו שורות לסמן. ה-INSERT ... SELECT משתמש בה כמקור. אותו טריק עובד עם DELETE FROM ... WHERE id IN (SELECT id FROM cte) למחיקות בשלבים, כשהלוגיקה שבוחרת את השורות מסובכת.

מתי להשתמש ב-CTE

כמה כללי אצבע:

• לשאילתה יש יותר משלב לוגי אחד. צבירה, ואז סינון לפי הצבירה, ואז JOIN לתוצאה: זה צינור, ו-CTE לכל שלב הופכת אותו לקריא.
• אחרת הייתם חוזרים על אותה תת שאילתה. הגדירו אותה פעם אחת, פנו אליה פעמיים.
• לתת השאילתה מגיע שם. אם הייתם שמים הערה מעל תת השאילתה כדי להסביר מה היא מייצגת, השם של ה-CTE הוא ההערה הזו, והתחביר אוכף אותה.
• אתם עומדים לכתוב שאילתה רקורסיבית. זה אפשרי רק עם WITH RECURSIVE, והנושא מכוסה בעמוד הבא.

מתי לא לטרוח:

• תת שאילתה פשוטה אחת שמשמשת במקום אחד. WHERE id IN (SELECT id FROM ...) בסדר גמור כמו שהוא.
• שאילתות קריטיות לביצועים, שבהן כבר וידאתם שכתיבת הלוגיקה בתוך השאילתה עוזרת. בדרך כלל SQLite מתייחסת ל-CTE כמחסום אופטימיזציה פחות מכמה מסדי נתונים אחרים, אבל בנתיבים חמים שווה לבדוק עם EXPLAIN QUERY PLAN.

דוגמה מלאה

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

שתי CTE, וכל אחת עושה דבר אחד. ה-SELECT הראשי מעצב את התוצאה. אפשר לקרוא את השאילתה מלמעלה למטה ולהבין כל שלב בפני עצמו, וזה כל הרעיון של CTE.

הבא בתור: CTE רקורסיביות

כל מה שראינו עד עכשיו היה CTE רגילה: תת שאילתה עם שם, שמחושבת פעם אחת. SQLite תומכת גם ב-WITH RECURSIVE, שבה CTE פונה לעצמה כדי לעבור על היררכיות, לייצר רצפים או לעבור על גרפים. זה העמוד הבא.