Atlasīt pirmos N (2, 3, 5, 10, 20, 100 utt.) ierakstus ir diezgan izplatīta prasība. Atlasīt N lielākos, N mazākos, N jaunākos utt. Angliski to parasti sauc kā Top N analysis vai Top N Query. Savukārt (parasti webiskās aplikācijās) mēdz būt vēl viena diezgan tipiska prasība – lapošana (pagination) – ierakstu atlase pa porcijām. Šīs ir tās lietas, kur datu bāzu vadības sistēmu atšķirības spīd visā savā spožumā. Tikai relatīvi pēdējā laikā izmantojot analītiskās funkcijas vismaz dažās DBVS var mēģināt izlīdzēties ar vienu un to pašu SQL teikumu pirmo N ierakstu atlasē un lapošanā. Kā jau vienmēr esmu mēģinājis uzsvērt, tas gan nebūtu jāuzskata par milzīgu trūkumu, jo arī, iekāpjot cita ražotāja automašīnā, Jūs negaidat, ka ātruma pārslēgs slēgsies precīzi tāpat ka iepriekšējā. Truli laužot to pierastajā pozīcijā, Jūs varat iegūt tikai salauztu mašīnu un ar datubāzēm, protams, ir līdzīgi. Tiktāl ievads, bet tagad pēc kārtas par Oracle, MySQL, SQL Server un analītisko funkciju universālo risinājumu.
Datiem izmantosim to pašu tabuliņu, kas rakstā par indeksu pielietošanu. Atgādinu, ka tabulas izveides skripts un dati bija šādi:
CREATE TABLE pilsetas ( id INTEGER PRIMARY KEY, pilsetas_nosaukums varchar(40), iedz_skaits INTEGER, apgabals varchar(40) ); INSERT INTO pilsetas VALUES (1, 'Rīga', 719600, 'Rīga'); INSERT INTO pilsetas VALUES (2, 'Kuldīga', 13000, 'Kurzeme'); INSERT INTO pilsetas VALUES (3, 'Talsi', 11400, 'Kurzeme'); INSERT INTO pilsetas VALUES (4, 'Ventspils', 43400, 'Kurzeme'); INSERT INTO pilsetas VALUES (5, 'Rēzekne', 35900, 'Latgale'); INSERT INTO pilsetas VALUES (6, 'Daugavpils', 106100, 'Latgale'); INSERT INTO pilsetas VALUES (7, 'Ogre', 26800, 'Vidzeme'); INSERT INTO pilsetas VALUES (8, 'Valmiera', 27500, 'Vidzeme'); INSERT INTO pilsetas VALUES (9, 'Cēsis', 18200, 'Vidzeme'); INSERT INTO pilsetas VALUES (10, 'Krāslava', 10500, 'Latgale'); INSERT INTO pilsetas VALUES (11, 'Bauska', 10200, 'Zemgale'); INSERT INTO pilsetas VALUES (12, 'Liepāja', 85300, 'Kurzeme'); COMMIT;
Oracle
Lai veiktu TOP N pieprasījumus Oraclē, tiek izmantota pseidokolona rownum. Rownum ir skaitlis, kas sākas no 1 un tiek palielināts par 1 katram nākošajam atlasītajam ierakstam, kurš atbilst pieprasījuma WHERE nosacījumiem. Svarīgi tas – ka to palielina ierakstiem, kas apmierina WHERE nosacījumus, pirms vēl ir veiktas jebkādas citas darbības, piemēram, ierakstu kārtošana. Līdz ar to nākošais piemērs saturiski nozīmē atlasīt jebkurus 3 ierakstus, kas pirmie pagadās, un tad tos sakārtot pēc iedzīvotāju skaita dilstošā secībā. Ievērojam, ka tas nav tas pats kā atlasīt 3 pilsētas ar vislielāko iedzīvotāju skaitu!
Piemērs 1. Atlasa 3 jebkurus ierakstus un tos attēlo sakārtotus pēc iedzīvotāju skaita.
SELECT * FROM pilsetas WHERE rownum <= 3 ORDER BY iedz_skaits DESC; ID PILSETAS_NOSAUKUMS IEDZ_SKAITS APGABALS -- -------------------- ----------- -------- 1 Rīga 719600 Rīga 2 Kuldīga 13000 Kurzeme 3 Talsi 11400 Kurzeme
Tātad, lai patiesi atlasītu 3 pirmos ierakstus, ir nepieciešams lietot viens no apakšvaicājumu veidiem – vispirms apakšvaicājumā jāatlasa ieraksti vajadzīgajā sakārtojumā un pēc tam virsvaicājumā jāatlasa pirmie 3.
Piemērs 2. Atlasa 3 ierakstus ar vislielāko iedzīvotāju skaitu.
SELECT * FROM ( SELECT * FROM pilsetas ORDER BY iedz_skaits DESC) WHERE rownum <= 3; ID PILSETAS_NOSAUKUMS IEDZ_SKAITS APGABALS -- ------------------- ----------- -------- 1 Rīga 719600 Rīga 6 Daugavpils 106100 Latgale 12 Liepāja 85300 Kurzeme
Savukārt, ja mūsu mērķis ir atlasīt ierakstus no n līdz m, kas ir tipiska prasība lapošanā (pagination), tad atliek uzlikt vēl vienu virslīmeni vaicājumam un otrajā līmenī pielasīt arī pseidokolonu. Piemērā n = 4 un m = 6.
Piemērs 3. Atlasa 4to līdz 6to ierakstu ar vislielāko iedzīvotāju skaitu.
SELECT * FROM ( SELECT s1.*, rownum rn FROM ( SELECT * FROM pilsetas ORDER BY iedz_skaits DESC) s1 WHERE rownum <= 6 ) WHERE rn >= 4; ID PILSETAS_NOSAUKUMS IEDZ_SKAITS APGABALS RN ----- ------------------- ----------- --------- -- 4 Ventspils 43400 Kurzeme 4 5 Rezekne 35900 Latgale 5 8 Valmiera 27500 Vidzeme 6
Daži vārdi par TOP N vaicājumu ātrdarbību.
- Jāatceras, ka vairumā gadījumu, lai atrastu lielākos/mazākos/jaunākos/vecākos N, ir jāatrod arī visi pārējie ieraksti. Visu ierakstu atrašana var būt resursietilpīgs process. Tāpēc vienmēr ir vērts pārdomāt, vai, piemēram, meklēšanā ir jāatrod visi ieraksti, kas atbilst noteiktajiem kritērijiem, un no tiem jāatrod pirmie N. Otra iespēja, kas var prasīt ievērojami mazāk resursus, ir atlasīt vienkārši N ierakstus, kas atbilst kritērijiem, un tad sakārtot tos pirms attēlošanas.
- Sakārtot pilnībā visu (lielo) ierakstu kopu ir krietni resursietilpīgāks process, nekā atrast no visas ieralstu kopas pirmos N. Tāpēc Oraclei ir jāsniedz informācija par to, cik ierakstus jūs vēlaties atrast. Tādējādi var samazināt gan nepieciešamo atmiņas apjomu, gan izpildes laiku.
MySQL
MySQL šo problēmu ir atrisinājis krietni vienkāršāk. Order by klauza kopā ar LIMIT klauzu, kurā vienkāršākajā gadījumā raksta vēlamo ierakstu skaitu, dod nepieciešamo rezultātu.
Piemērs 4. Atlasa 3 ierakstus ar vislielāko iedzīvotāju skaitu.
mysql> SELECT * FROM pilsetas -> ORDER BY iedz_skaits DESC -> LIMIT 3; +----+--------------------+-------------+----------+ | id | pilsetas_nosaukums | iedz_skaits | apgabals | +----+--------------------+-------------+----------+ | 1 | Riga | 719600 | Riga | | 6 | Daugavpils | 106100 | Latgale | | 12 | Liepaja | 85300 | Kurzeme | +----+--------------------+-------------+----------+
Savukārt, ja nepieciešama lapošana, tas ir jāatrod, nevis pirmie trīs, bet nākošie trīs ieraksti, tad LIMIT klauzā jānorāda no kura ieraksta sākt un cik ierakstus ņemt. Tātad, ja mēs gribam otro trijnieku, tad jāsāk ir ar ceturto ierakstu un jāņem 3 ieraksti. Tiesa gan jāatceras, ka numurēšana sākas no nulles, tāpēc, ceturtā ieraksta numurs būs 3. Galu galā vaicājums izskatās šādi:
Piemērs 5. Atlasa 4to līdz 6to ierakstu ar vislielāko iedzīvotāju skaitu.
mysql> SELECT * FROM pilsetas -> ORDER BY iedz_skaits DESC -> LIMIT 3, 3; +----+--------------------+-------------+----------+ | id | pilsetas_nosaukums | iedz_skaits | apgabals | +----+--------------------+-------------+----------+ | 4 | Ventspils | 43400 | Kurzeme | | 5 | Rezekne | 35900 | Latgale | | 8 | Valmiera | 27500 | Vidzeme | +----+--------------------+-------------+----------+
Protams nevajadzētu aizmirst, ka, lai iegūtu pirmos N ierakstus arī MySQLā, kārtošanas problēma nekur nav pazudusi. Ieraksti ir jākārto tik un tā. Tikai, kā jau parasti to var darīt dažādos veidos. Var kārtot visus ierakstus vaicājuma izpildes laikā, var ierakstus sakārtot pirms tam izveidojot indeksu uz kārtošanas kolonu. Ja datu un vaicājumu daudzums pārsniedz spēlēšanās robežu, tad bez indeksa(-iem) iztikt būs ļoti nepatīkami. Sīkāk par Order by un Limit klauzas veiktspējas uzlabošanas iespējām var lasīt MySQL Performance Blogā ORDER BY … LIMIT Performance Optimization.
SQL Server
SQL Serverī pirmo N ierakstu atlase vistiešāk atbilst angliskajam TOP ļoti vienkārša iemesla dēļ – TOP klauza arī tam tiek lietota.
Piemērs 6. Atlasa 3 ierakstus ar vislielāko iedzīvotāju skaitu.
SELECT TOP 3 * FROM pilsetas ORDER BY iedz_skaits DESC id pilsetas_nosaukums iedz_skaits apgabals ------------------------------------------- 1 Rīga 719600 Rīga 6 Daugavpils 106100 Latgale 12 Liepāja 85300 Kurzeme
Diemžēl TOP ir tikai viens arguments un tādējādi var atlasīt tikai N pirmos ierakstus. Taču tas jau nav šķērslis, vai ne? Izmantojot apakšvaicājumus šo pasākumu var kombinēt vairākas reizes galu galā iegūstot vajadzīgo. Vispirms atlasam sešus pirmos iekšējā vaicājumā, tad trīs pirmos kārtojot pretējā kārtībā, tad apgriežam attēlošanai pareizajā virzienā ar ārējo Order by klauzu.
Piemērs 7. Atlasa 4to līdz 6to ierakstu ar vislielāko iedzīvotāju skaitu.
SELECT * FROM ( SELECT TOP 3 * FROM ( SELECT TOP 6 * FROM pilsetas ORDER BY iedz_skaits DESC ) AS q0 ORDER BY iedz_skaits ASC ) as q1 ORDER BY iedz_skaits DESC id pilsetas_nosaukums iedz_skaits apgabals ---------------------------------------- 4 Ventspils 43400 Kurzeme 5 Rēzekne 35900 Latgale 8 Valmiera 27500 Vidzeme
Pirmo N ierakstu atlase un analītiskās funkcijas
Ja nu tomēr esat nolēmuši spiest uz universāliem risinājumiem, kas derēs visām bāzēm, kas tik parādīsies pie apvāršņa, tad jāskatās analītisko funkciju virzienā. Funkcija row_number, kas vienkārši piešķir unikālus skaitļus sākot no 1, šai gadījumā lieti noder. Nākošais piemērs strādā gan uz Oracles, gan SQL servera bez izmaiņām un universālajiem kareivjiem, atvainojiet universālā SQL koda meklētājiem, tas ir tikpat kā svētais grāls.
Piemērs 8. Atlasa 3 ierakstus ar vislielāko iedzīvotāju skaitu, strādā gan uz Oracle, gan SQL Server.
SELECT * FROM ( SELECT p.*, row_number() OVER (ORDER BY iedz_skaits DESC) AS rn FROM pilsetas p ) q WHERE rn <=3; id pilsetas_nosaukums iedz_skaits apgabals rn --------------------------------------------- 1 Rīga 719600 Rīga 1 6 Daugavpils 106100 Latgale 2 12 Liepāja 85300 Kurzeme 3
To pašu bez īpašiem sarežģījumiem var izveidot arī lapošanai.
Piemērs 9. Atlasa 4to līdz 6to ierakstu ar vislielāko iedzīvotāju skaitu, strādā gan uz Oracle, gan SQL Server.
SELECT * FROM ( SELECT p.*, row_number() OVER (ORDER BY iedz_skaits DESC) AS rn FROM pilsetas p ) q WHERE rn >=4 AND rn <= 6; id pilsetas_nosaukums iedz_skaits apgabals rn --------------------------------------------- 4 Ventspils 43400 Kurzeme 4 5 Rēzekne 35900 Latgale 5 8 Valmiera 27500 Vidzeme 6
Diemžēl ar to universālumu līdz galam tā ir kā ir, kā jau Jūs droši vien ziniet MySQLā analītiskās funkcijas nav un nekas nav arī zināms, kad un vai tās būs.
Strādājot ar MSSQL man vienmēr ir gribējies kaut ko analogu MySqla LIMITam, ietverot arī ofsetu. Vispār es brīnos, ka tas nav SQL standartā, tā taču tāda elementāra un plaši izmantota lieta – nu nevar būt, ka SQL standarta izstrādes laikā par to neiedomājās; drīzāk bija kāds stulbs iemesls, kāpēc principā neiekļāva valodā. Droši vien nosprieda, ka visiem ļoti patiks darboties ar kursoriem.
Nevajag meklēt universālu SQL. Japieturās maksimāli tuvu optimizācijai.
Diemžēl lielākā daļa OS projektu ir balstītas uz universālumu un klibo ar perfomanci.
Spilgts piemērs jau ir Google meklētājs – visu tik zibenīgi atrod pilnīgi pofig pēc kā, visos savos katalogos, izlabo kļūdas vārdos un t.t. un `pagination` tur arī vispār nebremzē. Protams, ieguldītais darbs un resursi ir milzīgi… var tikai ņemt piemēru un mācīties.
Šie subselekti der mazām tabulām. Universālumam var rakstīt skatus/procedūras.
@kaitnieks
Njā nav man versijas kāpēc tas nav SQL standartā, izskatās, ka citiem uz pasaules arī īsti nav. No otras puses ne jau visi taisa (web) aplikācijas ar lapošanu, es pate teiktu, ka tas nemaz tik plaši izplatīts nav.
@Delfins
Yep piekrītu par universālo kodu un ātrdarbību, es ceru, ka tas bija noprotams no mana rakstītā toņa 🙂
Bet par googli es domāju, ka īsti nav vietā runāt. Google ir liels speciālgadījums un tam nav īsti sakara ne ar tradicionālām aplikācijām, ne tradicionālām DB. Man šis piemērs diez ko nepatīk. Un vēl – vienmēr ir vērts atcerēties, ka pat google nedod vairāk kā pirmos 1000 ierakstus, tāpēc nevajag vienkārši ļaut lapoties iekšā 8976236tajā lapppusē.