Posts tagged T-SQL

Po długich testach pora na ogłoszenie wyników konkursu T-SQL “Liga piłkarska”. W konkursie wzięło udział 17 osób, które nadesłały ponad 60 rozwiązań. Rekordzista – Leszek Gniadkowski – nadesłał 15 rozwiązań. Ale zdarzały się też osoby, które nadesłały jedno, ale za to całkiem nieźle wypadające w testach zapytanie.

Na początek słowo o teście finałowym skierowane głównie do finalistów. Polegał on na uruchamianiu Waszych skryptów na trzech zestawach danych – 20, 50 i 100 drużyn. Dwa z finałowych rozwiązań musiałem wykluczyć z testów już po pierwszym podejściu do zestawu 20 drużyn, ponieważ nie mogłem doczekać się wyników… Pozostałe zostały poddane testom wielokrotnym (na każdym zestawie puszczałem skrypty w pętli po 10 razy, a same pętle były puszczone po 5 razy – czyli w sumie każdy skrypt był uruchomiony 50 razy na każdym zestawie danych). Przed każdym uruchomieniem skryptu czyściłem bufor danych i plan cache, żeby uniknąć przypadkowości. Całość testu przeprowadziłem na laptopie o parametrach: Core 2 Duo (ale tylko jeden procesor używany przez SQL Server) + 4 GB RAM (1 GB przydzielony na bufor danych). Zapytania były monitorowane przez SQL Trace, z którego następnie wyciągałem średnie wartości czterech kryteriów (duration, CPU, reads, writes) dla poszczególnych testów (“20”, “50” i “100”). Dla każdego testu robiłem osobną tabelę. Oto owe tabele:

Test “20”

Legenda:

D – średnie duration
C – średnie CPU
R – średnie reads
W – średnie writes
RD – miejsce w kryterium duration
RC – miejsce w kryterium CPU
RR – miejsce w kryterium reads
RW – miejsce w kryterium writes
T – suma miejsc z wszystkich kryteriów (im mniejsza, tym lepiej)

Test “50”

Test “100”

Dla każdego rozwiązania przy każdym teście uzyskałem sumę pozycji w poszczególnych kryteriach (ostatnia kolumna wg której ustalałem kolejność rozwiązań w powyższych tabelach wynikowych dla poszczególnych testów). Do uzyskania pozycji w końcowej tabeli wynikowej zsumowałem te wartości ze wszystkich trzech testów i tak wyszła mi tabela końcowa konkursu:

Legenda:

T20 – suma miejsc z testu “20”
T50 – suma miejsc z testu “50”
T100 – suma miejsc z testu “100”
Total – suma miejsc ze wszystkich testów

A zatem… Jednogłośnym zwycięzcą konkursu ogłaszam Leszka Gniadkowskiego. Drugie miejsce wśród uczestników (choć Leszek zdominował konkurs totalnie) zajął Marcin Nowakowski, a trzecie – Krzysztof Śliwa. Postanowiłem też nagrodzić miejsca 4. i 5. – a więc Tomka Przeliorza oraz Aleksandra Poniatowskiego. Ze zwycięzcami będę kontaktował się indywidualnie drogą mailową.

Dziękuję wszystkim, którzy wzięli udział w moim konkursie. Wszyscy, którzy zakwalifikowali się do finału oprócz słów uznania otrzymają ode mnie mały prezent (szczegóły niebawem w mailu). Szczególnie gratuluję zwycięzcom. Dobra SQL-owa robota :-)

Tym samym konkurs uznaję za zamknięty. Dla wytrwałych i ciekawskich publikuję pełen zestaw rozwiązań finałowych w pliku ZIP.

Pobierz wszystkie rozwiązania finałowe (ZIP, 21 KB)

Parę minut temu zamknąłem listę finalistów konkursu T-SQL “Liga piłkarska”. Do finału dostały się poniższe rozwiązania:

Cerekwicki_Cezary_v01.sql
Gailard_Pawel_v02.sql
Gniadkowski_Leszek_v10.sql
Gniadkowski_Leszek_v12.sql
Gniadkowski_Leszek_v15.sql
Grabowska_Katarzyna_v04.sql
Jacewicz_Lukasz_v02.sql
Kulczynski_Przemyslaw_v01.sql
Kulczynski_Przemyslaw_v04.sql
Nowakowski_Marcin_v03.sql
Nowakowski_Marcin_v04.sql
Pakulski_Maciej_v02.sql
Pakulski_Maciej_v03.sql
Pater_Rafal_v03.sql
Pater_Rafal_v04.sql
Poniatowski_Aleksander_v01.sql
Powichrowski_Marek_v12.sql
Powichrowski_Marek_v13.sql
Przeliorz_Tomek_v01.sql
Przeliorz_Tomek_v02.sql
Sliwa_Krzysiek_v02.sql
Sowa_Piotr_v10.sql
Waluszko_Bartlomiej_v02.sql
Zmuda_Katarzyna_v01.sql

Gratuluję finalistom i dziękuję wszystkim osobom, które wzięły udział w konkursie. Najdalej w środę wieczór powinny ukazać się wyniki konkursu, o czym poinformuję też uczestników drogą mailową.

Obiecałem, że jak tylko będę miał możliwość, zorganizuję konkurs T-SQL inspirowany cyklicznymi konkursami T-SQL Challenge. I słowa dotrzymuję :-)

Dane wejściowe

Dane są dwie tabele o strukturach, jak poniżej:

Tabela dbo.Teams:

TeamId      TeamName
----------- --------------------
1           Poland
2           San Marino
3           Ivory Coast
4           Luxemburg

Tabela dbo.Games:

HomeId      VisitorId   HomeGoals   VisitorGoals
----------- ----------- ----------- ------------
1           2           0           0
1           3           2           0
1           4           1           0
2           1           1           1
2           3           1           1
2           4           2           1
3           1           2           0
3           2           1           0
3           4           0           1
4           1           0           1
4           2           0           0
4           3           2           0

Tabela dbo.Teams zawiera identyfikatory (TeamId) oraz nazwy (TeamName) drużyn piłkarskich rywalizujących ze sobą na zasadzie klasycznej ligi (każdy z każdym po dwa mecze – mecz u siebie i mecz na wyjeździe). Tabela dbo.Games zawiera wyniki meczów – identyfikator drużyny gospodarzy (HomeId), identyfikator drużyny gości (VisitorId), liczbę bramek strzelonych przez gospodarzy (HomeGoals) oraz liczbę bramek strzelonych przez gości (VisitorGoals).

Opis zadania

1. Wynikiem działania zapytania stworzonego w ramach konkursu ma być tabela wyniku rywalizacji drużyn zawierająca kolumny:- Position – pozycja drużyny w tabeli – nie ma miejsc ex-aequo,- Team – nazwa drużyny,- P – liczba rozegranych przez drużynę spotkań,- W – liczba zwycięstw drużyny,- D – liczba remisów drużyny,- L – liczba porażek drużyny,- F – liczba bramek zdobytych przez drużynę,- A – liczba bramek straconych przez drużynę,- Points – liczba punktów zdobytych przez drużynę zgodnie z regułą: 3 punkty za zwycięstwo, 1 punkt za remis oraz 0 punktów za porażkę.
2. O kolejności drużyn w tabeli decydują kolejno:- liczba punktów – im więcej, tym lepiej,- różnica między bramkami strzelonymi a straconymi – im większa, tym lepiej,- liczba bramek strzelonych – im większa, tym lepiej,- liczba bramek straconych – im mniejsza, tym lepiej,- tabela wyników bezpośrednich spotkań – jeśli dwie lub więcej drużyn mają taką samą pozycję w oparciu o powyższe kryteria, o kolejności decyduje tabela wyników zbudowana wyłącznie o wyniki bezpośredniej rywalizacji między zainteresowanymi drużynami (i stosujemy w tej tabeli takie same kryteria, jak powyżej – czyli zaczynamy od punktów, potem bramki etc.), w tabeli tej bramki strzelone na wyjazdach liczymy podwójnie (ale do liczenia punktów oczywiście bramki liczymy normalnie, bez podwajania tych strzelonych na wyjazdach),- jeżeli dwie lub więcej drużyn po zastosowaniu powyższych kryteriów kwalifikują się do zajęcia tego samego miejsca, o kolejności decyduje TeamId – zespół o mniejszym TeamId w tabeli znajdzie się przed zespołem o większym TeamId (załóżmy, że TeamId to miejsce w rankingu federacji piłkarskiej).

Oczekiwany wynik

Position Team        P W D L F A Points
-------- ----------- - - - - - - ------
1        Poland      6 3 2 1 5 3 11
2        San Marino  6 1 4 1 4 4 7
3        Luxemburg   6 2 1 3 4 4 7
4        Ivory Coast 6 2 1 3 4 6 7

Skypt generujący przykładowe dane

Skrypt do wygenerowania przykładowych danych:

IF OBJECT_ID(N'dbo.Games', 'U') IS NOT NULL
  DROP TABLE dbo.Games;
IF OBJECT_ID(N'dbo.Teams', 'U') IS NOT NULL
  DROP TABLE dbo.Teams;
CREATE TABLE dbo.Teams (
  TeamId int NOT NULL,
  TeamName varchar(20) NOT NULL
);
CREATE TABLE dbo.Games (
  HomeId int NOT NULL,
  VisitorId int NOT NULL,
  HomeGoals int NOT NULL,
  VisitorGoals int NOT NULL
);
INSERT INTO dbo.Teams (TeamId, TeamName)
VALUES (1, 'Poland');
INSERT INTO dbo.Teams (TeamId, TeamName)
VALUES (2, 'San Marino');
INSERT INTO dbo.Teams (TeamId, TeamName)
VALUES (3, 'Ivory Coast');
INSERT INTO dbo.Teams (TeamId, TeamName)
VALUES (4, 'Luxemburg');

INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (1, 2, 0, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (1, 3, 2, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (1, 4, 1, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (2, 1, 1, 1);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (2, 3, 1, 1);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (2, 4, 2, 1);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (3, 1, 2, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (3, 2, 1, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (3, 4, 0, 1);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (4, 1, 0, 1);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (4, 2, 0, 0);
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
VALUES (4, 3, 2, 0);

Zasady konkursu

1. Rozwiązaniem ma być pojedyncze zapytanie T-SQL rozpoczynające się od słowa “SELECT”, “WITH” lub “;WITH”.
2. W zapytaniu można używać podzapytań i wszystkich elementów języka T-SQL dostępnych w SQL Server 2008 R2.
3. W zapytaniu można użyć tabeli liczb, której definicja jest podana tutaj. W skrypcie nie umieszczaj definicji tabeli liczb (załóż, że będzie ona dostępna w bazie testowej).
4. Zapytania, które przejdą próbę poprawności zwracanego wyniku (testy będą prowadzone na nieco większej ilości danych), będą klasyfikowane wg następujących kryteriów: czas wykonania (duration), zużycie procesora (CPU), ilość odczytów (reads) oraz ilość zapisów (writes). Zwycięży rozwiązanie, które łącznie zdobędzie najwięcej punktów we wszystkich czterech kryteriach.
5. Rozwiązanie (możesz ich wysłać więcej niż jedno) wyślij mailem w postaci skryptu o rozszerzeniu .sql na adres: pawel.potasinski[małpa]sqlpass.org. W tytule maila wpisz “Konkurs T-SQL”. Otrzymasz ode mnie potwierdzenie (e-mail), jeżeli otrzymam od Ciebie rozwiązanie.
6. W razie pytań lub uwag użyj podanego powyżej adresu e-mail lub dodaj komentarz do tego wpisu.
7. Na rozwiązania czekam do 3 października 2010 do końca dnia. Ogłoszenie wyników przewiduję najdalej tydzień później.
8. Konkurs w całości organizuję sam (sam wymyśliłem zadanie, sam będę testował rozwiązania, sam przyznawał nagrody etc.), a co za tym idzieTwoje ewentualne dane osobowe oglądam wyłącznie ja – Paweł Potasiński (konkurs nie ma sponsorów).

Nagrody

Nagrody to prawdopodobnie najmniej istotna część konkursu, ale chyba dobrze wiedzieć, o co walczysz :-)

1. miejsce – subskrypcja MSDN + dysk przenośny 2,5 cala o pojemności 500 GB

2. miejsce – subskrypcja MSDN

3. miejsce – książka pt. “Serwer SQL 2008. Usługi biznesowe. Analiza i eksploracja danych” z autografem Marcina Szeligi

PS. Zastrzegam sobie prawo do zmiany zestawu nagród bez uprzedzenia – np. w sytuacji, gdy uznam, że należy wyróżnić więcej niż 3 uczestników konursu :-)

Jest o co walczyć? Myślę, że tak! A i tak nie ma to jak satysfakcja z samego działającego rozwiązania! ;-)

Powodzenia i czekam na Wasze rozwiązania!

[EDYCJA: 2010-09-20]

Na prośbę uczestników i osób postronnych załączam bardziej rzeczywiste dane testowe: Primera Division 2009/2010. Visca el Barca!

[/EDYCJA]

[EDYCJA: 2010-09-21]

Udostępniam zestaw danych, który powinien pomóc Wam w przetestowaniu Waszych rozwiązań pod kątem poprawności algorytmu sortowania tabeli wynikowej. Grupa śmierci normalnie :-)

Pobierz skrypt “Tricky data”

[/EDYCJA]

[EDYCJA: 2010-09-22]

Zmyłka ;-) Dzisiejszy alarm podniesiony przeze mnie po interwencji Marka Powichrowskiego (ech, mąciwoda!) okazał się fałszywy. Oczekiwany wynik we wpisie jest OK. W tym miejscu dziękuję Leszkowi Gniadkowskiemu za to, że nie dał się zasugerować ani Markowi, ani mnie, i podjął własne śledztwo :-) Walczymy dalej! Póki co zanotowałem ledwie 4 uczestników. Czyżby wszyscy czytelnicy mojego bloga mieli już własne subskrypcje MSDN i przenośne dyski 500 GB??? ;-)

[/EDYCJA]

Przeprowadziłem  test basic na wszystkich rozwiązaniach, które dotąd otrzymałem. Poniżej rozwiązania, które przeszły test, a zarazem zwróciły poprawne wyniki dla kilku prostych zestawów danych (darujcie brak polskich liter w nazwiskach):

Gniadkowski_Leszek_v05.sql
Gniadkowski_Leszek_v06.sql
Gniadkowski_Leszek_v07.sql
Gniadkowski_Leszek_v09.sql
Gniadkowski_Leszek_v10.sql
Gniadkowski_Leszek_v12.sql
Grabowska_Katarzyna_v02.sql
Jacewicz_Lukasz_v01.sql
Pakulski_Maciej_v01.sql
Poniatowski_Aleksander_v01.sql
Powichrowski_Marek_v07.sql
Powichrowski_Marek_v10.sql
Przeliorz_Tomek_v01.sql
Waluszko_Bartlomiej_v01.sql

Każdy pewnie wie, ile wersji wysyłał, więc nie powinniście mieć problemów z odnalezieniem tych właściwych :-) Gratuluję tym sześciu osobom, którym się udało, a tych, którzy nie dali rady lub jeszcze nie nadesłali swoich rozwiązań (?), zachęcam do działania. Czasu jeszcze jest sporo ;-) Wszelkich odpowiedzi na Wasze pytania udzielam indywidualnie na maila. Test na “tricky data” już niebawem. Stay tuned!

[/EDYCJA]

[EDYCJA: 2010-09-28 {BASIC TESTING UPDATE}]

Poniżej lista rozwiązań, które pomyślnie przeszły basic testing do dzisiaj (28.09):

Cerekwicki_Cezary_v01.sql
Gniadkowski_Leszek_v05.sql
Gniadkowski_Leszek_v06.sql
Gniadkowski_Leszek_v07.sql
Gniadkowski_Leszek_v09.sql
Gniadkowski_Leszek_v10.sql
Gniadkowski_Leszek_v12.sql
Gniadkowski_Leszek_v13.sql
Grabowska_Katarzyna_v02.sql
Jacewicz_Lukasz_v01.sql
Nowakowski_Marcin_v01.sql
Pakulski_Maciej_v01.sql
Pater_Rafal_v03.sql
Poniatowski_Aleksander_v01.sql
Powichrowski_Marek_v07.sql
Powichrowski_Marek_v10.sql
Powichrowski_Marek_v11.sql
Przeliorz_Tomek_v01.sql
Rozycki_Grzegorz_v01.sql
Sowa_Piotr_v05.sql
Sowa_Piotr_v06.sql
Waluszko_Bartlomiej_v01.sql

[/EDYCJA]

Panie i Panowie, zaczynamy tricky data testing! Poniżej oddaję w Wasze ręce kolejny zestaw spreparowanych wyników. Tym razem ex-aequo jest sporo i trzeba mieć w kodzie odpowiedzi na wiele pułapek. Sprawdziłem nadesłane do dziś rozwiązania i z niekrytą satysfakcją oznajmiam, że 17 rozwiązań stworzonych przez 9 zawodników spełniło rygorystyczne wymogi tego testu :-) Poniżej lista szczęśliwców. Niewykluczone, że wymyślę jeszcze jakiś zestaw testowy, by dokładnie sprawdzić, czy mnie nie nabieracie w swoich “dziełach sztuki SQL-owej” ;-)

Cerekwicki_Cezary_v01.sql
Gniadkowski_Leszek_v07.sql
Gniadkowski_Leszek_v09.sql
Gniadkowski_Leszek_v10.sql
Gniadkowski_Leszek_v12.sql
Gniadkowski_Leszek_v13.sql
Gniadkowski_Leszek_v15.sql
Grabowska_Katarzyna_v02.sql
Nowakowski_Marcin_v01.sql
Pater_Rafal_v03.sql
Powichrowski_Marek_v07.sql
Powichrowski_Marek_v10.sql
Powichrowski_Marek_v11.sql
Przeliorz_Tomek_v01.sql
Sowa_Piotr_v05.sql
Sowa_Piotr_v06.sql
Waluszko_Bartlomiej_v01.sql

*** Pobierz zestaw danych “tricky data” ***

[/EDYCJA]

[EDYCJA: 2010-09-29 {TRICKY DATA} ]

WAŻNE!!! W tabeli dbo.Teams mogą znaleźć się drużyny, które nie rozegrały żadnego spotkania (ich identyfikatory nie występują w dbo.Games). W takiej sytuacji drużyny te mają pojawić się w tabeli wynikowej z zerami w odpowiednich kolumnach i z zachowaniem kolejności zgodnie z podanym algorytmem (czyli po prostu decyduje ranking). A jeśli takich drużyn bez rozegranego meczu jest więcej niż jedna, o ich kolejności decyduje pozycja w rankingu (TeamId).

Na razie żadne (!) z nadesłanych przez Was rozwiązań nie spełnia tej reguły, więc proszę o szybkie poprawienie kodu!

Zmieniłem w związku z tym ostatnio zamieszczony zestaw TRICKY DATA.

Poniżej będę publikował aktualną listę rozwiązań, które pomyślnie przeszły basic + tricky data test.

Cerekwicki_Cezary_v01.sql
Gailard_Pawel_v02.sql
Gniadkowski_Leszek_v10.sql
Gniadkowski_Leszek_v12.sql
Gniadkowski_Leszek_v15.sql
Grabowska_Katarzyna_v04.sql
Jacewicz_Lukasz_v02.sql
Kulczynski_Przemyslaw_v01.sql
Kulczynski_Przemyslaw_v04.sql
Nowakowski_Marcin_v03.sql
Nowakowski_Marcin_v04.sql
Pakulski_Maciej_v02.sql
Pakulski_Maciej_v03.sql
Pater_Rafal_v03.sql
Pater_Rafal_v04.sql
Poniatowski_Aleksander_v01.sql
Powichrowski_Marek_v12.sql
Powichrowski_Marek_v13.sql
Przeliorz_Tomek_v01.sql
Przeliorz_Tomek_v02.sql
Sliwa_Krzysiek_v02.sql
Sowa_Piotr_v10.sql
Waluszko_Bartlomiej_v02.sql
Zmuda_Katarzyna_v01.sql

[/EDYCJA]