Andmebaaside ajaveeb

PostgreSQLis on andmebaas järgmise kontseptuaalse struktuuriga.

[Facility_type]-1-------0..*-[Facility]-1---------------0..*-[Health_care_visit]

Tabelis Facility_type on 5 rida.
Tabelis Facility on 5_000 rida.
Tabelis Health_care_visit on 1_000_000 rida.

Järgnevalt esitatakse kolm ülesannet ja nende lahendused PostgreSQLis (16).

Lahendus 1 korral tehakse kaks päringut samadesse tabelitesse. Need päringud vormistatakse parema loetavuse huvides ühiste tabeli avaldistena (common table expression) - need on lause alguses WITH klauslis. Lausete täitmisplaanid näitavad, et samu tabeleid ja nende indekseid loetakse mitmekordselt.
Lahendus 2 korral tehakse samade tabelite põhjal üks päring. Protsendi arvutamiseks vajalike arvude leidmiseks kasutatakse Count kokkuvõttefunktsiooni koos FILTER predikaadiga. Lausete täitmisplaanid näitavad, et samu tabeleid ja nende indekseid loetakse ühekordselt.
PostgreSQLis peab jagamisoperatsioonide täpsuse huvides vähemalt ühe argumendi teisendama Decimal tüüpi (seda teeb ::decimal).
Kõigi lausete puhul kasutatakse nulliga jagamise vältimiseks Nullif funktsiooni, mis tagastab NULL kui argumendid on võrdsed (Väärtus/NULL=> NULL).
Kõigi lausete puhul kasutatakse ümardamiseks Round funktsiooni.
Kõigi lausete puhul vaadatakse täitmisplaani ja täitmise kiirust kasutades EXPLAIN ANALYZE lauset.

Ülesanne 1: Leia, kui mitu protsenti moodustab tervishoiuasutuste külastuste arv, kus tasu on alla 9000, külastuste arvust, kus tasu on 9000 või rohkem. Väldi nulliga jagamist. Ümarda tulemus ühe kohani peale koma.

Lahendus 1

WITH smaller_fee AS (SELECT Count(*) AS cnt FROM Health_care_visit WHERE visit_fee<9000),

higher_fee AS (SELECT Count(*) AS cnt FROM Health_care_visit WHERE visit_fee>=9000)

SELECT Round(smaller_fee.cnt::decimal*100/nullif(higher_fee.cnt::decimal,0),1) AS percent

FROM smaller_fee, higher_fee;

Täitmise aeg: 208 ms.

Lahendus 2

SELECT Round(Count(*) FILTER (WHERE visit_fee<9000)::decimal/nullif(Count(*) FILTER (WHERE visit_fee>=9000)::decimal,0),1) AS percent

FROM Health_care_visit;

Täitmise aeg: 136 ms.

Ülesanne 2: Leia, kui mitu protsenti moodustab kliinikute külastuste arv haiglate külastuste arvust. Väldi nulliga jagamist. Ümarda tulemus ühe kohani peale koma.

Lahendus 1

WITH hospital_visits AS (SELECT Count(*) AS cnt

FROM Facility AS F INNER JOIN Health_care_visit AS Hcv USING (facility_id)

WHERE facility_type_code='HOS01'),

clinic_visits AS (SELECT Count(*) AS cnt

FROM Facility AS F INNER JOIN Health_care_visit AS Hcv USING (facility_id)

WHERE facility_type_code='CLI06')

SELECT Round(clinic_visits.cnt::decimal*100/nullif(hospital_visits.cnt::decimal,0),1) AS percent

FROM hospital_visits, clinic_visits;

Täitmise aeg: 113 ms.

Lahendus 2

SELECT Round(Count(*) FILTER (WHERE facility_type_code='CLI06')::decimal*100/nullif(Count(*) FILTER (WHERE facility_type_code='HOS01')::decimal,0),1) AS hospital_visits_cnt

FROM Facility AS F INNER JOIN Health_care_visit AS Hcv USING (facility_id);

Täitmise aeg: 98 ms.

Ülesanne 3: Leia, kui mitu protsenti moodustab tervishoiuasutuste arv, kus on vähemalt üks külastus, mille eest tasuti üle 9000 ühiku raha, kõigi asutuste arvust, kus on tehtud vähemalt üks külastus. Väldi nulliga jagamist. Ümarda tulemus ühe kohani peale koma.

Lahendus 1

WITH total_facilities AS (SELECT Count(*) AS cnt FROM Facility),

facilities_with_high_fees AS (SELECT Count(*) AS cnt FROM Facility AS F

WHERE EXISTS (SELECT * FROM Health_care_visit AS Hcv WHERE F.facility_id=Hcv.facility_id AND visit_fee>9000 ))

SELECT Round(facilities_with_high_fees.cnt*100/nullif(total_facilities.cnt,0),1) AS percent

FROM total_facilities, facilities_with_high_fees;

Täitmise aeg: 122 ms.

Lahendus 2

SELECT Round(Count(DISTINCT Hcv.facility_id) FILTER (WHERE visit_fee>9000)::decimal*100/nullif(Count(DISTINCT F.facility_id)::decimal,0),1) AS percent

FROM Facility AS F INNER JOIN Health_care_visit AS Hcv USING (facility_id);

Täitmise aeg: 462 ms.

Järeldused.

Sama ülesannet saab lahendada mitmel erineval viisil.
Protsendi arvutamine nii, et sama tabelit ei pea mitu korda lugema on mõnikord kiirem kui protsendi arvutamine nii, et sama tabelit loetakse mitu korda (ülesanded 1 ja 2) kuid mõnikord ei ole (ülesanne 3).
Ülesande 3 lahenduse 2 puhul tundub probleemiks olevat vajadus eemaldada korduseid.

Hinda postitust:

Keskmine hinne : Pole veel hinnanguid!