(Nie)Eleganckie zapytanie tablicowe., Prośba o pomoc w sformułowaniu zapytania… Opcje

[Morfina]

Witam,
Mam problem w odpytaniu Bazy danych w sposób „elegancki”, już tłumaczę o co chodzi.
Dane:
(IMG:http://imageshack.com/a/img827/9602/ncvm.jpg)

Zadanie:
Wyszukaj pracowników zarabiających najmniej na danym etacie.

Rozwiązanie, którego bym oczekiwał i uważam za eleganckie powinno mieć postać:

SELECT nazwisko FROM pracownicy
WHERE ( Etat , Placa_pod ) IN (SELECT Etat, MIN(Placa_pod) FROM pracownicy GROUP BY etat);

Niestety nie działa ono na MS SQL.

Rozwiązanie, które działa ale jest wg. mnie gorsze (nieeleganckie) ma postać:

SELECT dbo.Pracownicy.Nazwisko, dbo.Pracownicy.Imie
FROM dbo.Pracownicy INNER JOIN
(SELECT MIN(Placa_Pod) AS Min, Etat
FROM dbo.Pracownicy AS Pracownicy_1
GROUP BY Etat) AS P_1 ON dbo.Pracownicy.Placa_Pod = P_1.Min AND dbo.Pracownicy.Etat = P_1.Etat

Czy któryś z szanownych forumowiczów ma pomysł na rozwiązanie bez użycia JOIN ?

Z góry dziękuję Morf.



Witam ponownie,
po nocnych przemyśleniach:

SELECT Nazwisko, Imie
FROM dbo.Pracownicy AS T1
WHERE (Placa_Pod =
(SELECT MIN(Placa_Pod) AS Low
FROM dbo.Pracownicy
WHERE (Etat = T1.Etat)))

Poziom elegancji 7 / 10 i dodatkowy punkcik za bark JOIN czyli 8 /10 :-)

w zasadzie jest OK chyba, że...

Pozdrawiam Morf

[pmir13]

Z odpowiednimi indeksami rozwiązanie klasyczne jest wystarczająco szybkie dla stosunkowo niedużej bazy danych, a zapewne taka jest baza pracowników, bo nawet biorąc pod uwagę wszystkich zatrudnionych na naszych uczelniach pracowników mielibyśmy ilość rekordów rzędu 100k, a to jest liczba, którą może obsłużyć testowy serwer postawiony na leciwym laptopie. Oczywiście zakładając prawidłowe zaindeksowanie, bo bez indeksów nawet przy tej niewielkiej ilości danych serwer może się zmęczyć.

Dopiero gdy dane idą w miliony trzeba myśleć o rozwiązaniach dedykowanych dla silnika i wykorzystujących specyfikę rozkładu danych, mówiąc wcześniej o takim rozwiązaniu dla sql servera z wykorzystaniem CTE i CROSS APPLY chodziło mi o coś w tym stylu:

[SQL] pobierz, plaintext 
WITH etaty AS
  (  
    SELECT MIN(etat) AS etat
    FROM pracownicy
    UNION ALL
    SELECT eo.etat
    FROM
    ( 
      SELECT p.etat, ROW_NUMBER() OVER (ORDER BY p.etat) AS nr_etatu
      FROM etaty e 
      JOIN pracownicy p 
      ON p.etat > e.etat
      WHERE e.etat IS NOT NULL
    ) eo
    WHERE eo.nr_etatu = 1
  )
SELECT pd.*
FROM etaty e
CROSS APPLY
  (
    SELECT TOP 1 pr.*
    FROM pracownicy pr
    WHERE pr.etat = e.etat
    ORDER BY placa_pod, id
  ) pd
OPTION (MAXRECURSION 0)
[SQL] pobierz, plaintext 

Czyli wykorzystanie rekurencji CTE do szybkiego znalezienia z indeksu wszystkich różnych etatów a następnie CROSS APPLY by w zależnym podzapytaniu móc umieścić własne TOP i ORDER BY, dzięki czemu dostajemy dla każdego etatu wykorzystując ten sam indeks najniższą płacę, co przy dość zaawansowanym optymalizatorze sql servera pozwala uniknąć pełnego skanu indeksu (a skan całej tabeli to w ogóle byłby horror), a zamiast tego szukamy tylko tyle ile mamy różnych etatów ( czyli w miarę ograniczona, można nawet założyć stała ilość - nawet O(log(N)) jest przecież dużo gorsze niż O(1)).

Polecam do testowania na nieco większej bazie, dla ułatwienia:

[SQL] pobierz, plaintext 
CREATE TABLE pracownicy (
  id INT NOT NULL PRIMARY KEY,
  nazwisko VARCHAR(64),
  imie VARCHAR(64),
  etat VARCHAR(16),
  placa_pod DECIMAL(20,2)
)
GO
 
CREATE INDEX etat_placa_id ON pracownicy( etat, placa_pod, id );
GO
 
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1+FLOOR(RAND(1))
WHILE @i < 10000000
BEGIN
  INSERT INTO pracownicy( id, nazwisko, imie, etat, placa_pod )
  VALUES(
     @i,
     'Nazwisko_'+CAST(@i AS VARCHAR(9)),
     'Imie_'+CAST(@i AS VARCHAR(9)),
     'Etat_'+CAST(FLOOR(RAND()*100)+1 AS VARCHAR(3)),
     (FLOOR(RAND()*300000+100000))/100
     )
  SET @i = @i+1;
END
COMMIT
[SQL] pobierz, plaintext 

Mamy tutaj 10M losowych rekordów dla 100 różnych etatów i prawidłowy indeks, z trzecim członem dla ewentualnego rozstrzygania remisów w płacach wg najniższego id. Samo tworzenie tabeli zajmuje kilka-kilkanaście minut na starym kompie, ale zapytanie wciąż pozostaje w obszarze milisekund. Dla porównania klasyczne rozwiązanie powinno trwać kilka sekund.