Sträng-aggregering med SQL

SQL

Många av oss Javautvecklare har en förkärlek till att göra relativt generella SQL-frågor för att sedan förfina resultatet på Java-sidan. Är svarstider inte av yttersta vikt och det inte gör något att systemen tuggar i sig ett par CPU-cykler extra kan det vara svårt att motivera sig att bemästra SQL lite bättre. Ibland händer det dock, och därför ska vi här tipsa om ett sätt att aggregera data på SQL-sidan.

SQL-funktionen

Funktionen, som finns inbyggd i nyare databaser, heter string_agg i PostgreSQL (9.0+) samt MS SQL Server (2017+) och LISTAGG i Oracle (11G+). Det denna funktion gör är att den aggregerar matchande resultat på en rad med en valfri separator, t.ex. “;”. Syftet är att reducera mängden redundant data i resultatet redan på SQL-server sidan. Därmed nyttjas optimerade funktioner i SQL, mängden data som behöver skickas minskas och sist men inte minst förenklas Java-koden.

Uppsättning

För att demonstrera funktionen behövs en enkel tabell med data. Nedan beskrivs en minimalistisk modell med anställda som hör till en avdelning hos sin arbetsgivare. Notera att all syntax i detta inlägg är skrivet mot en PostgreSQL-databas. Vissa justeringar är nödvändiga för att det ska fungera mot en Microsoft eller Oracle databas.

CREATE TABLE employees(l_name varchar(100) PRIMARY KEY, department integer);

INSERT INTO employees(l_name, department) VALUES ('Backstrom', 1);
INSERT INTO employees(l_name, department) VALUES ('Eliasson', 2);
INSERT INTO employees(l_name, department) VALUES ('Strindberg', 1);
INSERT INTO employees(l_name, department) VALUES ('Ranelid', 2);
INSERT INTO employees(l_name, department) VALUES ('Lackberg', 3);
INSERT INTO employees(l_name, department) VALUES ('Ahlberg', 2);

Listning av innehållet i tabellen ger följande:

SELECT * FROM employees;
l_name department
Backstrom 1
Eliasson 2
Strindberg 1
Ranelid 2
Lackberg 3
Ahlberg 2


Inga konstigheter här, allt innehåll i listan presenteras rakt upp och ner.

SQL-aggregering

Det är relativt vanligt att nästa steg blir att skriva en snutt Java-kod som t.ex. grupperar anställda per avdelning. Alternativet är att låta SQL aggregera datat istället vilket är den approach som väljs här.

SELECT e.department, 
  string_agg(e.l_name, ';' ORDER BY e.l_name)
FROM employees AS e
GROUP BY e.department;

string_agg(e.l_name, ‘;’ ORDER BY e.l_name) innebär att alla rader som uppfyller ett senare kriterium ska sammanfogas med ett ; som separator. Vidare skall värdet i fältet l_name sorteras A-Z (ascending, vilket är default i SQL). Funktionen i sig själv fungerar inte då den inte vet på vilket fält som grupperingen ska ske. Det etableras senare med GROUP BY e.department, d.v.s. gruppera per avdelning. Resultatet blir då:

department string_agg
1 Backstrom;Strindberg
2 Ahlberg;Eliasson;Ranelid
3 Lackberg


Ett resultat på endast tre rader kontra tidigare sex och det är nu en smal sak att lista alla anställda per avdelning.

Slutsats

String_agg i PostgreSQL eller MS SQL Server samt LISTAGG i Oracle DB är inga silver-bullets, men de kan helt klart göra vardagen lite enklare och reducera lasten mellan noder. Framförallt om det är stora resultsets med höga nivåer av redundant data per rad. Prova och utvärdera.

Hör gärna av er till oss med era erfarenheter!

Vi har mottagit ditt meddelande och återkommer inom kort.
Hoppsan! Något gick dessvärre fel, vänligen verifiera att du inte är en robot eller ladda om sidan och försök igen.
Vi vill göra dig uppmärksam på att vi behandlar dina uppgifter i strikt enlighet med vår Integritetspolicy. Allt för att du ska känna dig trygg i att vi värnar om din integritet.