SQL “with” klauza

septembris 16, 2008

Ja kāds vēl nav pamanījis, tad jau labu laiciņu dažās DBVS Select teikums var sākties nevis ar Select, bet ar atslēgas vārdu With. Tas ir kaut kādā mērā viens no apakšvaicājumu veidiem, kā tas tika apskaidrots iepriekšējā rakstā apakšvaicājumi – ievads.

Tātad kad to pielietot un kādi ir tā galvenie labumi? Ir divas galvenās lietas kādēļ ir vērts to lietot – lasāmībai un ātrdarbības uzlabošanai.

  • Lasāmību tas var uzlabot tādā veidā, ka, ja Jums ir SQL teikums, kurā vairākkārt tiek lietots viens un tas pats (vai līdzīgs) apakšvaicājums, tad Jūs varat izdalīt no tā ārā kopīgo daļu – tāda kas ir kopīga visos gadījumos un to rakstīt pašā sākumā zem šī With un tālāk atsaukties jau kā uz zināmu defīnīciju.
  • Savukārt ātrdarbībai optimizatoram tagad ir 2 iespējas. Viena iespēja ir ievietot Jūsu apakšvaicājuma definīciju pa taisno iekšā SQL teikumā visur, kur tas tiek izmantots, tādējādi it kā nonākot atpakaļ pie situācijas, kāda bija bez šīs klauzas pielietošanas. Otra iespēja ir vispirms materializēt Jūsu apakšvaicājumu pagaidu tabulā un tālāk veikt pamata SQL teikuma izpildi jau izmantojot šo materializēto pagaidu tabulu.

Es turpmāk apskatīšu sīkāk šīs With klauzas implementāciju divās DBVS – Oracle un SQL Server. Tradicionāli es mēdzu rakstīt arī par MySQL, bet tur šāda klauza nav pieejama.

Sintakse

WITH <vaicājuma nosaukums1>
AS (<apakšvaicājums1>)
[, <vaicājuma nosaukums2> AS (<apakšvaicājums2>), ...]

Tātad kā redzams viss sākas ar atslēgas vārdu WITH, pēc tam nāk <vaicājuma nosaukums>. Šeit ir nepieciešams vienkārši piešķirt vārdu šim apakšvaicājumam, lai vēlāk būtu iespējams uz to atsaukties. Pēc tam ir atslēgas vārds AS, aiz kura iekavās seko <apakšvaicājums>. Apakšvaicājums parasti var būt pilnvērtīgs Select teikums ar savienojumiem un citiem apakšvaicājumiem. Vienlaicīgi var būt vairāki šādā veidā definēti apakšvaicājumi, kas tiek viens no otra atdalīti ar komatu. It kā nekas sarežģīts tāpēc pārejam pie konkrētām DBVS un konkrētiem piemēriem. Lasīt pārējo šī ieraksta daļu »


Apakšvaicājumi – ievads

septembris 3, 2008

Apakšvaicājumi ir viena no SQL pamatlietām, ar kuru nākas sastapties jebkuram, kas pēc analoģijas ar gramatiku vēlas izteikties mazliet sarežģītāk nekā vienkāršos nepaplašinātos teikumos. Apakšvaicājumi ir dažādu veidu un katrā DBVS tos mēģina klasificēt un nosaukt diezgan atšķirīgi. Papildus tam gluži tāpat kā tabulu savienojumiem klasificēšanas kritēriji ir dažādi, tāpēc daži veidi var daļēji pārklāties. Attiecīgi šoreiz es necentīšos to visu pārskaitīt un klasificēt, bet parādīšu piemēru, kas to vien dara kā atlasa kaut ko no dual tabulas (speciāla Oracle tabula ar vienu kolonu dummy un vienu rindu, kas satur ‘X’). Daži termini varētu būt specifiski Oracle izpratnei par šo jomu, bet jēga jau nosaukuma dēļ nemainās; ja jūs zināt ko šis veids dara, tad arī citās DBVS, tas darīs to pašu, tikai varbūt tiks saukts citādi. 

Jau iepriekš brīdinu – nemeklējiet šajā piemērā nekādu semantisku jēgu, izņemot tikai to, ka tas ir lietots kā piemērs tam, kur tik vien apakšvaicājumus nevar iebāzt.

WITH a AS (SELECT * FROM dual)
SELECT (SELECT 1 FROM dual) col1, 3 col2
FROM (SELECT * FROM a) outer1,
  (SELECT * FROM a)
INNER JOIN (SELECT * FROM a)
  ON (1=(SELECT 1 FROM dual))
WHERE 1 >= ANY (
  SELECT 1 FROM dual
  UNION ALL
  SELECT 2 FROM dual
)
  AND EXISTS (SELECT 1 FROM dual)
  AND 1 IN (SELECT 1 FROM dual)
  AND EXISTS (
    SELECT 1 FROM dual inner
    WHERE outer1.dummy = inner.dummy
  )
GROUP BY 1
HAVING count(*) >= (SELECT 1 FROM dual)
ORDER BY (SELECT 1 FROM dual);

Tālāk šis pats SQL vaicājums ir krietni krāsainākā variantā:

Apakšvaicājumu piemērs

Apakšvaicājumu piemērs

Kas šeit ir kas?

  • Sarkanā krāsā ir iezīmētas vienkārša SQL Select teikuma sastāvdaļas.
  • 1 – SQL with klauza. Oracle to sauc par subquery factoring klauzu, savukārt SQL server par common table expression. Arī ar to var sākties Select teikums un tā galvenā ideja ir iznest ārā kopīgas SQL teikuma sastāvdaļas.
  • 2 – with klauzas vaicājums. Tālāk Select vaicājumā var lietot definēto apakšvaicājumu a (iezīmētu melnā krāsā) un “atlasīt” datus no tā.
  • 3 – apakšvaicājums kolonas vietā, saukts arī par skalāro apakšvaicājumu (scalar subquery). Kā jau pats nosaukums norāda, šim apakšvaicājuma veidam ir jāatgriež tieši viena vērtība, t.i. jāatlasa viena ieraksta viena kolona, kas protams var būt arī izteiksme. Visi zīmējumā iekrāsotie apakšvaicājumi dzeltenā krāsā ir skalārie apakšvaicājumi.
  • 4 – piemērs tam, ka savienojumus jeb tabulu kombinēšanas nebūt nav jāveido tikai no tabulām, bet tie var būt apakšvaicājumi. Kā redzam piemērā ir miksēta tabulu kombinēšana izmantojot sintaksi, kad kombinējamās ierakstu kopas vienkārši raksta FROM klauzā atdalot ar komatu (pirmā un otrā, faktiski tur ir Dekarta reizinājums, jo WHERE klauzā nav nekāda kombinēšanas nosacījuma), kā arī raksta tiešu ANSI savienojumu sintaksi izmantojot INNER JOIN atslēgas vārdu. Apakšvaicājumus FROM klauzā sauc arī par iekļautajiem skatījumiem (inline view). Tas ir mans brīvs tulkojums, jo izskatās, ka pat Google latviešu lapās šādu terminu praktiski atrast nespēj.
  • 5 – piemērs jau augstāk minētajam skalārajam apakšvaicājumam, šoreiz tas lietots kombinēšanas nosacījumā.
  • 6 – apakšvaicājums WHERE klauzā, tādus sauc arī par nested subquery (šķiet latviski būtu ligzdvaicājums, izbēgot no “apakš“. Par “ligzd” skat. pamatojumu šeit un iebildes šeit). Šajā gadījumā tiek izmantots modificēts (ar atslēgas vārdu ANY) lielāks vienāds operators. Tā kā šis operators ir modificēts, tad var apakšvaicājumā atlasīt arī vairākus ierakstus.
  • 7 – apakšvaicājums WHERE klauzā izmantojot EXISTS operatoru.
  • 8 – apakšvaicājums WHERE klauzā izmantojot IN operatoru.
  • 9 – it kā tas pats EXISTS operators, kas punkta 7. Bet ir viena atsķirība – šo apakšvaicājumu ir jāizpilda katrai virsvaicājumā atlasītajai rindiņai, jo tas ir atkarīgs no virsvaicājuma atlasītā ieraksta. Šādus apakšvaicājumus, kur rezultāts ir atkarīgs no virsvaicājuma atlasītā ieraksta, sauc par korelētajiem apakšvaicājumiem (correlated subquery). Protams, ka korelētais apakšvaicājums var būt arī citiem operatoriem, ne tikai EXISTS.
  • 10 – skalārais apakšvaicājums HAVING klauzā.
  • 11 – skalārais apakšvaicājums ORDER BY klauzā.

Dažas papildus piezīmes

  • Apakšvaicājuma galvenā jēga ir uzzināt kaut ko tādu, ko vienā vaicājumā vien sakombinēt nav iespējams, vai arī tas ir pārāk sarežģīti.
  • Kā redzams apakšvaicājumi var ielīst daudz un dažādās vietās. Protams, dažkārt tiem var būt šādi tādi ierobežojumi, bet tas ir jāskatās katrā gadījumā atsevišķi.
  • Apakšvaicājumi, protams, ir pielietojami arī Insert, Update, Delete un Merge teikumos, ne tikai Select.
  • Ir vērts atcerēties, ka katrs apakšvaicājums var savukārt būt pietiekami sarežģīts, atlasīt datus no vairākām tabulām, kā arī nekas mūs nekavē veidot iekļautus apakšvaicājumus vairākos līmeņos. Tehniski parasti tam ir kādas robežas, bet tās ir daudz tālāk nekā jebkurš saprātīgs un lasāms SQL teikums pieļautu.
  • Apakšvaicājumi un tabulu savienojumi (join) var tikt lietoti, lai funkcionāli sasniegtu vienu un to pašu rezultātu. Ne visu ko var sasniegt ar apakšvaicājumiem, var sasniegt ar savienojumiem, tāpat ne visu ko var sasniegt ar savienojumiem, var sasniegt ar apakšvaicājumiem. Tās ir divas dažādas lietas, ko SQL piedāvā un abas var tikt lietotas, lai panāktu vēlamo rezultātu. Nav iespējams pateikt, ka vienmēr labāki ir savienojumi, tāpat kā nav iespējams pateikt, ka vienmēr labāki ir apakšvaicājumi.
  • Dažas datu bāzu vadības sistēmas apakšvaicājumus uztver mierīgāk, dažas ne tik mierīgi 🙂 Piemēram MySQL, šķiet nav diez ko draudzīgs apakšvaicājumiem, jo vismaz FROM klauzas apakšvaicājumus katru izrēķina atsevišķi. Tas tikai vēl vairāk pastiprina izteikumu – it depends, nirvānu (vēlamo rezultātu) var sasniegt pa daudziem un dažādiem ceļiem un nebūt nav tikai viens vienīgais pareizais.
  • Oracle (un iespējams arī citās DBVS) izpildes plāna veidošanas laikā Optimizators vispār var transformēt savienojumu par apakšvaicājumu un otrādi, ja to uzskata par vajadzīgu un tas konkrētā gadījumā ir iespējams. Tāpēc šī robeža ir vēl jo vairāk izplūdusi.

Tālākā lasāmviela

Turpinājums sīkāk par katru no veidiem kaut kad sekos 🙂


Jaunu tabulu veidošana no esošajām

augusts 26, 2008

Īsti vīri, kā zināms, bekapus (latviski gan būtu rezerves kopijas) netaisa. Un šis arī īsti nebūs raksts par rezerves kopijām to klasiskajā izpratnē, bet par to kā ātri, ērti un vienkārši izveidot kopijas tabulai (-lām) līdz ar visiem datiem, kuru datus Jūs taisaties mainīt vai arī gribat patestēt, vai arī jebkādiem citiem nolūkiem. Un te nu mēs nonākam pie situācijas, kad kārtējo reizi katra DBVS dara tā kā pašai patīk (drīzāk gan jau kā tās izstrādātāji nolēmuši) un vismaz trīs zemāk apskatītajās (Oracle, Microsoft SQL Server un MySQL) katrā tas ir savādāk.

Oracle

Lai izveidotu jaunu tabulu Oracle tiek lietots CREATE TABLE DDL teikums. Tas paliek spēkā arī tad, ja tabula tiek veidota balstoties uz esošu vienu vai vairākām tabulām, t.i., apakšvaicājumu. Tātad ļooooooti vienkāršota sintakse ir šāda:

CREATE TABLE <tabulas nosaukums>
[<kolonu definīcijas>]
[<fiziskie atribūti>]
[<tabulas atribūti>]
AS <apakšvaicājums>

CREATE TABLE <tabulas nosaukums>

Atslēgvārdi CREATE TABLE sāk jebkuras tabulas izveides SQL teikumu. Tālāk tiek rakstīts tabulas nosaukums. Šeit jāatceras, ka Oracle noklusēti visus tabulas nosaukumus un vispār identifikatorus pārvērš uz lielajiem burtiem, tātad datu vārdnīcā Jūsu izveidotā tabula tabulas_nosaukums tiks pārveidota kā TABULAS_NOSAUKUMS. Ja nu Jums vien zināmu iemeslu dēļ tā nevēlaties, tad ir iespējams arī saglabāt tabulas nosaukumu precīzi tādu kā ievadījāt, ja to iekļauj pēdiņās. Piemēram, tabula “manas šausmas $%” tieši tā arī tiks saglabāta datubāzē, taču ar to nekādā ziņā nevajadzētu aizrauties, jo pēc tam Jums vienmēr nāksies nosaukumu rakstīt pēdiņās, kā arī tas ir perfekts veids kā rakstīt neuzturamu kodu. Protams nevajadzētu lietot arī rezervētos vārdus, jo tādu nosaukumu tabulas Oracle neļaus izveidot (ja vien atkal nelietosiet pēdiņas).

<kolonu definīcijas>

Šī daļa ir neobligāta. Pamatā šeit var definēt divas lietas – kolonu nosaukumus un ierobežojumus. Tas, protams, nebūt nav viss iespējamais, bet vairumā gadījumu jo sevišķi šai gadījumā ar to pietiek. Jāņem vērā vairākas lietas:

  • kolonu nosaukumiem jāatbilst pieļaujamiem identifikatoru nosaukumiem Oraclē;
  • kolonu skaitam jāsakrīt ar kolonu skaitu apakšvaicājumā;
  • datu tipu definēt šeit nedrīkst, jo to nosaka apakšvaicājuma atgrieztais rezultāts.

<fiziskie atribūti>

Parasti šeit neko norādīt nevajag, jo visas noklusētās vērtības ir gana labas un pieņemamas. Taču iespēja ir un norādīt var tādas lietas kā:

  • tabultelpu (tablespace);
  • atribūtus ar kādiem, tabula fiziski tiks glabāta (pctfree, pctused u.c.);
  • vai tabula tiks kompresēta (compressed);
  • tabulas veidu (heap – parastā tabula, index organized – glabāta kā indeksa struktūra, external – ārējā), katrai no šiem tabulas veidiem vēl ir milzums atribūti;
  • kādā klāsterī tā tiks glabāta;
  • u.c.

<tabulas atribūti>

Līdzīgi kā iepriekšējā klauza, šī parasti ātrai tabulu izveidošanai nav nepieciešama, taču šeit var norādīt tādas lietas, kā:

  • vai tabula tiks particionēta (partitioned);
  • tabulas paralelitātes atribūtus;
  • vai tabulai tiks glabāta noklusēta kolona (rowdependencies) norādot pēdējo sistēmas izmaiņu kārtas numuru (scn, system change number);
  • iespējot/atspējot ierakstu pārvietošanu (row movement);
  • u.c.

Šo un iepriekšējo klauzu es pieminu tikai tāpēc, lai saprastu, ka principā šeit ir iespējams definēt praktiski visas iespējamās lietas, ko Oracle atļauj CREATE TABLE teikumam. Aptuvenai apjoma sapratnei tās ir 73 lapas no dokumentācijas, kurā iespējas ir pamatā tikai uzskaitītas, nevis sīki apstāstīts, ko katra nozīmē 😉

AS <apakšvaicājums>

Šeit grūti ko piebilst – šis var būt jebkurš Select teikums, sākot ar relatīvi vienkāršu Select teikumu, tajā var būt iekļautas kopas operācijas, kā arī nekas nekavē lietot kādu no savienojumu veidiem vai, piemēram, analītiskās funkcijas. Īsumā – visu, ko Jūs spējat Select teikumā sarakstīt, var arī izveidot kā CREATE TABLE AS <apakšveicājums> teikumu. Dažas piezīmes un nelieli triki, kas var kādreiz noderēt:

  • dažkārt ir vēlme izveidot tabulu, kas būtu ar tādām pašām kolonām kā kāda cita (vai citu apvienojums), bet datus tai nepievienot. Savukārt kolonu skaits ir liels un slinkums visas tās pārskaitīt. Tad vienkārši jāpievieno SELECT apakšvaicājumam WHERE klauza, kas visām rindām dod aplamu rezultātu, piemēram, WHERE 1 = 0 (skat. piemēru 2).
  • dažkārt gribas pievienot papildus kolonu ar nedefinētām (NULL) vērtībām, kas SELECT teikumā tieši atlasīta netiek, bet kura tiks vēlāk koriģēta izmantojot UPDATE teikumu. Tādā gadījumā select teikumam vienkārši jāpievieno jauna kolona, kurā atlasam NULL un tipizējam to ar noteiktu funkciju, nodrošinot to, ka kolona ir ar nepieciešamo datu tipu (skat. piemēru 6).

Oracle piemēri

Lasīt pārējo šī ieraksta daļu »


Analītiskās funkcijas

augusts 21, 2008

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

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

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

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

Partition by un Order by klauzu vizualizācija

Lasīt pārējo šī ieraksta daļu »


Oracle Merge teikuma sintakse

jūnijs 9, 2008

Pārējos rakstus var lasīt SQL pamatos.

Pēc pāris intermēdijām par to kā uzdot saprātīgu jautājumu un stāstu par to, ka vajadzētu padomāt kādus datus glabājam pie sevis un kādus dodam citiem internetā, paskatīsimies uz mazliet sausākām lietām. Oracle Merge SQL teikumu ir nodrošinājusi kopš 9i versijas, kas tika izlaista jau diezgan tālajā 2001 gadā. Sākumā tajā bija tikai UPDATE un INSERT klauzas, pie tam abas bija obligātas. 2003 gadā līdz ar versiju 10g MERGE funkcionalitāte tika papildināta pieļaujot neobligātu UPDATE, DELETE un INSERT. 11g versijā nekādi būtiski papildinājumi klāt nav nākuši.
Oracle Merge sintakse lielā mērā ir līdzīga SQL Server Merge sintaksei (kas vēl produkcijā īsti nav), bet tai ir savas atšķirības:
1) Nekur neparādās atslēgas vārdi TARGET un SOURCE;
2) Ierakstu dzēšana (Delete) ir zem Update operācijas, kas arī fiziski nozīmē, ka ieraksti vispirms tiek koriģēti un pēc tam iespējams izdzēsti, ja nosacījums to pieļauj.

Tātad sintakse ir šāda:

MERGE INTO <mērķa tabula>
USING <izejas dati>
ON (<salīdzināšanas nosacījums> )
WHEN MATCHED THEN <darbības, ja nosacījums patiess>
WHEN NOT MATCHED THEN <darbības, ja nosacījums aplams> Lasīt pārējo šī ieraksta daļu »