SQLite DISTINCT: הסרת כפילויות משאילתות SELECT

DISTINCT מסיר שורות כפולות

כברירת מחדל, SELECT מחזיר כל שורה מתאימה, כולל כפילויות. DISTINCT אומר ל-SQLite לכווץ שורות שזהות בכל העמודות שבחרתם, כך שכל צירוף ייחודי מופיע רק פעם אחת.

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT DISTINCT customer FROM orders;

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

DISTINCT חל על כל רשימת ה-SELECT

זה מפיל אנשים. DISTINCT לא בוחר עמודה אחת להסיר בה כפילויות; הוא מסיר שורות כפולות שלמות לפי כל העמודות שבחרתם.

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT DISTINCT customer, country FROM orders;

כל זוג (customer, country) ייחודי מופיע פעם אחת. אם אותו לקוח היה מופיע עם שתי מדינות שונות, הייתם רואים את שתי השורות: מבחינת SQLite הן לא כפולות.

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

COUNT(DISTINCT col)

צורך נפוץ: כמה ערכים ייחודיים יש בעמודה? COUNT(*) סופר שורות, COUNT(col) סופר ערכים שאינם NULL, ו-COUNT(DISTINCT col) סופר ערכים ייחודיים שאינם NULL.

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT
    COUNT(*)                  AS total_orders,
    COUNT(DISTINCT customer)  AS unique_customers,
    COUNT(DISTINCT country)   AS unique_countries
FROM orders;

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

שימו לב ש-SQLite מאפשרת רק עמודה אחת בתוך COUNT(DISTINCT ...). כדי לספור צירופים ייחודיים של כמה עמודות, עטפו אותן בתת שאילתה: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t).

איך DISTINCT מתייחס ל-NULL

ל-NULL יש מוניטין מוזר ב-SQL, כי NULL = NULL מחזיר NULL, לא TRUE. אבל DISTINCT עושה חריגה מיוחדת: לצורך הסרת כפילויות, כל ה-NULL נחשבים שווים זה לזה.

CREATE TABLE contacts (name TEXT, email TEXT);
INSERT INTO contacts VALUES
    ('Ada',   'ada@example.com'),
    ('Boris', NULL),
    ('Cleo',  NULL),
    ('Dan',   'dan@example.com'),
    ('Eve',   NULL);

SELECT DISTINCT email FROM contacts;

חוזרות שלוש שורות: 'ada@example.com', 'dan@example.com' ו-NULL יחיד. שלוש כתובות האימייל שהן NULL התכווצו לאחת. אותו כלל חל על GROUP BY ועל פעולות קבוצה כמו UNION: שימושי לזכור כשמחפשים את התשובה ל"למה שורת ה-NULL הזו מופיעה פעם אחת במקום שלוש?"

DISTINCT רץ לפני ORDER BY ו-LIMIT

לפסוקיות ב-SELECT יש סדר לוגי: FROM, ואז WHERE, ואז GROUP BY, ואז HAVING, ואז SELECT/DISTINCT, ואז ORDER BY, ולבסוף LIMIT. כך ש-DISTINCT מסנן כפילויות קודם, אחר כך ORDER BY ממיין את מה שנשאר, ואז LIMIT חותך.

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0),
    (5, 'Boris', 30.0);

SELECT DISTINCT customer
FROM orders
WHERE amount > 15
ORDER BY customer
LIMIT 2;

WHERE משאיר ארבע שורות, DISTINCT מכווץ את הכפילויות של Boris, ORDER BY ממיין לפי סדר אלפביתי, LIMIT מחזיר את השתיים הראשונות. שווה לעקוב אחרי זה פעם אחת: בלבול לגבי סדר התוצאות נובע בדרך כלל משכחה של איזה שלב קורה מתי.

DISTINCT מול GROUP BY

להסרת כפילויות בלבד, שתי השאילתות האלה מחזירות את אותן שורות:

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0);

SELECT DISTINCT customer FROM orders;
SELECT customer FROM orders GROUP BY customer;

אותה תוצאה. ההבדל הוא מה אפשר לעשות אחר כך:

• DISTINCT נועד ל"תנו לי שורות ייחודיות" ותו לא.
• GROUP BY נועד ל"חלקו שורות לדליים וחשבו משהו לכל דלי": COUNT(*), SUM(amount), MAX(created_at) וכן הלאה.

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

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0);

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

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

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

• ביצועים. DISTINCT דורש בדרך כלל ש-SQLite תמיין את השורות או תבצע עליהן hash כדי למצוא כפילויות. בתוצאות גדולות, אינדקס על העמודה או העמודות שבהן מסירים כפילויות עוזר. אם אתם עושים SELECT DISTINCT על כל העמודות של טבלה רחבה, שאלו את עצמכם אם אתם באמת צריכים כל עמודה.
• DISTINCT * נדיר. הוא חוקי, SELECT DISTINCT * FROM t מסיר שורות כפולות שלמות, אבל אם לטבלה יש מפתח ראשי, כל שורה כבר ייחודית, כך שזה לא עושה שום דבר מועיל.
• אל תבלבלו עם UNIQUE. UNIQUE הוא אילוץ על טבלה שמונע מלכתחילה הכנסה של ערכים כפולים. DISTINCT הוא מסנן בזמן השאילתה שמסתיר כפילויות בתוצאה. כלים שונים, תפקידים שונים.

הבא בתור: ביטויי CASE

ברגע שאתם יודעים לעצב שורות תוצאה עם SELECT, WHERE, ORDER BY ו-DISTINCT, הצעד הבא הוא לוגיקה מותנית בתוך שאילתה. ביטויי CASE מאפשרים להחזיר ערכים שונים לפי תנאים, המקבילה ב-SQL לסולם של if/else, והעמוד הבא מכסה אותם.