دومین چالش (خلاصه و کلاسه کردن اطلاعات)
 
   T-SQL Challenges
   ۱۵۹۴۲
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۹۰/۲/۲۹
ارسال لینک صفحه برای دوستان ارسال لینک صفحه برای دوستان  اضافه کردن به علاقه مندیها اضافه کردن به علاقه مندیها   نسخه قابل چاپ نسخه قابل چاپ

 

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

مقدمه

این چالش بسیار ساده طراحی شده تا دوستان بیشتری قادر به حل آن باشند. چالش مرتبط به خلاصه کردن و کلاسه کردن داده هاست.
فردی که مساله را با کوتاه ترین کد بطور کامل حل کند برنده اعلام خواهد شد.
راه حل محدودیت خاصی ندارد. شما قادر هستید از هر دستوری (چه 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 طبقه بندی می شوند.
هدف دسته بندی کردن نمرات هر دانشجو و بدست آوردن تعداد نمره در هر کلاس همراه با میانگین نمرات است.
هدف همانطور که قبلا اعلام شد بدست آوردن کوتاه ترین روش است.


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

پاسخ ها

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

/*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

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

Case    
    When nbr < 10 Then 'D'
    When nbr < 14 Then 'C'
    When nbr < 17 Then 'B'
    Else 'A' End

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

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

ولی مشکلی که این روش CASE دارد پویا و انعطاف پذیر نبودنش هست. چه میشود که کاربر بخواهد این 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

البته هنوز داده ها از جدول خوانده نشده اند. برای این منظور باید یک جدول ایجاد کنیم با در نظر گرفتن چند قید مهم و اساسی.
هر داده ای نباید در جدول درج شود. اگر با دقت 2 رقم اعشار نمرات محاسبه می شوند. باید بین بازه ها یک صدم فاصله وجود داشته باشد. (این بررسی، شرط تداخل هم پوشش می دهد). کوچکترین مقدار شروع بازره باید 0 و بزرگترین 20 باشد. شروع بازه باید از پایان بازه کوچکتر باشد.

تصویر زیر میتونه در تفهیم بحث کمک کند:


برای بررسی بیشتر این شروط و قیود (که قابل پیاده سازی در خود تعریف جدول نیستند) نیاز به trigger هست که من در اینجا از اینکار صرف نظر کردم و فقط توسط کوئری این ارزیابی را انجام داده ام:

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

;WITH Cte(c, i, j) AS
(SELECT 'A', 17, 20 UNION ALL
 SELECT 'B', 14, 16.99 UNION ALL
 SELECT 'C', 10, 13.99 UNION ALL
 SELECT 'D', 0, 9.99
)
INSERT INTO Classes (cls_no, start_range, end_range)
SELECT * FROM Cte A
WHERE NOT EXISTS --No Conflict
      (SELECT *
       FROM Cte
       WHERE A.i BETWEEN i AND j
       AND c <> A.c)
  AND EXISTS --No Gap
     (SELECT *
      FROM Cte
      WHERE (CHAR(ASCII(c) - 1) = A.c
      AND A.i - j = .01)
      OR A.c = 'D')
  AND (SELECT MIN(i) FROM Cte) = 0
  AND (SELECT MAX(j) FROM Cte) = 20;

بعد از ساخت جدول و درج این چهار سطر کوئری که توسط اون نتیجه مورد نظر رو بدست میاریم:

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;