SQL — STRING_AGG

SQL disponuje méně známými funkcemi, které jsou však velmi užitečné. Některé z nich natolik, že když se je naučíte používat, budete se divit, jak jste se bez nich mohli obejít.

Jednou z takových funkcí je STRING_AGG, který má podle názvu asi něco společného s agregací. Jak moc, si vyzkoušíte v tomto článku.

Pokud nemáte databázový server nainstalovaný lokálně, můžete využít SQL v cloudu. Např. sqliteonline.com nebo sqlfiddle.com.

Agregační funkce

O některých SQL agregačních funkcích jsme už na LovelyData psali - např. zde nebo zde. Pokud vás zajímá efektivní používání SQL v praxi, rozhodně doporučujeme přečíst.

Co umí STRING_AGG

Agregační funkce STRING_AGG spojí řetězce (text) a přidá mezi ně oddělovač. Laicky řečeno, pokud potřebujete hodnoty v řádcích dostat do jednoho sloupce, STRING_AGG se vám bude hodit.

Tuto funkci obsahují všechny standardní databáze - SQL Server nebo PostgreSQL. MySQL pro ni používá jiný název - GROUP_CONCAT. A aby se to nepletlo, tak jí v Oracle můžete najít jako LISTAGG.

Protože příklady vydají za tisíc slov, necháme teorii teorií a rovnou si vše vyzkoušíte.

Vytvoření databáze

Nejdřív si vytvořte databázi a tabulky. Jako testovací data použijte tuto vzorovou databázi pro SQL Server.

-- SQL Server

CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR(10),
  job VARCHAR(9),
  mgr INT NULL,
  hiredate DATETIME,  -- TIMESTAMP(0) pro PostgreSQL
  sal NUMERIC(7,2),
  comm NUMERIC(7,2) NULL,
  dept INT
);

insert into emp values (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);
insert into emp values (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3);
insert into emp values (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3);
insert into emp values (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3);
insert into emp values (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4);
insert into emp values (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4);
insert into emp values (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4);
insert into emp values (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2);
insert into emp values (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4);
insert into emp values (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2);
insert into emp values (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2);
insert into emp values (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4);
insert into emp values (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2);
insert into emp values (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1);

CREATE TABLE dept (
  deptno INT NOT NULL,
  dname VARCHAR(14),
  loc VARCHAR(13)
);

insert into dept values (1,'ACCOUNTING','ST LOUIS');
insert into dept values (2,'RESEARCH','NEW YORK');
insert into dept values (3,'SALES','ATLANTA');
insert into dept values (4, 'OPERATIONS','SEATTLE');

Seznam zaměstnanců, oddělený čárkou

Asi nejjednodušší příklad, který krásně ilustruje funkci STRING_AGG.

-- Seznam zaměstnanců, oddělený čárkou
SELECT STRING_AGG(ename, ', ') AS "Jméno"
FROM emp
;

 Jméno
-------
 JOHNSON, HARDING, TAFT, HOOVER, LINCOLN, GARFIELD, POLK, GRANT, JACKSON, FILLMORE, ADAMS, WASHINGTON, MONROE, ROOSEVELT

Seznam zaměstnanců podle roku nástupu

Následující SQL příkaz vrátí seznam zaměstnanců oddělených čárkou podle roku, kdy nastoupili do pracovního poměru. Tady už budete potřebovat klauzuli GROUP BY.

SELECT YEAR(hiredate) AS "Rok nástupu",
  STRING_AGG(ename, ', ') AS "Jméno"
FROM emp
GROUP BY YEAR(hiredate)
;

 Rok nástupu | Jméno
------------ | ------
 1990        | JOHNSON, HOOVER, JACKSON
 1993        | GARFIELD
 1994        | FILLMORE, LINCOLN
 1995        | ROOSEVELT
 1996        | TAFT, ADAMS
 ....        | .....

SQL pro každý den

Jak si užít SQL a nezabloudit v kódu. Nebaví vás znovu vynalézat kolo? Inspirujte se SQL příklady z praxe nebo je klidně okopírujte! Šetřete svůj čas při každodenních analytických úkolech.

Prozkoumat

Výsledek je sice správně, ale jména nejsou seřazená podle abecedy. To se dá snadno napravit pomocí ORDER BY a následující úpravy.

SELECT YEAR(hiredate) AS "Rok nástupu",
  STRING_AGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS "Jméno"
FROM emp
GROUP BY YEAR(hiredate)
;

 Rok nástupu | Jméno
------------ | ------
 1990        | HOOVER, JACKSON, JOHNSON
 1993        | GARFIELD
 1994        | FILLMORE, LINCOLN
 1995        | ROOSEVELT
 1996        | ADAMS, TAFT
 ....        | .....

Profese v jednotlivých odděleních

Jaké profese jsou v jednotlivých odděleních? Zkuste si nejdřív naivní přístup.

SELECT d.dname,
  STRING_AGG(e.job, ', ') AS job
FROM emp e INNER JOIN dept d
  ON e.dept = d.deptno
GROUP BY d.dname
;

 dname      | job
----------- | ----
 ACCOUNTING | CPA
 OPERATIONS | ADMIN, TECH, MANAGER, TECH, CEO, ADMIN
 RESEARCH   | ENGINEER, MANAGER, ENGINEER, ENGINEER
 SALES      | MANAGER, SALES I, SALES I

Proč naivní? Pohledem na výsledky dotazu zjistíte, že se některé profese opakují. A to nechcete. K lepšímu výsledku pomůže DISTINCT.

Poznámka: Tady se jednotlivé SQL dialekty liší. Pro PostgreSQL stačí jen přidat DISTINCT a je hotovo.

-- Funguje pro PostgreSQL
SELECT d.dname,
  STRING_AGG(DISTINCT e.job, ', ') AS job
FROM emp e INNER JOIN dept d
  ON e.dept = d.deptno
GROUP BY d.dname
;

Pro SQL Server je to trochu komplikovanější a vyžaduje to mezikrok.

-- SQL Server
WITH base_query AS (
  select DISTINCT d.dname, e.job
  FROM emp e INNER JOIN dept d
      ON e.dept = d.deptno
)
SELECT dname, STRING_AGG(job, ', ') AS job
FROM base_query
GROUP BY dname
;

 dname      | job
----------- | ----
 ACCOUNTING | CPA
 OPERATIONS | ADMIN, CEO, MANAGER, TECH
 RESEARCH   | ENGINEER, MANAGER
 SALES      | MANAGER, SALES I

Kde pracují manažeři

V jakých odděleních pracují manažeři?

Všimněte si, že se tentokrát jako oddělovač použilo lomítko.

SELECT e.job, STRING_AGG(d.dname, '/') AS 'Oddělení'
FROM emp e INNER JOIN dept d
  ON e.dept = d.deptno
WHERE e.job = 'MANAGER'
GROUP BY e.job
;

 job     | Oddělení
-------- | ---------
 MANAGER | RESEARCH/SALES/OPERATIONS