Menu

string_agg ו-array_agg ב-PostgreSQL (כמו GROUP_CONCAT)

string_agg מחבר את הערכים של קבוצה למחרוזת אחת ו-array_agg אוסף אותם למערך. סדר, DISTINCT, ‏FILTER, טיפול ב-NULL והשגיאות שאנשים נתקלים בהן.

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

string_agg(expression, separator) מחבר את הערכים של כל קבוצה למחרוזת אחת, ו-array_agg(expression) אוסף אותם למערך. שימו ORDER BY בתוך הקריאה כדי לשלוט בסדר: string_agg(name, ', ' ORDER BY name).

IT מחזירה Bo, Kim, Li ו-{6100,4800,3900}; ‏Sales מחזירה Ana, Raj ו-{5000,4200}. שתיהן פונקציות צבירה רגילות כמו count או sum: הן עובדות לכל קבוצה עם GROUP BY, ועל כל הטבלה בלעדיו.

לסדר את הערכים

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

string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)

להסיר כפילויות עם DISTINCT

Ada קנתה עט פעמיים, אבל DISTINCT מציג אותו פעם אחת: book, pen. עם DISTINCT, ה-ORDER BY יכול להשתמש רק בערך שנצבר. מיון לפי עמודה אחרת נכשל עם "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".

איך מתנהגים NULL וקבוצות ריקות

string_agg מדלג על ערכי NULL ומחזיר Bo, Li. ‏array_agg שומר אותם: {Bo,Li,NULL}. כדי להשמיט NULL מ-array_agg, הוסיפו FILTER (WHERE name IS NOT NULL).

על אפס שורות שתיהן מחזירות NULL, לא מחרוזת ריקה או מערך ריק. עטפו אותן כשהקוד הקורא מצפה לערך: COALESCE(string_agg(...), '') או COALESCE(array_agg(...), '{}').

לצבור רק חלק מהשורות עם FILTER

FILTER (WHERE ...) מגביל פונקציית צבירה אחת לשורות שמתאימות, בזמן ששאר השאילתה רואה את כולן:

ב-IT יש 3 אנשים והרשימה היא Bo, Li; ב-Sales יש 2 והרשימה היא Ana.

לצרף רשימה לכל שורה של טבלה אחרת

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

SELECT c.id, c.name, c.email,
       (SELECT string_agg(o.product, ', ' ORDER BY o.product)
        FROM orders o
        WHERE o.customer_id = c.id) AS products
FROM customers c;

לקוחות בלי הזמנות נשארים בתוצאה, עם products שהוא NULL. צירוף עם GROUP BY שומר אותם רק אם הוא LEFT JOIN.

שגיאות נפוצות וגרסאות

  • מספרים: string_agg מקבל טקסט, ולכן string_agg(id, ',') נכשל עם "function string_agg(integer, unknown) does not exist". המירו: string_agg(id::text, ',').
  • N הראשונים בכל קבוצה: חתכו את המערך הממוין, (array_agg(name ORDER BY salary DESC))[1:2].
  • פלט JSON: ‏json_agg ו-jsonb_agg אוספים שורות למערך JSON, ו-jsonb_object_agg(key, value) בונה אובייקט; ראו JSONB.
  • כבר יש לכם מערך: array_to_string(arr, ', ') מחבר מערך קיים, ו-unnest הופך אותו בחזרה לשורות (מערכים).
  • גרסת חלון: שתיהן עובדות גם כפונקציות חלון, למשל string_agg(name, ', ') OVER (PARTITION BY dept) כדי להציג את כל הרשימה בכל שורה.

שאלות נפוצות

מה המקבילה של GROUP_CONCAT ב-PostgreSQL?

string_agg(column, ','). בניגוד ל-GROUP_CONCAT של MySQL, המפריד הוא חובה, והסדר נכתב בתוך הקריאה: string_agg(name, ', ' ORDER BY name).

איך משתמשים ב-string_agg עם מספרים?

המירו את הערך לטקסט: string_agg(id::text, ','). ‏string_agg מקבל רק טקסט (או bytea), ולכן ארגומנט מספרי נכשל עם "function string_agg(integer, unknown) does not exist".

האם יש ל-string_agg מגבלת אורך?

אין מגבלה נפרדת; התוצאה היא ערך text רגיל, שיכול להחזיק עד 1 GB. ‏GROUP_CONCAT של MySQL חותך ב-group_concat_max_len, אבל PostgreSQL לא חותך.

איך מקבלים את N הערכים הראשונים עם array_agg?

ממיינים בתוך פונקציית הצבירה וחותכים את התוצאה: (array_agg(name ORDER BY score DESC))[1:3] מחזיר את שלושת השמות המובילים בכל קבוצה.

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

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

להתחיל