SQL — STRING_AGG

Zpět na blog

SQL — STRING_AGG

sql string_agg agregační funkce

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
 ....        | .....


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

Líbil se vám článek? Sdílejte ho s ostatními

nebo nám napište něco hezkého. Děkujeme!

Zpět na blog

Power BI - Základy

I business uživatel může být datový analytik. Zjednodušte si práci s daty díky Power BI.

Zobrazit kurz

SQL pro analytiky 2 - pokročilí

Naučte se vytvářet složité dotazy, databázové objekty a používat pokročilé funkce jazyka SQL.

Zobrazit kurz

Vizualizace pro analytiky

Matplotlib, Pandas a Seaborn. Naučte se vizualizovat a prezentovat data v Pythonu.

Zobrazit kurz

Hledáme další autory

Publikujte na Lovely Blogu a inspirujte ostatní! Sdílením svých znalosti si budujete osobní značku.

Kontaktujte nás

Odběr novinek

Novinky, návody a tipy přímo do vašeho emailu.

Copyright © 2018-2024, Colorbee, s.r.o.

Designed by grafikli.cz in Prague.