دومین چالش SQL

[محمد سلیم آبادی]

مقدمه

این چالش بسیار ساده طراحی شده تا دوستان بیشتری قادر به حل آن باشند. چالش مرتبط به خلاصه کردن و کلاسه کردن داده هاست.

فردی که مساله را با کوتاه ترین کد بطور کامل حل کند برنده اعلام خواهد شد.

راه حل محدودیت خاصی ندارد. شما قادر هستید از هر دستوری (چه DML و چه DDL) برای گرفتن نتیجه استفاده کنید.

مساله

جدولی داریم که نتایج آزمون های دروس مختلف دانشجویان را نگهداری میکند. شماره دانشجو، شماره درس و نمره. یک دانشجو از یک درس بیش از یک نمره نمی تواند داشته باشد. نمرات بین 0 تا 20 هستند.

کد زیر را برای ایجاد جدول همراه با سطرهای نمونه اجرا کنید:

CREATE TABLE Results

  (std_nbr INTEGER NOT NULL, --Student No

   --REFERENCES Students(std_nbr)

   crs_nbr INTEGER NOT NULL,--Course No

   --REFERENCES Courses(crs_nbr)

   nbr REAL NOT NULL --Number of course

   CHECK (nbr BETWEEN 0 AND 20),

   PRIMARY KEY (std_nbr, crs_nbr));

  

  INSERT INTO Results(std_nbr, crs_nbr, nbr) VALUES

  (1, 01, 16.00),

  (1, 02, 16.50),

  (1, 05, 15.00),

  (1, 07, 17.00),

  (1, 10, 16.00),

  (1, 11, 19.75),

  

  (2, 03, 09.75),

  (2, 04, 07.25),

  (2, 05, 10.00),

  (2, 06, 14.25),

  (2, 07, 13.75),

  

  (3, 15, 20.00),

  (3, 16, 19.50),

  (3, 17, 19.50),

  (3, 18, 19.50),

  (3, 19, 17.75),

  (3, 25, 16.25);

نتیجه ی مورد نظر به شکل زیر است، نتیجه ابتدا بر اساس ستون std_nbr بصورت صعودی و بعد توسط Class آن هم بصورت صعودی مرتب می شود:

std_nbr     Class Cnt         Average

----------- ----- ----------- ----------------------

1           A     2           18.375

1           B     4           15.875

2           B     1           14.25

2           C     2           11.875

2           D     2           8.5

3           A     5           19.25

3           B     1           16.25

نمرات زیر 10 در کلاس D، نمرات بین 10 و زیر 14 در کلاس C، نمرات بین 14 و زیر 17 در کلاس B و نمرات برابر یا بزرگتر از 17 در کلاس A طبقه بندی می شوند.

هدف دسته بندی کردن نمرات هر دانشجو و بدست آوردن تعداد نمره در هر کلاس همراه با میانگین نمرات است.

هدف همانطور که قبلا اعلام شد بدست آوردن کوتاه ترین روش است.

از راه حل های صحیح دوستان استقبال خواهد شد حتی اگر خیلی طولانی و ابتدایی باشد. یک هفته ی آینده راه حل خودم را همراه با راه حل برنده ارسال میکنم.

کوتاه ترین راه حلی که من تاکنون بدست آوردن مجموعا 182 کاراکتر طول دارد.

[محمد سلیم آبادی]

/*msalim 1: 366 chars*/

SELECT std_nbr, 'A' class, COUNT(*) cnt, AVG(nbr) average

   FROM Results

  WHERE nbr BETWEEN 17 AND 20

  GROUP BY std_nbr



  UNION

  

 SELECT std_nbr, 'B', COUNT(*), AVG(nbr)

   FROM Results

  WHERE nbr >= 14 AND nbr < 17

  GROUP BY std_nbr

    

  UNION

  

 SELECT std_nbr, 'C', COUNT(*), AVG(nbr)

   FROM Results

  WHERE nbr >= 10 AND nbr < 14

  GROUP BY std_nbr

    

  UNION

  

 SELECT std_nbr, 'D', COUNT(*), AVG(nbr)

   FROM Results

  WHERE nbr < 10

  GROUP BY std_nbr

  ORDER BY std_nbr, class;

[behrouzlo]

/*behrouzlo: 231 chars*/

Select Std_nbr,Class,Count(*),Avg(nbr)  From (

Select Std_nbr,Case When nbr BETWEEN 17 AND 20 Then  'A' 

                    When nbr >= 14 AND nbr < 17 Then 'B'

                    When nbr >= 10 AND nbr < 14 Then 'C'

                    When nbr < 10 Then 'D' End As Class,

                nbr    

From Results) As List

Group By Std_nbr,Class

Order By Std_nbr,Class

[behrouzlo]

یک پیشنهاد دارم در صورتیکه دوستان تمایل داشته باشند بهینه بودن کدها را از نظر کارایی باهم مقایسه کنیم می تواند خیلی مفید و آموزنده باشد

[محمد سلیم آبادی]

/*behrouzlo: 231 chars*/

Select Std_nbr,Class,Count(*),Avg(nbr)  From (

Select Std_nbr,Case When nbr BETWEEN 17 AND 20 Then  'A' 

                    When nbr >= 14 AND nbr < 17 Then 'B'

                    When nbr >= 10 AND nbr < 14 Then 'C'

                    When nbr < 10 Then 'D' End As Class,

                nbr    

From Results) As List

Group By Std_nbr,Class

Order By Std_nbr,Class

تبریک میگم.
ولی چرا برای دو ستون عبارتی آخر (تعداد و میانگین) نام مستعار در نظر نگرفتین؟
فراموش نکنید که خروجی باید دقیقا اونی باشه که به نمایش گذاشته شده.

چند نکته میگم که توسط اون میتونید کد رو کوتاه تر کنید:
1. AS برای نام مستعار اختیاری هست میتونید ننویسیدش
2. آخرین WHEN عبارت CASE رو میتونید تبدیل به ELSE کنید با این کار 10 کاراکتر کمتر میشه
3. WHEN اول رو میتونید اینطوری بنویسید nbr >= 17 با اینکار 10 کاراکتر دیگر رو cut میکنید
4. عبارت CASE رو حتی میتونید بسیار ساده تر کنید، با پی بردن دقیق به مکانیزم CASE این کار ممکنه

[محمد سلیم آبادی]

یک پیشنهاد دارم در صورتیکه دوستان تمایل داشته باشند بهینه بودن کدها را از نظر کارایی باهم مقایسه کنیم می تواند خیلی مفید و آموزنده باشد

بله، یکی از فاکتور های بسیار مهم برای مقایسه راه حل ها همین عملکرد هست.
ولی فراموش نکنید که ساده کردن کدها (simplified) و حذف بررسی ها و شرط های اضافه یک هنر بزرگی هست که نیاز به مهارتهای زیادی داره.

[behrouzlo]

تبریک میگم.
ولی چرا برای دو ستون عبارتی آخر (تعداد و میانگین) نام مستعار در نظر نگرفتین؟
فراموش نکنید که خروجی باید دقیقا اونی باشه که به نمایش گذاشته شده.

چند نکته میگم که توسط اون میتونید کد رو کوتاه تر کنید:
1. AS برای نام مستعار اختیاری هست میتونید ننویسیدش
2. آخرین WHEN عبارت CASE رو میتونید تبدیل به ELSE کنید با این کار 10 کاراکتر کمتر میشه
3. WHEN اول رو میتونید اینطوری بنویسید nbr >= 17 با اینکار 10 کاراکتر دیگر رو cut میکنید
4. عبارت CASE رو حتی میتونید بسیار ساده تر کنید، با پی بردن دقیق به مکانیزم CASE این کار ممکنه

گزینه 1 تبدیل به عادت شده یعنی به خود خود می نویسم. ولی گزینه 2 و 3 را بعد از ارسال پاسخ تشخیص دادم ولی دیگه نخواستم ویرایش کنم. یک سوال در مورد گزینه 4 آیا اگر این تغییراتی را که در گزینه 2 و 3 بدهیم باز می شود عبارت CASE را از ان ساده تر هم نوشت.

[Reza_Yarahmadi]

یک سوال در مورد گزینه 4 آیا اگر این تغییراتی را که در گزینه 2 و 3 بدهیم باز می شود عبارت CASE را از ان ساده تر هم نوشت.

Case     

    When nbr < 10 Then 'D'

    When nbr < 14 Then 'C'

    When nbr < 17 Then 'B'

    Else 'A' End

[محمد سلیم آبادی]

با تشکر از آقای یاراحمدی که جواب دقیق رو گفتن.
عبارت CASE مذکور رو که از نظر سادگی بنظر نمیرسه بشه ساده تر کرد. از طرفی ما میتونیم یک ستون محاسباتی به جدول اضافه کنیم که خودش این کلاس ها رو بدست بیاره سپس با یک کوئری فوق العاده ساده به جواب برسیم یعنی:

ALTER TABLE Results

ADD C AS

       CASE WHEN nbr >= 17 THEN 'A'

            WHEN nbr >= 14 THEN 'B'

            WHEN nbr >= 10 THEN 'C'

            ELSE 'D'

       END C



SELECT std_nbr, C Class, COUNT(*) Cnt, AVG(nbr) Average

FROM Results

GROUP BY std_nbr, C

ORDER BY std_nbr, c

مشکلی که این روش داره پویا نبودنش هست. یعنی اگه بخواهیم این Range از کلاس ها را توسط یک جدول بخوانیم و خروجی بگیریم روش مذکور جواب گو نیست. در نتیجه یک روش ساده تر و کوتاه دیگه که این مشکل رو حل میکنه رو پیشنهاد میکنم:

/*msalim 2: 182 chars*/

SELECT std_nbr, c class, COUNT(*) cnt, AVG(nbr) Average

  FROM (VALUES ('A', 17, 20),

               ('B', 14, 16.99),

               ('C', 10, 13.99),

               ('D', 0, 9.99)

       ) D(c, i, j)

       JOIN Results

         ON nbr BETWEEN i AND j

 GROUP BY std_nbr, c

 ORDER BY std_nbr, c

ما میتونیم این داده های مرتبط به کلاسه ها رو داخل یک جدول درج کنیم. البته برای جامعیت داده های جدول نیاز به کار زیادی هست. که یکیشون نداشتن تداخل Range هاست و دیگری نداشتن gap بین این بازه ها.
به هر حال جدول همراه با کوئری به این شکل در اومده:

CREATE TABLE Classes

(cls_no CHAR(1) NOT NULL PRIMARY KEY,

Start_range INTEGER NOT NULL,

End_range REAL NOT NULL,

CHECK (start_range < end_range),

CHECK (start_range BETWEEN 0 AND 20

       AND end_range BETWEEN 0 AND 20));

--no conflict

--no gap



INSERT INTO Classes (cls_no, start_range, end_range)

VALUES ('A', 17, 20),

       ('B', 14, 16.99),

       ('C', 10, 13.99),

       ('D', 0, 9.99);



SELECT std_nbr, cls_no, COUNT(*) cnt, AVG(std_nbr) average

  FROM Results

       INNER JOIN Classes

       ON nbr BETWEEN start_range AND end_range

 GROUP BY std_nbr, cls_no

 ORDER BY std_nbr, cls_no;

تصویر زیر هم بحث هم پوشانی مقادیر شروع و پایان ragne ها رو نشون میده:
ranges.png

[ققنوس در ایران]

من این تایپک رو که خوندم این را ه حل به ذهنم رسید، همون جوابی که خواستین رو می ده، اگر اشتباه هست یا یه جاهاییش مشکل داره ممنون می شم بگین

Create table r2

,std_nbr integer not null)

,Class nvarchar(50) not null

,Count_nbr integer not null

,Avg_nbr flaot not null

(Primary key (std_nbr, class

(

Insert into t2

(Select std_nbr, ‘A’, count(nbr), avg(nbr

From Results 

Where nbr>=17

Group by std_nbr
 

(Select std_nbr, ‘B’, count(nbr), avg(nbr

From Results 

Where nbr<17 and nbr>=14

Group by std_nbr
 

 

(Select std_nbr, ‘C’, count(nbr), avg(nbr

From Results 

Where nbr<14 and nbr>=10

Group by std_nbr
 

 

(Select std_nbr, ‘D’, count(nbr), avg(nbr

From Results 

Where nbr<10

Group by std_nbr