Tabulu saistīšana ar SQL datubāzi

Lai varētu sekot šim rakstam, ir nepieciešams, lai uz datora būtu instalēts SQL Server Express, kā arī SQL Server Management Studio (SSMS), lai lietotājs spētu tajā izveidot datubāzi un mācētu veikt vaicājumus. Tas tika apskatīts iepriekšējā rakstā.

Tagad aplūkosim, kā ar SQL sasaistīt vairākas tabulas.

Izmantosim datus par starppilsētu autobusu kustību, kurus var atrast:

https://data.gov.lv/dati/lv/dataset/atd-gtfs

Datus var lejuplādēt un līdzīgi, kā jau iepriekš aprakstīts, importēt SQL Server datubāzē. Par cik tie var mainīties, raksts ir balstīts uz jau sagatavotu datubāzi no:

https://github.com/gjdata/bus/raw/master/autobus.zip.

Šī datubāze turpretī netiek atjaunināta un paredzēta vienīgi šim rakstam. To var lejuplādēt, atarhivēt un importēt, izmantojot SQL Server Managment Studio – ar labo pogu uz Databases, izvēlamies Restore Database, dialogā izvēlamies radiopogu Device, spiežam pogu "...", nākošajā dialogā pogu Add, izvēlamies atarhivēto failu autobus.bak, un Ok, Ok, Ok.

Skat. attēlā!

Šajā datubāzē ir vairākas, savstarpēji saistītas tabulas. Apskatīsim sekojošās:

stops – Pieturas, kurās uzņem vai izlaiž pasažierus.

routes – Maršruti. Maršruts ir braucienu grupa, kas tiek piedāvāta kā viens pakalpojums.

trips – Braucieni. Brauciens ir divu vai vairāku pieturu virkne specifiskā laika periodā.

stop_times – Laiki, kuros transportlīdzeklis iebrauc pieturās un izbrauc no tām.

calendar – Pakalpojumi ar nedēļas grafiku, sākuma un beigu datumiem.

Importētajā datubāzē izpildīsim sekojošu SQL vaicājumu:

-- Dati par vienu konkrētu braucienu:
 SELECT * FROM trips
  WHERE trip_id = 49272

Ar WHERE palīdzību var atfiltrēt tikai tos ierakstus, kuri atbilst kadam nosacījumam. Iegūstam:

Kā redzams, ieraksts no tabulas trips satur tikai skaitļus. Šie skaitļi ir vienādi ar citu tabulu ierakstu primārajām atslēgām – identifikatoriem. Informāciju no citām tabulām var piesaistīt izmantojot JOIN … ON …

Veicot vaicājumus no vairākām tabulām, ir vēlams specificēt – no kuras tabulas nāk kurš lauks. To dara pievienojot tabulas nosaukumu pirms punkta, piemēram: routes.route_long_name

Tā ir vēlams darīt, jo dažādās tabulās var but lauki ar vienādiem nosaukumiem, piemēram: routes.route_id un trips.route_id

Mēs gribam arī uzzināt, vai dotajā dienā šis brauciens notiek. Tādu informāciju var iegūt no tabulas calendar, izmantojot lauku service_id.

-- Uzzināt informāciju par braucienu:

SELECT trips.trip_id, routes.route_long_name, calendar.*

    FROM trips

        JOIN routes ON routes.route_id = trips.route_id

        JOIN calendar ON calendar.service_id = trips.service_id

    WHERE trips.trip_id = 49272

Tā kā maršruts ir braucienu grupa, tad viens un tas pats maršruta nosaukums var būt vairākiem braucieniem, tāpēc tas tiek glabāts atsevišķi un ir piesaistīts ar identifikatoru. Šādā gadījumā, kad informācija glabājas tikai vienā vietā, tad saka – dati ir normalizēti.

Divi galvenie ieguvumi normalizējot:

1. Datus ir viegli rediģēt. Nav jāraizējas, vai veicot izmaiņas vienā vietā, kādā citā nepaliks vecā vērtība.

2. Tiek samazināts kopējais datu izmērs un tas uzlabo ātrdarbību, jo vairāk dažādu datu var ielasīt operatīvajā atmiņā.

Līdzīgi, savukārt, vienam braucienam atbilst vairāki apstāšanās laiki pieturās. Lai tos redzētu pareizā secībā, ar ORDER BY ir jānorāda ierakstu kārtošana pieaugoši pēc kārtas numura.

 -- Uzzināt brauciena visus apstāšanās laikus:  

SELECT stop_times.*   

FROM stop_times 

WHERE stop_times.trip_id = 49272   

ORDER BY stop_times.stop_sequence ASC

Bet apstāšanās laiks vēl nav pati pietura! Vienai pieturai atbilst daudzi apstāšanās laiki. Piesaistīsim arī pieturas nosaukumu.

-- Uzzināt brauciena apstāšanās laikus un pieturu datus: 
 SELECT stop_times.*, stops.stop_name, stops.stop_lat, stops.stop_lon
  FROM stop_times JOIN stops ON stop_times.stop_id = stops.stop_id
  WHERE stop_times.trip_id = 49272
  ORDER BY stop_times.stop_sequence ASC

Apvienojot to ar agrāko vaicājumu, iegūstam pilnu informāciju: par braucienu un tā pieturām:

Saistītas tabulas uzskatāmi var attēlot ar Entity-Relationship (ER) diagrammām. Tām ir dažādi pieraksti, mēs izmantosim “vārnu kāju” simbolus.

Šajā diagrammā redzamas visas līdz šim lietotās tabulas, to lauki un saites starp tām. Redzams, ka ar JOIN viegli var savienot tabulas, kuras diagrammā ir savienotas ar līniju. Lai struktūra būtu skaidrāka, savienojamiem laukiem parasti ir doti vienādi vai līdzīgi nosaukumi. Viens no tiem ir primārā atslēga Primary Key (PK), otrs ir ārējā atslēga Foreign Key (FK). Kā jau minēts iepriekšējā rakstā, primārā atslēga ir unikāla. Tāpēc vienam ierakstam no PK puses atbilst nulle, viens vai vairāki ieraksti FK pusē.

Mēs apskatījām, kā iegūt informāciju par vienu konkrētu braucienu. No vaicājuma, izņemot nosacījumu WHERE stop_times.trip_id = 49272, varētu iegūt visu braucienu sarakstu.

Lai orientētos, var lietot citu filtru, izmantojot saistītu tabulu:

-- Visi braucieni maršrutā Rīga-Cēsis

SELECT trips.trip_id, routes.route_long_name

    FROM trips 

       JOIN routes ON routes.route_id = trips.route_id

    WHERE route_long_name = N'Rīga-Cēsis'
  

Tomēr tie vēl nebūs visi braucieni no Rīgas autoostas uz Cēsu autoostu.

Pilnīgākus un vispārīgākus rezultātus var iegūt izmantojot pieturas.

-- Braucienu identifikatori - no Rīgas autoostas uz Cēsu autoostu: 

SELECT destination.trip_id

    FROM stop_times AS origin

    JOIN stop_times AS destination

        ON destination.trip_id = origin.trip_id

    WHERE origin.stop_sequence < destination.stop_sequence

        AND destination.stop_id = 11292

        AND origin.stop_id = 11382

Mēs meklējam visus tādus pieturlaiku pārus, kuriem sakrīt trip_id un kuriem pirmā pieturlaika kārtas numurs ir pirms otrā. Šajā vaicājumā divreiz izmantota tā pati tabula, saistot to pašu ar sevi un katrai saites pusei dodot iesauku ar AS. Šīs iesaukas ir origin un destination.

Rezultātā iegūstam tikai identifikatorus. Bet mēs jau apskatījām, kā brauciena identifikatoram piesaistīt informāciju. Šajā gadījumā tabulas trips vietā ir jāņem viss iepriekšējais vaicājums, liekot to iekavās un pēc iekavām rakstot iesauku

-- Visi braucieni no Rīgas autoostas uz Cēsu autoostu:

SELECT riga_cesis.trip_id, routes.route_long_name
  FROM (

        SELECT destination.trip_id

            FROM stop_times AS origin

                JOIN stop_times AS destination ON destination.trip_id = origin.trip_id

            WHERE origin.stop_sequence < destination.stop_sequence

                AND destination.stop_id = 11292

                AND origin.stop_id = 11382

) AS riga_cesis
  JOIN trips ON trips.trip_id = riga_cesis.trip_id
  JOIN routes ON routes.route_id = trips.route_id

Līdz ar to ir apskatītas galvenās konstrukcijas un daži piemēri, kā iegūt informāciju no vairākām saistītām tabulām. Šīs ir būtiskas zināšanas, lai varētu sekmīgi lietot SQL datu analīzei!