Menu

DISTINCT ON ב-PostgreSQL: שורה אחת לכל קבוצה

SELECT DISTINCT ON (key) ... ORDER BY key, sort שומר את השורה הראשונה בכל קבוצה, למשל ההזמנה האחרונה של כל לקוח. זה קיים רק ב-PostgreSQL.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

SELECT DISTINCT ON (expr) ... מחזיר שורה אחת לכל ערך שונה של expr: השורה הראשונה בקבוצה לפי סדר ה-ORDER BY. מיינו קודם לפי מפתח הקבוצה, ואז לפי מה שקובע מי "ראשונה". להזמנה האחרונה של כל לקוח, זה ORDER BY customer, created_at DESC.

השאילתה מחזירה שלוש שורות: הזמנה 2 של asha, ‏4 של ben ו-5 של chen, האחרונה של כל לקוח. DISTINCT ON הוא הרחבה של PostgreSQL. מסדי נתונים אחרים צריכים את הצורה עם פונקציית חלון שמופיעה בהמשך.

איך ה-ORDER BY קובע איזו שורה נשמרת

PostgreSQL ממיין את השורות, ואז שומר את השורה הראשונה בכל פעם שהערך של DISTINCT ON משתנה. לכן:

  • ה-ORDER BY חייב להתחיל בביטויים של DISTINCT ON, אחרת PostgreSQL מעלה "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • בלי ORDER BY שממשיך אחרי הביטויים האלה, אי אפשר לדעת איזו שורה תישמר, והיא יכולה להשתנות בין הרצות.

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

עכשיו השורה של asha היא הזמנה 1 (40) ושל ben היא הזמנה 3 (60).

לשבור תיקו בכוונה

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

לשורות 2 ו-3 יש 95 נקודות. id כמפתח האחרון קובע ששורה 2 מנצחת.

קיבוץ לפי יותר מעמודה אחת

כתבו כמה ביטויים כדי לקבל שורה אחת לכל צירוף:

כל זוג של חיישן וסוג שומר את הקריאה האחרונה שלו: s1 לחות 40, ‏s1 טמפרטורה 22, ‏s2 טמפרטורה 19.

הגרסה הניידת עם ROW_NUMBER

ROW_NUMBER() ממספר את השורות בתוך כל partition. שמירה של מספר 1 נותנת את אותה תוצאה כמו DISTINCT ON, וזה עובד גם ב-MySQL 8, ב-SQL Server וב-SQLite:

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

השתמשו בצורה הזו כשצריך את n הראשונות בכל קבוצה (rn <= 3) או כשהשאילתה צריכה לרוץ על מסד נתונים אחר. הדף על פונקציות חלון מסביר את PARTITION BY.

להפוך את זה למהיר

DISTINCT ON ממיין את הקלט, אז בטבלה גדולה תנו לו אינדקס שמתאים ל-ORDER BY:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

אז PostgreSQL יכול לקרוא את השורות לפי הסדר במקום למיין אותן. כשיש מעט קבוצות והרבה שורות בכל קבוצה, צירוף LATERAL שלוקח LIMIT 1 לכל לקוח מאותו אינדקס בדרך כלל קורא פחות שורות. הדף על צירופים מציג את LATERAL. השוו בין השניים עם EXPLAIN ANALYZE על הנתונים שלכם.

שאלות נפוצות

מה ההבדל בין DISTINCT ל-DISTINCT ON?

DISTINCT מסיר שורות שזהות בכל העמודות הנבחרות. DISTINCT ON (a) שומר שורה אחת לכל ערך של a, ושאר העמודות מגיעות מהשורה שממוינת ראשונה בקבוצה הזו.

האם DISTINCT ON הוא SQL סטנדרטי?

לא. זו הרחבה של PostgreSQL, ולכן היא לא עובדת ב-MySQL או ב-SQL Server. הגרסה הניידת היא ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) בתוך תת שאילתה, עם סינון למספר שורה 1.

למה אני מקבל "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

ה-ORDER BY חייב להתחיל באותם ביטויים כמו DISTINCT ON, באותו סדר. כתבו DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, ולא ORDER BY created_at DESC לבד.

האם DISTINCT ON יכול להחזיר את 3 השורות הראשונות בכל קבוצה?

לא, הוא מחזיר בדיוק שורה אחת לכל קבוצה. ל-n הראשונות, מספרו את השורות עם ROW_NUMBER() והשאירו rn <= 3, או השתמשו בתת שאילתת LATERAL עם LIMIT 3.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל