Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] Konkurs "Co to za query" – po 1. etapie

Tada! Jesteśmy po pierwszym etapie konkursu "Co to za query". Na wstępie dziękuję wszystkim, którzy wykazują zainteresowanie moim pomysłem. Mam nadzieję, że bawicie się nieźle, a jeszcze sporo przed nami. A teraz pora na…

Hall of Fame

Każdy z uczestników otrzymał swój identyfikator (jakby ktoś zapomniał swój numerek, niech pisze maila). Będę się posługiwał tymi numerkami, żeby nie używać Waszych danych osobowych do ostatniej chwili i ujawnić Wasze nazwiska dopiero za Waszą zgodą :-) Więc jak się trafi jakiś MVP, co nie ustrzeli ani punktu, to może mu się upiec :-P

Niech nie łamią rąk ci, którym nie poszło. Ponoć było wcale nie tak łatwo. Ale jeszcze będzie okazja, żeby się "odkuć". Niech nie cieszą się za mocno ci, którym się udało zmaksować. To nie koniec zabawy :-)

Miejsce ID uczestnika Punkty Czas (dni)*
1 2 70 0,34
2 3 70 1,28
3 5 70 3,90
4 10 70 6,06
5 13 70 6,32
6 16 70 7,28
7 11 55 6,19
8 4 50 3,74
9 12 50 5,20
10 7 35 4,01
11 6 35 4,26
12 9 35 4,97
13 14 25 6,76
14 15 25 6,93
15 1 10 0,00
16 8 0 4,68
17 17 0 7,29

* Czas w dniach będący różnicą między danym uczestnikiem a uczestnikiem, który najszybciej nadesłał odpowiedzi do etapu

Odpowiedzi do zadań z 1. etapu

Jak się powiedziało "A", to trzeba powiedzieć "B". Poniżej odpowiedzi do zadań z 1. etapu wraz z krótkim komentarzem.

Zadanie 1 - "Weterani"

Opis: Lista pracowników (login + ilość lat pracy), którzy przepracowali co ponad 10 lat (no, z grubsza…).

Kod:

SELECT 
e.LoginID,
DATEDIFF(year,HireDate,GETDATE()) AS YearsInCompany
FROM HumanResources.Employee AS e
WHERE HireDate < DATEADD(year,-10,GETDATE())

Komentarz:

Tu raczej nie było problemów, choć kilku uczestników poległo na tym, że czytając postać XML planu za bardzo wzięli do siebie CONVERT_EXPLICIT (niejawna konwersja) i używali jawnie funkcji CONVERT. Ciekawe, że w powyższym wywołaniu wynik GETDATE jest konwertowany na DATETIMEOFFSET(3), chociaż kolumna HireDate jest typu date :-)

Zadanie 2 - "Złap vendora"

Opis: Lista vendorów z najwyższym CreditRating.

Kod:

SELECT 
v.AccountNumber,
v.Name
FROM Purchasing.Vendor AS v
WHERE v.CreditRating = (
SELECT MAX(v1.CreditRating)
FROM Purchasing.Vendor AS v1
)

Komentarz:

W zasadzie równie proste jak zadanie 1. Alternatywnym rozwiązaniem jest użycie JOINa. Wpadki notowaliście głównie na braku aliasu v1 (tak, jeśli takowy jest w planie, to należy go umieścić w zapytaniu) oraz na zbyt dużej liczbie kolumn zwracanej przez zapytanie (zwracaliście czasem CreditRating).

Zadanie 3 - "Piramida"

Opis: Lista pracowników – login, login przełożonego, poziom zagnieżdżenia w hierarchii firmy.

Kod:

SELECT 
e1.LoginID AS Employee,
e2.LoginID AS Boss,
e1.OrganizationNode.GetLevel() AS Lvl
FROM HumanResources.Employee AS e1
LEFT OUTER JOIN HumanResources.Employee AS e2
ON e1.OrganizationNode.GetAncestor(1) = e2.OrganizationNode
ORDER BY e1.OrganizationNode

Komentarz:

Przykład użycia typu hierarchyid nie sprawił większości z Was problemów. Dwa miejsca, w których się myliliście – nieprawidłowa kolumna w ORDER BY (roztargnienie?) i poziom wybierany przy użyciu kolumny wyliczanej OrganizationLevel zamiast przy użyciu jawnego odwołania do metody GetLevel (różnicę w planie widać dość wyraźnie – o jeden Expr więcej jest w przypadku GetLevel). Niektórym zdarzyło się pomylić rodzaj złączenia (INNER zamiast LEFT).

Przy okazji tego zapytania wyszło na jaw parę ciekawostek. SQL Server wylicza wartości w kolumnach obliczanych (tu – kolumna OrganizationLevel) przy skanie indeksu clustered, ale mimo to wartości te są liczone drugi raz przy jawnym wywołaniu metody GetLevel. Jeden z uczestników powielił jedną z kolumn na liście kolumn wybieranych przez zapytanie, a mimo to plan się nie zmienił.

Zadanie 4 - "Los szczęścia"

Opis: Duży Lotek oczywiście (6 liczb z 49) :-)

Kod:

;WITH CTE AS (
SELECT 1 AS number
UNION ALL
SELECT c.number + 1
FROM CTE AS c
WHERE number < 49
)
SELECT TOP 6 number AS Lotto
FROM CTE
ORDER BY RAND(BINARY_CHECKSUM(NEWID()))

Komentarz:

W pierwszej chwili z planu nie wynika praktycznie nic :-) Ale jak się przyjrzeć temu, co jest w XML-u, to w parę minut można dojść do powyższego CTE. Jeden z uczestników odruchowo wpisał w ORDER BY samo NEWID…

Przy okazji analizowania tego planu można zauważyć, że jeśli sortujemy po jakimś wyrażeniu czy kolumnie, to owo wyrażenie lub owa kolumna znajdą się w OutputList.

I wreszcie – czy zauważyliście, w jaki sposób w tym planie zostały zawarte informacje o MAXRECURSION? :-) Jest w planie XML taki fragment:

CASE WHEN [Expr1007]&gt;(100) THEN (0) ELSE NULL END

Powyższy warunek zapewne sprawdza, czy dane rekursywne CTE ma w ogóle prawo się wykonać (czy nie został przekroczony maksymalny dopuszczany poziom rekursywności – domyślnie 100). Sprawdźcie sami, dopisując na koniec zapytania OPTION(MAXRECURSION 101).

Podsumowanie

To co, bardzo trudne było (bardzo łatwe być nie może, w końcu ma być wysiłek intelektualny!)? Myślę, że nie, ale poprawcie mnie, jeśli się mylę :-)

Powodzenia w 2. etapie, którego wyniki opublikuję za tydzień.

Opublikowane 20 maja 2009 07:00 przez brejk

Komentarze:

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 07:24 by Dalian

Ale porażka...

W trzecim brałem OrganizationLevel, OganizationNode.ToString,

ale nie pomyślałem o GetLevel...

buuuuuuuuuuuuuuu

Pozdr

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 07:54 by brejk

@Dalian: Nie łam się. To nie było bardzo proste. Ilość Expr w jednym elemencie planu była tu podpowiedzią. A to łatwo przegapić.

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 08:17 by pegaz_mk

A podobno od przybytku głowa nie boli ;) Jednak moje nadmiarowe kolumny trochę punktów kosztowały...

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 08:30 by brejk

@pegaz_mk: Przez moment się wahałem, czy przyznawać ułamki punktów za zadania. Ale stwierdziłem, że byłoby to nie fair wobec tych, którzy dali radę :-) Zabawa trwa dalej, jeszcze ciągle można powalczyć!

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 08:43 by pegaz_mk

Czyli jak rozumiem, za 2 dostałem 0, a za 3 i 4, w którym również były nadmiarowe kolumny (tyle, że szczęśliwie nie odzwierciedlone w planie) odpowiednio 20 i 25?

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 08:56 by brejk

@pegaz_mk: Tak właśnie :-) Napisałem nawet w tej notce, czemu w zadaniach 3 i 4 kolumny są w OutputList (patrz komentarz do zadania 4).

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 10:19 by lk-net_leszek

Wow - jedno wielkie Wow.

Chociaż mam 0 pkt....;-) To cieszę się, że moje odpowiedzi różnią się tylko w malutkich szczegółach (za dużo funkcji, brak aliasu, za dużo kolumn). No czwartego nie rozumiem dlaczego nie... ale zapytam się na PLSSUG'u

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 10:46 by brejk

@leszek: Już Ci mówię - wygenerowałeś w wyniku kolumnę, która była sumą n i RAND(...). A chodziło tylko o zwrócenie n i sortowanie wg RAND(...) :-)

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 12:50 by lk-net_leszek

@brejk: Aha. Rozumiem...;-) I tak jest super....

Teraz wiedząc co jest źle - mogę ponownie popatrzeć na plany i skorygować swój tok myślenia....

# re: [PL] Konkurs "Co to za query" – po 1. etapie

20 maja 2009 12:59 by brejk

@leszek: Wierzę, że Twoje pierwsze punkty przyjdą już w 2. etapie :-)

# re: [PL] Konkurs "Co to za query" – po 1. etapie

21 maja 2009 21:18 by lk-net_leszek

W końcu jestem nieułomek ... to się może jakiś punktu ułomek znajdzie.

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek