SQL:Norimas Elementu Kiekis Grupuojant Pagal Atributa
Kadangi laikas nuo laiko kam nors prireikia tiesiai duomenų bazėje išgauti tik tam tikrą kiekį elementų turinčių vienodą atributą ir ne visiem tai pavyksta suprogramuoti tai pateikiu trumpą pavyzdėlį kokia logika vadovaujantis reikėtų spręsti šį lengvą uždavinuką.
Pirma kartą susidūrus su tokiu uždaviniu, sekant SQL'o logiką, kylą noras iš karto grupuoti duomenis pagal atributą ir kažkokiu tai būdų grupėse atvaizduoti tik ribotą kiekį elementų. Šiuo atveju tai nėra teisingas sprendimas, nes tokio funkcionalumo nėra. Vadinasi reikia ieškoti kitokio kelio ;)
Tas kitas kelias jau reikalauja truputį detalesnio paaiškinimo.
Tarkime turime tokią duomenų schemą:
CREATE TABLE `test` (
`elementas` int(10) unsigned NOT NULL auto_increment,
`atributas` varchar(255) NOT NULL,
PRIMARY KEY (`elementas`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-----------+-----------+
| elementas | atributas |
+-----------+-----------+
| 1 | zalias |
| 2 | zalias |
| 3 | zalias |
| 4 | raudonas |
| 5 | raudonas |
| 6 | melynas |
| 7 | zalias |
| 8 | raudonas |
| 9 | melynas |
| 10 | zalias |
| 11 | melynas |
| 12 | melynas |
+-----------+-----------+Ir uždavinys būtų toks: reikia išgauti tik po du elementus turinčius vienodus atributus.
Žodinis šio uždavinio sprendimas būtų toks:
- reikalingas skaitliukas, kuris skaičiuotų/numeruotų elementus su vienodu atributu - tai būtų kaip papildomas stulpelis, rodantis kelintas tai elementas turintis tą patį atributą;
- baigiantis užklausai nufiltruoti įrašus, kurių skaitliuko reikšmė daugiau už 2;
Paprasta ;) Taigi gaminam užklausą:
SET @counter := 0, @attribute := '';
SELECT elementas,
atributas,
IF(atributas = @attribute, @counter := @counter + 1, @counter := 0) AS counter,
@attribute := atributas AS tmp_attribute
FROM test
+-----------+-----------+---------+---------------+
| elementas | atributas | counter | tmp_attribute |
+-----------+-----------+---------+---------------+
| 1 | zalias | 0 | zalias |
| 2 | zalias | 1 | zalias |
| 3 | zalias | 2 | zalias |
| 4 | raudonas | 0 | raudonas |
| 5 | raudonas | 1 | raudonas |
| 6 | melynas | 0 | melynas |
| 7 | zalias | 0 | zalias |
| 8 | raudonas | 0 | raudonas |
| 9 | melynas | 0 | melynas |
| 10 | zalias | 0 | zalias |
| 11 | melynas | 0 | melynas |
| 12 | melynas | 1 | melynas |
+-----------+-----------+---------+---------------+
</source/
Matome, kad viskas veikia truputi neteisingai, nes įrašai yra nesurūšiuoti pagal atributą. Kuriam toliau:
<source lang=sql>
SET @counter := 0, @attribute := '';
SELECT elementas,
atributas,
IF(atributas = @attribute, @counter := @counter + 1, @counter := 0) AS counter,
@attribute := atributas AS tmp_attribute
FROM (SELECT elementas,
atributas
FROM test
ORDER BY atributas
)tmp_test
+-----------+-----------+---------+---------------+
| elementas | atributas | counter | tmp_attribute |
+-----------+-----------+---------+---------------+
| 12 | melynas | 0 | melynas |
| 11 | melynas | 1 | melynas |
| 6 | melynas | 2 | melynas |
| 9 | melynas | 3 | melynas |
| 4 | raudonas | 0 | raudonas |
| 5 | raudonas | 1 | raudonas |
| 8 | raudonas | 2 | raudonas |
| 10 | zalias | 0 | zalias |
| 7 | zalias | 1 | zalias |
| 3 | zalias | 2 | zalias |
| 2 | zalias | 3 | zalias |
| 1 | zalias | 4 | zalias |
+-----------+-----------+---------+---------------+Jau geriau ;) Belieka nufiltruoti rezultatus:
SET @counter := 0, @attribute := '';
SELECT elementas,
atributas,
IF(atributas = @attribute, @counter := @counter + 1, @counter := 0) AS counter,
@attribute := atributas AS tmp_attribute
FROM (SELECT elementas,
atributas
FROM test
ORDER BY atributas
)tmp_test
HAVING counter < 2
+-----------+-----------+---------+---------------+
| elementas | atributas | counter | tmp_attribute |
+-----------+-----------+---------+---------------+
| 12 | melynas | 0 | melynas |
| 11 | melynas | 2 | melynas |
| 4 | raudonas | 0 | raudonas |
| 5 | raudonas | 2 | raudonas |
| 10 | zalias | 0 | zalias |
| 7 | zalias | 2 | zalias |
+-----------+-----------+---------+---------------+Hmm... kažkas negerai ;)
Pasitikrinam dokumentacijoje/kituose šaltiniuose ir surandam, kad kintamųjų reikšmės šiuo atveju yra paskaičiuojamos netgi vėliau negu suveikia HAVING sąlyga. Todėl viską keliame dar į atskirą užklausą:
SET @counter := 0, @attribute := '';
SELECT elementas,
atributas,
counter
FROM (SELECT elementas,
atributas,
IF(atributas = @attribute, @counter := @counter + 1, @counter := 0) AS counter,
@attribute := atributas AS tmp_attribute
FROM (SELECT elementas,
atributas
FROM test
ORDER BY atributas
)tmp_test
)tmp2_test
HAVING counter < 2
+-----------+-----------+---------+
| elementas | atributas | counter |
+-----------+-----------+---------+
| 12 | melynas | 0 |
| 11 | melynas | 1 |
| 4 | raudonas | 0 |
| 5 | raudonas | 1 |
| 10 | zalias | 0 |
| 7 | zalias | 1 |
+-----------+-----------+---------+Štai ir rezultatas ;)
Bet dar ne pavyždžio pabaiga. Nes dabar realiai vykdomos dvi užklausos: pirma nustato pradinės kintamųjų @counter ir @attribute reikšmes, o antra viską suskaičiuoja. Kartais gali kilti noras viską atlikti per vieną užklausą. Žvilgterėjus į užklausas galima sugalvoti, kad pradines reikšmes galima nustatyti vidinėje užklausoje, kuri vykdoma pati pirmoji (ta, kurioje surūšiojami duomenys pagal atributą).
SELECT elementas,
atributas,
counter
FROM (SELECT elementas,
atributas,
IF(atributas = @attribute, @counter := @counter + 1, @counter := 0) AS counter,
@attribute := atributas AS tmp_attribute
FROM (SELECT elementas,
atributas,
@counter := 0,
@attribute := ''
FROM test
ORDER BY atributas
)tmp_test
)tmp2_test
HAVING counter < 2
+-----------+-----------+---------+
| elementas | atributas | counter |
+-----------+-----------+---------+
| 12 | melynas | 0 |
| 11 | melynas | 1 |
| 4 | raudonas | 0 |
| 5 | raudonas | 1 |
| 10 | zalias | 0 |
| 7 | zalias | 1 |
+-----------+-----------+---------+Na štai ir viskas ;)
Dar pridėsiu keletą komentarų, kurie tinka prie šito uždavinio:
- Reikia atkreipti dėmesį, kad reikšmės priskyrimas kintamajam yra atliekamas naudojant ":=" operatorių, o ne su paprasta lygybę;
- Pradiniu reikšmių nustatymas kartais nėra būtinas, bet labai rekomenduoju jas nustatinėti, nes atliekant tokias užklausas iš eilės galima gauti labai netiketų ir neteisingų rezultatų;
- Paskutinėje užklausoje tikriausiai galima naudoti ir WHERE sąlygą, tokiu būdu "counter" strulpelio reikšmės galima ir nepateikinėti, jį išmetus iš "SELECT" sąrašo;
- Šis sprendimas yra nelabai efektyvus, nes skaitliukas yra kuriamas dinamiškai ir IF sąlyga pritaikoma kiekvienai eilutei, daug efektyviau yra turėti tokį pastovų stulpelį, kuriame būtų saugomas elemento numeris vienodų atributų kontekste, tada visa užklausa būtų DAUG trumpesnė ir efektyvesnė; Kitaip sakant pavyzdys yra pritaikomas tais atvejais, kai negalime keisti jau turimos duomenų struktūros, kuri nėra pritaikyta spręsti tokį konkretų uždavinį;
- Pateiktas pavyzdys veiks tik nuo MySQL 4.1.x versijos, nes yra naudojamos vidinės užklausos (subquery);
- Kaip veikia skaitliukas:
SET @skaitliukas := 0;
SELECT *,
@skaitliukas := @skaitliukas +1 AS skaitliukas
FROM lentele;