ข้อมูลคิวรี

หน่วยนี้ครอบคลุมคุณลักษณะและรูปแบบการสืบค้นเฉพาะ PostgreSQL ที่จําเป็นสําหรับแอปพลิเคชัน AI หน่วยนี้มุ่งเน้นไปที่ความสามารถเฉพาะของ PostgreSQL และรูปแบบขั้นสูงสําหรับการจัดการประวัติการสนทนาสถานะการประมวลผลและข้อมูลเมตาของแอปพลิเคชัน

หน่วยนี้ถือว่าคุ้นเคยกับแนวคิด SQL มาตรฐาน เช่น SELECT, WHERE, รวม และการรวม หากคุณต้องการทบทวน เส้นทางการเรียนรู้ เริ่มต้นใช้งานคิวรีด้วย Transact-SQL เป็นแหล่งข้อมูลที่ดี

การดําเนินการคิวรีและขอบเขตนามแฝง

แม้แต่นักพัฒนา SQL ที่มีประสบการณ์ก็ยังสะดุดกับลําดับการดําเนินการ โดยเฉพาะอย่างยิ่งเมื่อใช้นามแฝงคอลัมน์ คําสั่ง SQL จะดําเนินการในลําดับตรรกะที่เฉพาะเจาะจงซึ่งแตกต่างจากวิธีที่คุณเขียน และการทําความเข้าใจลําดับนี้จะช่วยให้คุณแก้ไขปัญหาข้อผิดพลาด "คอลัมน์ไม่มีอยู่จริง" ที่คลุมเครือ

กฎขอบเขตนามแฝง: นามแฝงคอลัมน์ที่กําหนดไว้ใน 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 มาตรฐานด้วยตัวดําเนินการที่ลดความซับซ้อนของงานการกรองทั่วไป คุณลักษณะเหล่านี้มีประโยชน์สําหรับแอปพลิเคชัน AI ที่ต้องการการค้นหาข้อความที่ยืดหยุ่นและการสืบค้นข้อมูลเมตาที่มีโครงสร้าง

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 ส่งคืนค่าที่ไม่ใช่ Null แรกจากอาร์กิวเมนต์ ใช้เพื่อระบุค่าเริ่มต้นในผลลัพธ์แบบสอบถาม (COALESCE(title, 'Untitled')) หรือเพื่อจัดการคอลัมน์ที่ Nullable ในนิพจน์

สืบค้นข้อมูล JSONB

ประเภท JSONB ของ PostgreSQL จัดเก็บข้อมูลที่มีโครงสร้างที่ไม่ตรงกับสคีมาคงที่ แอปพลิเคชัน AI มักใช้ 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

นิพจน์ตารางทั่วไป (CTE)

CTE กําหนดชุดผลลัพธ์ชั่วคราวที่มีชื่อซึ่งมีอยู่ระหว่างการดําเนินการคิวรีเท่านั้น ปรับปรุงความสามารถในการอ่านโดยให้คุณสร้างคิวรีที่ซับซ้อนทีละขั้นตอน และเปิดใช้งานคิวรีแบบเรียกซ้ําสําหรับข้อมูลตามลําดับชั้น

ใช้ 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;

CTE แบบเรียกซ้ําจะสืบค้นโครงสร้างแผนผัง เช่น ลําดับชั้นของงาน แผนผังองค์กร หรือการสนทนาแบบเธรด ประกอบด้วยกรณีพื้นฐาน (จุดยึด) และกรณีเรียกซ้ําที่อ้างอิงถึง 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;

แอปพลิเคชัน AI มักจะต้องดึงเธรดการสนทนาที่ข้อความอ้างอิงข้อความหลัก:

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 จะดึงค่าจากแถวที่แทรก อัปเดต หรือลบในครั้งเดียว นี่เป็นสิ่งสําคัญสําหรับการรับ ID การประทับเวลา หรือค่าเริ่มต้นที่คํานวณโดยอัตโนมัติโดยไม่ต้องใช้คิวรีแยกต่างหาก

-- 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" ที่แทรกแถวใหม่หรืออัปเดตแถวที่มีอยู่ รูปแบบนี้มีประโยชน์สําหรับการดําเนินงานที่สมบูรณ์และการจัดการสถานะในแอปพลิเคชัน AI

เมื่อเกิดข้อขัดแย้งบนข้อจํากัดที่ไม่ซ้ํากัน คุณสามารถอัปเดตแถวที่มีอยู่ด้วยค่าใหม่ได้โดยใช้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 สําหรับแถวที่อัปเดต

รูปแบบการปฏิบัติสําหรับแอปพลิเคชัน AI

รูปแบบเหล่านี้รวมคุณลักษณะที่กล่าวถึงก่อนหน้านี้เข้ากับโซลูชันสําหรับข้อกําหนดทั่วไปของแอปพลิเคชัน AI คิวรีต่อไปนี้จะดึงประวัติการสนทนาพร้อมข้อมูลเมตาสําหรับการสร้างบริบท:

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
• PostgreSQL พร้อมการสืบค้น (CTE)