Srodne teme
Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

SQL Mozgalica za Juni 2007

[es] :: Baze podataka :: SQL Mozgalica za Juni 2007

Strane: 1 2

[ Pregleda: 3867 | Odgovora: 27 ]

 Postavi temu Odgovori

Autor
Pretraga teme: Traži
Markiranje Štampanje RSS

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon SQL Mozgalica za Juni 200722.06.2007. u 17:53

Imam prijatelja koji radi u turistickoj agenciji koja organizuje naucne seminare. Ucesnici seminara zovu se Guests, gosti. On ima spisak ucesnika u taneli Guests (Ime, pol, predmet koji predaje). Treba da napravi kveri koji ucesnike uparuje dva po dva, da bi ih smestili u dvokrevetne sobe. Dok su koristili Excel, to je bilo lako: utvrdis polovinu spiska i onda donju polovinu CUT pa Paste pored gornje polovine. E, onda su presli na Access i pojavio se problem. Kako napraviti parove?

Evo tabela i test podaci (koriscen MS SQL):

Code:

CREATE TABLE dbo.Guests
(GuestName varchar(25) PRIMARY KEY
, Gender varchar(1)
, Topic varchar(25)
)

INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Pera','M','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Laza','M','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('MIka','M','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Zika','M','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Goran','M','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Janko','M','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Milan','M','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Djura','M','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Zoran','M','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Krle','M','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Cica','F','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Maca','F','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Jeca','F','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Veca','F','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Zoca','F','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Mica','F','Muzika')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Jaca','F','Istorija')
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Juca','F','Istorija')


Za pocetak, trazi se da se samo upare. U tets primeru ima 19 gostiju. Treba da dobijemo listu od 2x9=18 parova i za jednog rasparenog da napiseme 'SINGLE'. Nesto ovako:

Prvi Drugi
-----------
Pera Laza
Mika Zika
Goran janko
Milan Goran
Cica Mica
Jeca Goca
Djura SINGLE <----- Djura nema s kim da deli sobu

Nije vazno ko je skim i kom ostaje sam.

Posle cemo da prosirimo zadatak pa da kazemo da u istu sobu mgu samo dva muskarca ili dve zene. Ali ovo tek posto uspemo da ih uparimo bez ikakvih uslova.

Na kraju cemo da kazemo da u istoj sobi mogu biti samo dve osobe istog pola, ali da ne predaju isti Topic. Dve osobe koje predaju Istoriju ne smeju da budu zajedno u sobi. Ovo tek posto uspemo da uparimo zene sa zenama i muskarce sa muskarcima.

Sve je dozvoljeno, kursori nisu pozeljni.

Napred.

:-)





[Ovu poruku je menjao misk0 dana 22.06.2007. u 21:15 GMT+1]
22.06.2007. u 17:53 

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon Re: Mozgalica za Juni 200722.06.2007. u 18:42
Ispostavilo se da zadatk nije nimalo trivijalan :-)
Sad vidim kako je Excel mocna alatka za neke slucajeve :-)

Evo ovako, za najprostiji slucaj, da se samo upare dva po dva, bez ikakvih ogranicenja. Ovako sam to nekako resio, korak po korak:

-- zadatak: napraviti parove, 2 po dva, da bi se smestili u dvokrevetne sobe.
-- ako ima neko nesparen, da se napise 'SINGLE'
-- Za pocetak, dozvoljavamo da Muski (M) i zene (F) budu u istoj sobi

-- Da im dodelimo nekakav redosled
Code:

SELECT 
GuestName
, 1 + (SELECT COUNT(*) 
        FROM Guests AS B 
        WHERE A.GuestName > B.GuestName)  AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A

-- dobijemo nesto ovako:
GuestName Redosled UkupanBroj
Cica 1 19
Djura 2 19
Goran 3 19
Jaca 4 19
Janko 5 19
Jeca 6 19


-- da napisemo levu polovinu spiska:
Code:

SELECT 
    GuestName
    , Redosled
    , UkupanBroj
 FROM
(
SELECT 
GuestName
, 1 + (SELECT COUNT(*) 
        FROM Guests AS B 
        WHERE A.GuestName > B.GuestName)  AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS A
WHERE Redosled <= UkupanBroj/2


-- dobijemo ovo:
GuestName Redosled UkupanBroj
Cica 1 19
Djura 2 19
Goran 3 19
Jaca 4 19
Janko 5 19
Jeca 6 19
Juca 7 19
Krle 8 19
Laza 9 19

-- da pokusamo da dodamo desnu polovinu:
Code:

SELECT 
        A.GuestName AS Prvi
        , A.Redosled AS RedosledPrvog
        , A.UkupanBroj
        , A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
        
     FROM
    (
        SELECT 
        GuestName
        , 1 + (SELECT COUNT(*) 
                FROM Guests AS B 
                WHERE A.GuestName > B.GuestName)  AS Redosled
        , (SELECT COUNT(*) FROM Guests) AS UkupanBroj
        FROM Guests AS A
    ) AS A
    WHERE Redosled <= UkupanBroj/2

-- i dobijemo ovo:
Prvi RedosledPrvog UkupanBroj RedosledDrugog
Cica 1 19 10
Djura 2 19 11
Goran 3 19 12
Jaca 4 19 13
Janko 5 19 14
Jeca 6 19 15
Juca 7 19 16
Krle 8 19 17
Laza 9 19 18

-- Vidimo koji redni broj odgovara kom rednom broju
-- Ali, nama trebau imena :-(

-- mozda ovako:
Code:

SELECT U.Prvi, U.RedosledPrvog, W.GuestName AS Drugi, U.RedosledDrugog
FROM 
-- U = ceo izraz koji smo imali kad smo 
-- dodali redne brojeve za desnu polovinu 
(
    SELECT 
        A.GuestName AS Prvi
        , A.Redosled AS RedosledPrvog
        , A.UkupanBroj
        , A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
        
     FROM
    (
        SELECT 
        GuestName
        , 1 + (SELECT COUNT(*) 
                FROM Guests AS B 
                WHERE A.GuestName > B.GuestName)  AS Redosled
        , (SELECT COUNT(*) FROM Guests) AS UkupanBroj
        FROM Guests AS A
    ) AS A
    WHERE Redosled <= UkupanBroj/2
) AS U
JOIN
-- W = izraz koji smo upotrebili za oderdjivanje redosleda
(

    SELECT 
    GuestName
    , 1 + (SELECT COUNT(*) 
            FROM Guests AS B 
            WHERE A.GuestName > B.GuestName)  AS Redosled
    , (SELECT COUNT(*) FROM Guests) AS UkupanBroj
    FROM Guests AS A

) AS W
ON U.RedosledDrugog = W.Redosled

-- dobili smo ovo:
Prvi RedosledPrvog Drugi RedosledDrugog
Cica 1 Maca 10
Djura 2 Melanija 11
Goran 3 Mica 12
Jaca 4 MIka 13
Janko 5 Milan 14
Jeca 6 Pera 15
Juca 7 Veca 16
Krle 8 Zika 17
Laza 9 Zoca 18

-- Ostaje da dodamo osobu sa redosledom 19
-- Mozda UNION sa ovim:
Code:

SELECT
    GuestName
    , Redosled AS RedosledPrvog
    , 'SINGLE' AS drugi
    , NULL AS RedosledDrugog
FROM
(
SELECT 
GuestName
, 1 + (SELECT COUNT(*) 
        FROM Guests AS B 
        WHERE A.GuestName > B.GuestName)  AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj

-- UNION je OK, ali samo ako je UkupanBroj neparan
-- pa treba da uvedemo taj uslov u igru:
Code:

SELECT
    GuestName
    , Redosled AS RedosledPrvog
    , 'SINGLE' AS drugi
    , NULL AS RedosledDrugog
FROM
(
SELECT 
GuestName
, 1 + (SELECT COUNT(*) 
        FROM Guests AS B 
        WHERE A.GuestName > B.GuestName)  AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj
AND UkupanBroj % 2 = 1

-- matematicki UkupanBroj MOD 2 = 1
--, moj SQL koristi % za MOD

-- sto nam daje konacno resenje:
Code:

SELECT U.Prvi, U.RedosledPrvog, W.GuestName AS Drugi, U.RedosledDrugog
FROM 
-- U = ceo izraz koji smo imali kad smo 
-- dodali redne brojeve za desnu polovinu 
(
    SELECT 
        A.GuestName AS Prvi
        , A.Redosled AS RedosledPrvog
        , A.UkupanBroj
        , A.Redosled + A.UkupanBroj/2 AS RedosledDrugog
        
     FROM
    (
        SELECT 
        GuestName
        , 1 + (SELECT COUNT(*) 
                FROM Guests AS B 
                WHERE A.GuestName > B.GuestName)  AS Redosled
        , (SELECT COUNT(*) FROM Guests) AS UkupanBroj
        FROM Guests AS A
    ) AS A
    WHERE Redosled <= UkupanBroj/2
) AS U
JOIN
-- W = izraz koji smo upotrebili za oderdjivanje redosleda
(

    SELECT 
    GuestName
    , 1 + (SELECT COUNT(*) 
            FROM Guests AS B 
            WHERE A.GuestName > B.GuestName)  AS Redosled
    , (SELECT COUNT(*) FROM Guests) AS UkupanBroj
    FROM Guests AS A

) AS W
ON U.RedosledDrugog = W.Redosled
UNION
SELECT
    GuestName
    , Redosled AS RedosledPrvog
    , 'SINGLE' AS drugi
    , NULL AS RedosledDrugog
FROM
(
SELECT 
GuestName
, 1 + (SELECT COUNT(*) 
        FROM Guests AS B 
        WHERE A.GuestName > B.GuestName)  AS Redosled
, (SELECT COUNT(*) FROM Guests) AS UkupanBroj
FROM Guests AS A
) AS X
WHERE X.Redosled = X.UkupanBroj
AND UkupanBroj % 2 = 1

Ako dodate jos jednu osobu u tabelu, nestace ovaj deo sa SINGLE i NULL.

Da dodamo jos jednog
INSERT INTO Guests (GuestName,gender,Topic) VALUES ('Ludwig','F','Muzika')

Jednostavno, zar ne :-)

I ovo je tek najjednostavniji slucaj. Nisam siguran da li umem da resim exstenzije zadatka. Probacu posle vikenda.
A dotle mozda neko i resi ceo problem.

;-)
22.06.2007. u 18:42 

_owl_
Centar - BG

Član broj: 318
Poruke: 989
*.dynamic.sbb.co.yu.

Sajt: home.drenik.net/~owl


Profil

icon Re: SQL Mozgalica za Juni 200722.06.2007. u 23:17
Ala si ga ti zakomplikovao. Resenje za pocetnu postavku problema u Oracle-u bi moglo da se napise kao
Code:

CREATE TABLE GuestPair (
GuestName1 varchar(25),
GuestName2 varchar(25),
constraint GuestName1FK foreign key (GuestName1) references Guests(GuestName),
constraint GuestName2FK foreign key (GuestName1) references Guests(GuestName)
)

INSERT INTO GuestPair SELECT  t1.GuestName as gn1, t2.GuestName as gn2 
FROM (SELECT g1.GuestName, g1.rb1 FROM (SELECT  ROWNUM as rb1, GuestName FROM Guests WHERE ROWNUM<=(SELECT CEIL(COUNT(*)/2) FROM Guests)) g1) t1 LEFT OUTER JOIN (SELECT  (rb-(SELECT CEIL(COUNT(*)/2) FROM Guests)) as rb2, GuestName FROM (SELECT ROWNUM as rb, GuestName FROM Guests) WHERE rb>(SELECT CEIL(COUNT(*)/2) FROM Guests)) t2 
ON t1.rb1=t2.rb2


A lista parova bi se dobijala sa
Code:

SELECT * FROM GuestPair
Owl
22.06.2007. u 23:17 

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


Profil

icon Re: SQL Mozgalica za Juni 200723.06.2007. u 10:00
@Zidar: U navodjenju test podataka si zaboravio da das podatke o Melaniji.

Ubacio sam Melaniju u test podatke, ali mi fali njeno zanimanje.


Ja cu opet podeliti podatke iz jedne kolone u dve kolone metodom koju sam nedavno demonstrirao u dve teme (plus Mozgalica 7):
Da li je ovaj upit uopšte moguć?
SQL pomoc.Hitno!

Koristicu ovaj metod dok vam ne dosadim :)

1. KORAK - Uvodjenje poretka

Mora se uvesi neki poredak u skup gostiju. Zidar se opredelio za abecedni poredak po imenu, a i meni je najlogicniji izbor. To se radi sa INNER JOIN-ovanjem tabele sa samo sobom:
Code:

SELECT COUNT(g1.guestname) AS ord_num,
       g1.guestname
  FROM guests AS g1
       INNER JOIN
       guests AS g2
         ON g1.guestname >= g2.guestname
 GROUP BY g1.guestname
Ovaj upit vraca:
Code:

ord_num   guestname
-------   ---------
      1   Cica
      2   Djura
      3   Goran
      ...
     19   Zoran

2. KORAK - Podela jedne kolone na dve - prvi pokusaj

Podelicu neparne goste u jednu kolonu, a parne u drugu. Za ovo cu iskoristiti deljenje po modulu 2, tj gledacu samo ostatak pri celobrojnom deljenju rednog broja sa 2. Neparne goste cu dobiti sa jednim SELECT-om, a parne sa drugim, a onda cu ova dva SELECT-a spojiti sa UNION ALL (smem da iskoristim ALL jer se parni i neparni gosti uzajamno iskljucuju.
Code:

-- neparni gosti
SELECT COUNT(g1.guestname) AS ord_num,
       g1.guestname AS first_guest,
       NULL AS second_guest
  FROM guests AS g1
       INNER JOIN
       guests AS g2
         ON g1.guestname >= g2.guestname
 GROUP BY g1.guestname
HAVING COUNT(g1.guestname) % 2 = 1

 UNION ALL
 
-- parni gosti
SELECT COUNT(g1.guestname) AS ord_num,
       NULL AS first_guest,
       g1.guestname AS second_guest
  FROM guests AS g1
       INNER JOIN
       guests AS g2
         ON g1.guestname >= g2.guestname
 GROUP BY g1.guestname
HAVING COUNT(g1.guestname) % 2 = 0
(NAPOMENA: U PostgreSQL-u se za ostatak pri deljenje koristi znak %)
Posle ovoga dobijam
Code:

ord_num   first_guest   second_guest
-------   -----------   ------------
      1   Cica          NULL
      3   Goran         NULL
      ...
     19   Zoran         NULL
      2   NULL          Djura
      4   NULL          Jaca
      ...
     18   NULL          Zoca

3. KORAK - Podela jedne kolone na dve - drugi pokusaj

Iako sam u predhodnom koraku dobio ono sto sam hteo, nisam zadovoljan. Spajanje tabele same sa sobom uz prebrojavanje je zahtevna operacija, a ja sam je odradio dva puta! Da li isti rezultat mogu da dobijem bez dvostrukog spajanja tabele same sa sobom? Mogu, ako umesto UNION ALL upotrebim CASE izraz u kojeg cu preneti uslove iz HAVING klauzule.
Code:

SELECT COUNT(g1.guestname) AS ord_num,
       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.guestname >= g2.guestname
 GROUP BY g1.guestname
Ovaj upit daje isti rezultat kao i upit iz drugog koraka, ali je jeftiniji za izvrsavanje. Iz tog razloga cu se opredeliti za njega.

4. KORAK - Rasporedjivanje gostiju po sobama

U gornji upitu cu umesto rednog broja gosta ubaciti redni broj sobe u koju smestam gosta. Broj sobe dobijam celobrojnim deljenjem rednog broja gosta sa 2.
Code:

SELECT (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.guestname >= g2.guestname
 GROUP BY g1.guestname
Dobijam
Code:

room_number   first_guest   second_guest
-----------   -----------   ------------
          1   Cica          NULL
          1   NULL          Djura
          2   Goran         NULL
          2   NULL          Jaca
          ...
          9   Zika          NULL
          9   NULL          Zoca
         10   Zoran         NULL
(NAPOMENA: U PostgreSQL-u se za celobrojno deljenje koristi znak /)

5. KORAK - Izbacivanje nepotrebnih NULL-ova i formiranje parova

Dobio sam gomilu nepotrebnih NULL-ova, a parovi su mi formirani po brojevima soba, ali se ne nalaze u istom redu. Ovo resavam sa grupisanjem dobijenog rezultata po broju sobe i upotrebom MAX funkcije za izbacivanje NULL-ova.
Code:

SELECT pairs.room_number,
       MAX(pairs.first_guest) AS first_guest,
       MAX(pairs.second_guest) AS second_guest
  FROM (SELECT (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.guestname >= g2.guestname
         GROUP BY g1.guestname
        ) AS pairs
 GROUP BY pairs.room_number
 ORDER BY pairs.room_number
(NAPOMENA: Moguce je da se na nekim sistemima umesto funkcije MAX mora koristit funkcija MIN.)


Svi prikazani SQL upiti su testirani na PostgreSQL 8.2 sistemu za upravljanje bazama podataka.
"The best code is no code at all." - Zidar
23.06.2007. u 10:00 

Mr. Rejn
Deki Karamatijević
Srbija.Niš.MedianaCity

Član broj: 2514
Poruke: 389
*.adsl-1.sezampro.yu.



Profil

icon Re: SQL Mozgalica za Juni 200723.06.2007. u 19:17
Ja sam pokušao nešto u Firebirdu,stavio dve for petlje u PSQL,dodao sam
id-ove gostima (prvo muškima,pa ženama),tako da tabela izgleda ovako:
Code:

ID      IME_GOSTA                 POL    TEMA                      
======= ========================= ====== ========================= 
      1 Zoran                     M      Istorija                  
      2 Djura                     M      Muzika                    
      3 Goran                     M      Muzika                    
      4 Milan                     M      Muzika                    
      5 Janko                     M      Muzika                    
      6 Laza                      M      Istorija                  
      7 Krle                      M      Istorija                  
      8 Pera                      M      Istorija                  
      9 MIka                      M      Istorija                  
     10 Zika                      M      Muzika                    
     11 Jeca                      F      Muzika                    
     12 Mica                      F      Muzika                    
     13 Juca                      F      Istorija                  
     14 Jaca                      F      Istorija                  
     15 Veca                      F      Muzika                    
     16 Maca                      F      Istorija                  
     17 Zoca                      F      Muzika                    
     18 Cica                      F      Istorija                  


Grupisanje muškaraca po parovima (neparni,pa parni ID-ovi):
Code:

execute block
returns (Prva varchar(25),Druga varchar(25))
as
begin
for
 select  gosti.ime_gosta  from gosti where    mod(gosti.id,2)=0 and   gosti.pol='M'  into :Prva
 do
  for
  select   gosti.ime_gosta   from gosti where    mod(gosti.id,2)=1 and  gosti.pol='M'  into :Druga
  do
  suspend;
 suspend;
end

ali ovo daje:

itd. (nastavlja se)
Možda neko ima ideju kako otkloniti ova ponavljanja.

[Ovu poruku je menjao Mr. Rejn dana 23.06.2007. u 22:16 GMT+1]
"I prefer the term:artificial person..."-Bishop the android,Alien 2
Prikačeni fajlovi
exec_bl.jpg exec_bl.jpg - 15.32k
23.06.2007. u 19:17 

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon Re: SQL Mozgalica za Juni 200725.06.2007. u 14:31
Super, imamo 2.5 resenja za pocetnu pretpostavku.

Chachkino mise teorijski najvise svidja, stosta sam naucio. Slobodno ponavljaj 'metod', dobar je a treba vise puta proci kroz to da bi se potpuno razumelo sta se radi. Barem meni, moj mozak je malo ostareo pa mi treba vise puta ponoviti

Medjutim, u praksi bih i sam verovatno radio nesto kao _OWL_, verovatno bih upotrebio temp tabele i stored proceduru koja vraca trazeni rekordset. Problem sa permanentnom tabelom koja se puni kodom je sto ako neko prmeni podatke posle punjenja tabele, moramo da menjamo podatke u GuestPair. Radije bih stored procedure, pa GuestPair postane temp tabela #GuestPair (postoji u memoriji samo dok se procedura izvrsava). Kako god se promene podaci u polaznoj tabeli, Guests, stored procedure ce uvek vratiti sta treba. To sve naravno mzoe do nekog momenta kad kazemo 'dosta, sad svi mars u sobe'. Tako da se opet vracamo na resenje koje je _OWL_ ponudio, pitanje je samo momenat kad cemo odraditi taj kod.

Ako primenimo dodatne permanentne ili temp tabele, i ostatak zadatka je lak. Medjutim, da li je moguce pronaci resenje koje ne zahteva punjenje dodatnih tabela, to jest bazira se na SQL kverijima, ma kako kompleksni bili? Svrha mozgalica je da nas natera da malo razmisljamo i da razmenimo ideje. Potpuno je OK ponuditi i najprakticnije resenje, ali da su ljudi gledali samo prakticna resenja, matematika bi se i dalje sastojala od cetiri racunske radnje i nesto malo elementarne geometrije.

Za Mr. Rejn - moraces sam da pronadjes resenje, ili da ti pomogne neko ko koristi isti sistem. I ja sam bio poceo sa kombinacijom 'svako sa svakim' pa nisam mogao da nadjem nacin da odbacim sta mi ne treba. Uoci da smo i _OWL_ i Chachka i ja u startu podelili grupu na dve polovine. Oni su delili na parne i neparne, ja na 'gornju' i 'donju' polovinu, pa smo onda uparivali srodne redne brojeve. Malo matematike ovde pomaze. Medjutim, nigde ne pise da je resenje sa podelom u startu jedino ispravno i najbolje. Mnogo problema se moze svesti na ovo sto si ti uradio - iskombinuj sve pa odbaci sta ne treba. Ako nesto iskopas u tom pravcu, bicemo zahvalni da naucim nesto novo.
25.06.2007. u 14:31 

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


Profil

icon Re: SQL Mozgalica za Juni 200725.06.2007. u 21:04
Drugi deo mozgalice - razdeliti muskarce i zene po sobama, nije neki problem. Iskoristicu postojeci upit i izvrsiti grupisanje po polu.
Code:

SELECT pairs.gender,
       pairs.room_number,
       MAX(pairs.first_guest) AS first_guest,
       MAX(pairs.second_guest) AS second_guest
  FROM (SELECT g1.gender,
               (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.guestname >= g2.guestname
         GROUP BY g1.gender, g1.guestname
        ) AS pairs
 GROUP BY pairs.gender, pairs.room_number
 ORDER BY pairs.gender, pairs.room_number
"The best code is no code at all." - Zidar
25.06.2007. u 21:04 

mm2000

Član broj: 149978
Poruke: 5
*.bredband.comhem.se.



Profil

icon Re: SQL Mozgalica za Juni 200726.06.2007. u 17:31
Uradjeno u MySQL-u.... ali slicno (OUTER JOIN) se moze primeniti i u MS SQL-u

Code:

SELECT G1.GuestName, CASE WHEN G2.GuestName IS NULL THEN 'SINGLE' ELSE G2.GuestName END, G1.Redosled + 1 AS BrojSobe
FROM 
(SELECT GuestName, (SELECT COUNT(*)
                    FROM Guests AS B
                    WHERE A.GuestName > B.GuestName) AS Redosled
 FROM Guests AS A
 HAVING (SELECT COUNT(*)
         FROM Guests AS B
         WHERE A.GuestName > B.GuestName) < (SELECT COUNT(*) FROM Guests) / 2
) G1
LEFT OUTER JOIN
(SELECT GuestName, (SELECT COUNT(*)
                          FROM Guests AS B
                          WHERE A.GuestName > B.GuestName) - ((SELECT COUNT(*) FROM Guests) DIV 2) AS Redosled
 FROM Guests AS A
 HAVING (SELECT COUNT(*)
         FROM Guests AS B
         WHERE A.GuestName > B.GuestName) >= (SELECT COUNT(*) FROM Guests) / 2
) G2
ON G1.Redosled = G2.Redosled


[Ovu poruku je menjao mm2000 dana 26.06.2007. u 22:18 GMT+1]

[Ovu poruku je menjao mm2000 dana 26.06.2007. u 22:26 GMT+1]
26.06.2007. u 17:31 

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


Profil

icon Re: SQL Mozgalica za Juni 200726.06.2007. u 18:27
Upit od mm2000 je proradio i na PostgreSQL-u, nakon sto sam dodao dva GROUP BY. Upit radi za paran broj gostiju, ali ne radi kada je broj gostiju neparan :(
"The best code is no code at all." - Zidar
26.06.2007. u 18:27 

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


Profil

icon Re: SQL Mozgalica za Juni 200727.06.2007. u 05:42
Mene je mozgalica naterala na mozganje :)

Ovde su demonstrirana dva nacina za prebrojavanje redova.
1. nacin:
Code:

SELECT a.neka_kolona, 1 + (SELECT COUNT(b.*)
                             FROM neka_tabela AS b
                            WHERE a.neka_kolona > b.neka_kolona)
  FROM neka_tabela AS a

2.nacin
Code:

SELECT a.neka_kolona, COUNT(b.*)
  FROM neka_tabela AS a
       INNER JOIN
       neka_tabela AS b
         ON a.neka_kolona >= b.neka_kolona
 GROUP BY a.neka_kolona


Upit kojeg je poslao mm2000 i pored njegove naknadne intervencije, i dalje ne radi sa neparnim brojem gostiju. Ovo me je nateralo da na osnovu njegove ideje ja formiram slican upit:
Code:

SELECT f.guestname,
       s.guestname,
       f.room_number
  FROM (SELECT a.guestname, (SELECT COUNT(*)
                               FROM guests AS b
                              WHERE a.guestname > b.guestname
                            ) AS room_number
          FROM guests AS a
         GROUP BY a.guestname
        HAVING (SELECT COUNT(*)
                  FROM guests AS b
                 WHERE a.guestname > b.guestname) < (SELECT COUNT(*) FROM guests) / 2
                                                  + (SELECT COUNT(*) FROM guests) % 2
       ) AS f
       LEFT OUTER JOIN
       (SELECT a.guestname, (SELECT COUNT(*)
                               FROM guests AS b
                              WHERE a.guestname > b.guestname) - (SELECT COUNT(*) FROM guests) / 2
                                                               - (SELECT COUNT(*) FROM guests) % 2 AS room_number
          FROM guests AS a
         GROUP BY a.guestname
        HAVING (SELECT COUNT(*)
                  FROM guests AS b
                 WHERE a.guestname > b.guestname) >= (SELECT COUNT(*) FROM guests) / 2
                                                  +  (SELECT COUNT(*) FROM guests) % 2
       ) AS s
         ON f.room_number = s.room_number
 ORDER BY f.room_number

(Nakon konstruisanog upita mi se cini da mm2000 ne uspeva zato sto ne koristi MOD 2 za identifikaciju slucajeva sa parnim i neparnim brojem gostiju.)
Ovde mi se nije svidela kompleksnost HAVING klauzula i racunanje broja sobe u drugom podupitu. Pokusao sam to da izmenim.
Promenio sam:
1. Nacin prebrojavanja redova,
2. Izvukao sam HAVING klauzule van podupita,
3. Izvukao sam racunanje broja sobe drugog podupita van podupita,
i dosao sam do sledeceg upita:
Code:

SELECT f.guestname,
       s.guestname,
       f.ord_num
  FROM (SELECT COUNT(g1.guestname) AS ord_num,
               g1.guestname
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.guestname >= g2.guestname
         GROUP BY g1.guestname
       ) AS f
       LEFT OUTER JOIN
       (SELECT COUNT(g1.guestname) AS ord_num,
               g1.guestname
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.guestname >= g2.guestname
         GROUP BY g1.guestname
       ) AS s
         ON f.ord_num = s.ord_num  -  (SELECT COUNT(*) FROM guests) / 2  -  (SELECT COUNT(*) FROM guests) % 2
 WHERE f.ord_num <= (SELECT COUNT(*) FROM guests) / 2  +  (SELECT COUNT(*) FROM guests) % 2
 ORDER BY f.ord_num

Gornja dva upita listu gostiju seku na gornju i donju polovinu.
Dalje sam modifikovao drugi upit i ubacio sam podelu gostiju na parne i neparne.
Code:

SELECT f.guestname,
       s.guestname,
       f.ord_num / 2 + f.ord_num % 2 
  FROM (SELECT COUNT(g1.guestname) AS ord_num,
               g1.guestname
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.guestname >= g2.guestname
         GROUP BY g1.guestname
       ) AS f
       LEFT OUTER JOIN
       (SELECT COUNT(g1.guestname) AS ord_num,
               g1.guestname
          FROM guests AS g1
               INNER JOIN
               guests AS g2
                 ON g1.guestname >= g2.guestname
         GROUP BY g1.guestname
       ) AS s
       ON f.ord_num + f.ord_num % 2 = s.ord_num
 WHERE f.ord_num % 2 = 1
 ORDER BY f.ord_num

S obzirom da Oracle ima ROWNUM ovaj upit se za Oracle moze napisati:
Code:

SELECT f.guestname,
       s.guestname,
       CEIL(f.ord_num / 2) + MOD(f.ord_num, 2) - 1
  FROM (SELECT rownum AS ord_num, guestname FROM guests) f
       LEFT OUTER JOIN
       (SELECT rownum AS ord_num, guestname FROM guests) s
         ON f.ord_num + MOD(f.ord_num, 2) = s.ord_num
 WHERE MOD(f.ord_num, 2) = 1
 ORDER BY f.ord_num
Prilicno jednostavan Oracle upit :)

Svi ovi upiti resavaju pocetni problem, bez dodatnih zavrzlama oko pola i teme.

Poslednji upit je testiran na Oracle 10g XE, dok su ostali upiti testirani na PostgreSQL 8.2
"The best code is no code at all." - Zidar
27.06.2007. u 05:42 

broker

Član broj: 2415
Poruke: 8521
212.62.59.*



Profil

icon Re: SQL Mozgalica za Juni 200727.06.2007. u 07:01
Ubacicu samo jedan komentar, mislim da je problem pogresno postavljen. Naime u realnom svetu, sumnjam da ovakva formula ima smisla. Postoji dovoljno razloga da se automatizam preskoci i uparivanje vrsi rucno: recimo zato sto gosti vec imaju zelju sa kim bi delili sobu. Mnogo je efikasnije napraviti resenje koje je manje automatizovano, a samim tim fleksibilnije i jednostavnije. Cak i da se uradi automatizovano uparivanje, mora postojati mehanizam da se to radi i rucno.

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.
27.06.2007. u 07:01 

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net


Profil

icon Re: SQL Mozgalica za Juni 200728.06.2007. u 10:59
Citat:
Zidar: Ispostavilo se da zadatk nije nimalo trivijalan :-)

U postavci zadatka nije receno, ali se podrazumeva: Zauzece soba mora biti minimalno!

Sa ovim uslovom zadatak upada u domen operacionih istrazivanja. Metode resavanja zadataka operacionih istrazivanja su po prirodi iterativne. SQL nije iterativan, te se on ne moze ni koristiti za resavanje zadatka. Zadatak se moze resiti upotrebom FOR petlji, kurzora, privremenih promenjljivih, ali i dalje zahteva poznavanje operacionih istrazivanja.

Prva dva slucaja:
1. prosto grupisanje,
2. grupisanje zene sa zenama, muskarci sa muskarcima
su specijalni slucajevi optimizacije iskoriscavanja minimalnog broja soba. Za ove slucajeve, kako je vec pokazano, se moze upotrebiti SQL.

SQL se moze upotrebiti i za slucaj: Grupisi muskarce sa muskarcima, zene sa zenama i tematski iste osobe. Ovo je opet specijalan slucaj. I ovde dominira rec grupisanje (GROUP BY) te se moze resiti sa:
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

Ono sto je Zidar naveo kao treci deo mozgalice nije grupisanje, nego 'razgrupisanje' (Da li postoji bolja rec? :), te stoga mora da se primeni iterativni postupak u resavanju. Posto nisam strucnjak za operaciona istrazivanja treci deo zadatka necu resavati.

Nadam se da ce neko ipak prikazati resenje:)
"The best code is no code at all." - Zidar
28.06.2007. u 10:59 

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon Re: SQL Mozgalica za Juni 200728.06.2007. u 14:13
Srdjan je ponovo u pravu. Treci deo zadatka, 'razgrupisavanje' je jedini deo koji sam izmislio, i verovatno ne moze da prodje. SQL moze da resi zene sa zenama, matemamaticarke sa matematicarkama, visestrukim grupisanjem. Nisam imao vremena da probam drugi deo ni treci, pa neka ostane ovako za sada.

Za Gorana: U pravu si, u praksi je verovatno brze resiti problem nekim drugim nacinom. Problem koji sam postavio jeste iz prakse, osim treceg dela. Mi zaista imamo definitivan skup gostiju, nece dolaziti novi, neke su zene neki su muskarci, ne poznaju se i svejedno im je s kim ce deliti sobu. Mogli smo to da ostavimo recepcionaru da resava, ali smo odabrali (ili nam je neko naredio da resimo problem unapred. Oko 300 gostiju ce se pojaviti u roku od dva sata na prijavnici hotela, pa je efikasnije da se im se samo predaju kljucevi soba. Dobro kazes da za neke druge okolnosti bi neko drugo resenja bilo bolje, na nivou front enda ili cak na nivou poslovnog procesa. Svrha mog pitanja nije da resimo zadatak mom drugaru, on je to resio u Excelu cut/paste/move i gotovo, nego da vidimo sat sev SQL moze a sta ne moze. Kroz ponudjena resenja videli smo subqueries, dodeljivanje redosleda, parne i neparne brojeve, LEFT JOIN, UNION, MOD i CEILING funkcije, i najvaznije, videli smo nacin razmisljanja. 90% ljudi koje se bave bazama to sve ne koriste, ovo ih mozda malo podastakne da izadju iz dnevne rutine. Mozgalice su kao neko teoretisanje, cista nauka, koja moze u nekim slucajevima da se primeni i u praksi. U svakom slucaju, ponudimo i razmenimo ideje. Ako bi uvek mislili samo o praksi, matematika bi i danas imala samo cetiri oblasti: sabiranje, oduzimanje, mnozenj i deljenje.

Mozgalica nije zavrsena, videli smo samo sta je Srdjan uradio. Ja cu da probam da dobijem isti rezultat, ako moze, mojim nacinom, kao i da prevedem Srdjanovo resenje u Access. Nazalost, ne danas, mozda iduce nedelje.

Hvala svima na komentarima, ja sam mnogo naucio iz ove diskusije. Kao sto rekoh, tema nije zakljucana
28.06.2007. u 14:13 

Ana Komazec
Beograd

Član broj: 76433
Poruke: 22
*.adsl-2.sezampro.yu.

ICQ: 274954884


Profil

icon Re: SQL Mozgalica za Juni 200701.07.2007. u 11:35
Za Access, ukoliko je dozvoljeno da ubacimo redni broj u tabelu, nezavisno od pola i predmeta, parove mozemo dobiti na sledeci nacin:

Code:

SELECT t1.ord_num, t2.ord_num, t1.ime AS gost1, t2.ime AS gost2
FROM tabela1 AS t1, tabela1 AS t2
WHERE ( t1.ime in (select t1.ime from tabela1 where (t1.ord_num)mod(2)<>0) and t2.ime in (select t2.ime from tabela1 where (t2.ord_num)mod(2)=0)) and  ( t1.ord_num=(t2.ord_num-1));


U ovom slucaju, parovi su 1.i 2., 3.i4. itd.

Samca dobijamo kao osobu koja za redni broj ima najveci, a ujedno i neparni broj:

Code:

SELECT ime
FROM tabela1
WHERE ((ord_num)mod(2)<>0) and ord_num in (select max(ord_num) from tabela1);


Pozdrav
01.07.2007. u 11:35 

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon Re: SQL Mozgalica za Juni 200703.07.2007. u 14:00
Ana, mozes li da nam zakacis Access bazu sa primerom i podacima? (ZIP ne moze RAR)
03.07.2007. u 14:00 

Ana Komazec
Beograd

Član broj: 76433
Poruke: 22
*.adsl-1.sezampro.yu.

ICQ: 274954884


Profil

icon Re: SQL Mozgalica za Juni 200703.07.2007. u 18:13
Evo baze

Pozdrav
Prikačeni fajlovi
db1.zip db1.zip - 48.86k
03.07.2007. u 18:13 

Zidar
Canada

Član broj: 15387
Poruke: 1692
*.100.46-69.q9.net.



Profil

icon Re: SQL Mozgalica za Juni 200703.07.2007. u 18:40
Hvala, Ana :-)

Dodao sam nekoliko redova u tvoju tabelu tako da ima 11 m i 9 z. Oops, parovi su izmesani po polu, Rade i Isidora su u istoj sobi. Rade nema nista protiv, verovatno ni Isidora, ali Isidorina mama se ne slaze :-)

Sad imamo dva samca, samca i samicu, a kveri ne vraca ni jedno od njih dvoje.

Razlog je proizvoljno numerisanje. Da bi kveri 'pravljenjeparova' radio ispravno, mora biti numeracija 1 do K za z i onda od 1 do N za m. Moguce je napisati front end koji to obezbedjuje, ali to ne bi bilo SQL resenje. Zato su svi resavaci do sada na neki nacin kverijem (subkverijem) dodeljivali redosled za oba pola ponaosob, da bi redosled garantovano bio kakav treba. Zasto kevri 'samac' ne vraca nista, pa verovatno iz istog razloga, nisam proveravao, a mozad je i nesto trece u pitanju.

Pokusaj da dodelis redosled subkverijem, pa takav kveri vedi u 'pravljenjparove' i 'samac', trebalo bi bar nesto da proradi.

I na kraju ih nekako stavi zajedno, parove i samce, bilo sa UNION bilo sa LEFT/RIGHT JOIN ili nesto trece. trazili smo da vidimo listu 'ko je skim u sobi' plus samci, ako ih ima. Nismo trazili dve liste. ;-)


Dobro si krenula, samo nastavi i proradice.

:-)
03.07.2007. u 18:40 

CandyMan

Član broj: 3420
Poruke: 83