- Guna Kaņepe
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!