SQL Mozgalica za Juni 2007

Re: SQL Mozgalica za Juni 2007 16.07.2007. u 20:36 · pre 234 meseca #21

Pa zar poenta zadatka nije bila da se imena gostiju prikazu uparena u dve kolone (jedna soba jedan red)?

Ako se iz mog poslednjeg upita

Code:

SELECT pairs.gender,
       pairs.topic,
       pairs.room_number,
       MAX(pairs.first_guest) AS first_guest,
       MAX(pairs.second_guest) AS second_guest
  FROM (SELECT g1.gender,
               g1.topic,
               (COUNT(g1.guestname) + 1) / 2 AS room_number,
               CASE WHEN COUNT(g1.guestname) % 2 = 1
                    THEN g1.guestname
                    ELSE NULL
               END AS first_guest,
               CASE WHEN COUNT(g1.guestname) % 2 = 0
                    THEN g1.guestname
                    ELSE NULL
               END AS second_guest
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.gender = g2.gender
                AND g1.topic = g2.topic
                AND g1.guestname >= g2.guestname
         GROUP BY g1.gender, g1.topic, g1.guestname
        ) AS pairs
GROUP BY pairs.gender, pairs.topic, pairs.room_number
ORDER BY pairs.gender, pairs.topic, pairs.room_number

izvuce unutrasnji upit, izbaci podela na dve kolone (CASE izrazi) i doda sortiranje dolazi se do

Code:

SELECT g1.gender,
       g1.topic,
       (COUNT(g1.guestname) + 1) / 2 AS room_number,
       g1.guestname
  FROM guests AS g1
       INNER JOIN
       guests AS g2
         ON g1.gender = g2.gender
        AND g1.topic = g2.topic
        AND g1.guestname >= g2.guestname
GROUP BY g1.gender, g1.topic, g1.guestname
ORDER BY g1.gender, g1.topic, g1.guestname

sto je identicno CandyMan-ovom resenju :)

Potpis

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Re: SQL Mozgalica za Juni 2007 16.07.2007. u 20:47 · pre 234 meseca #22

Chacka je u pravu. Zaboravio sam sta sam trazio na pocetku

To valjda dodje s godinama. Tacno, treba ih pokazati 'jedan red jedna soba' a za to treba vanjski i unutrasnji kveri. Oba resenja, chackino i CandiMana, su bolja nego ono gde sam ja krenuo, subquery se prakticnone i ne vidi.

Re: SQL Mozgalica za Juni 2007 16.07.2007. u 21:20 · pre 234 meseca #23

Da se ne zaborave Oracle-ove analiticke funkcije

Code:

SELECT gender, topic, guestname,
       TRUNC((ROW_NUMBER() OVER
              (PARTITION BY gender, topic
                   ORDER BY gender, topic
              ) + 1) / 2) AS room_number
  FROM guests
ORDER BY gender, topic, guestname

sto je identicno predhodnom upitu.

Jedan SELECT iz jedne tabele :) Oracle ubija!

Potpis

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Re: SQL Mozgalica za Juni 2007 19.07.2007. u 16:43 · pre 234 meseca #24

I MS SQl konja za trku ima (Row_Number :-)

Prvo sam napravio ovo, po ugledu na CandyMan i Chacku:

Code:

SELECT
    Gender
    , Topic
    , GuestName
    , OrderNumber = Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic )
FROM Guests

i dobio ovo:

Code:

Gender    Topic    GuestName    OrderNumber
F    Istorija    Cica    1
F    Istorija    Jaca    2
F    Istorija    Juca    3
F    Istorija    Maca    4
F    Istorija    Melanija    5
F    Muzika    Mica    1
F    Muzika    Jeca    2
F    Muzika    Veca    3
F    Muzika    Zoca    4

Onda sam uvideo da vazi sledece:
ako je OrderNumber [paran, onda je [broj sobe] = OrderNumber /2
ako je oredr number neparan, onda je [broj sobe] = (Ordernumber+1)/2

sto se da lako proveriti. Parnost se lako proveri celobrojnim deljenjem, kao

IF INT(x/2) = X THEN X je paran ELSE x je neparan

sto se u MS SQL moze uraditi pomocu CASE izraza.

medjutim, napravio sam neke greske u kucanju i ipak dobio tacan rezultat na sledeci nacin:

Code:

SELECT
    Gender
    , Topic
    , GuestName
    , RoomNumber = (1+Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic ))/2
FROM Guests

Code:

Gender    Topic    GuestName    RoomNumber
F    Istorija    Cica    1
F    Istorija    Jaca    1
F    Istorija    Juca    2
F    Istorija    Maca    2
F    Istorija    Melanija    3
F    Muzika    Mica    1
F    Muzika    Jeca    1
F    Muzika    Veca    2
F    Muzika    Zoca    2

Znaci, nisat CASE nista celobrojno deljenje, samo dodas 1 na Row_number i to podelis sa dva => dobijes Room_Number.
Ne pitajte me zasto je ovo ovako i u cemu je stos, ne znam. Ako neko zna da objasni ovo, molim da se javi.
Onda sam hteo da budem pametan i da bas sve resim u jednom kveriju, pa sam pokusao ovo:

Code:

SELECT
    Gender
    , Topic
    , Guest_1 = MIN(GuestName)
    , Guset_2 = MAX(GuestName)
    , RoomNumber = (1+Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic ))/2
FROM Guests
GROUP BY
    Gender
    , Topic
    , (1+Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic ))/2

idobio ovo:

Citat:

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Naravno da je ovo proradilo:

Code:

SELECT
    Gender
    ,Topic
    , RoomNumber
    , Guest_1 = MIN(GuestName)
    , Guest_2 = MAX(GuestName)
FROM
(
SELECT
    Gender
    , Topic
    , GuestName
    , RoomNumber = (1+Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic ))/2
FROM Guests
) AS X
GROUP BY
    Gender
    ,Topic
    , RoomNumber

Ali, pogledajte rezultate:

Code:

Gender    Topic    RoomNumber    Guest_1    Guest_2
F    Istorija    1    Cica    Jaca
F    Istorija    2    Juca    Maca
F    Istorija    3    Melanija    Melanija
F    Muzika    1    Jeca    Mica
F    Muzika    2    Veca    Zoca
M    Istorija    1    Pera    Zoran
M    Istorija    2    Krle    MIka
M    Istorija    3    Laza    Laza
M    Muzika    1    Janko    Ludwig
M    Muzika    2    Djura    Goran
M    Muzika    3    Milan    Zika

Melanija u sobi sa Melanijom i Laza u sobi sam sa sobom, U sustini tacno, ali zelimo da pokazemo da je drugi krevet slobodan.

Upotrebio sam NULIIF funkciju:

Code:

SELECT
    Gender
    ,Topic
    , RoomNumber
    , Guest_1 = MIN(GuestName)
    , Guest_2 = NULLIF(MAX(GuestName),MIN(GuestName))
FROM
(
SELECT
    Gender
    , Topic
    , GuestName
    , RoomNumber = (1+Row_number() OVER  (PARTITION BY gender, topic ORDER BY gender, topic ))/2
FROM Guests
) AS X
GROUP BY
    Gender
    ,Topic
    , RoomNumber

i rezultat:

Code:

Gender    Topic    RoomNumber    Guest_1    Guest_2
F    Istorija    1    Cica    Jaca
F    Istorija    2    Juca    Maca
F    Istorija    3    Melanija    NULL
F    Muzika    1    Jeca    Mica
F    Muzika    2    Veca    Zoca
M    Istorija    1    Pera    Zoran
M    Istorija    2    Krle    MIka
M    Istorija    3    Laza    NULL
M    Muzika    1    Janko    Ludwig
M    Muzika    2    Djura    Goran
M    Muzika    3    Milan    Zika

Pazljivo sa NULLIF funkcijom. Zamenite redosled argumanata u NULLIF i vidite sta cete dobiti :-)

Svaka cast CandyMan i Chachka za ideju sa Row_Count. Ko u sistemu nema row_count, ima chachkino resenje sa subkverijima

Re: SQL Mozgalica za Juni 2007 19.07.2007. u 18:39 · pre 234 meseca #25

Lepa razrada teme :)

Citat:

Zidar: Znaci, nisat CASE nista celobrojno deljenje, samo dodas 1 na Row_number i to podelis sa dva => dobijes Room_Number.
Ne pitajte me zasto je ovo ovako i u cemu je stos, ne znam. Ako neko zna da objasni ovo, molim da se javi.

U standardnom SQL-u je

Code:

integer / integer = integer
3 / 2 = 1
4 / 5 = 0

Zidarova upotreba '/' jeste celobrojno deljenje. Ovde se MS SQL Server drzi SQL standarda, dok se Oracle u ovom slucaju ne pridrzava, pa je potrebno upotrebiti TRUNC.

Evo i gornjeg upita prevedenog na standardni SQL (bez upotrebe analitickih funkcija):

Code:

SELECT x.gender,
       x.topic,
       x.room_number,
       MIN(x.guestname) AS first_guest,
       CASE WHEN MAX(x.guestname) = MIN(x.guestname)
            THEN NULL
            ELSE MAX(x.guestname)
       END AS second_guest
  FROM (SELECT g1.gender,
               g1.topic,
               g1.guestname,
               (COUNT(g1.guestname) + 1) / 2 AS room_number
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.gender = g2.gender
                AND g1.topic = g2.topic
                AND g1.guestname >= g2.guestname
         GROUP BY g1.gender, g1.topic, g1.guestname
       ) AS x
GROUP BY x.gender, x.topic, x.room_number
ORDER BY x.gender, x.topic, x.room_number

Potpis

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Re: SQL Mozgalica za Juni 2007 05.08.2007. u 04:05 · pre 234 meseca #26

Citat:

Pre svega, celo resenje ne treba traziti u SQL-u, jer po samoj logici pristupa, ne mora rasporednjivanje da se radi odjednom, vec moze postupno kako se novi gosti prijavljuju. Svakako treba obezbediti da se uparivanje, cak i kada se radi rucno izvodi po zadatim pravilima i to u dva oblika: prilikom unosa, apliakcija treba da ponudi samo one osobe koje odgovaraju po pravilima a koje nemaju para (a da operateru ostavi izbor koga ce zaista sa kim upariti) i prilikom upisa podataka treba obezbediti da se ne mogu upisati parovi koji ne odgovaraju pravilima.

U potpunosti se slazem. U praksi se mnogo puta javlja problem koji je lakse resiti rucno nego po nekom automatizmu. Pre cu cak irucno upisati u bazu ko ce s kim da spava neko pisati upit i posle te podatke menjati ako osoba insistira da spava sa svojim dragim prijateljem u sobi.

Potpis

Na posao sam uvek stizao sa mučninom ali živ. Što pokazuje da mi je Šuman bliži od Šostakoviča!

Re: SQL Mozgalica za Juni 2007 07.08.2007. u 22:49 · pre 234 meseca #27

E, ja se ne slažem!
Mi ne rasporedjujemo ljude u sobe nego rešavamo interesantne probleme, kako god oni bili formulisani.
Ovo nije tema o izradi hotelske aplikacije nego tema za rešavanje SQL mozgalice.

Nemojte pogrešno da me shvatite - ja takođe mislim da treba biti maksimalno racionalan i praktičan, ali ovo definitivno nije mesto za tako nešto. Ovde pokušavamo da pokrijemo čitavu kategoriju problema koja može da se javi u ovom ili onom obliku u svakodnevnom radu i potrebno je da taj problem razumemo i prepoznamo kao nešto što je neko ovde uspešno rešio.

Drugarski pozdrav!

Potpis

Nisam ni znao da znam dok nisam prob'o!

Re: SQL Mozgalica za Juni 2007 08.08.2007. u 10:05 · pre 234 meseca #28

Slazem se sa CandyMan-om i zamolio bih sve sadasnje i buduce ucesnike da se drze 'mozgalice' tj problema a ne opravdanosti i realne potrebe istog.

Potpis

:: Nemoj se svadjati sa budalom, ljudi cesto nece primjetiti razliku ::

Forum kao aplikacija na telefonu

Srodne teme