נתוני שאילתה

הושלם

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

יחידה זו מניחה היכרות עם מושגי SQL סטנדרטיים כמו SELECT, WHERE, join ואגרגציה. אם אתה צריך רענון, נתיב הלמידה של תחילת השאילתות עם Transact-SQL הוא משאב טוב.

ביצוע שאילתה ותחום כינוי

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

סדר	סעיף	מטרה
1	FROM	זיהוי טבלאות מקור
2	WHERE	סינון שורות
3	GROUP BY	שורות קבוצה לאגרגציה
4	HAVING	קבוצות סינון
5	SELECT	בחר עמודות וחשב ביטויים
6	ORDER BY	מיון תוצאות
7	LIMIT / OFFSET	הגבלת ספירת התוצאות

כלל היקף כינוי: כינויי עמודה המוגדרים ב- SELECT נראים רק למשפטים שמתבצעים לאחרSELECT מכן — כלומר ORDER BY ו- LIMIT/OFFSET. סעיפים שמופיעים מוקדם יותר (WHERE, GROUP BY, HAVING) לא יכולים להתייחס לכינויים האלה כי הם עדיין לא מוגדרים.

זה אומר שאי אפשר לסנן לפי כינוי ב- WHERE, אבל אפשר למיין לפי אחד ב ORDER BY:

-- This FAILS: WHERE executes before SELECT, so 'msg_date' doesn't exist yet
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE msg_date > '2024-01-01';  -- Error: column "msg_date" does not exist

-- This WORKS: repeat the expression in WHERE
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01';

-- This also WORKS: ORDER BY executes after SELECT, so aliases are available
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01'
ORDER BY msg_date;

סינון ספציפי ל-PostgreSQL

PostgreSQL מרחיב SQL סטנדרטי עם אופרטורים שמפשטים משימות סינון נפוצות. תכונות אלו שימושיות ליישומי בינה מלאכותית הזקוקים לחיפוש טקסט גמיש ושאילתות מטא-דאטה מובנות.

האופרטור ILIKE מספק התאמת תבניות ללא אותיות ללא צורך בפונקציות כמו LOWER(). זה שימושי לתכונות חיפוש למשתמש שבהן אתה רוצה להתאים ללא קשר לאותיות גדולות: SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL מאפשר לך לשלוט היכן NULL הערכים מופיעים בתוצאות ממוינות באמצעות NULLS FIRST או NULLS LAST. כברירת מחדל, NULL ערכים ממוינים כאילו גדולים מכל ערך אחר. להשתמש ORDER BY ended_at NULLS LAST בו כדי לשמור שיחות לא שלמות בסוף, או ORDER BY completed_at NULLS FIRST להראות משימות לא מעובדות קודם.

הפונקציה COALESCE מחזירה את הערך הלא-אפסי הראשון מתוך הארגומנטים שלה. השתמש בו כדי לספק ערכים ברירת מחדל בתוצאות השאילתות (COALESCE(title, 'Untitled')) או לטיפול בעמודות ריקות בביטויים.

שאילתא לנתוני JSONB

סוג JSONB של PostgreSQL מאחסן נתונים מובנים שאינם מתאימים לסכימה קבועה. יישומי בינה מלאכותית משתמשים בדרך כלל ב-JSONB למטא-דאטה, קונפיגורציה, פרמטרי מודל ומבני תגובה משתנים. PostgreSQL מספקת אופרטורים מיוחדים לחילוץ ערכים, בדיקת מבנה וסינון על בסיס תוכן JSON.

משתמשים -> בו כדי לחלץ אלמנט JSON כ-JSON, או ->> לחילוץ שלו כטקסט (להשוואות ולתצוגה). לנתיבים מקוננים, השתמש #> ב(מחזיר JSON) או #>> (מחזיר טקסט). לדוגמה, metadata->>'status' מחלץ את שדה הסטטוס כטקסט, תוך כדי checkpoint_data#>>'{results,0,score}' ניווט בנתיב מקונן כדי לקבל ערך מסוים.

אופרטורי קיום והכלה מאפשרים סינון יעיל. המפעיל ? בודק קיומו של מפתח (WHERE metadata ? 'priority'), בזמן שבודק @> הכלה (WHERE checkpoint_data @> '{"status": "completed"}'). אופרטורים אלו יכולים להשתמש באינדקסים של GIN לסינון יעיל בטבלאות גדולות.

כאשר עמודות JSONB מכילות מערכים, השתמש jsonb_array_elements בהרחבתן לסינון או אגרגציה:

-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
     jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';

דפדוף יעיל עם עמודי מפתחות

עמודים מסורתיים OFFSETמבוססי -הופכים לאיטיים בטבלאות גדולות כי PostgreSQL חייב לסרוק ולהשליך את כל השורות שנדלגו. עמוד 1,000 עם 20 שורות בכל עמוד דורש סריקה של 20,000 שורות והשלכת 19,980. עמוד מפתחות (המכונה גם עמוד מבוסס סמן) משתמש WHERE במשפטים כדי לדלג על שורות, וזה מתבצע באופן עקבי בלי קשר לעומק הדפים.

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

-- First page: get the 20 most recent messages
SELECT id, conversation_id, content, created_at
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: filter by the last seen timestamp and id
SELECT id, conversation_id, content, created_at
FROM messages
WHERE (created_at, id) < ('2024-06-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

הכללה id בשני המשפטים ORDER BY ו WHERE --היא מטפלת בקשרים כאשר למספר שורות יש חותמת זמן זהה. היישום שלך שומר את ערכי המיון של השורה האחרונה ומעביר אותם לשאילתה הבאה. לסדר עולה, החלף < ל- > ו DESC - ל- ASC.

ביטויים נפוצים בטבלה (CTEs)

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

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

WITH recent_conversations AS (
    SELECT id, user_id, started_at
    FROM conversations
    WHERE started_at > CURRENT_DATE - INTERVAL '7 days'
),
message_stats AS (
    SELECT conversation_id, COUNT(*) AS message_count, MAX(created_at) AS last_message_at
    FROM messages
    GROUP BY conversation_id
)
SELECT rc.user_id, rc.started_at, COALESCE(ms.message_count, 0) AS message_count
FROM recent_conversations rc
LEFT JOIN message_stats ms ON rc.id = ms.conversation_id;

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

WITH RECURSIVE task_tree AS (
    -- Base case: start with the parent task
    SELECT id, parent_id, title, 1 AS depth
    FROM tasks WHERE id = 1
    UNION ALL
    -- Recursive case: find children of current level
    SELECT t.id, t.parent_id, t.title, tt.depth + 1
    FROM tasks t
    INNER JOIN task_tree tt ON t.parent_id = tt.id
    WHERE tt.depth < 10
)
SELECT * FROM task_tree ORDER BY depth, id;

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

WITH RECURSIVE thread AS (
    -- Start with the root message
    SELECT id, parent_id, content, role, 0 AS depth
    FROM messages
    WHERE id = :root_message_id

    UNION ALL

    -- Get all replies
    SELECT m.id, m.parent_id, m.content, m.role, t.depth + 1
    FROM messages m
    INNER JOIN thread t ON m.parent_id = t.id
    WHERE t.depth < 50
)
SELECT * FROM thread ORDER BY depth, id;

הכנסה עם חזרה

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

-- Get the generated ID after inserting a conversation
INSERT INTO conversations (user_id, session_id)
VALUES ('user123', 'sess_abc')
RETURNING id;

-- Get multiple generated values
INSERT INTO messages (conversation_id, role, content)
VALUES (1, 'user', 'Hello')
RETURNING id, created_at;

-- Use RETURNING with UPDATE
UPDATE tasks SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = 5
RETURNING id, status, completed_at;

UPSERTS עם ON CONFLICT

הסעיף INSERT ... ON CONFLICT מטפל בהפרות אילוצים ייחודיות, ומאפשר פעולות "upsert" שמוסיפות שורות חדשות או מעדכנות שורות קיימות. תבנית זו חשובה לפעולות אידמפוטנטיות ולניהול מצבים ביישומי בינה מלאכותית.

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

INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES ('user123', 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET
    preference_value = EXCLUDED.preference_value,
    updated_at = CURRENT_TIMESTAMP;

להשתמש DO NOTHING כדי לדלג בשקט על שורות שעלולות להפר מגבלות: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

ניתן להוסיף WHERE פסוק ל- DO UPDATE עבור לוגיקה מותנית, ולעדכן רק כאשר הערך החדש שונה מהקיים. שלב ON CONFLICT עם RETURNING כדי לדעת אם שורה הוכנסה או עודכנה—הביטוי (xmax = 0) חוזר true עבור שורות שהוכנסו חדשות ולשורות false מעודכנות.

דפוסים מעשיים ליישומי בינה מלאכותית

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

WITH conversation_context AS (
    SELECT c.id, c.session_id, c.metadata->>'model' AS model
    FROM conversations c
    WHERE c.session_id = :session_id AND c.ended_at IS NULL
)
SELECT cc.session_id, cc.model, m.role, m.content, m.created_at
FROM conversation_context cc
INNER JOIN messages m ON cc.id = m.conversation_id
ORDER BY m.created_at
LIMIT 50;

הדוגמה הבאה מציגה רישום נקודות ביקורת של משימות עם ניהול מצב באמצעות upsert:

INSERT INTO task_checkpoints (task_id, step_number, checkpoint_data)
VALUES (:task_id, :step_number, :checkpoint_json::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET checkpoint_data = EXCLUDED.checkpoint_data, updated_at = CURRENT_TIMESTAMP
RETURNING id, created_at, (xmax = 0) AS is_new;

הדוגמה הבאה מציגה חיפוש מודפס עם מסנני JSONB:

SELECT c.id, c.session_id, c.started_at, c.metadata->>'status' AS status
FROM conversations c
WHERE c.user_id = :user_id
  AND c.metadata @> :filter_json::jsonb
  AND (c.started_at, c.id) < (:last_started_at, :last_id)
ORDER BY c.started_at DESC, c.id DESC
LIMIT 20;

משאבים נוספים

• פונקציות ואופרטורים של PostgreSQL JSON
• PostgreSQL INSERT documentation
• PostgreSQL WITH שאילתות (CTEs)