7. Enkel SQL

SQL, eller ”Structured Query Language”, är ett sätt att ställa frågor till och uppdatera data i relationsdatabaser. Du har redan sett SQL när vi skapade vår första databas. Kom ihåg det:

SELECT postgis_full_version();

Men det var en fråga om databasen. Nu när vi har laddat in data i vår databas kan vi använda SQL för att ställa frågor om data! Ett exempel,

”Vad heter alla stadsdelar i New York City?”

Öppna SQL-frågefönstret i pgAdmin genom att klicka på knappen ”Query Tool”.

ange sedan följande fråga i frågefönstret

SELECT name FROM nyc_neighborhoods;

och klicka på knappen Execute Query (den gröna triangeln).

Frågan körs i några (milli)sekunder och returnerar de 129 resultaten.

Men vad var det egentligen som hände här? För att förstå det måste vi börja med de fyra ”verben” i SQL,

• SELECT, returnerar rader som svar på en fråga

• INSERT, lägger till nya rader i en tabell

• UPDATE, ändrar befintliga rader i en tabell

• DELETE, tar bort rader från en tabell

Vi kommer nästan uteslutande att arbeta med SELECT för att ställa frågor om tabeller med hjälp av spatiala funktioner.

7.1. SELECT-frågor

En select-fråga är i allmänhet av formen:

SELECT some_columns FROM some_data_source WHERE some_condition;

Observera

För en sammanfattning av alla SELECT parametrar, se PostgresSQL-dokumentationen.

some_columns är antingen kolumnnamn eller funktioner av kolumnvärden. some_data_source är antingen en enskild tabell eller en sammansatt tabell som skapats genom att två tabeller sammanfogats med en nyckel eller ett villkor. some_condition är ett filter som begränsar antalet rader som ska returneras.

”Vad heter alla stadsdelar i Brooklyn?”

Vi återvänder till vår tabell nyc_neighborhoods med ett filter i handen. Tabellen innehåller alla stadsdelar i New York, men vi vill bara ha dem i Brooklyn.

SELECT name
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

Frågan kommer att köras under ännu färre (milli)sekunder och ge 23 resultat.

Ibland behöver vi tillämpa en funktion på resultatet av vår fråga. Det kan till exempel vara

”Vad är antalet bokstäver i namnen på alla stadsdelar i Brooklyn?”

Lyckligtvis har PostgreSQL en stränglängdsfunktion, char_length(string).

SELECT char_length(name)
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

Ofta är vi mindre intresserade av de enskilda raderna än av en statistik som gäller för dem alla. Att veta längden på grannskapsnamnen kan alltså vara mindre intressant än att veta den genomsnittliga längden på namnen. Funktioner som tar in flera rader och returnerar ett enda resultat kallas ”aggregerade” funktioner.

PostgreSQL har en serie inbyggda aggregerade funktioner, inklusive det allmänna syftet avg() för medelvärden och stddev() för standardavvikelser.

”Vad är det genomsnittliga antalet bokstäver och standardavvikelsen för antalet bokstäver i namnen på alla stadsdelar i Brooklyn?”

SELECT avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

         avg         |       stddev
---------------------+--------------------
 11.7391304347826087 | 3.9105613559407395

De aggregerade funktionerna i vårt förra exempel tillämpades på varje rad i resultatuppsättningen. Vad händer om vi vill att sammanfattningarna ska utföras över mindre grupper inom den totala resultatuppsättningen? För det lägger vi till en GROUP BY-klausul. Aggregerade funktioner behöver ofta en extra GROUP BY-sats för att gruppera resultatuppsättningen efter en eller flera kolumner.

”Vad är det genomsnittliga antalet bokstäver i namnen på alla stadsdelar i New York City, redovisat per stadsdel?”

SELECT boroname, avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  GROUP BY boroname;

Vi inkluderar kolumnen boroname i utdataresultatet så att vi kan avgöra vilken statistik som gäller för vilken stadsdel. I en aggregerad fråga kan du bara mata ut kolumner som antingen är (a) medlemmar i grupperingsklausulen eller (b) aggregerade funktioner.

   boroname    |         avg         |       stddev
---------------+---------------------+--------------------
 Brooklyn      | 11.7391304347826087 | 3.9105613559407395
 Manhattan     | 11.8214285714285714 | 4.3123729948325257
 The Bronx     | 12.0416666666666667 | 3.6651017740975152
 Queens        | 11.6666666666666667 | 5.0057438272815975
 Staten Island | 12.2916666666666667 | 5.2043390480959474

7.2. Funktionslista

avg (uttryck): PostgreSQL-aggregatfunktion som returnerar medelvärdet för en numerisk kolumn.

char_length (sträng): PostgreSQL-strängfunktion som returnerar antalet tecken i en sträng.

stddev(uttryck): PostgreSQL-aggregatfunktion som returnerar standardavvikelsen för ingångsvärden.