Mozgalice, novi ciklus, druga najveca plata, isto to smao malo drugacije

[ Zidar @ 14.11.2011. 15:15 ] @

Ovo u stvari i nije nova mozgalica, nego komentar ponudjenih resenja za mozgalicu http://www.elitesecurity.org/t...ovi-ciklus-druga-najveca-plata.
Imali smo tri kategorije muzicara i trazili smo one muzicare koji imaju drugu najvecu platu u svojoj kategoriji. U nekim kategorijama bilo je vise od jednog muzicara koji imaju drugu najvecu platu. U svim kategorijama, imali smo tacno jednog muzicara sa najvecom platom. Sada sam to malo promenio, tako da u nekim kategorijama imamo vise od jednog muzicara sa najvecom platom.

Dodao sam dva reda u tabelu #Zaposleni i na moje iznennadjenje, pola uspesnih resenja vise ne radi.

Ovo mi je slucajno palo na pamet, nisam imao nameru da se sluzim trikovima i navedem nikoga na 'pogresna' resenja. Sva resenja su bila dobra, za ponudjeni primer i i dalje ostaju dobra - za ponudjeni primer. Medjutim, neka od njih ne bi prezivela dugo u praksi, jer smo svi precutno i nesvesno svi napravili istu predpostavku - da ce podaci u praksi uvek imati tacno jednog muzicara sa najvecom platom. Sa nekim resenjima imali smo srece a sa nekim nismo, iako sva izgledaju podjednako dobra za zadate uslove.

Evo nove tabele sa novim ulaznim podacima:

Code:

IF Object_ID('tempdb..#Zaposleni') IS NOT NULL DROP TABLE #Zaposleni
;
CREATE TABLE #Zaposleni (
    RadnikID INT IDENTITY,
    Radnik VARCHAR(15),
    RadnaJedinica VARCHAR(15),
    Plata NUMERIC(16,2)
)

INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('M Ilic','Narodnjaci', 40000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('D Zivkovic','Narodnjaci', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('T Zdravkovic','Narodnjaci', 20000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('P Gojkovic','Narodnjaci', 15000)
;

INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('G Bregovic','Rokeri', 80000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Dz Stulic','Rokeri', 40000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('R M Tocak','Rokeri', 50000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('B Djordjevic','Rokeri', 50000)
;

INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('D Zubovic','Klasicna', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('J JOvanovic','Klasicna', 18000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('R Bakocevic','Klasicna', 15000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('O Njego','Klasicna', 10000)
;
-- Ovo smo dodali, sada kategorije 'Klasicna' i 'Rokeri' imaju po dva muzicara na vrhu liste
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Z Saramandic','Klasicna', 25000)
INSERT INTO #Zaposleni(Radnik, RadnaJedinica, Plata)
VALUES('Dz Stulic','Rokeri', 80000)
;

-- Ulazni podaci:
SELECT * FROM #Zaposleni
ORDER BY RadnaJedinica, Plata DESC
;
/* Ulazni podaci:
RadnikID    Radnik          RadnaJedinica   Plata
----------- --------------- --------------- ---------------------------------------
9           D Zubovic       Klasicna        25000.00
13          Z Saramandic    Klasicna        25000.00
10          J JOvanovic     Klasicna        18000.00
11          R Bakocevic     Klasicna        15000.00
12          O Njego         Klasicna        10000.00
1           M Ilic          Narodnjaci      40000.00
2           D Zivkovic      Narodnjaci      25000.00
3           T Zdravkovic    Narodnjaci      20000.00
4           P Gojkovic      Narodnjaci      15000.00
5           G Bregovic      Rokeri          80000.00
14          Dz Stulic       Rokeri          80000.00
7           R M Tocak       Rokeri          50000.00
8           B Djordjevic    Rokeri          50000.00
6           Dz Stulic       Rokeri          40000.00

(14 row(s) affected)
*/
;

Ocekivani rezultat osto je isti:

Code:

-- Ocekivani rezultat:
/*
RadnaJedinica   Radnik          Plata
--------------- --------------- ---------------------------------------
Klasicna        J JOvanovic     18000.00
Narodnjaci      D Zivkovic      25000.00
Rokeri          B Djordjevic    50000.00
Rokeri          R M Tocak       50000.00

(4 row(s) affected)
*/

Testirao sam rezultat prve mozgalice i dobio ovo:
1. prvo resenje od mmix, bez rank funkcija, radi ispravno

Code:

-- MMIX 1, formatirano:
SELECT   RadnaJedinica, Radnik,
         Plata
FROM     #Zaposleni
         INNER JOIN
                  (SELECT  RadnaJedinica AS RJ2, MAX(Plata) AS DrugaPlata
                  FROM     #Zaposleni
                           INNER JOIN
                                    (SELECT  RadnaJedinica AS RJ, MAX(Plata) AS MaxPlata
                                    FROM     #Zaposleni
                                    GROUP BY RadnaJedinica
                                    )
                                    AS TMaxPlata
                           ON       #Zaposleni.RadnaJedinica = TMaxPlata.RJ
                  WHERE    Plata                             < MaxPlata
                  GROUP BY RadnaJedinica
                  )
                  AS TSecPlata
         ON       #Zaposleni.RadnaJedinica = TSecPlata.RJ2
WHERE    Plata                             = DrugaPlata
ORDER BY RadnaJedinica, Radnik;

RadnaJedinica   Radnik          Plata
--------------- --------------- ---------------------------------------
Klasicna        J JOvanovic     18000.00
Narodnjaci      D Zivkovic      25000.00
Rokeri          B Djordjevic    50000.00
Rokeri          R M Tocak       50000.00

(4 row(s) affected)

Drugo resenje od mmix, nazalost ne radi vise:

Code:

-- MMIX 2, formatirano:
SELECT   RadnaJedinica,
         Radnik       ,
         Plata
FROM     #Zaposleni
         INNER JOIN
                  (SELECT  RadnaJedinica  AS RJ       ,
                           Plata          AS rangPlata,
                           ROW_NUMBER() over (partition BY RadnaJedinica
                                                ORDER BY Plata DESC) AS pindex
                  FROM     #Zaposleni
                  )
                  AS TRPlate
         ON       #Zaposleni.RadnaJedinica = TRPlate.RJ
         AND      #Zaposleni.Plata         = TRPlate.rangPlata
WHERE    pindex                            = 2
ORDER BY RadnaJedinica,
         Radnik;

RadnaJedinica   Radnik          Plata
--------------- --------------- ---------------------------------------
Klasicna        D Zubovic       25000.00
Klasicna        Z Saramandic    25000.00
Narodnjaci      D Zivkovic      25000.00
Rokeri          Dz Stulic       80000.00
Rokeri          G Bregovic      80000.00

(5 row(s) affected)

Resenje koje je dao Vujkev je u sustini isto kao mmix drugo resenje, samo je upotrebljena WITH sintaksa umesto in-line SELECT, i naravno daje isti pogresan rezultat

Code:

--Vujkev, kao MMIX 2, upotreba WITH umesto in-line SQL, neformatirano, as-is
with e as (
select RadnaJedinica
     , Plata
     , row_number() over (partition by RadnaJedinica order by Plata desc) rb
     from #Zaposleni
)
select z.RadnaJedinica, z.Radnik, z.Plata  from e
inner join #Zaposleni z on z.Plata = e.Plata and z.RadnaJedinica = e.RadnaJedinica
     where rb = 2
     order by z.radnajedinica, z.radnik
     ;
RadnaJedinica   Radnik          Plata
--------------- --------------- ---------------------------------------
Klasicna        D Zubovic       25000.00
Klasicna        Z Saramandic    25000.00
Narodnjaci      D Zivkovic      25000.00
Rokeri          Dz Stulic       80000.00
Rokeri          G Bregovic      80000.00

(5 row(s) affected)

mmix resenje za RANK(), ono najbrze, promasilo je sve slucajeve gde imamo vise od jednog muzicara na vrhu:

Code:

-- MMIx: resenje sa RANK()
WITH TRangirano AS
     (
     SELECT   RadnaJedinica
            , Radnik
            , Plata
            , RANK() over (partition BY RadnaJedinica ORDER BY Plata DESC) AS redPlate
     FROM     #Zaposleni
     )
SELECT   RadnaJedinica
       , Radnik
       , Plata
FROM     TRangirano
WHERE    redPlate = 2
ORDER BY RadnaJedinica
       , Radnik;

RadnaJedinica   Radnik          Plata
--------------- --------------- ---------------------------------------
Narodnjaci      D Zivkovic      25000.00

(1 row(s) affected)

Markovo resenje, ono koje je radilo u prvoj verziji mozgalice, nije proslo.

Code:

--- Najnovije Markovo resenje: (radilo u prvoj verziji pitalice)
select * from
(
select * from #Zaposleni where plata in
(select  distinct(plata) from #Zaposleni where
( select  count(*) from #Zaposleni as t where #Zaposleni.RadnaJedinica=t.RadnaJedinica and t.plata>#Zaposleni.plata)=1)
) p
where
(
select count(*) from
(
select * from #Zaposleni where plata in
(select  distinct(plata) from #Zaposleni where
( select  count(*) from #Zaposleni as t where #Zaposleni.RadnaJedinica=t.RadnaJedinica and t.plata>#Zaposleni.plata)=1)
) p1 where p.RadnaJedinica=p1.RadnaJedinica and p.plata>p1.plata
)=0
;
RadnikID    Radnik          RadnaJedinica   Plata
----------- --------------- --------------- ---------------------------------------
2           D Zivkovic      Narodnjaci      25000.00
9           D Zubovic       Klasicna        25000.00
13          Z Saramandic    Klasicna        25000.00

(3 row(s) affected)

ZIdarevo resenje a DENSE_RANK je proslo, ali sasvim slucajno, ja jednostavno nisam umeo da napsiem resenje sa RANK pa sam napsiao sa DENSE_RANK.

Code:

-- Zidar:
;
WITH RankByRadnaJedinica AS
     ( SELECT DISTINCT
               RadnaJedinica ,
               Plata ,
               PlataOrder = dense_rank() OVER (PARTITION BY RadnaJedinica ORDER BY Plata DESC)
        FROM  #Zaposleni
     )
SELECT   E.RadnikID     ,
         E.Radnik       ,
         E.RadnaJedinica,
         E.Plata
FROM     #Zaposleni               AS E
         JOIN RankByRadnaJedinica AS R
         ON       R.RadnaJedinica = E.RadnaJedinica
         AND      R.Plata         = E.Plata
WHERE    R.PlataOrder             = 2
ORDER BY RadnaJedinica,
         E.Radnik ;

RadnikID    Radnik          RadnaJedinica   Plata
----------- --------------- --------------- ---------------------------------------
10          J JOvanovic     Klasicna        18000.00
2           D Zivkovic      Narodnjaci      25000.00
8           B Djordjevic    Rokeri          50000.00
7           R M Tocak       Rokeri          50000.00

(4 row(s) affected)

Verovatno se resenja koja nisu prosla mogu naterati da prorade, ali to nije poenta. Poenta je da je dobro postavljeno pitanje pola resenja. Inicijalno pitanje je bilo postavljeno lose. Ulazni podaci su (nenamerno) napravljeni tako da mogu da prodju i prividno tacna resenja. Ovo samo potvrdjuje satru istinu - testirati, testirati i samo testirati, za sto vise slucajeva. Da je na pocetku dat bolji skup podataka za primer, i resenja bi bila bolja.