sql - हिस्टोग्राम डेटा से प्रतिशतियल्स




postgresql (3)

SELECT name, exams,
       CASE WHEN 0.5 * exams <= grade_poor
                THEN 'grade_poor'
            WHEN 0.5 * exams <= grade_poor + grade_fair
                THEN 'grade_fair'
            WHEN 0.5 * exams <= grade_poor + grade_fair + grade_good
                THEN 'grade_good'
            ELSE 'grade_vgood' END AS median_grade;

इस दौर का संबंध तो नीचे आता है ताकि न्हा "ग्रेड_फेयर" स्कोर कर सकें और राधा "ग्रेड_गुड" स्कोर कर सकें। यदि आप गोल करना चाहते हैं, तो <= इन <

निम्न सारणी कई परीक्षाओं पर छात्र ग्रेड डेटा को कैप्चर करता है

CREATE TABLE grades
AS
  SELECT name, exams, grade_poor, grade_fair, grade_good, grade_vgood
  FROM ( VALUES
    ( 'arun'  , 8  , 1 , 4 , 2 , 1 ),
    ( 'neha'  , 10 , 3 , 2 , 1 , 4 ),
    ( 'ram'   ,  5 , 1 , 1 , 3 , 0 ),
    ( 'radha' ,  8 , 0 , 3 , 1 , 4 )
  ) AS t(name,exams,grade_poor,grade_fair,grade_good,grade_vgood);

ग्रेड को इस अर्थ में आदेश दिया जाता है कि vgood> अच्छा> निष्पक्ष> गरीब

क्या इस डेटा के साथ प्रत्येक छात्र के 50 वें प्रतिशतय ग्रेड को खोजना संभव होगा (या क्या यह समझ जाएगा)? उदाहरण के लिए - यदि हम ग्रेड श्रेणी की श्रेणियों के रूप में डेटा के बारे में सोचते हैं तो - 50 वीं प्रतिशतक grade_fair होगा।


पहले आपको इसे अनपिट करना होगा। हम ऐसा कर सकते हैं ...

SELECT name,
  ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
FROM grades

 name  |   array   
-------+-----------
 arun  | {1,4,2,1}
 neha  | {3,2,1,4}
 ram   | {1,1,3,0}
 radha | {0,3,1,4}

तो हमें ग्रेड में इंडेक्स की आवश्यकता है ... हम ऐसा करते हैं कि एक CROSS JOIN LATERAL साथ हमारे 4 की एक सरणी के साथ 4 पंक्तियाँ हैं। हम 4 * 4 पंक्तियाँ चाहते हैं।

SELECT name, grades, gs1.x, grades[gs1.x] AS gradeqty
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
  CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
ORDER BY name, x;


 name  |  grades   | x |  gradeqty
-------+-----------+---+----------
 arun  | {1,4,2,1} | 1 |        1
 arun  | {1,4,2,1} | 2 |        4
 arun  | {1,4,2,1} | 3 |        2
 arun  | {1,4,2,1} | 4 |        1
 neha  | {3,2,1,4} | 1 |        3
 neha  | {3,2,1,4} | 2 |        2
 neha  | {3,2,1,4} | 3 |        1
 neha  | {3,2,1,4} | 4 |        4
 radha | {0,3,1,4} | 1 |        0
 radha | {0,3,1,4} | 2 |        3
 radha | {0,3,1,4} | 3 |        1
 radha | {0,3,1,4} | 4 |        4
 ram   | {1,1,3,0} | 1 |        1
 ram   | {1,1,3,0} | 2 |        1
 ram   | {1,1,3,0} | 3 |        3
 ram   | {1,1,3,0} | 4 |        0
(16 rows)

अब क्या बनी हुई है, हमें कक्षा (ग्रेडिंग) पर एक्स (हमारे ग्रेड) को पुन: उत्पन्न करने के लिए फिर से जोड़ना आवश्यक है

SELECT name,
  gs1.x
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
ORDER BY name, gs1.x;

 name  | x 
-------+---
 arun  | 1
 arun  | 2
 arun  | 2
 arun  | 2
 arun  | 2
 arun  | 3
 arun  | 3
 arun  | 4
 neha  | 1
 neha  | 1
 neha  | 1
 neha  | 2
 neha  | 2
 neha  | 3
 neha  | 4
 neha  | 4
 neha  | 4
 neha  | 4
 radha | 2
 radha | 2
 radha | 2
 radha | 3
 radha | 4
 radha | 4
 radha | 4
 radha | 4
 ram   | 1
 ram   | 2
 ram   | 3
 ram   | 3
 ram   | 3
(31 rows)

अब हम GROUP BY name और फिर हम काम खत्म करने के लिए एक आदेश-निर्धारित कुल कार्य percent_disc का उपयोग करते हैं ..

SELECT name, percentile_disc(0.5) WITHIN GROUP (ORDER BY gs1.x)
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
GROUP BY name ORDER BY name;

 name  | percentile_disc 
-------+-----------------
 arun  |               2
 neha  |               2
 radha |               3
 ram   |               3
(4 rows)

इसे आगे बढ़ना और इसे सुंदर बनाना चाहते हैं ...

SELECT name, (ARRAY['Poor', 'Fair', 'Good', 'Very Good'])[percentile_disc(0.5) WITHIN GROUP (ORDER BY gs1.x)]
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
GROUP BY name
ORDER BY name;

 name  | array 
-------+-------
 arun  | Fair
 neha  | Fair
 radha | Good
 ram   | Good
(4 rows)

अगर हम एक नया उपयोगकर्ता जॅक करते हैं तो हम थोड़ी अधिक अलग-अलग हो सकते हैं।

INSERT INTO grades (name,grade_poor,grade_fair,grade_good,grade_vgood)
VALUES ('Bob', 0,0,0,100);

 name  |   array   
-------+-----------
 arun  | Fair
 Bob   | Very Good
 neha  | Fair
 radha | Good
 ram   | Good
(5 rows)

भाग 2 - सबक्विरीज़

ठीक है, अब बॉस फिर से फट गया है - मैं ब्रांड के साथ हमारी सभी कारों की एक सूची चाहता हूं और हमारे पास कितने ब्रांड हैं!

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

हमारे अनुरोध के लिए, पहले एक साथ एक साधारण क्वेरी डालें जो प्रत्येक कार और ब्रांड को सूचीबद्ध करेगी:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID

अब, अगर हम ब्रांड द्वारा क्रमबद्ध कारों की गिनती करना चाहते हैं, तो हम निश्चित रूप से इसे लिख सकते हैं:

select
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    b.brand

+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

तो, हम केवल हमारी मूल क्वेरी में गिनती फ़ंक्शन में जोड़ने में सक्षम होना चाहिए?

select
    a.ID,
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    a.ID,
    b.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

अफसोस की बात है, नहीं, हम ऐसा नहीं कर सकते हैं। इसका कारण यह है कि जब हम कार आईडी (कॉलम ए.आईडी) में जोड़ते हैं तो हमें इसे समूह में जोड़ना होता है - इसलिए अब, जब गिनती फ़ंक्शन काम करता है, तो प्रति आईडी से मेल खाने वाली केवल एक आईडी होती है।

यह वह जगह है जहां हम एक सबक्वायरी का उपयोग कर सकते हैं - असल में हम दो पूरी तरह से विभिन्न प्रकार के सबक्वायरी कर सकते हैं जो उसी परिणाम को वापस कर देंगे जो हमें इसके लिए चाहिए। सबसे पहले select खंड में सबक्वायरी डालना है। इसका मतलब यह है कि हर बार जब हमें डेटा की एक पंक्ति मिलती है, तो सबक्वायरी समाप्त हो जाएगी, डेटा का एक स्तंभ प्राप्त करें और फिर इसे डेटा की हमारी पंक्ति में पॉप करें।

select
    a.ID,
    b.brand,
    (
    select
        count(c.ID)
    from
        cars c
    where
        a.brand=c.brand
    ) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

और बाम !, यह हमें करेगा। यदि आपने देखा है, तो इस सब क्वेरी को हमारे द्वारा लौटाए गए डेटा की प्रत्येक पंक्ति के लिए चलना होगा। यहां तक ​​कि इस छोटे से उदाहरण में, हमारे पास केवल पांच अलग-अलग ब्रांड हैं, लेकिन सबक्वायरी ग्यारह बार दौड़ गई है क्योंकि हमारे पास डेटा की ग्यारह पंक्तियां हैं जिन्हें हम लौट रहे हैं। इसलिए, इस मामले में, यह कोड लिखने का सबसे प्रभावी तरीका प्रतीत नहीं होता है।

एक अलग दृष्टिकोण के लिए, एक सबक्वायरी चलाएं और दिखाएं कि यह एक सारणी है:

select
    a.ID,
    b.brand,
    d.countCars
from
    cars a
        join brands b
            on a.brand=b.ID
        join
            (
            select
                c.brand,
                count(c.ID) as countCars
            from
                cars c
            group by
                c.brand
            ) d
            on a.brand=d.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

ठीक है, तो हमारे पास एक ही परिणाम हैं (थोड़ा अलग आदेश दिया गया - ऐसा लगता है कि डेटाबेस इस बार उठाए गए पहले कॉलम द्वारा आदेशित परिणामों को वापस करना चाहता था) - लेकिन वही सही संख्याएं।

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

अब, पहले सबक्वायरी पर वापस देखकर, कुछ सीमाएं भी हैं। क्योंकि हम डेटा को एक पंक्ति में वापस खींच रहे हैं, हम केवल डेटा की एक पंक्ति वापस खींच सकते हैं। किसी क्वेरी के select खंड में उपयोग की जाने वाली सबक्विरी अक्सर sum , count , max या किसी अन्य समान कार्य जैसे कुल कार्य का उपयोग करती है। उन्हें नहीं करना है, लेकिन अक्सर यह लिखा जाता है कि वे कैसे लिखे गए हैं।

इसलिए, आगे बढ़ने से पहले, आइए देखें कि हम एक सबक्वायरी का उपयोग कहां कर सकते हैं। हम इसका उपयोग where कर सकते हैं - अब, यह उदाहरण हमारे डेटाबेस में थोड़ा सा योगदान है, निम्नलिखित डेटा प्राप्त करने के बेहतर तरीके हैं, लेकिन यह देखते हुए कि यह केवल एक उदाहरण के लिए है, देखते हैं:

select
    ID,
    brand
from
    brands
where
    brand like '%o%'

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

यह हमें ब्रांड आईडी और ब्रांड नामों की एक सूची देता है (दूसरा कॉलम केवल हमें ब्रांड दिखाने के लिए जोड़ा जाता है) जिसमें नाम में अक्षर o होता है।

अब, हम इस क्वेरी के परिणामों का उपयोग उस खंड में कर सकते हैं जहां यह खंड है:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in
        (
        select
            ID
        from
            brands
        where
            brand like '%o%'
        )

+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

जैसा कि आप देख सकते हैं, भले ही सबक्वायरी तीन ब्रांड आईडी लौट रही थी, फिर भी हमारी कार टेबल में उनमें से दो के लिए प्रविष्टियां थीं।

इस मामले में, अधिक जानकारी के लिए, सबक्वायरी काम कर रही है जैसे कि हमने निम्नलिखित कोड लिखा है:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in (1,2,6)

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

दोबारा, आप देख सकते हैं कि मैन्युअल इनपुट बनाम एक सबक्वायरी ने डेटाबेस से लौटने पर पंक्तियों के क्रम को कैसे बदल दिया है।

जबकि हम subqueries पर चर्चा कर रहे हैं, देखते हैं कि हम सबक्वायरी के साथ और क्या कर सकते हैं:

  • आप एक और सबक्वायरी के भीतर एक सबक्वायरी डाल सकते हैं, और इसी तरह से। एक सीमा है जो आपके डेटाबेस पर निर्भर करती है, लेकिन कुछ पागल और मनोचिकित्सक प्रोग्रामर के पुनरावर्ती कार्यों से कम, अधिकांश लोग कभी भी उस सीमा को नहीं मारेंगे।
  • आप एक ही क्वेरी में कई उपक्विरी रख सकते हैं, कुछ select खंड में कुछ, खंड from कुछ और where क्लॉज में कुछ और - बस याद रखें कि आपके द्वारा डाला गया प्रत्येक व्यक्ति आपकी क्वेरी को अधिक जटिल बना रहा है और संभवतः निष्पादित करने में अधिक समय लगता है।

यदि आपको कुछ कुशल कोड लिखने की आवश्यकता है, तो क्वेरी को कई तरीकों से लिखना फायदेमंद हो सकता है और देख सकता है (या तो इसे समयबद्ध करके या समझाया योजना का उपयोग करके) जो आपके परिणाम प्राप्त करने के लिए इष्टतम क्वेरी है। काम करने का पहला तरीका हमेशा ऐसा करने का सबसे अच्छा तरीका नहीं हो सकता है।





sql postgresql