Напредни извештаи од базата (SQL)

Дестинации со најголем број на разновидни настани

Идентификување на дестинации кои имаат најголем број на различни видови настани. Во долунаведениот код се пребаруваат сите дестинации и ги брои различните видови на настани (како концерти, фестивали, саеми и сл.) поврзани со секоја дестинација. Потоа, резултатите се сортираат по бројот на различни настани во опаѓачки редослед и се добива еден вид на рангирање. Од особено значење за корисници што сакаат да вклучат повеќе разновидни настани при нивното патување.

SELECT d.imeLokacija AS destinacija, COUNT(DISTINCT n.naziv) AS broj_nastani
FROM DESTINACII d
JOIN NASTANI n ON d.idDest = n.idDest
GROUP BY d.imeLokacija
HAVING COUNT(DISTINCT n.naziv) = (
    SELECT MAX(broj_nastani)
    FROM (
        SELECT COUNT(DISTINCT n.naziv) AS broj_nastani
        FROM DESTINACII d
        JOIN NASTANI n ON d.idDest = n.idDest
        GROUP BY d.imeLokacija
    ) AS temp
);

Настани на одредена дестинација

Дополнително, за да се прикажат различни категории на настани за одредена дестинација. За пример, доколку сакате да ги видите сите видови настани што се одржуваат во Охрид:

SELECT DISTINCT n.naziv
FROM NASTANI n
JOIN DESTINACII d ON n.idDest = d.idDest
WHERE d.imeLokacija = 'Охрид';

Топ 10 дестинации според рецензии (атрибут kvalitet)

Овде се зема просечниот квалитет на рецензиите за секоја дестинација и ги прикажува првите 10 дестинации со највисок квалитет на рецензии. Корисно за корисниците што сакаат да ја изберат најдобро оценетата дестинација.

SELECT d.imeLokacija, AVG(r.kvalitet) AS prosekKvalitet
FROM DESTINACII d
JOIN RECENZII r ON d.idDest = r.idDest
GROUP BY d.imeLokacija
ORDER BY prosekKvalitet DESC
LIMIT 10;

Дестинации со најголем број на активности под различни цени т.е. опсези

Приказ на дестинациите и брои колку активности постојат во различни ценовни групи (пример под 20, под 50, под 100 евра). Тоа му помага на корисникот да избере дестинација според неговиот буџет и преференции за активности.

SELECT d.imeLokacija,
       SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) AS aktivnostiPod20,
       SUM(CASE WHEN a.iznos < 50 THEN 1 ELSE 0 END) AS aktivnostiPod50,
       SUM(CASE WHEN a.iznos < 100 THEN 1 ELSE 0 END) AS aktivnostiPod100
FROM DESTINACII d
JOIN AKTIVNOSTI a ON d.idDest = a.idDest
GROUP BY d.imeLokacija
ORDER BY aktivnostiPod20 DESC, aktivnostiPod50 DESC, aktivnostiPod100 DESC;

Дестинации со најголем процент на достапни(евтини) активности

Се прикажуваат дестинации со евтини активности.

SELECT d.imeLokacija,
       COUNT(a.idAktivnost) AS vkupnoAktivnosti,
       SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) AS efiniAktivnosti,
       (SUM(CASE WHEN a.iznos < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.idAktivnost)) AS procentEfiniAktivnosti
FROM DESTINACII d
JOIN AKTIVNOSTI a ON d.idDest = a.idDest
GROUP BY d.imeLokacija
HAVING COUNT(a.idAktivnost) > 0
ORDER BY procentEfiniAktivnosti DESC;

Топ корисници со најголем број напишани рецензии

Најактивни корисници т.е. оние кои имаат напишано најголем број на рецензии.

SELECT idKorisnik, COUNT(*) AS broj_recenzii
FROM RECENZII
GROUP BY idKorisnik
HAVING COUNT(*) > (SELECT COUNT(*) * 0.9 FROM RECENZII);

Најисплатливи пакети (Best value for money)

Пресметување на цената по ден за пакети, што помага да се дознае кој пакет има најдобра вредност според сумата. Во случај да има пакет од 20 евра за 1 ден или пакет од 50 евра за 1 недела, би можело да се прикаже која опција е најисплатлива.

SELECT p.imePaket AS paket, 
       p.cena / DATEDIFF(p.kraj, p.pochetok) AS cena_po_den
FROM PAKETI p
WHERE p.cena / DATEDIFF(p.kraj, p.pochetok) = (
    SELECT MIN(p.cena / DATEDIFF(p.kraj, p.pochetok))
    FROM PAKETI p
);

Приказ на бројот на резервации по месец

Помага да се следи трендот на резервации преку времето.

SELECT MONTH(vremenskaTochka) AS mesec, COUNT(idRezervacija) AS broj_rezervacii
FROM REZERVACII
GROUP BY MONTH(vremenskaTochka)
ORDER BY mesec;

Приказ на бројот на нови корисници по месец

Покажување на новорегистрираните корисници по месец. Овозможува да се види растот на бројот на корисници во различни месеци.

SELECT MONTH(datumRagjanje) AS mesec, COUNT(idKorisnik) AS broj_novi_korisnici
FROM KORISNICI
GROUP BY MONTH(datumRagjanje)
ORDER BY mesec;

Приказ на бројот на пакети во резервации по месец

Ги брои пакетите што се резервирани по месец. Можност за следење на популарноста на различни пакети во текот на годината.

SELECT MONTH(p.pochetok) AS mesec, COUNT(p.idPaket) AS broj_paketi
FROM PAKETI p
JOIN REZERVACII r ON p.idRezervacija = r.idRezervacija
GROUP BY MONTH(p.pochetok)
ORDER BY mesec;

Приказ на бројот на настани по месец

Корисно е за да се согледа колку настани се организирани секој месец.

SELECT MONTH(pochetenDatum) AS mesec, COUNT(idNastan) AS broj_nastani
FROM NASTANI
GROUP BY MONTH(pochetenDatum)
ORDER BY mesec;

Приказ на најпопуларните активности на месечно ниво

Овде е прикажана бројката на активности по категорија, со цел да се утврди која активност е најпопуларна во текот на месецот.

SELECT MONTH(r.vremenskaTochka) AS mesec, a.kategorija, COUNT(a.idAktivnost) AS broj_aktivnosti
FROM REZERVACII r
JOIN AKTIVNOSTI a ON r.idAktivnost = a.idAktivnost
GROUP BY MONTH(r.vremenskaTochka), a.kategorija
ORDER BY mesec, broj_aktivnosti DESC;

Приказ на број на резервации и активности по месец и корисник

Статистика за секој корисник по месец, вклучувајќи број на резервации и активности. Така, се гледа активноста на корисниците и популарноста на нивните активности.

SELECT MONTH(r.vremenskaTochka) AS mesec, k.ime, k.prezime, COUNT(r.idRezervacija) AS broj_rezervacii, COUNT(a.idAktivnost) AS broj_aktivnosti
FROM KORISNICI k
JOIN REZERVACII r ON k.idKorisnik = r.idKorisnik
JOIN AKTIVNOSTI a ON r.idAktivnost = a.idAktivnost
GROUP BY MONTH(r.vremenskaTochka), k.idKorisnik
ORDER BY mesec, broj_rezervacii DESC;