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

مساله 3.1: بالاترین ترافیک

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

به تصویر زیر دقت کنید:

این تصور بازه های زمانی که کاربر به سیستم متصل بوده را به نمایش گذاشته است. این ورود و خروج مرتبط به 24 ساعت یک روز هست. البته برای ساده سازی مساله در اینجا از اعداد 1تا24 به جای تاریخ و زمان استفاده شده است.

اولین اتصال به سیستم در زمان 1 صورت گرفت و تا زمان 15 ادامه داشته است. در این بین 7 اتصال دیگر به سیستم رخ داده است.
در زمان 1 تعداد کاربران همزمان متصل به سیستم 1 نفر است. در زمان 2 دو نفر، در زمان 5 چهار نفر، در زمان 21 نیز هیچ کاربر متصلی وجود ندارد.

تعداد کاربران متصل به سایت در زمان های مختلف در نمودار زیر دیده میشود:

همانطور که از نمودار فوق همچنین نمودار اولیه پیداست. تعداد بیشترین کاربر همزمان متصل به سیستم (بالاترین ترافیک) 4 است. کار شما پیدا کردن این عدد است.
جدولی که وظیفه ذخیره داده ها را به عهده گرفته همراه با داده های نمونه برای بدست آوردن خروجی در زیر وجود دارد:

CREATE TABLE Logining
(nbr INTEGER IDENTITY NOT NULL PRIMARY KEY,
 log_in INT NOT NULL,
 log_out INT NOT NULL,
 CHECK (log_in < log_out),
 CHECK (log_in BETWEEN 1 AND 24 AND log_out BETWEEN 1 AND 24));

INSERT Logining(log_in, log_out) VALUES
(1, 15), (2, 6), (3, 6), (4, 5), (7, 9), (8, 12), (10, 12), (12, 13), (17, 20);
 

راه حل پیشنهادی برای حل مساله 3.1 نباید نیازمند به جدول کمکی اعداد باشد. و برای هر تعداد کاربر همزمان متصل مناسب باشد (منطور از جدول می تواند ویو، CTE، TVF یا derived table باشد). روش (query) باید مجموعه گرا بوده به این معنا که از حلقه، کرسر و چیزهایی شبیه به اینها صرف نظر کنید.

مساله 3.2: زوج کاربران آنلاین همراه با تعداد برخورد

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

declare @sample table
(u_nbr int not null,
 i int not null,
 j int not null,
 check (i<=j)); 

 insert @sample values
 (1,1,5),
 (2,2,2),
 (2,3,3),
 (2,6,10),
 (1,7,7),
 (1,8,8),
 (3,11,16),
 (1,12,12),
 (2,13,15),
 (1,14,18),
 (4,17,17);

خروجی مطلوب:

u_nbr       u_nbr       cnt
----------- ----------- -----------
1           2           5
1           3           2
1           4           1
2           3           1

مساله 3.3 (بازه ای که بالاترین ترافیک را داشته)

با توجه به داده های درج شده در مساله 3.1 نتیجه ی مطلوب به شرح زیر است:

starting             ending               cnt
-------------------- -------------------- -----------
4                    5                    4
12                   12                   4

برای حل این مساله شما ناچارین از جدول اعداد استفاده کنید. نیازی نیست که اعداد بین 1 تا 24 را در خود راه حل تولید کنید. کافیست از نام یک جدول کمکی اعدادی که از قبل تولید شده در راه حلتون استفاده کنید. البته با ذکر اینکه این جدول یک جدول کمکی اعداد متوالی است.

پاسخها

مساله 3.1

جواب شماره 1:
اولین راه حلی که به ذهنم رسید استفاده از جدول اعداد بود. کافی بود اعداد را با بازهایی که با آن تداخل داشتند Join کنیم. سپس بر اساس اعداد گروه بندی کنیم تا تعداد افراد همزمان متصل در ساعت های مختلف بدست بیاد. و بعد از آن کافی بود بیشترین عدد رو به عنوان جواب انتخاب کنیم. به همین سادگی.

/*Msalim: Number Table*/
SELECT MAX(Cnt) AS Maximum
  FROM (
          SELECT COUNT(*) AS Cnt
            FROM Nbrs AS N
              JOIN Logining AS L
                ON N.n BETWEEN L.log_in AND L.log_out
               AND N.n BETWEEN 1 AND 24
           GROUP BY N.n
       ) AS D;

اولین نسخه ی این روش به شکل فوق بود. که ابتدا تعداد کاربر همزمان متصل در ساعات مختلف در عبارت جدولی D بدست می آمد سپس با کمک تابع MAX بزرگترینش انتخاب میشد.

ولی آیا واقعا به عبارت جدولی D احتیاجی بود؟ جواب کوتاه نه است. چرا که در SQL Server ماده ی بومی وجود داره به نام TOP که توسط اون میتونیم از عبارت جدولی D صرف نظر کنیم و حتی کد را کوتاه تر کنیم. یعنی:

/*Msalim: Without Derived Table Version*/
SELECT TOP 1 COUNT(*) AS Maximum
  FROM Nbrs AS N
    JOIN Logining AS L
      ON N.n BETWEEN 1 AND 24
     AND N.n BETWEEN L.log_in AND L.log_out
 GROUP BY N.n
 ORDER BY Maximum DESC;

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

جواب شماره 2:

با توجه به اینکه من ذکر کردم راه حل باید بی نیاز به اعداد متوالی پشت سر هم بین 1 تا 24 باشد. بازهم پاسخی را از طریق ایمیل غیر مستقیم (فرسنده 30Sharp_info) دریافت کردم که نیازمند این اعداد بود. به هر حال از ایشان به خاطر تلاشی که کرده ممنونیم.

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

1. هیچگاه اعداد متوالی مورد نیاز را داخل راه حل تولید نکنید. همیشه یک جدول کمکی اعداد در بانک داشته باشید که هر گاه نیاز به آن داشتین به راحتی از آن استفاده کنید. فراموش نکنید که این جدول یکبار قرار هست برای همیشه تولید شود.
2. اگر از نسخه های بالای SQL Server استفاده می کنید می توانیم عبارت جدولی که saat (ساعت) نام داره و وظیفه ی تولید اعداد 1 تا 24 را به عهده گرفته است را توسط تولید کننده سطر VALUES بهبود دهیم.
3. و همچنین اگه نسخه ی 2005 به بالا دارین میتوانید subquery را از ماده ی SELECT به عملگر جدولی APPLY انتقال دهید که مجبور به استفاده از عبارت جدولی total برای بدست آوردن ماکسیمم نداشته باشید.

select max(cnt)
from (select saat.item ,(select count(*)
                         from Logining
                         where saat.item between log_in and log_out) as cnt
      from (select item=1 union
            select 2 union
            select 3 union
            select 4 union
            select 5 union
            select 6 union
            select 7 union
            select 8 union
            select 9 union
            select 10 union
            select 11 union
            select 12 union
            select 13 union
            select 14 union
            select 15 union
            select 16 union
            select 17 union
            select 18 union
            select 19 union
            select 20 union
            select 21 union
            select 22 union
            select 23 union
            select 24 )as saat
      )as total

پس با اعمال نکات 2 و 3 نسخه ی جدید راه حل فوق به شکل زیر در میاید:

/*Msalim Version*/
SELECT MAX(cnt)
  FROM (VALUES (1), (2), (3), (4), (5),
               (6), (7), (8), (9), (10),
               (11), (12), (13), (14), (15),
               (16), (17), (18), (19), (20),
               (21), (22), (23), (24)
       ) D(n)
      CROSS APPLY (SELECT COUNT(*)
                     FROM Logining
                    WHERE D.n BETWEEN log_in AND log_out) T(cnt);

جواب شماره 3:

راه حلی را دریافت کردم توسط جناب Behrouzlo که جالب بود. چرا که منطق ساده شده ای داشت.

/* behrouzlo 126 chars */
Select  Max(cnt)
From    ( Select    ( Select    Count(*)
                      From      Logining L2
                      Where     L2.Log_in <= L1.Log_in
                                And L2.log_out >= L1.Log_in
                    ) cnt
          From      Logining L1
        ) List

این روش هم دقیقا مثل روش قبلی از subquery در ماده ی select استفاده کرده که برای بهبود آن در نسخه ی 2005 به بالا میتونیم آن سابکوئری را به Apply انتقال دهیم با این کار حداقل خوانایی کد را به مراتب افزایش میابد:

/*Msalim Version*/
SELECT MAX(cnt)
  FROM Logining L1
       CROSS APPLY (SELECT COUNT(*) cnt
                      FROM Logining L2
                     WHERE L2.log_in <= L1.log_in
                       AND L2.log_out >= L1.log_out) List

البته از JOIN هم میشود اینکار را انجام داد یعنی:

/*Msalim Version*/
SELECT TOP 1 COUNT(*) Mx
  FROM Logining L1
       INNER JOIN
       Logining L2
       ON L2.log_in <= L1.log_in
       AND L2.log_out >= L1.log_out
 GROUP BY L1.nbr
 ORDER BY Mx DESC;

جواب شماره 4:

/*Classic Version*/
with c as
(select l1.nbr nbr,
        l2.log_in, l2.log_out
   from logining l1, logining l2
  where  l2.log_in between l1.log_in and l1.log_out
     or l2.log_out between l1.log_in and l1.log_out
     or l1.log_in between l2.log_in and l2.log_out
     or l1.log_out between l2.log_in and l2.log_out
)
select max(cnt) Maximum
from (select nbr, count(*) cnt
       from c
       group by nbr
     ) l
where not exists
      (select *
       from c l1    
       where nbr = l.nbr
       and not exists
         (select 1
          from c l2
          where l2.nbr = l1.nbr
          and ( l2.log_in between l1.log_in and l1.log_out
          or l2.log_out between l1.log_in and l1.log_out
          or l1.log_in between l2.log_in and l2.log_out
          or l1.log_out between l2.log_in and l2.log_out)   
          having count(*) = l.cnt)
      );

مساله 3.2

آقای Reza_Yarahmadi این مساله را توسط راه حل زیر حل نمودند:

Select  
        S1.u_nbr,
        S2.u_nbr,
        cnt = COUNT(*)
 From
        @Sample S1, @Sample S2
 Where
        S1.u_nbr < S2.u_nbr
        AND(
        (S1.i >= S2.i AND S1.i <= S2.j)
        OR
        (S2.i >= S1.i AND S2.i <= S1.j))
Group By S1.u_nbr, S2.u_nbr
Order By S1.u_nbr, S2.u_nbr

دو نکته ی کوچک راجب کد فوق:

1. برای خوانایی بیشتر می شد از عملگر BETWEEN استفاده کرد.
2. برای اینکه portability راه حل افزایش پیدا کند باید از عملگر = برای دادن نام مستعار صرف نظر کرد. چرا که استاندارد نیست. و ظاهرا فقط در SQL Server قابل تشخیص هست.

مساله 3.3

/*Msalim Solution*/
with c as
(
      select n, count(*) cnt
      from Nbrs N join logining l
      on n between l.log_in and l.log_out
      --and n between 1 and 24
      group by n
)
select min(n) starting, max(n) ending,  cnt
from (
            select n,cnt, n - row_number() over(order by n) fct
            from c
            where cnt = (select max(cnt) from c)
      )d
group by fct, cnt