The Data Game

Że co?

Mamy proste zadanie. Określić medianę czasu odpowiedzi na pytanie zadane na StackOverflow w tagu “sql-server” w czasie tygodnia. Kiedy najszybciej otrzymamy odpowiedź? O której godzinie zadać pytanie? Którego dnia tygodnia?

Użyjemy do tego celu średniej bazy StackOverflow z roku 2013 (trochę stare dane) stąd.

Zaczynamy!

Dane na temat wpisów znajdują się w tabeli [dbo].[Posts]. Jednakże, znajdują się tam tak samo pytania jak i wszystkie odpowiedzi! Zerknijmy na wyniki.

SELECT TOP 100
    *
FROM
    [dbo].[Posts]

Poszukując tego czego szukamy możemy określić, że:

• Id to unikatowy numer wpisu w tabeli
• CreationDate to data utworzenia pytania bądź odpowiedzi
• PostTypeId to odwołanie do tabeli [dbo].[PostTypes], która określa jakiego typu jest dany wpis
• ParentId to odwołanie do tabeli [dbo].[Posts] ale z Id pytania (czyli <> 0 jednoznacznie wskazuje, że jest to odpowiedź)
• Tags to tagi załączone do pytania (odpowiedzi ich nie mają), ale wszystkie na raz w jednym polu

Mając tę wiedzę, spróbujmy wyciągnąć wszystkie pytania pod tagiem “sql-server”.

SELECT
    [Id],
	[CreationDate],
	[Tags]
FROM
    [dbo].[Posts]
WHERE
   [Tags] LIKE '%<sql-server>%';

Dostaliśmy w wyniku 82 248 pytań pod tagiem “sql-server”. Nieźle! Czas na znalezienie pierwszej odpowiedzi do każdego z nich.

SELECT
    p.[Id] as [PostId],
	p.[CreationDate] as [PostCreationDate],
	p.[Tags] as [PostTags],
	pFA.[Id] as [AnswerId],
	pFA.[CreationDate] as [AnswerCreationDate]
FROM
    [dbo].[Posts] p
CROSS APPLY
    (SELECT TOP(1)
        [Id],
        [CreationDate]
    FROM
        [dbo].[Posts] pA
    WHERE
        p.[Id] = pA.[ParentId]
    ORDER BY
        pA.[CreationDate]) pFA
WHERE
   p.[Tags] LIKE '%<sql-server>%';

Dlaczego CROSS APPLY a nie OUTER APPLY? Nie interesują nas aktualnie pytania bez odpowiedzi, czym zaciemnimy sobie trochę wyniki naszej analizy, ale co tam.

I tutaj muszę się zatrzymać na chwilę, bo plan zapytania jest cudowny, a dokładniej jeden jego element <3

Widzicie go? Jak nie to jeszcze wrócimy do tego 😉 Koniec przerwy.

W wyniku mamy 80 283 wiersze. Super, czyli tylko ~2 tysiące pytań pozostało bez odpowiedzi! To teraz tylko wyliczyć czas w sekundach pomiędzy zadaniem pytania a pierwszą odpowiedzią, oraz wyznaczenie dnia tygodnia + czau zadania pytania.

SELECT
    p.[Id] as [PostId],
    DATEDIFF(SECOND,p.[CreationDate],pFA.[CreationDate]) as [TimeElapsedInSeconds],
    DATEPART(WEEKDAY,p.[CreationDate]) as [WeekdayOfQuestion],
    CAST(p.[CreationDate] as TIME(0)) as [TimeOfQuestion]
FROM
    [dbo].[Posts] p
CROSS APPLY
    (SELECT TOP(1)
        [Id],
        [CreationDate]
    FROM
        [dbo].[Posts] pA
    WHERE
        p.[Id] = pA.[ParentId]
    ORDER BY
        pA.[CreationDate]) pFA
WHERE
   p.[Tags] LIKE '%<sql-server>%';

Mamy to! Zwróćcie uwagę na to co wyszło nam w kolumnie [WeekdayOfQuestion]. Niedziele są oznaczone jako 1! Tak, domyślnie SQL Server w większości ustawień traktuje niedzielę jak pierwszy dzień tygodnia. Tutaj nie będziemy tego zmieniać, ale zainteresowanych odsyłam do wuja Google i zapytania “SET DATEFIRST SQL Server“. Ponadto godziny z sekundami to tak średnio, najlepiej jakbyśmy to badali w “grupach” naliczanych co 15 minut. Let’s fix it!

SELECT
    p.[Id] as [PostId],
    DATEDIFF(SECOND,p.[CreationDate],pFA.[CreationDate]) as [TimeElapsedInSeconds],
    DATEPART(WEEKDAY,p.[CreationDate]) as [WeekdayOfQuestion],
    CAST(CASE
        WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 0 AND 14 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),0,0,0,0)
        WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 15 AND 29 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),15,0,0,0)
        WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 30 AND 44 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),30,0,0,0)
        ELSE
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),45,0,0,0)
    END AS TIME(0)) as [TimeOfQuestion]
FROM
    [dbo].[Posts] p
CROSS APPLY
    (SELECT TOP(1)
        [Id],
        [CreationDate]
    FROM
        [dbo].[Posts] pA
    WHERE
        p.[Id] = pA.[ParentId]
    ORDER BY
        pA.[CreationDate]) pFA
WHERE
   p.[Tags] LIKE '%<sql-server>%';

Yay, już prawie! To teraz jeszcze tylko mediana per połączenie dzień tygodnia i czas i jesteśmy w domu.

;WITH RawData AS (
SELECT
    p.[Id] as [PostId],
    DATEDIFF(SECOND,p.[CreationDate],pFA.[CreationDate]) as [TimeElapsedInSeconds],
    DATEPART(WEEKDAY,p.[CreationDate]) as [WeekdayOfQuestion],
    CAST(CASE
        WHEN DATEPART(MINUTE,p.[CreationDate]) < 15 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),0,0,0,0)
        WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 15 AND 29 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),15,0,0,0)
        WHEN DATEPART(MINUTE,p.[CreationDate]) BETWEEN 30 AND 44 THEN
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),30,0,0,0)
        ELSE
            TIMEFROMPARTS(DATEPART(HOUR,p.[CreationDate]),45,0,0,0)
    END AS TIME(0)) as [TimeOfQuestion]
FROM
    [dbo].[Posts] p
CROSS APPLY
    (SELECT TOP(1)
        [Id],
        [CreationDate]
    FROM
        [dbo].[Posts] pA
    WHERE
        p.[Id] = pA.[ParentId]
    ORDER BY
        pA.[CreationDate]) pFA
WHERE
   p.[Tags] LIKE '%<sql-server>%'
)
SELECT DISTINCT
    [WeekdayOfQuestion],
    [TimeOfQuestion],
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [TimeElapsedInSeconds]) OVER (PARTITION BY [WeekdayOfQuestion],[TimeOfQuestion]) AS [Median]
FROM
    [RawData]
ORDER BY
    [Median];

Chwilę to trwało, ale już wiemy. Najlepiej zadawać pytania w sobotę między 02:15:00 a 02:29:59 czasu StackOverflowowego! A najgorzej między 07:00:00 a 07:14:59 w niedzielę!
Chyba…

Zakończenie?

Oczywiście, że nie, bo nasze zapytanie chodzi jak roczne dziecko, czyli ledwo. W następnym wpisie spróbujemy coś z tym zrobić.

Oki, to pa!