Při trochu pokročilejších databázových dotazech jsou často využívané korelované dotazy, kde výstup jednoho dotazu poskytuje datovou množinu pro další dotaz. Nejsou velmi efektivní, při požadavku na jejich rychlost je nezřídka nutné mít vytvořený i jinak zcela nelogický index a často je lze nahradit mnohem „čistším“ JOINem. Další možnost nabízí Common Table Expressions (dále jen CTE), které jsou zatím opomíjené.
CTE definuje SQL:1999 (pdf), funkčnost je podporována v PostgreSQL, umí je FirebirdSQL od verze 2.1, MariaDB (10.2.1 a novější) i MySQL počínaje verzí 8. Překvapivě, vzhledem k minimalističnosti, umí CTE i SQLite. Syntaxe by měla být stejná, či podobná, vzhledem k tomu, že je používám především v PgSQL, jsou příklady právě pro tento server. Komerční databáze (MSSQL, Oracle, …) by měli CTE nabízet také, ale bohužel ji nemám kde prakticky ověřit.
CTE lze, zjednodušeně, popsat jako vytvoření množiny dočasných pohledů pomocí SELECTů. Dočasné pohledy jsou vytvářeny v pořadí, jak jsou za sebou napsány a výstupní hodnoty z jednoho dotazu lze použít při vytváření dalšího (avšak nesmí se zapomenout na pořadí jejich vytváření). CTE lze použít i pro rekurzivní dotazy.
V následujících příkladech jsou vynechány indexy a nedůležité atributy.
Common Table Expressions (CTE) pro nerekurzivní SELECT
Nejtriviálnější použití CTE, které nabízí dosti výhod.
Definice tabulek:
Tabulka měřících přístrojů
CREATE TABLE meters
(
meters_id serial NOT NULL,
locations_id integer NOT NULL,
metersr character varying(14),
metername character varying(10),
metercom smallint,
active boolean NOT NULL,
firstvalue double precision,
CONSTRAINT pk_meters PRIMARY KEY (meters_id)
)
WITH (
OIDS=FALSE
);
INSERT INTO meters(meters_id, locations_id, metersr, metername, metercom, active, firstvalue) VALUES
(9, 1, '1234567890','main', 1, TRUE, 0),
(10, 1, '12345a789aa','cooling',2, TRUE, 15889324),
(11, 1, '000888256','lights', 3, TRUE,0);
Tabulky naměřených dat
Data z měřících přístrojů se ukládají do tabulek, jejichž definice je shodná s registry měřícího přístroje. Některý typ poskytuje jinou strukturu dat.
CREATE TABLE meter1data
(
meter1data_id serial NOT NULL,
meters_id integer NOT NULL,
recordtime timestamp without time zone NOT NULL,
l1u real DEFAULT 0,
l2u real DEFAULT 0,
l3u real DEFAULT 0,
l1i real DEFAULT 0,
l2i real DEFAULT 0,
l3i real DEFAULT 0,
l1ap real DEFAULT 0,
l2ap real DEFAULT 0,
l3ap real DEFAULT 0,
CONSTRAINT pk_data1 PRIMARY KEY (recordid),
CONSTRAINT meters_fk FOREIGN KEY (meterid_meters)
REFERENCES meters (meterid) MATCH FULL
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
CREATE TABLE meter2data
(
meter2data_id serial NOT NULL,
meterid_meters integer NOT NULL,
recordtime timestamp without time zone NOT NULL,
powerf real,
u12 real,
u31 real,
u32 real,
l1_v real,
l2_v real,
l3_v real,
t1crp_q1 double precision,
t1crp_q2 double precision,
t1crp_q3 double precision,
t1crp_q4 double precision,
t1crp_s double precision,
l1_c real,
l2_c real,
l3_c real,
mta1 smallint,
mta2 smallint,
t1cap_s double precision,
t1cap_p double precision,
CONSTRAINT pk_data2 PRIMARY KEY (recordid),
CONSTRAINT meters_fk FOREIGN KEY (meterid_meters)
REFERENCES meters (meterid) MATCH FULL
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
INSERT INTO meter1data(
"meter1data_id", "recordtime", "l1u", "l2u", "l3u", "l1i", "l2i", "l3i",
"l1ap","l2ap","l3ap"
) VALUES
(9, '2017-04-05 21:00:00', 235.15, 240.0, 237.9, 1.5, 0.8, 15.1, 270.0, 151.7, 2895.5),
(9, '2017-04-05 21:15:00', 238.15, 240.0, 237.9, 1.0, 0.8, 15.1, 217.0, 151.7, 2895.5),
(10, '2017-04-05 21:00:00', 235.15, 240.0, 237.9, 0.5, 2.2, 15.9, 108.5, 420.8, 251.8),
(10, '2017-04-05 21:15:00', 238.15, 240.0, 237.9, 0.5, 1.2, 16.1, 108.0, 213.4, 3256.5);
INSERT INTO meter2data(
"meter2data_id", "recordtime", "powerf", "u12", "u31", "u32", "l1_v", "l2_v", "l3_v",
"t1crp_q1", "t1crp_q2", "t1crp_q3", "t1crp_q4", "t1crp_s", "l1_c", "l2_c", "l3_c", "mta1",
"mta2", "t1cap_s", "t1cap_p"
) VALUES
(11, '2017-04-05 21:00:00', 98,410,410,412,237,238,238,3002160,8102400,2414200,361580000,1450606720,
82,79,61,200,5,9328,1402283136),
(11, '2017-04-05 21:15:00', 98,410,412,412,237,237,238,3002160,8102400,2414200,361582688,1450619392,
81,75,59,200,5,21768,1402295552);
Pokud jsou potřeba data z lokality s id = 1, tak pomocí CTE lze dotaz napsat triviálně takto:
WITH
"mids" AS (select array_agg(meters_id) AS "ma" from meters WHERE locations_id=1)
((SELECT meter1data_id, recordtime, (l1ap + l2ap + l3ap) AS "power" FROM meter1data, "mids" WHERE (meter1data_id=ANY("mids"."ma")))
UNION
(SELECT meter2data_id, recordtime, (t1cap_p - t1cap_s) AS "power" FROM meter2data, "mids" WHERE (meter2data_id=ANY("mids"."ma")))
) ORDER BY recordtime, meterid_meters;
V prvním kroku dotazu se připraví pole „mids“.“ma“, které se použije v hlavních dotazech. Výsledek by měl být stejný, jako při použití korelovaného dotazu:
(
(
SELECT meter1data_id, recordtime, (l1ap + l2ap + l3ap) AS "power" FROM meter1data WHERE (
meter1data_id IN (select meters_id from meters WHERE locations_id=1)
)
) UNION (
SELECT meter2data_id, recordtime, (t1cap_p - t1cap_s) AS "power" FROM meter2data WHERE (
meter2data_id IN (select meters_id from meters WHERE locations_id=1)
)
)
) ORDER BY recordtime, meterid_meters;
Efektivita, zvláště při větším množství typů měřících přístrojů, bude ve prospěch varianty s CTE, protože se poddotaz ve WHERE části nebude provádět několikrát. V tomto malém příkladu nejspíše něco zachytí planner a podrží si potřebná data pro opakovaný dotaz v paměti, ale v příkladu jsou opravdu malá data, nikoliv 2500 lokalit, každá v průměru se 3 měřícími přístroji, jež odesílají data v 15 minutových intervalech.
CTE rekurzivní SELECT
Pro pochopení rekurzivního modelu použiji tabulku zaměstnanců, jež mimo jiné ukazuje kdo je čí nadřízený.
CREATE TABLE "prac"(
"prac_id" serial NOT NULL,
"first_name" character varying(50),
"sure_name" character varying(50),
"manag_id" integer
)
WITH (
OIDS=FALSE
);
INSERT INTO
prac("first_name","sure_name", "manag_id")
VALUES
('Jan','Kohoutek',NULL),
('Pavel','Rybář',1),
('Richard','Abrahám',NULL),
('David','Kadlec',3),
('Tomáš','Marný',4),
('Jiří','Pavelka',3),
('Alena','Zábranská',3),
('Pavla','Dudečková',2);
Rekurzivní dotazy se dají rozebrat na několik částí. První je základní/inicializační a s ní je sjednocená (UNION) část druhá která rekurzivně doplňuje data na základě těch z inicializační části dotazu. Poslední část je SELECT, který pošle data na výstup.
Data která ukáží strukturu s nadřízeným a „vzdáleností“ pracovníka od nejvyššího vedení lze získat poměrně jednoduchým dotazem:
WITH
RECURSIVE prac_strom ("ManagId", "PracId", "FullName", "Level") AS (
SELECT COALESCE("manag_id",0), "prac_id", "first_name" || ' ' || "sure_name", 0 AS "Level"
FROM "prac"
WHERE ("manag_id" IS NULL) OR ("manag_id"=0)
UNION
SELECT COALESCE("pr"."manag_id",0), "pr"."prac_id", "pr"."first_name" || ' ' || "pr"."sure_name", "Level" + 1
FROM "prac" AS "pr"
INNER JOIN "prac_strom" "pr2"
ON "pr"."manag_id" = "pr2"."PracId"
)
SELECT * FROM "prac_strom" ORDER BY "ManagId" ASC, "Level" ASC;
CTE pro UPDATE/DELETE
Zde už to není úplně triviální, CTE pomůže především když je potřeba předpřipravit data, nebo neexistuje rozumný JOIN mezi upravovanou tabulkou a opěrnými tabulkami, případně by se opět zanořený dotaz opakoval v plném rozsahu.
CREATE TABLE watchdogs
(
watchdogid integer NOT NULL DEFAULT nextval('watchdogs_watchdog_seq'::regclass),
siteid_sites integer NOT NULL,
meterid_meters integer NOT NULL,
users_id integer NOT NULL,
active boolean NOT NULL,
watchdogstart time without time zone,
watchdogend time without time zone,
watchdogname character varying(30),
watchdogtext character varying(50),
maxvalue double precision DEFAULT 0,
actvalue double precision DEFAULT 0,
sendmail boolean DEFAULT true,
sendsms boolean DEFAULT true,
days integer[],
wdsent boolean DEFAULT false,
CONSTRAINT pk_watchdogs PRIMARY KEY (watchdogid),
)
WITH (
OIDS=FALSE
);
Definice je teoreticky jasná. Podle aktuálního času jsou aktualizovány příslušné věty (tj. ty kde je čas v rozmezí a navíc je správný den). Dobný zádrhel nastává u watchdogů, které jsou noční, tj. čas od je větší než čas do a zároveň je potřeba hlídat den ve kterém začal interval. Update mám řešený ve stored proceduře, samozřejmě s využitím CTE, ale stored proceduru ospravedlňuje potřeba několika dalších výpočtů. Pro vynulování záznamu (tj. nastavení atributu „actvalue“ na 0) je potřeba určit zda je watchdog mimo aktivní časový úsek a pochopitelně u „nočních“ je potřebné ohlídat i konec platnosti v aktuálním dni týdne (začátek je definován v poli „days“).
Dotaz pro výmaz vypadá takto:
WITH
"ad" AS (SELECT CASE WHEN (extract(DOW from (NOW()::date))<>0) THEN (extract(DOW from (NOW()::date))) ELSE 7 END AS "_dow"),
"pd" AS (SELECT CASE WHEN (extract(DOW from (NOW()::date - interval '1 days'))<>0) THEN (extract(DOW from (NOW()::date - interval '1 days'))) ELSE 7 END AS "_pdow"),
"atime" AS (SELECT round_timestamp(now()::timestamp without time zone, 900)::time AS "_atime")
UPDATE
"public"."watchdogs" AS "wd" SET "wdsent"=False, "actvalue"=0
WHERE
(
"actvalue"<>0
) AND (
CASE WHEN ("wd"."watchdogstart"<"wd"."watchdogend") THEN
"wd"."watchdogend" < (SELECT "_atime" FROM "atime")
ELSE
(("wd"."watchdogstart" > (SELECT "_atime" FROM "atime")) AND ((SELECT "_dow" FROM "ad")=ANY("wd"."days")))
AND
(("wd"."watchdogend" < (SELECT "_atime" FROM "atime")) AND ((SELECT "_pdow" FROM "pd")=ANY("wd"."days")))
END
)
V první části (WITH) definuji 3 dočasné pohledy; „ad“ pro aktuální kalendářní den, „pd“ pro předchozí kalendářní den, „atime“ pro aktuální zaokrouhlený čas; které pak využiji. Pohledy „ad“ a „pd“ jsou evidentně zbytečné, dal by se použít prostý korelovaný dotaz, trochu na úkor přehlednosti. Definice „atime“ je využitá na 3 místech, databáze by zbytečně 3× vypočítávala zaokrouhlení a ještě by se značně snížila přehlednost dotazu, včetně nárůstu pravděpodobnosti chyby.
Důležité je vidět, že pro DELETE a UPDATE není CTE tak přímočaré, jako pro SELECT.
BTW: O existenci rozsahů (ranges) v PgSQL vím, ale návrh databáze je starší (než jsou podporovány v PgSQL) a přepsání všech dotčených zdrojových kódů není realizovatelné v řádu hodin/dní.
V tomto příkladě je zároveň i ukázka použití více Common Table Expression částí. Jsou prostě odděleny čárkou.
Začátek místo závěru?
CTE není samospásné řešení, využívám jej v momentech, kdy bych stejný poddotaz (subSELECT) měl v korelovaném dotazu vícekrát, než jen jednou, nebo potřebuji z datových tabulek vybrat několik hodnot do dočasného pohledu pro další práci. CTE se hodí i pro rekurzivní dotazy, těm se snažím vyhnout, protože se velmi snadno stanou nepřehlednými. Pochopitelně počítám s vyššími paměťovými nároky, než při běžných spojeních.
CTE je jednou z lehce pokročilejších funkcionalit SQL, bohužel drtivá většina „programátorů“ nedojde dále než k základním JOINům a řeší komplikovanější problematiku „neefektivní hrubou silou“, případně s použitím cacheovacích obezliček, místo pořádného návrhu databáze a kvalitně postavených dotazů. Pro efektivní využití je potřebné se učit. Směle si troufám tvrdit, že CTE je možné dát do základních znalostí SQL, tj. jedné z prvních věcí, které by měl programátor znát.
BTW: Nezapomeňte optimalizovat, testovat a pořádně vyzkoušet intexy. CTE nejsou univerzální recept, v nejednom případě bude lépe fungovat propojení (JOIN) tabulek, jindy budou mnohem efektivnější vnořené SELECTy.
