Analītiskās funkcijas

In my opinion, analytic functions are the coolest thing to happen to SQL since the SELECT keyword was introduced. Thomas Kyte
(Manuprāt analītiskās funkcijas ir kolosālākais izgudrojums SQLā kopš Select atslēgas vārda)

Vienkāršā SQL Select teikumā katrā konkrētā brīdī ir iespējams vienlaicīgi operēt (“redzēt”) ar vienu ierakstu un tā kolonām.  Var piemēram saskaitīt kolonu A un B saturu, var noskaidrot vai kolona A ir lielāka kā B, var veikt daudz un dažādas citas funkcijas no vienas vai vairākām kolonām. Taču tiklīdz kā rodas vēlme paskatīties uz jebkuru citu ierakstu šī paša vaicājuma rezultātā, tad tas nav iespējams.

Vienkāršs select teikums

Protams, to var izdarīt ar kādu no savienojumu (join) veidiem, bet tas nozīmē krietni sarežģītāku Select vaicājumu, kas var diezgan pamatīgi iespaidot izpildes laiku. Pie tam reizēm to izdarīt ir ļoti sarežģīti. Otra iespēja ir dabūt kaut kādas summārās, vidējās un citas grupēšanas vērtības, taču tādā gadījumā tiek zaudēta informācija par ierakstu, no kurienes informācija nākusi un nav iespējams iegūt pārējās ieraksta kolonas.

Select teikums ar grupēšanu

Analītiskās funkcijas dod iespēju iet lielu un pamatīgu soli tālāk – redzēt vērtības no citām rindām un iegūt grupēšanas rezultātus saglabājot arī katra ieraksta specifiskās vērtības. Kā to praktiski panākt – sadaļā Piemēri.

Select teikums ar kolonām no analītiskajām funkcijām

SQL standartā lielākā daļa no tālākajām funkcijām tiek sauktas par logu (window) funkcijām, jo tās rezultāts tiek noskaidrots it kā uzliekot noteiktu logu virsū vaicājumā atgrieztajiem ierakstiem. Taču vienkāršības labad es arī turpmāk tās visas saukšu par analītiskajām funkcijām un attiecīgajos smalkumos vismaz šai rakstā neiedziļināšos.

Sintakse

Vienkāršota analītisko funkciju sintakse ir šāda:

<funkcija> ([<arguments>]) OVER ([PARTITION BY <kolonu saraksts>] ORDER BY <kolonu saraksts> )

Elementu nozīme:

• <funkcija> – konkrētā izmantojamā analītiskā funkcija. Šeit der lielākā daļa no grupēšanas funkcijām, piemēram, Min, Max, Sum, Avg, kā arī dažas citas jaunas funkcijas, kas parasti nav pieejamas, piemēam, row_number, rank, dense_rank utt.
• OVER  – atslēgas vārds, kas norāda, ka šī ir analītiskā f-ja, piemēram, atšķirība no parastas grupēšanas funkcijas.
• PARTITION BY <kolonu saraksts> – nodalījums, kādā tiek sadalīts viss vaicājuma rezultāts. Kā redzams šī klauza ir neobligāta, tādā gadījumā nodalījums ir viens – viss vaicājuma rezultāts. Ja šī klauza eksistē, tad tiklīdz kā kolonu sarakstā minētās kolonas vērtība mainās, tā tas ir nākošais nodalījums – skat. nākošajā attēlā.
• ORDER BY – kārtība kādā ieraksti tiks sakārtoti katra nodalījuma ietvaros. Sakārtojumam vēlams būt viennozīmīgam, pretējā gadījumā nav garantijas, kurš no ierakstiem būs pirmais, kurš nākošais. Pie tam ļoti svarīgi atcerēties – šis sakārtojums nenozīmē kā rindas būs sakārtotas gala rezultātā, gala rezultāta definētu sakārtojumu nodoršina tikai un vienīgi Select teikuma Order By klauza.

Partition by un Order by klauzu vizualizācija

Analītiskās funkcijas un Oracle

Analītiskās funkcijas Oraclē parādījās jau vismaz sākot ar versiju 8.1.6, kas tika izlaista ap 2000. gadu. Attiecīgi nav nekāda pamata uzskatīt, ka šī lieta ir kaut kas ļoti jauns ;), diemžēl daudz cilvēku tā arī par tām neko nav dzirdējuši, vai vismaz nav līdz galam sapratuši. Katrā versijā kaut kas ir nācis klāt un pēdējā 11.1 versijā ir vismaz 30 jau gatavas analītiskās funkcijas, kā arī iespēja izveidot pašam savu analītisko funkciju, ja nu izrādās, ka ar iebūvētām lietām vien nepietiek, kas gan ir ļoti mazticami. Analītiskās funkcijas ir pieejamas jebkurā Oracle redakcijā, tai skaitā arī tajā, kas ir par velti – Express Edition.

Analītiskās funkcijas un Microsoft SQL Server

SQL Server 2005, kas apgrozībā jau ir labu laiku, ir iespējams izmantot vairākas analītiskās (šeit tās sauc par ranking) funkcijas – skaitā ap 15. Diemžēl 2008 versijā nekas jauns klāt nav nācis un, atšķirībā no Oracles, nav vairākas ļoti jaukas analītiskās funkcijas, piemēram, lag, lead, first_value, last_value, kā arī nav iespējas veidot īstas augšminētās slīdošo logu funkcijas, kad funkcija tiek izrēķināta pirmajai, pēdējai vai vairākām rindām kādā noteiktā logā (neskaitot partition by klauzu!).

Analītiskās funkcijas un MySQL

Tu-tū. Pagaidām nekā. Vienīgais, ko varu ieteikt, – meklēt googlē un piemēram atrast tādus rakstus kā Emulating Analytic (AKA Ranking) Functions with MySQL, kur patiesībā nekādu ameriku jau autors nav izgudrojis – to visu (vai gandrīz visu 😉 var izdarīt ar savienojumiem un apakšvaicājumiem, tikai tāds sīkums – vaicājums ir krietni garāks gan tīri teksta ziņā, gan arī izpildes laika ziņā. Vēl protams atliek iespēja cerēt, ka kādu dienu šis uzlabojuma pieprasījums tiks realizēts.

Piemēri

Tagad ķersimies pie dažiem vienkāršiem piemēriem, lai sāktu saprast, kā šīs funkcijas darbojas. Vispirms kā parasti izveidojam tabulu un aizpildam datus. Dati īpaši neatšķiras no tā, kas ir redzams augstāk attēlos.

CREATE TABLE personas (
  prs_id INTEGER NOT NULL PRIMARY KEY,
  prs_vards VARCHAR(40),
  prs_pilseta VARCHAR(20));
INSERT INTO personas VALUES (1, 'Jānis Bērziņš', 'Alūksne');
INSERT INTO personas VALUES (2, 'Līga Bērziņa', 'Jelgava');
INSERT INTO personas VALUES (3, 'Žanete Kalniņa', 'Jelgava');
INSERT INTO personas VALUES (4, 'Žanis Kociņš', 'Rīga');
INSERT INTO personas VALUES (5, 'Kaspars Krūmiņš', 'Rīga');
INSERT INTO personas VALUES (6, 'Žanis Kociņš', 'Rīga');
INSERT INTO personas VALUES (7, 'Kārlis Krastiņš', 'Talsi');
COMMIT;

Viena no reizē visvienkāršākajām un arī visnoderīgākajām funkcijām ir row_number(), kas vienkārši dod numuru pēc kārtas sākot ar 1.

Piemērs 1. Atlasa identifikatoru, vārdu, pilsētu un numuru pēc kārtas, kārtojot pēc vārda un identifikatora (lai būtu viennozīmīgs sakārtojums).

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    row_number() OVER (ORDER BY prs_vards, prs_id) rn
  3  FROM personas;
PRS_ID PRS_VARDS           PRS_PILSETA    RN
------ ------------------- ------------- ---
     1 Jānis Bērziņš       Alūksne         1
     5 Kaspars Krūmiņš     Rīga            2
     7 Kārlis Krastiņš     Talsi           3
     2 Līga Bērziņa        Jelgava         4
     3 Žanete Kalniņa      Jelgava         5
     4 Žanis Kociņš        Rīga            6
     6 Žanis Kociņš        Rīga            7

It kā ļoti vienkārši un  to var panākt ar daudziem un dažādiem citiem variantiem. Taču skatamies kas notiek tagad, kad pievienojam PARTITION BY klauzu skatoties pēc kolonas pilsēta. Tagad numuru piešķiršana katram nodalījumam sākas no jauna un panākt šādu rezultātu bez analītiskajām funkcijām jau ir ļoti grūti.

Piemērs 2. Atlasa identifikatoru, vārdu, pilsētu un numuru pēc kārtas, nodalot ierakstu pēc pilsētas un kārtojot pēc vārda un identifikatora (lai būtu viennozīmīgs sakārtojums).

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    row_number() OVER (
  3               PARTITION BY prs_pilseta
  4               ORDER BY prs_vards, prs_id) rn
  5  FROM personas;
PRS_ID PRS_VARDS           PRS_PILSETA     RN
------ ------------------- ------------- ----
     1 Jānis Bērziņš       Alūksne          1
     2 Līga Bērziņa        Jelgava          1
     3 Žanete Kalniņa      Jelgava          2
     5 Kaspars Krūmiņš     Rīga             1
     4 Žanis Kociņš        Rīga             2
     6 Žanis Kociņš        Rīga             3
     7 Kārlis Krastiņš     Talsi            1

Tātad mēs viegli varam redzēt kura persona ir “pirmā” katrā pilsētā, līdz ar tās identifikatoru un piemēram gala rezultātā pielietojot apakšvaicājumu atlasīt tikai tās. Apakšvaicājuma jālieto, jo vispirms tiek pielietota Where klauza un tikai pēc tam analītiskās funkcijas. Līdzīgi varētu darīt izmantojot arī parastu grupēšanas funkciju min(prs_vards), bet tādā gadījumā mēs pazaudētu šīs personas identifikatoru.

Piemērs 3. Atlasa tikai “pirmās” personas pilsētās.

SQL> SELECT prs_id, prs_vards, prs_pilseta FROM (
  2    SELECT prs_id, prs_vards, prs_pilseta,
  3      row_number() OVER (
  4                 PARTITION BY prs_pilseta
  5                 ORDER BY prs_vards, prs_id) rn
  6    FROM personas) p
  7  WHERE rn = 1;
PRS_ID PRS_VARDS          PRS_PILSETA
------ ------------------ ---------------
     1 Jānis Bērziņš      Alūksne
     2 Līga Bērziņa       Jelgava
     5 Kaspars Krūmiņš    Rīga
     7 Kārlis Krastiņš    Talsi

Skatamies nākošo piemēru. Mums ir jāuzzin kādas konkrēti personas dzīvo kādā pilsētā, kā arī pilsētā kopējo dzīvojošo skaitu. Tradicionālo mums ir select vaicājums ar group by klauzu.

Piemērs 4. Tradicionāls vaicājums ar grupēšanu, iegūstot personu skaitu katrā pilsētā.

SQL> SELECT count(*), prs_pilseta
  2  FROM personas
  3  GROUP BY prs_pilseta;
  COUNT(*) PRS_PILSETA
---------- --------------------
         3 Rīga
         2 Jelgava
         1 Talsi
         1 Alūksne

Diemžēl informācija par katru konkrēto personu ir zaudēta un mēs to iegūt varam tikai veicot pašsavienojumu ar to pašu tabulu, kā savienojuma nosacījumu izmantojot pilsētas nosaukumu.

Piemērs 5. Tradicionāls vaicājums ar grupēšanu, iegūstot personu skaitu katrā pilsētā un veicot pašsavienojumu ar to pašu tabulu iegūstot arī detaļas par personu.

SQL> SELECT prs_id, prs_vards, p.prs_pilseta, cn
  2  FROM personas p INNER JOIN (
  3    SELECT count(*) cn, prs_pilseta
  4    FROM personas
  5    GROUP BY prs_pilseta
  6  ) p_grupets
  7  ON (p.prs_pilseta = p_grupets.prs_pilseta)
  8  ORDER BY prs_pilseta, prs_vards, prs_id;
PRS_ID PRS_VARDS          PRS_PILSETA                  CN
------ ------------------ -------------------- ----------
     1 Jānis Bērziņš      Alūksne                       1
     2 Līga Bērziņa       Jelgava                       2
     3 Žanete Kalniņa     Jelgava                       2
     5 Kaspars Krūmiņš    Rīga                          3
     4 Žanis Kociņš       Rīga                          3
     6 Žanis Kociņš       Rīga                          3
     7 Kārlis Krastiņš    Talsi                         1

Ar count(*) funkcijas analītisko (nevis agregācijas!) variantu to var panākt daudz vienkāršāk:

Piemērs 6. Detaļas par personu, līdz ar kopējo personu skaitu pilsētā izmantojot analītisko funkciju count(*).

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    count(*) OVER (PARTITION BY prs_pilseta) cn
  3  FROM personas;
PRS_ID PRS_VARDS          PRS_PILSETA                  CN
------ ------------------ -------------------- ----------
     1 Jānis Bērziņš      Alūksne                       1
     2 Līga Bērziņa       Jelgava                       2
     3 Žanete Kalniņa     Jelgava                       2
     4 Žanis Kociņš       Rīga                          3
     5 Kaspars Krūmiņš    Rīga                          3
     6 Žanis Kociņš       Rīga                          3
     7 Kārlis Krastiņš    Talsi                         1

Nākošajā piemērā redzam, ka arī funkcijai min ir analītiskais variants, kas ļoti viegli ļauj iegūt pie katra ieraksta klāt arī piemēram šīs pilsētas “pirmo” personu – atceramies piemēru 3. Šeit beidzot arī mēs padodam reālu argumentu funkcijai – sakam, ka minimālā vērtība jārēķina kolonai prs_vards, nodalījumā pēc pilsētas. Tātad nekādu pašsavienojumu, kas citādi būtu neizbēgams!

Piemērs 7. Visas personas un pie katras piekārtota arī “pirmā persona pilsētā”.

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    min(prs_vards) OVER (PARTITION BY prs_pilseta) pirma_persona
  3  FROM personas;
PRS_ID PRS_VARDS         PRS_PILSETA  PIRMA_PERSONA
------ ----------------- ------------ ---------------
     1 Jānis Bērziņš     Alūksne      Jānis Bērziņš
     2 Līga Bērziņa      Jelgava      Līga Bērziņa
     3 Žanete Kalniņa    Jelgava      Līga Bērziņa
     4 Žanis Kociņš      Rīga         Kaspars Krūmiņš
     5 Kaspars Krūmiņš   Rīga         Kaspars Krūmiņš
     6 Žanis Kociņš      Rīga         Kaspars Krūmiņš
     7 Kārlis Krastiņš   Talsi        Kārlis Krastiņš

Diemžēl šādā veidā iegūt pirmās personas identifikatoru neizdosies. Jo tas nozīmē, ka mums ir jāņem minimālā vērtība no identifikatora, bet ne vienmēr pirmā persona būs arī ar minimālo identifikatoru (pievērst uzmanību Rīgas Kasparam Krūmiņam kā pirmai personai un tās identifikatoram!): 

Piemērs 8. Visas personas un pie katras piekārtota arī “pirmā persona pilsētā”, bet ar nepareizu pirmās personas identifikatoru.

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    min(prs_vards) OVER (PARTITION BY prs_pilseta) pirma_persona,
  3    min(prs_id) OVER (PARTITION BY prs_pilseta) pp_id
  4  FROM personas;
PRS_ID PRS_VARDS        PRS_PILSETA PIRMA_PERSONA   PP_ID
------ ---------------- ----------- --------------- -----
     1 Jānis Bērziņš    Alūksne     Jānis Bērziņš       1
     2 Līga Bērziņa     Jelgava     Līga Bērziņa        2
     3 Žanete Kalniņa   Jelgava     Līga Bērziņa        2
     4 Žanis Kociņš     Rīga        Kaspars Krūmiņš     4
     5 Kaspars Krūmiņš  Rīga        Kaspars Krūmiņš     4
     6 Žanis Kociņš     Rīga        Kaspars Krūmiņš     4
     7 Kārlis Krastiņš  Talsi       Kārlis Krastiņš     7

Šajā gadījumā mums nāk talkā analītiskā funkcija first_value, kas aprēķina nevis minimālo vertību kādā kopā, bet pirmo vērtību kādā sakārtojumā.

Piemērs 9. Visas personas un pie katras piekārtota arī “pirmā persona pilsētā”, šoreiz ar pareizu pirmās personas identifikatoru.

SQL> SELECT prs_id, prs_vards, prs_pilseta,
  2    min(prs_vards) OVER (
  3        PARTITION BY prs_pilseta) pirma_persona,
  4    first_value(prs_id) OVER (
  5        PARTITION BY prs_pilseta
  6        ORDER BY prs_vards, prs_id) pp_id
  7  FROM personas;
PRS_ID PRS_VARDS        PRS_PILSETA PIRMA_PERSONA    PP_ID
------ ---------------- ----------- ---------------- -----
     1 Jānis Bērziņš    Alūksne     Jānis Bērziņš        1
     2 Līga Bērziņa     Jelgava     Līga Bērziņa         2
     3 Žanete Kalniņa   Jelgava     Līga Bērziņa         2
     5 Kaspars Krūmiņš  Rīga        Kaspars Krūmiņš      5
     4 Žanis Kociņš     Rīga        Kaspars Krūmiņš      5
     6 Žanis Kociņš     Rīga        Kaspars Krūmiņš      5
     7 Kārlis Krastiņš  Talsi       Kārlis Krastiņš      7

Vēl ir ļoti labas un derīgas funkcijas lag un lead, kam kā argumentu padod kolonu un kas attiecīgi dod iepriekšējo vai nākošo vērtību sakārtojumā. Pie tam pievērsiet uzmanību – lead funkcijai ir padots otrs arguments 2, kas dod aiznākošo personu, nevis tikai nākošo.

Piemērs 10. Visas personas līdz ar iepriekšējo un aiznākošo personu.

SQL> SELECT prs_id, prs_vards,
  2    lag(prs_vards) OVER (
  3        ORDER BY prs_vards, prs_id) iepr_persona,
  4    lead(prs_vards, 2) OVER (
  5        ORDER BY prs_vards, prs_id) nak_otra_persona
  6  FROM personas;
PRS_ID PRS_VARDS          IEPR_PERSONA      NAK_OTRA_PERSONA
------ ------------------ ----------------- ----------------
     1 Jānis Bērziņš                        Kārlis Krastiņš
     5 Kaspars Krūmiņš    Jānis Bērziņš     Līga Bērziņa
     7 Kārlis Krastiņš    Kaspars Krūmiņš   Žanete Kalniņa
     2 Līga Bērziņa       Kārlis Krastiņš   Žanis Kociņš
     3 Žanete Kalniņa     Līga Bērziņa      Žanis Kociņš
     4 Žanis Kociņš       Žanete Kalniņa
     6 Žanis Kociņš       Žanis Kociņš

 Vēl gribu pieminēt funkcijas rank() un dense_rank(), kas darbojas līdzīgi kā row_number(), taču nevis vienkārši skaita cipariņus uz priekšu, bet piešķir vietas. Pie tam rank() vairāku vienādu vietu gadījumā atbilstoši nākošās vietas izlaiž, bet dense_rank() skaita tieši no nākošās vietas – pievērsiet uzmanību pirmajiem trīs ierakstiem.

Piemērs 11. Visas personas ar tām piešķirtām “vietām” divos dažādos veidos.

SQL> SELECT prs_id, prs_vards,
  2    rank() OVER (
  3        ORDER BY prs_vards desc) personas_vieta,
  4    dense_rank() OVER (
  5        ORDER BY prs_vards desc) personas_vieta_saspiesta
  6  FROM personas;
PRS_ID PRS_VARDS         PERSONAS_VIETA PERSONAS_VIETA_SASPIESTA
------ ----------------- -------------- ------------------------
     4 Žanis Kociņš                   1                        1
     6 Žanis Kociņš                   1                        1
     3 Žanete Kalniņa                 3                        2
     2 Līga Bērziņa                   4                        3
     7 Kārlis Krastiņš                5                        4
     5 Kaspars Krūmiņš                6                        5
     1 Jānis Bērziņš                  7                        6

Šai reizei pietiks!

Noslēgumā

Šis bija diezgan īss ieskats analītisko funkciju pasaulē. Tika apskatītas tikai to pamatiespējas un netika pieminētas citas papildu klauzas. Protams, tās nav panaceja, kas ārstēs visas kaites un maģiski iegūs visus iespējamos rezultātus, vēl vairāk – ikdienā transakciju apstrādē tās vispār ir reti nepieciešamas. Taču, rakstot atskaites un rēķinot dažādus statistikas pārskatus, šīs funkcijas ļauj daudz lietas vienkāršot, izrēķināt ātrāk un daudz lietas vienkārši atļauj paveikt, kas līdz šim tīrā SQLā principiāli nebija iespējamas.

Tālākā lasāmviela

• Oracle analītisko funkciju dokumentācija 11g versijai;
• SQL Server 2008 Ranking Functions (Transact-SQL) un Aggregate Functions (Transact-SQL);
• Atskats uz Oracle dienu 2008, kuras laikā es lasīju prezentāciju par analītiskājm funkcijām un iespēja to lejuplādēt.