Vastus (14.10.2023 12:00): Arvutatud veeru puhul arvutatakse väljas olev väärtus teiste samas reas olevate väärtuste puhul ja salvestatakse kettale.

Sellistest veergudest võib olla kasu koondandmete päringute puhul.

Toon näite PostgreSQL 16 põhjal.

Ülesanne: Tellimuste andmebaasis on tellimused ja tellimuste read. Ülesandeks on leida iga kuu(+aasta) kohta, kus on tehtud mõni tellimus, millised kliendid on teinud selles kuus(+aastas) kõige suuremas mahus tellimusi.

Andmebaasi kontseptuaalne struktuur: [Tellimus]-1-----------1..*-[Tellimuse_rida]

Loon tabelid.

CREATE TABLE Tellimus (tellimus_kood INTEGER NOT NULL,
klient_id INTEGER NOT NULL,
tellimuse_kp DATE NOT NULL,
tellimuse_aasta SMALLINT GENERATED ALWAYS AS (extract(year FROM tellimuse_kp)) STORED NOT NULL,
tellimuse_kuu SMALLINT GENERATED ALWAYS AS (extract(month FROM tellimuse_kp)) STORED NOT NULL,
CONSTRAINT pk_tellimus PRIMARY KEY (tellimus_kood));

CREATE TABLE Tellimuse_rida (tellimus_kood INTEGER NOT NULL,
toode_id INTEGER NOT NULL,
yhiku_hind INTEGER NOT NULL,
kogus INTEGER NOT NULL,
summa INTEGER GENERATED ALWAYS AS (kogus * yhiku_hind) STORED NOT NULL,
CONSTRAINT pk_tellimuse_rida PRIMARY KEY (tellimus_kood, toode_id),
CONSTRAINT fk_tellimuse_rida_tellimus FOREIGN KEY (tellimus_kood)
REFERENCES Tellimus (tellimus_kood) ON DELETE CASCADE);

Lisan testandmed.

INSERT INTO Tellimus (tellimus_kood, klient_id, tellimuse_kp)
SELECT generator AS tellimus_kood,
floor(random() * (1_000 - 1 + 1) + 1)::int AS klient_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS tellimuse_kp
FROM generate_series(1, 15_000) AS generator;

INSERT INTO Tellimuse_rida (tellimus_kood, toode_id, yhiku_hind, kogus)
SELECT floor(random() * (1_000 - 1 + 1) + 1)::int AS tellimus_kood,
floor(random() * (2_000 - 1 + 1) + 1)::int AS toode_id,
floor(random() * (1_000 - 1 + 1) + 1)::int AS yhiku_hind,
floor(random() * (10_000 - 1 + 1) + 1)::int AS kogus
FROM generate_series(1, 100_000) AS generator
ON CONFLICT DO NOTHING;

DELETE FROM Tellimus WHERE NOT EXISTS (SELECT *
FROM Tellimuse_rida
WHERE Tellimus.tellimus_kood=Tellimuse_rida.tellimus_kood);

ANALYZE;

Katsetan kahte lahendust, millest kummagi puhul on kaks variatsiooni. Variant 1 korral arvutatakse päringus kasutatavad väärtused käigult. Variant 2 korral kasutatakse andmete registreerimisel välja arvutatud ja salvestatud väärtuseid. Töökiiruse leidmiseks kasutan EXPLAIN ANALYZE lauset.

Lahendus 1, variant 1

EXPLAIN ANALYZE WITH Tellimus_stats AS (SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats AS Ts
WHERE summa=(SELECT Max(summa) AS m
FROM Tellimus_stats AS Ts2
WHERE Ts.aasta=Ts2.aasta
AND Ts.kuu=Ts2.kuu);

Lahendus 1, variant 2

EXPLAIN ANALYZE WITH Tellimus_stats AS (SELECT tellimuse_aasta AS aasta,
tellimuse_kuu AS kuu,
klient_id, Sum(summa) AS summa
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats AS Ts
WHERE summa=(SELECT Max(summa) AS m
FROM Tellimus_stats AS Ts2
WHERE Ts.aasta=Ts2.aasta
AND Ts.kuu=Ts2.kuu);

Lahendus 2, variant 1

EXPLAIN ANALYZE WITH Tellimus_stats AS (SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa,
Rank() OVER (PARTITION BY extract(year FROM tellimuse_kp), extract(month FROM tellimuse_kp) ORDER BY Sum(yhiku_hind*kogus) DESC) AS asetus
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats
WHERE asetus=1;

Lahendus 2, variant 2

EXPLAIN ANALYZE WITH Tellimus_stats AS (SELECT tellimuse_aasta AS aasta, tellimuse_kuu AS kuu, klient_id, Sum(summa) AS summa, Rank() OVER (PARTITION BY tellimuse_aasta, tellimuse_kuu ORDER BY Sum(summa) DESC) AS asetus
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats
WHERE asetus=1;

Töökiiruse tulemused:

Lahendus 1, variant 1: 280 ms
Lahendus 1, variant 2: 166 ms
Lahendus 2, variant 1: 100 ms
Lahendus 2, variant 2: 65 ms

Antud juhul parandavad arvutatud veerud päringute töökiirust.