Pirmo N ierakstu atlase

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.

Advertisements