क्वेरी डेटा

12 मिनट्स

यह इकाई PostgreSQL-विशिष्ट क्वेरी सुविधाओं और पैटर्न को कवर करती है जो AI अनुप्रयोगों के लिए आवश्यक हैं। इकाई PostgreSQL की अनूठी क्षमताओं और वार्तालाप इतिहास, प्रसंस्करण स्थिति और एप्लिकेशन मेटाडेटा के प्रबंधन के लिए उन्नत पैटर्न पर ध्यान केंद्रित करती है।

यह इकाई मानक SQL अवधारणाओं जैसे , SELECTWHERE, जोड़ता है और एकत्रीकरण से परिचित है। यदि आपको एक पुनश्चर्या की आवश्यकता है, तो Transact-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 पहले निष्पादित करने वाले खंड (,WHEREGROUP 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 आपको यह नियंत्रित करने देता है कि या का NULLS LASTउपयोग करके NULLS FIRST क्रमबद्ध परिणामों में मान कहां NULL दिखाई देते हैं। डिफ़ॉल्ट रूप से, NULL मान किसी भी अन्य मान से बड़े के रूप में सॉर्ट करते हैं। अंत में अपूर्ण वार्तालापों को रखने के लिए, या पहले ORDER BY completed_at NULLS FIRST असंसाधित कार्यों को दिखाने के लिए उपयोग करेंORDER BY ended_at NULLS LAST।

COALESCE फ़ंक्शन अपने तर्कों से पहला गैर-शून्य मान लौटाता है। क्वेरी परिणामों ()COALESCE(title, 'Untitled') में डिफ़ॉल्ट मान प्रदान करने के लिए या व्यंजकों में शून्य स्तंभों को हैंडल करने के लिए इसका उपयोग करें.

JSONB डेटा क्वेरी करें

PostgreSQL का JSONB प्रकार संरचित डेटा संग्रहीत करता है जो एक निश्चित स्कीमा में फिट नहीं होता है। 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;

और क्लॉज WHERE दोनों में ORDER BY शामिल होने से id संबंध तब होते हैं जब कई पंक्तियों में एक ही टाइमस्टैम्प होता है। आपका एप्लिकेशन अंतिम पंक्ति के सॉर्ट मानों को संग्रहीत करता है और उन्हें अगली क्वेरी पर भेजता है। आरोही क्रम के लिए, में और > करने DESC के लिए ASCबदलें<।

सामान्य तालिका अभिव्यक्ति (सीटीई)

सीटीई नामित अस्थायी परिणाम सेट को परिभाषित करते हैं जो केवल किसी क्वेरी के निष्पादन के दौरान मौजूद होते हैं। वे आपको जटिल क्वेरीज़ को चरण दर चरण बनाने देकर पठनीयता में सुधार करते हैं, और वे पदानुक्रमित डेटा के लिए पुनरावर्ती क्वेरीज़ सक्षम करते हैं।

जटिल प्रश्नों को तार्किक चरणों में विभाजित करने के लिए सीटीई का उपयोग करें। प्रत्येक सीटीई पहले से परिभाषित सीटीई का संदर्भ दे सकता है, जिससे परिवर्तनों की एक पाइपलाइन बन सकती है:

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;

पुनरावर्ती सीटीई क्वेरी ट्री संरचनाओं जैसे कार्य पदानुक्रम, संगठनात्मक चार्ट, या थ्रेडेड वार्तालाप। इनमें एक बेस केस (एंकर) और एक रिकर्सिव केस होता है जो सीटीई को ही संदर्भित करता है। यदि आपके डेटा में चक्र हैं, तो अनंत लूप को रोकने के लिए हमेशा एक गहराई सीमा या अन्य समाप्ति स्थिति शामिल करें:

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;

रिटर्निंग के साथ सम्मिलित करें

PostgreSQL का RETURNING खंड एक ही राउंड ट्रिप में डाली, अपडेट की गई या हटाई गई पंक्तियों से मान पुनर्प्राप्त करता है। यह एक अलग क्वेरी के बिना ऑटोजेनरेट आईडी, टाइमस्टैम्प या कंप्यूटेड डिफॉल्ट प्राप्त करने के लिए आवश्यक है।

-- 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;

संघर्ष पर के साथ अपसर्ट्स

खंड INSERT ... ON CONFLICT अद्वितीय बाधा उल्लंघनों को संभालता है, "अपसर्ट" संचालन को सक्षम करता है जो नई पंक्तियों को सम्मिलित करते हैं या मौजूदा लोगों को अपडेट करते हैं। यह पैटर्न एआई अनुप्रयोगों में प्रतिष्ठित संचालन और राज्य प्रबंधन के लिए मूल्यवान है।

जब किसी अनन्य बाधा पर कोई विरोध होता है, तो आप मौजूदा पंक्ति को नए मानों के साथ अद्यतन कर सकते हैं 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 CONFLICTRETURNING कि क्या कोई पंक्ति सम्मिलित की गई थी या अद्यतन की गई थी—अभिव्यक्ति (xmax = 0) नई सम्मिलित पंक्तियों के लिए और अद्यतन false पंक्तियों के लिए लौटाती true है.

एआई अनुप्रयोगों के लिए व्यावहारिक पैटर्न

ये पैटर्न पहले कवर की गई सुविधाओं को सामान्य 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;

निम्न उदाहरण अपसर्ट का उपयोग करके राज्य प्रबंधन के साथ कार्य चौकियों को रिकॉर्ड करता है:

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;

अतिरिक्त संसाधन

प्रतिक्रिया

क्या यह पेज मददगार था?