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

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

Drugi etap konkursu "Co to za query" przechodzi do historii. Podobno było trudniej niż w początkowej fazie konkursu. Hmmm, czy ja wiem :-)

Hall of Fame

Dwóch śmiałków utrzymało wyborną formę z 1. etapu i ma na koncie "maksa". Cieszy mnie to, że prawie nikt nie zrezygnował po 1. etapie. Dajecie radę!

[EDIT] Ha! Nie jestem nieomylny. W wyniki po 2. etapie wkradł się błąd. Dziękuję zawodnikowi nr 4 za uczciwość. Zrewidowałem wyniki i jeszcze raz przejrzałem skrypty. Teraz tabelka się zgadza. Mamy zwycięzcę w klasyfikacji fair-play :-) [/EDIT]

Miejsce ID uczestnika Punkty Czas (dni)*
1 5 140 3,90
2 3 140 4,20
3 12 120 11,18
4 2 115 1,32
5 13 115 11,15
6 10 115 12,05
7 16 115 13,34
8 11 100 12,26
9 4 95 9,21
10 7 80 5,87
11 9 80 6,97
12 15 70 12,45
13 14 55 12,82
14 6 45 9,91
15 17 45 12,14
16 8 30 10,21
17 1 10 6,07
18 18 10 13,37

* 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 2. etapu

Zadanie 1 - "Tylko dla snobów"

Opis: Lista 10 najdroższych produktów będących w sprzedaży (ex aequo).

Kod:

SELECT TOP 10 WITH TIES Name, ListPrice
FROM Production.Product
WHERE DiscontinuedDate IS NULL
ORDER BY ListPrice DESC

Komentarz: W zasadzie żadnych problemów, choć chyba zdarzyło się komuś wsadzić za dużo o jedną kolumnę do wyniku. Kilka osób kombinowało niepotrzebnie z podzapytaniami (ale plany się zgadzały, więc tragedii nie ma).

Zadanie 2 - "Najbliżej do Jumping Street"

Opis: Lista adresów w kolejności od najbliższego do 9178 Jumping St. w Dallas :-)

Kod:

SELECT 
a2.AddressLine1,
a2.City,
a1.SpatialLocation.STDistance(a2.SpatialLocation)/1000 AS Distance
FROM Person.Address AS a1
CROSS JOIN Person.Address AS a2
WHERE a1.AddressID <> a2.AddressID
AND a1.AddressLine1 = '9178 Jumping St.' AND a1.City = 'Dallas'
ORDER BY 3 ASC

Komentarz: Stworzyłem tu małego potworka :-) Użycie CROSS JOIN w miejsce INNER JOIN zmienia co najwyżej (a i chyba nie zawsze) jeden atrybut w elemencie NestedLoops w planie XML. Dlatego odpowiedzi z INNER JOIN przyjmowałem jako prawidłowe. Zdarzało się też, że zapominaliście o podzieleniu STDistance przez 1000 (a tylko wtedy wynik jest w kilometrach).

Zadanie 3 - "Klienci trzej"

Opis: Zestawienie ilości zamówień trzech wybranych klientów per rok kalendarzowy.

Kod:

SELECT CalendarYear, [29825], [29826], [29827]
FROM (
SELECT YEAR(OrderDate) AS CalendarYear, CustomerID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (29825, 29826, 29827)
) AS Q
PIVOT
(
COUNT(CustomerID) FOR CustomerID IN ([29825], [29826], [29827])
) AS P

Komentarz: Prościutkie (chyba) ćwiczenie z klasyki PIVOTa :-) Opcjonalnie można było przepisać z planu XML wymyślne COUNTy z CASE WHEN, dodać GROUP BY po roku i też było dobrze. Daliście radę bez problemu.

Zadanie 4 - "Money, money, money"

Opis: Wyciąg informacji o zarobkach osób z danych demograficznych trzymanych w dokumentach XML.

Kod:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
SELECT
p.FirstName,
p.LastName,
T.x.value('YearlyIncome[1]', 'varchar(100)') AS YearlyIncome
FROM Person.Person AS p
CROSS APPLY p.Demographics.nodes('/IndividualSurvey') AS T(x)
WHERE T.x.exist('YearlyIncome[1]') = 1

Komentarz: Tak, to było trudne :-) W planie były liczne odwołania do indeksów XML i do tabel wewnętrznych tworzonych na potrzeby owych indeksów. Dokładne okiełznanie tego planu wymagało choć minimalnej znajomości metod typu danych XML (nodes, value i exist). Dodatkowo użycie przestrzeni nazw (można było ją deklarować jak powyżej albo w metodach XQuery za pomocą declare default element namespace) i powstało naprawdę wymyślne zadanie. Szacunek dla tych, którzy podołali (4 osoby). Największymi przegranymi tego zadania są ci, którzy wpadli na samą składnię, a polegli na typie danych użytym w metodzie value (wpisywali coś innego niż varchar(100)). Wiecie co? Chyba napiszę na blogu jakiś artykulik o zastosowaniach typu XML :-)

Podsumowanie

Tak, to prawdopodobnie był najtrudniejszy etap konkursu ;-) A przynajmniej tak wynika z Waszych wypowiedzi. Tym bardziej cieszą wyniki, które są naprawdę niezłe. Wiele osób już nadesłało rozwiązania do zadań z 3. etapu. Wyniki ogłoszę w środę – 3 czerwca. Rozdanie nagród (części) - na spotkaniu PLSSUG Warszawa.

Natomiast już dzisiaj proszę, by każdy z uczestników zajrzał na stronę z listą nagród i wysłał mi na maila (pawelp[at]plssug[dot]org[dot]pl) listę 5 książek w kolejności "chcenia" :-) O tym, kto dostanie książkę i jaki będzie tytuł, zadecyduje oczywiście końcowa klasyfikacja (trzeba być w TOP 10, żeby załapać się na literaturę).

Trzymam kciuki za Was w 3. etapie. Finiszujemy :-)

Opublikowane 27 maja 2009 00:12 przez brejk

Komentarze:

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

27 maja 2009 11:15 by pegaz_mk

To ja, to ja, co tak z case'ami kombinował ;)

Tyle, że jak wytworzyłem pivota, który daje identyczne wyniki, to plan wykonania był zuuupełnie inny i w stosunku do case'ów był 10x mniej wydajny...

select orderyear, [29825], [29826], [29827] from ( select CustomerID,

DATEPART(YEAR, orderdate) as orderyear from Sales.SalesOrderHeader ) as D

pivot ( count( customerid ) for D.CustomerID in ( [29825], [29826], [29827] ) ) as P

Cóż, wychodzi brutalna prawda, na codzień człowiek obcuje najczęściej ze zwykłymi selectami, a jak się trafi jakiś left outer join to już jest coś ;)

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

27 maja 2009 11:27 by brejk

@pegaz_mk: Bo Twój PIVOT zrobił coś na kształt HAVING zamiast WHERE :-) Dodając warunek w podzapytaniu dostajesz dobry plan.

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

27 maja 2009 13:20 by Michał

@pegaz_mk: ja też kombinowałem z group by bo nie wpadłem na to żeby pivota użyć :[

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

27 maja 2009 16:35 by lk-net_leszek

czyli wychodzi mi że 1 i 3 zrobilem dobrze.... bez pivota, ale za to z dwutaktem..;-)

W domu sprawdzę, ale coś mi się kojarzy że na drugim znowu poległem na jakimś aliasie...;-(

No a czwarte to realnie poległem na cross apply (to znaczy nawet gdzieś w necie wygooglalem podobne zapytanie odnośnie AW) - ale przy próbach dostosowania ... coś się "poparsowało" .. nie tak jak trzeba.

Ale XML'a liznąłem.....

No i tak jak pegaz_mk napisał: inner join'y ze zwykłych kolumn = spoko... czegoś więcej i często wtopa.... Repetitio est mater studiorum.

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

1 czerwca 2009 09:58 by Dalian

A u mnie padłem na kilometrach ....

A i COUNTY też moje...

Pozdr

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek