Millal võiks olla kasu baastabeli arvutatud veergudest?

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.

---

Kas postitus oli kasulik? Hinda seda!

---