ورود به سیستم 
 
    محتویات سایت
        برچسب های محبوب 








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

مساله زیر را جهت تقویت مهارت های SQL برنامه نویسان طراحی کرده ام. و بعد از طرح آن در سایت برنامه نویس و سپرسی شدن فرصت یک هفته ای راه حل های بدست آمده را در انتهای مقاله لیست کرده ام.

مقدمه

راه حل باید دارای شرایط زیر باشد:

استاندارد بوده به این معنا که از Syntax هایی که مخصوص RDBMS های مختلف هست استفاده نشده باشد. این ویژگی باعث می شود راه حل قابل حمل (Portable) شود و در نرم افزارهایی که طبق استاندارد های SQL کار می کنند قابل اجرا شود.
راه حل تنها از یک عبارت SELECT تشکیل شده باشد.
حق استفاده از View یا Common Table Expression وجود ندارد.
استفاده از توابع Ranking ممنوع است.

هدف ارائه کوتاه ترین راه حل است. (کوتاهی راه حل الزاما به معنا بهینه بودن یا خوانایی و... نیست)

پس کوتاه ترین راه حل به عنوان راه حل برنده شناخته خواهد شد. از راه حل هایی که دوستان ارسال می کنند استقبال خواهد شد. 7 روز دیگر من راه حل های خودم را همراه با راه حلهایی که دوستان ارسال کردند (در صورت ارسال) را انتشار خواهم داد.

مساله

فروشگاهی را در نظر بگیرید که تعدادی فروشنده در آن کار می کنند. فروشندگان در تمام روزهای سال مشغول به کار نیستند و ممکن است روزهای تعطیل فروشگاه بسته شود یا اینکه در ایام غیر تعطیل فروشنده به مرخصی رود.
مدیر فروشگاه قصد دارد جهت تشویق کارکنان خود افرادی را که 5 روز کاری، بطور متوالی توانسته اند فروش صعودی مطلق داشته باشند را مورد تشویق نقدی قرار دهد.
منظور از فروش صعودی مطلق این است که فروش روز بعد از فروش روز قبل بیشتر بوده (حتی برابر هم نباشد) و فروش روز سوم از روز دوم بیشتر بوده و همینطور تا 5 روز.
این 5 روز باید 5 روز متوالی کاری فرد بوده باشد. به این معنا که می تواند بین این 5 روز، روزهایی باشد که فرد به مرخصی رفته باشد یا اینکه آن روزها تعطیل بوده باشند.
به نمودار دو بعدی زیر دقت کنید:


کاربر (فروشنده) ای که با رنگ قرمز مشخص شده است به عنوان نتیجه باید اعلام شود. به دلیل اینکه توانسته 5 روز متوالی کاری موفق به فروش صعودی مطلق شود. یعنی فروش روز پانزدهم از روز دوازدهم بیشتر بوده و فروش روز شانزدهم از پانزدهم بیشتر همینطور تا روز بیستم.

روزهای 13و 14و 18 این فرد مشغول کار نبوده است و مشکلی هم در روال کار بوجود نیامده است. اگر روزی که بوده باشد و کاری انجام نداده باشد فروش صفر برای فرد در نظر گرفته می شود مثل روز 21 یکم کاربری که با رنگ آبی مشخص شده است.

برای ذخیره کردن این اطلاعات جدول زیر را در نظر گرفته ایم:

CREATE TABLE Sales
(user_nbr INTEGER NOT NULL,
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 365),
Value INTEGER NOT NULL
CHECK(value >= 0),
PRIMARY KEY (user_nbr, day_nbr));

حالا قصد ما این است که توسط یک SELECT تنها لیست افرادی را بدست آوریم که دارای شرط ما باشند. نکته اینکه ممکن است یک فرد در چند توالی فروش صعودی داشته باشد ولی یکبار بیشتر نمی خواهیم نام فرد انتخاب شود. مثلا از روز پنجم تا نهم پنج روز متوالی فروش صعودی داشته است و همچنین در روز بیستم تا بیست هشتم بازهم 5 روز متوالی فروش صعودی داشته است.

داده های آزمایشی را برای تست راه حلتان استفاده کنید. جواب باید 1 باشد.

 

INSERT INTO Sales
VALUES (1, 12, 60),
(1, 15, 70),
(1, 16, 80),
(1, 17, 83),
(1, 19, 86),
(1, 20, 90),
(1, 21, 90),

 
 
(2, 12, 80),
(2, 13, 70),
(2, 14, 70),
(2, 17, 50),
(2, 18, 60),
(2, 19, 70),
(2, 20, 80),
(2, 21, 0),


(3, 12, 70),
(3, 13, 60),
(3, 14, 50),
(3, 15, 60),
(3, 16, 60),
(3, 17, 65),
(3, 21, 65),
  

(4, 1, 5),
(4, 2, 6),
(4, 3, 6),
(4, 4, 7),
(4, 5, 8),


(5, 1, 1),
(5, 2, 2),
(5, 3, 3);


راه حل ها

اولین جواب صحیح (راه حل) توسط آقای رضا یاراحمدی پیشنهاد شد. راه حل زیر:

Select    Distinct S1.user_nbr
From
    Sales S1, Sales S2, Sales S3, Sales S4, Sales S5
Where
   
        
S1.user_nbr = S2.user_nbr
        
AND
        
S2.day_nbr = (Select MIN(day_nbr) From Sales Where user_nbr = S1.user_nbr AND day_nbr > S1.day_nbr)
        
AND
        
S1.user_nbr = S3.user_nbr
        
AND
        
S3.day_nbr = (Select MIN(day_nbr) From Sales Where user_nbr = S1.user_nbr AND day_nbr > S2.day_nbr)
        
AND
        
S1.user_nbr = S4.user_nbr
        
AND
        
S4.day_nbr = (Select MIN(day_nbr) From Sales Where user_nbr = S1.user_nbr AND day_nbr > S3.day_nbr)
        
AND
        
S1.user_nbr = S5.user_nbr
        
AND
        
S5.day_nbr = (Select MIN(day_nbr) From Sales Where user_nbr = S1.user_nbr AND day_nbr > S4.day_nbr)
    
AND
        
S1.Value < S2.Value
        
AND
        
S2.Value < S3.Value
        
AND
        
S3.Value < S4.Value
        
AND
        
S4.Value < S5.Value

 روشی که معرفی شد توسط ایشان سطر جاری را با 4 سطر بعدی (بر اساس نزدیک ترین روز) اتصال می داد و در صورتی که 5 مقدار به ترتیب از همدیگر بزرگتر بودند در خروجی اعلام می شد. و از آنجایی که ممکن بود فردی در بیش از 5 روز فروش صعودی داشته باشد یا اینکه در دو بازه ی زمانی جدا از هم 5 روز فروش صعودی داشته باشد از کلید واژه ی DISITNCT استفاده شده بود تا فروشنده های تکراری را حذف کند.
subquery های وابسته قطعا هزینه ی بالایی را به سیستم تحمیل می کرد و همچنین هزینه توسعه ی این روش بسیار بالا بود و به عبارتی قابل تعمیم به سختی بود. و برای مقادیر متغیر قابل استفاده نبود.

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

Select S1.User_Nbr,COUNT(Distinct S1.Day_nbr) As DaySales
    
From Sales S1
    
Left Outer Join
        
Sales S2
    
On S1.user_Nbr=S2.User_nbr
Where
S1.Value>S2.Value And S1.day_nbr>S2.Day_nbr
Group
by S1.User_Nbr
Having
count(distinct S1.Day_nbr)>5



و در انتها بعد از چندین تلاش:

/*Msalim 1: 435  Characters (no space)*/
SELECT DISTINCT user_nbr

  
FROM (
          
SELECT *,
                 
(SELECT COUNT(*)
                    
FROM Sales S5
                   
WHERE S5.user_nbr = S1.user_nbr
                     
AND S5.day_nbr <= S1.day_nbr) rn
            
FROM Sales S1
       
) AS S1
 
WHERE EXISTS (SELECT 1
                 
FROM (
                         
SELECT (SELECT COUNT(*)
                                   
FROM Sales S2
                                  
WHERE S2.user_nbr = S3.user_nbr
                                    
AND S2.day_nbr <= S3.day_nbr) rn1,
                                
(SELECT COUNT(*)
                                   
FROM Sales S4
                                  
WHERE S4.user_nbr = S3.user_nbr
                                    
AND S4.value < S3.value) + 1 rn2, *
                           
FROM sales s3
                     
) d
               
WHERE D.user_nbr = S1.user_nbr
                 
AND D.rn1 BETWEEN S1.rn
                               
AND rn + 4
              
HAVING COUNT(*) = 5
                 
AND MAX(D.rn1 - D.rn2) = 0
            
);

 

/*Msalim 2: 413 Chars*/
SELECT DISTINCT a.user_nbr

  
FROM Sales A, Sales B, Sales C, Sales D, Sales E
 
WHERE A.user_nbr = B.user_nbr
   
AND B.user_nbr = C.user_nbr
   
AND C.user_nbr = D.user_nbr
   
AND D.user_nbr = E.user_nbr
   
AND B.day_nbr > A.day_nbr
   
AND C.day_nbr > B.day_nbr
   
AND D.day_nbr > C.day_nbr
   
AND E.day_nbr > D.day_nbr
   
AND A.value < B.value
   
AND B.value < C.value
   
AND C.value < D.value
   
AND D.value < E.value
   
AND (SELECT COUNT(1)
          
FROM sales
         
WHERE user_nbr = a.user_nbr
           
AND day_nbr BETWEEN a.day_nbr AND e.day_nbr) = 5;

/*Msalim 3: 306 Chars*/
SELECT DISTINCT A.user_nbr

  
FROM Sales A
    
JOIN Sales B
      
ON A.user_nbr = B.user_nbr
      
AND A.day_nbr < B.day_nbr
      
AND A.value < B.value
      
AND EXISTS
          
(SELECT 1
             
FROM (SELECT CASE WHEN value > A.value THEN 1 END C
                     
FROM Sales
                    
WHERE user_nbr = A.user_nbr
                      
AND day_nbr > A.day_nbr AND day_nbr < B.day_nbr
                  
)D
           
HAVING COUNT(*) = SUM(C))
  
GROUP BY A.user_nbr, A.day_nbr
  
HAVING COUNT(*) >= 4;

 

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

/*Msalim 4: 238 Chars*/
SELECT DISTINCT user_nbr

  
FROM Sales S
 
WHERE (SELECT COUNT(*)
          
FROM Sales D
         
WHERE S.user_nbr = user_nbr
           
AND S.day_nbr < day_nbr
           
AND S.value < value
           
AND NOT EXISTS
               
(SELECT *
                  
FROM Sales
                 
WHERE user_nbr = S.user_nbr
                   
AND day_nbr > S.day_nbr AND day_nbr < D.day_nbr
                   
AND value <= S.value)) >= 5;

برچسب های مرتبط