Oracle SQL की बोली: उदाहरण में मॉडल। भाग 1

कोई भी डेवलपर जो डेटाबेस से रिपोर्टिंग की पीढ़ी से संबंधित है, नियमित रूप से भारी प्रश्नों के निर्माण का सामना करता है। अक्सर यह डेटाबेस डिज़ाइन त्रुटियों के कारण होता है, और अधिक बार, निकाले गए डेटा को परिवर्तित करने की जटिलता के कारण भी। उत्तरार्द्ध में गणना के पुनरावृत्त तरीकों का उपयोग शामिल है, उपसमूहों के लिए उप-योगों की गणना, गणनाएं जो आसन्न नमूना पंक्तियों के मूल्यों का उपयोग करती हैं, तार के जटिल स्वरूपण और इसी तरह के कार्यों का उपयोग करती हैं। इस तरह के परिवर्तन अक्सर डेटाबेस स्तर से एप्लिकेशन सर्वर या क्लाइंट स्तर तक किए जाते हैं, जो कोड के प्रदर्शन और आसानी को प्रभावित करता है। इन SQL समस्याओं को हल करने के लिए, Oracle DBMS विश्लेषणात्मक कार्य और MODEL ऑपरेटर प्रदान करता है, जिसकी चर्चा इस लेख में की जाएगी।

SELECT कंस्ट्रक्शन का यह विस्तार DBMS संस्करण 10g के साथ उपलब्ध हो गया है। मॉडल आपको नमूना डेटा को बहुआयामी सरणियों के रूप में एक्सेस करने, बदलने और तत्वों को जोड़ने, जटिल एकत्रीकरण का संचालन करने और कई कार्यों को हल करने की अनुमति देता है जो पहले पीएल / एसक्यूएल के उपयोग की आवश्यकता होती है। इसी समय, भाषा निर्माण पठनीय और घोषणापत्र बने हुए हैं। एक शब्द में - एक्सेल जैसा, प्लस पूरा लोड डेटाबेस सर्वर के कंधों पर पड़ता है।

वाक्य-विन्यास

MODEL [ IGNORE NAV ] [ RETURN UPDATED ROWS ]
[ PARTITION BY (partition_column_1, ...)]
DIMENSION BY (dimension_column_1, ...)
MEASURES (measured_column_1, ...)
RULES [ AUTOMATIC ORDER | ITERATE (value) [ UNTIL (expression)]] (
rule_1, ...
);

MODEL कथन को बाद में संसाधित किया जाता है, उसके बाद केवल DISTINCT और ORDER BY द्वारा। अनुप्रयोग के परिणामस्वरूप, नमूना स्तंभों को__पुष्कर_ के लिए मैप किया जाता है, जो आयाम_कोलि__ आयामों के साथ सरणियाँ देता है। वैकल्पिक विभाजन बाय पैरामीटर विश्लेषणात्मक कार्यों में उपयोग किए जाने वाले समान विभाजन को परिभाषित करता है (उनमें से प्रत्येक को नियम_ * नियमों द्वारा एक स्वतंत्र सरणी के रूप में माना जाता है)। नियमों की गणना की जाती है।

सबसे सरल उदाहरण हैं

शुरू करने के लिए, हम संख्या 1, 2 और 3 के नमूने का अनुकरण करेंगे:

SELECT *
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (dummy)
RULES (
dummy[5] = 1,
dummy[6] = 2,
dummy[7] = 3
);

DIMENSION R
---------- -
0 X
7 3
6 2
5 1

इस मामले में, तीन नियमों के अनुसार, आयाम आयाम के साथ डमी सरणी भरा हुआ है। एक नया स्तंभ जोड़ने के लिए उपनाम 0 आयाम को परिभाषित किया गया है। आइए परिवर्तन का अधिक विस्तार से विश्लेषण करें। पहला चरण चयन स्तंभों को निर्धारित करना और प्रदर्शित करना है ( आयाम में 0 आयाम , MEASURES में डमी ), फिर कॉलम चुनें (वापसी डमी = एक्स, आयाम = 0 ) और उसके बाद ही नियमों को निष्पादित किया जाता है। सबसे पहले, आयाम = 5 के साथ एक स्ट्रिंग की खोज की जाती है, लेकिन जब से यह नहीं मिला है, एक नया बनाया गया है और डमी = 1 भरा हुआ है, इसी तरह शेष दो नियमों के लिए। यदि आवश्यक हो, RETURN UPDATED ROWS निर्देश का उपयोग करते हुए, आप केवल अपडेट की गई लाइनें प्रदर्शित कर सकते हैं:

SELECT result, dummy
FROM dual
MODEL RETURN UPDATED ROWS
DIMENSION BY (dummy)
MEASURES (0 result)
RULES (
result[5] = 1,
result[6] = 2,
result[7] = 3
);

RESULT DUMMY
---------- ------
3 7
2 6
1 5

आप लूप में नियम सेट भी लागू कर सकते हैं। निम्नलिखित प्रश्न एक फाइबोनैचि अनुक्रम के कई तत्वों की गणना करता है:

SELECT sequence
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (0 sequence)
RULES ITERATE (100500) UNTIL (sequence[iteration_number] > 10) (
sequence[iteration_number] =
CASE iteration_number
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE sequence[iteration_number - 2] + sequence[iteration_number - 1]
END
);

SEQUENCE
----------
0
1
1
2
3
5
8
13

ITERATE लूप के पुनरावृत्तियों की संख्या सेट करता है (0 से शुरू), और वैकल्पिक UNTIL निर्देश इसे (जो काम किया, इस तथ्य को देखते हुए निर्धारित करता है कि आपने अभी तक Ctrl + End को दबाया नहीं है) के लिए शर्त सेट करता है। काउंटर तक पहुंच iteration_number चर के माध्यम से है।

रंग और एकत्रीकरण

ऊपर, हमने व्यक्तिगत तत्वों के मूल्यों की गणना के साथ उदाहरण देखे। इसके अलावा, नियमों को उनके समूहों के लिए निर्धारित किया जा सकता है, निर्दिष्ट शर्तों द्वारा फ़िल्टर किया गया। इसके लिए, दूसरे प्रकार के लिंक का उपयोग किया जाता है - प्रतीकात्मक (पहले केवल स्थितीय थे)। प्रतीकात्मक लिंक में अनुक्रमणिका में सत्यापन की स्थिति हो सकती है, उदाहरण के लिए:

• cnt [दिन <6, टाइप करें 'latt%']
• cnt [दिन IN (3, 6), cv (प्रकार)]
• cnt [दिन BETWEEN 1 और 16, regexp_like (प्रकार, '^। + (ste tte) $')]

स्थितीय लिंक में, सूचकांक मूल्यों को विशिष्ट रूप से परिभाषित किया गया है:

• cnt [2, 'ब्लैक']
• cnt [7, 'लट्टे']

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

सप्ताह के दौरान कॉफी के नशे के बारे में जानकारी संग्रहीत करने वाली एक तालिका पर विचार करें:

SELECT * FROM coffee;

TYPE CNT DAY
-------------------- ---------- ----------
turkish 1 1
espresso 1 1
turkish 2 2
black 1 2
espresso 1 2
latte 3 3
black 2 4
ice 1 4

मान लीजिए कि हम एक रिपोर्ट प्राप्त करना चाहते हैं कि प्रत्येक दिन कितने कप कॉफी पिया गया था, और सामान्य तौर पर, एक ही समय में, हम इस बात पर ध्यान देंगे कि गुरुवार को ब्लैक कॉफ़ी की मात्रा दोगुनी थी। तो अनुरोध है:

SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[4, 'black' ] = cnt[cv(day), 'black' ] * 2,
cnt[ FOR day FROM 1 TO 4 INCREMENT 1, ' total for day' ] = sum<(cnt)[cv(day), ANY ],
cnt[ NULL , 'GRAND TOTAL' ] = sum(cnt)[ ANY , ' total for day' ]
)
ORDER BY day, type DESC ;

DAY TYPE CNT
--------- -------------------- ---------
1 turkish 1
1 espresso 1
1 total for day 2
2 turkish 2
2 espresso 1
2 black 1
2 total for day 4
3 latte 3
3 total for day 3
4 ice 1
4 black 4
4 total for day 5
GRAND TOTAL 14

हम नियमों का अधिक विस्तार से विश्लेषण करेंगे। पहला गुरुवार को कॉफी के नशे की मात्रा को दोगुना कर देता है। Cv (ampl_name) फ़ंक्शन फ़ंक्शन को संसाधित किए जा रहे तत्व के लिए ampl_name आयाम के लिए वर्तमान सूचकांक मान लौटाता है (यानी, इस मामले में, cv (दिन) के बजाय , आप दिन = 4 निर्दिष्ट कर सकते हैं या, यदि वांछित हो, तो पिछले दिन को दिन के रूप में देखें = cv () दिन) - 1) । दूसरा नियम सोमवार से गुरुवार तक उप-योगों की गणना करता है। लूप के अलावा (जो पहले से ही चित्रण है), यहां समानता के दाएं-बाएं पक्षों में तत्वों की श्रेणियों का संदर्भ दिया गया है। रंग उसी तरह निर्दिष्ट किए जा सकते हैं जैसे कि WHERE क्लॉज़ में चेक, किसी भी इंडेक्स मान का चयन करने के लिए किसी भी अतिरिक्त कीवर्ड का उपयोग किया जाता है। समीकरण के दाईं ओर श्रेणियों के लिंक को एकत्र किया जाना चाहिए, इस मामले में, योग फ़ंक्शन का उपयोग किया जाता है। और अंत में, तीसरा नियम उप योग का योग मानता है।

चल रहा है। एक क्वेरी पर विचार करें जो कॉफी के प्रकार से एक पेय के योग को प्रदर्शित करता है, मंगलवार को लट्टे की मात्रा, साथ ही एक संदेश के बारे में कि क्या सप्ताह का लक्ष्य पूरा हो गया है - बुधवार को एस्प्रेसो पीने के लिए:

SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[ NULL , FOR type IN ( SELECT DISTINCT type FROM coffee)] = sum(cnt)[ ANY , cv(type)],
cnt[ NULL , 'GRAND TOTAL' ] = sum(cnt)[ NULL , ANY ],
cnt[ NULL , ' drank ' || cnt[2, 'latte' ] || ' cups of latte on 2 day' ] = NULL ,
cnt[ NULL , CASE
WHEN cnt[3, 'espresso' ] IS PRESENT THEN ' ACHIEVED'
ELSE ' FAILED'
END || ': drank espresso on 3 day' ] = NULL
)
ORDER BY day, type DESC ;

DAY TYPE CNT
---------- ---------------------------------------- ----------
1 turkish 1
1 espresso 1
2 turkish 2
2 espresso 1
2 black 1
3 latte 3
4 ice 1
4 black 2
turkish 3
latte 3
ice 1
espresso 2
black 3
GRAND TOTAL 12
drank cups of latte on 2 day
FAILED: drank espresso on 3 day

पहला नियम नमूना मूल्यों पर पुनरावृत्तियों के साथ एक लूप का उपयोग करता है। MODEL के अंदर उपयोग किए गए नेस्टेड क्वेरीज़ असंबद्ध होना चाहिए। निम्नलिखित नियम कुल राशि की गणना करता है। लाइन पर ध्यान दें "2 दिन पर लट्टे के प्याले"। क्योंकि तत्व cnt [2, 'लट्टे'] नहीं मिला था , हमें संदर्भ द्वारा NULL मिला। इस व्यवहार को IGNORE NAV निर्देश (MODEL शब्द के बाद जोड़ा गया) के साथ बदला जा सकता है, फिर तत्वों के बजाय नहीं मिला और NULL, गणना प्रतिस्थापित की जाएगी: 0 के लिए संख्या, 1 जनवरी 2001, तारीखों के लिए, स्ट्रिंग प्रकारों के लिए एक खाली स्ट्रिंग और बाकी सब कुछ के लिए NULL। और अंत में, चौथा नियम IS PRESENT के अभिव्यक्ति के उपयोग को दर्शाता है, यह सच है अगर निर्दिष्ट तत्व मौजूद है, लेकिन, अफसोस, एस्प्रेसो बुधवार को नशे में नहीं था।

इस परिचयात्मक पूर्ण होने पर, मुझे आशा है कि आपको एक सामान्य विचार मिला और वाक्यविन्यास की आदत हो गई। दूसरे भाग में, हम अपडेट करने और तत्वों के प्रबंधन, मॉडल के उत्पादन को नियंत्रित करने, विधि प्रयोज्यता के मुद्दों और इसके प्रदर्शन के बारे में बात करेंगे। और, ज़ाहिर है, अधिक जटिल उदाहरणों पर चर्चा की जाएगी।

और पढ़ें

साहित्य

1. Oracle® Database Data Warehousing Guide 11g रिलीज़ 2 - अध्याय 22 "मॉडलिंग के लिए SQL"।
2. Oracle डाटाबेस 10g का SQL मॉडल क्लॉज।