Relationsdatabaser är något många av oss kommer i kontakt med på en daglig basis. Behöver du spara strukturerad data mellan körningar av en mobilapp? Både Android och iPhone har SQLite-databaser inbäddade i operativsystemet. Vill du mellanlagra data från en klientsides-webbapp innan du skickar det till servern? Moderna webbläsare har inbyggda SQL-databaser för ändamålet.
Relationsdatabaser har dock blivit mer och mer komplexa med tiden. Att ha goda kunskaper om deras möjligheter och begränsningar kan vara avgörande för din applikations prestanda. I denna artikel ska vi titta närmare på några mer eller mindre ovanliga egenskaper och funktioner som kan vara till användning för att förbättra prestanda, öka pålitligheten på ditt data eller öka läsligheten på din SQL-kod.
Alla exempel är i Postgres-dialekten av SQL om inget annat anges.
0. Exekveringsordning för SELECT-satser
Frågor i SQL kan något förenklat sägas exekveras enligt följande ordning:
- FROM
- WHERE
- GROUP BY
- HAVING
- OVER
- SELECT
- ORDER BY
- DISTINCT
Denna exekveringsordning är viktig att ha i åtanke när man planerar, skriver och inte minst optimerar SQL-frågor. En av grundprinciperna för att åstadkomma snabba frågor är att minimera antalet rader som frågor måste arbeta med. Har man en fråga som returnerar ett stort antal rader bör man undvika att göra tidsödande arbete i SELECT-satsen. Har man komplexa villkor i WHERE-satsen bör man försöka filtrerar ut rader innan de når denna sats, vilket vi återkommer till i punkt 2.
1. Case … when … then
I vissa fall kan det vara önskvärt att ändra resultatsdatatypen på en svarskolumn baserat på olika villkor. Exempelvis kan man göra som i nedanstående exempel:
SELECT CASE WHEN cost < 10 THEN 'Low' WHEN cost > 10 AND cost < 90 THEN 'Medium' ELSE 'High' END FROM inventoryTable;
Här översätts cost-värden under 10 till strängen “Low”, cost-värden över 90 till “High” och allt däremellen till “Medium”.
Case-satser är en grundläggande funktionalitet i SQL, men är värd att ta upp då många missar den och göra liknande omvandlingar i applikationskoden istället.
2. Join conditions
Att använda villkor i JOIN-satsen är en enkel men kraftfull teknik som dock är lätt att missa. Det kan se ut som i följande exempel:
SELECT FROM employeeTable as et JOIN salaryTable as st on et.id = st.employeeId AND et.id > 9000 WHERE et.lastname LIKE ‘A%’;
I detta exempel frågar vi efter id, namn och löner för alla anställda vars efternamn börjar på A och vars anställnings-id är över 9000. Här hade vi kunnat placera et.id > 9000 inuti WHERE-satsen istället, men genom att placera den i FROM-satsen som ett villkor för en JOIN-operation så har vi på så sätt minskat urvalet av rader till den mängd av rader vars id är över 9000 innan vi kontrollerar villkoret om efternamnets stavning. I de fall då WHERE-satsens villkor är särskilt krävande, exempelvis om de gör subselects för varje rad, så kan detta spara mycket tid och processorcykler.
3. Constraints
Constraints är en funktionalitet som ofta används för att exempelvis se till att kolumner i en tabell inte tillåter NULL-värden genom nyckelorden NOT NULL eller för att förhindra duplicerade värden från att sättas in genom nyckelordet UNIQUE. Det finns dock fler användbara egenskaper hos constraints.
Unique constraints kan användas för att förhindra att man försöker återanvända värden i en kolumn på flera rader. Anta att vi har följande tabell:
CREATE TABLE userTable ( id serial NOT NULL, username character varying(50), email character varying(50), organization integer, CONSTRAINT pk_id PRIMARY KEY (id) );
I vårt användningsfall vill vi tillåta att användare har samma användarnamn om de tillhör olika organisationer. Men om vi använder unique constraints på följande sätt:
CREATE TABLE userTable ( id serial NOT NULL, username character varying(50) UNIQUE, email character varying(50), organization integer, CONSTRAINT pk_id PRIMARY KEY (id) );
Så kommer det inte vara möjligt för två användare att ha samma användarnamn även om det är i olika organisationer, då dessa constraints tillämpas för alla enskilda username kolumner i tabellen.
Om vi istället skriver som följer:
CREATE TABLE userTable ( id serial NOT NULL, username character varying(50), email character varying(50), organization integer, CONSTRAINT pk_id PRIMARY KEY (id), UNIQUE (username, organization) );
Så blir det istället kombinationen av username och organization som används vid kontroller av duplicerade värden.
Vi kan även namnge våra constraints för att tydliggöra deras intentioner:
CREATE TABLE userTable ( id serial NOT NULL, username character varying(50), email character varying(50), organization integer, CONSTRAINT pk_id PRIMARY KEY (id), UNIQUE unique_names_within_organization (username, organization) );
En annan form av constraint, check, ger oss ännu större möjligheter att kontrollera vilka värden som är tillåtna för en kolumn:
CREATE TABLE salaryTable ( id serial NOT NULL PRIMARY KEY, salary integer NOT NULL CHECK (salary > 0), employeeId integer NOT NULL );
I exemplet tillåter vi endast positiva värden på salary-kolumnen. Vi kan även för denna constraint lägga till ett namn:
CREATE TABLE salaryTable ( id serial NOT NULL PRIMARY KEY, salary integer NOT NULL, employeeId integer NOT NULL, CONSTRAINT positive_salaries_only CHECK (salary > 0) );
Check constraints kan liksom unique constraints hantera flera olika kolumner.
4. Window functions
Window functions kan något förenklat beskrivas som aggregatfunktioner som istället för att utföras på alla rader i resultatet utförs på grupperade delmängder av resultatet. Som synes i exekveringsordningen så utförs de efter from, where, group by och having. Ett exempel på användningsområde är denna tabell över anställda på ett företag:
id| salary| department 1 | 23000 | software 2 | 21000 | software 3 | 24000 | software 4 | 26000 | hardware 5 | 25500 | hardware 6 | 43000 | admin 7 | 45000 | admin
Om man vill ta reda på medellönen för hela företaget så kan man ställa följande fråga:
SELECT id, dept, salary, (SELECT avg(salary) FROM empl) FROM empl; 1 | software | 23000 | 29642.857142857143 2 | software | 21000 | 29642.857142857143 3 | software | 24000 | 29642.857142857143 4 | hardware | 26000 | 29642.857142857143 5 | hardware | 25500 | 29642.857142857143 6 | admin | 43000 | 29642.857142857143 7 | admin | 45000 | 29642.857142857143
Vill man däremot ta reda på den genomsnittliga lönen i varje avdelning så kan man använda window functions enligt följande:
SELECT dept, id, salary, avg(salary) OVER (PARTITION BY dept) FROM empl ORDER BY id; software | 1 | 23000 | 22666.666666666667 software | 2 | 21000 | 22666.666666666667 software | 3 | 24000 | 22666.666666666667 hardware | 4 | 26000 | 25750.000000000000 hardware | 5 | 25500 | 25750.000000000000 admin | 6 | 43000 | 44000.000000000000 admin | 7 | 45000 | 44000.000000000000
Nyckelorden partition by används här för att gruppera på avdelningsnamnet (dept) vilken sedan skickas till aggregatfunktionen avg som beräknar medelvärdet av de grupperade raderna. För varje rad i resultatet används sedan resultatet av operationen som gjorts på alla rader med det gemensamma attributet, i detta fall avdelningsnamnet.
Vi kan även använda order by-satsen inuti over-satsen för att sortera innehållet inom grupperingen. Detta kan exempelvis låta oss rangordna resultaten inom varje gruppering.
Lästips och avslutande reflektion
Avslutningsvis kan man konstatera att relationsdatabaser kommer att fortsätta spela en central roll inom mjukvaruutvecklingen framöver, inte minst då fler och fler verksamheter får upp ögonen för värdet av sitt data och för att hitta nya sätt att sammanställa datat för att öka effektiviteten, hitta nya produkter eller förbättra befintliga tjänster.
För den som vill läsa mer om praktisk använding av SQL kan jag rekommendera dokumentationen för PostgreSQL, som är både välskriven och koncis. I bokform finns dels den omfattande Database in Depth: Relational Theory for Practitioners av C.J. Date eller den mer uppgiftsfokuserade SQL Cookbook av Anthony Molinaro.
Missa inte senaste blogginläggen – Ta del av vårt nyhetsbrev!
