Changes between Version 2 and Version 3 of AdvancedApplicationDesign

Timestamp:

08/20/24 19:28:38 (5 weeks ago)

Author:

216049

Comment:

--

AdvancedApplicationDesign

@@ -22 +22 @@
 [https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/ruta Стартување на рута на автобус]
 
+== Материјализирани views
+
+Напредните извештаи од минатите фази се искористени за да се направат materialized views. Материјализираните views значително го намалуваат времето на извршување на квери поради тоа што го чуваат и резултатот. Ова е соодветно поради тоа што ваквите статистики нема потреба да бидат up to date во секој момент, доволно е да се рефрешираат на одреден период (на пример 12 часа).
+
+Во продолжение се прикажани материјалиизираните views (за некои од нив има и графици кои може да се погледнат во амин панелот).
+
+==== Средна разлика на простигнување на инстанците од постојка А до постојка Б
+
+Идејата е да може да се има естимација на време за кое автобусот би стигнал на одредена постојка.
+
+{{{#!div
+{{{#!sql
+
+CREATE MATERIALIZED VIEW avg_time_diffs AS
+WITH ordered_instanca AS (SELECT ilpl.instanca_na_linija_id,
+                                 ilpl.postojka_na_linija_id,
+                                 pnl.reden_broj,
+                                 ilpl.timestamp
+                          FROM instanca_na_linija_postojka_na_linija ilpl
+                                   JOIN
+                               postojka_na_linija pnl ON ilpl.postojka_na_linija_id = pnl.id
+                          ORDER BY ilpl.instanca_na_linija_id,
+                                   pnl.reden_broj),
+     time_differences AS (SELECT o1.instanca_na_linija_id,
+                                 o1.postojka_na_linija_id                          AS start_station_id,
+                                 o1.timestamp                                      AS start_time,
+                                 o2.postojka_na_linija_id                          AS end_station_id,
+                                 o2.timestamp                                      AS end_time,
+                                 EXTRACT(EPOCH FROM (o2.timestamp - o1.timestamp)) AS time_diff
+                          FROM ordered_instanca o1
+                                   JOIN
+                               ordered_instanca o2 ON o1.instanca_na_linija_id = o2.instanca_na_linija_id
+                          WHERE o1.reden_broj + 1 = o2.reden_broj)
+SELECT
+    row_number() over () as id,
+    start_station_id,
+       end_station_id,
+       AVG(time_diff)::double precision AS avg_time_diff_seconds
+FROM time_differences
+GROUP BY start_station_id,
+         end_station_id
+ORDER BY start_station_id,
+         end_station_id;
+
+create unique index avg_time_diffs_index on avg_time_diffs (id);
+
+
+}}}
+}}}
+
+==== Број на казни според линија
+
+{{{#!div
+{{{#!sql 
+
+create materialized view kazna_po_linija as
+select row_number() over () as id, l.id as line_id, count(l.id)
+from linija l
+         left join instanca_na_linija inl on l.id = inl.linija_id
+         left join kontrola k on inl.id = k.instanca_na_linija_id
+         left join kazna kaz on k.id = kaz.kontrola_id
+where extract(year from kaz.date_created) = extract(year from now())
+group by l.id
+order by count(l.id) desc;
+
+create unique index index_kazna_po_linija on kazna_po_linija(id);
+
+}}}
+}}}
+
+
+==== Зафатеност на линиите по интервали од 1 час
+
+{{{#!div
+{{{#!sql
+
+create materialized view most_busy_part_of_the_day as
+select row_number() over () as id, id as linija_id, interval_1h, count(id || '-' || interval_1h) broj_pati
+from (select li.id,
+             (to_char(extract(hour from v.start_date), '00') || ':00 -' || to_char(extract(hour from (v.start_date
+                 + '1 hours'::interval))::int, '00') || ':00') as interval_1h
+      from linija li
+               left join instanca_na_linija inl on li.id = inl.linija_id
+               left join vozenje v on inl.id = v.instanca_na_linija_id
+      where v.start_date between (make_date(extract(year from (now()))::int, 1, 1)) and (make_date(extract(year from (now()))::int, 12, 31))) as hours
+group by id, interval_1h
+order by interval_1h desc;
+
+create unique index index_most_busy_part_of_the_day on most_busy_part_of_the_day(id);
+
+}}}
+}}}
+
+==== Commutes per line
+
+{{{#!sql
+create materialized view commutes_by_line as
+select row_number() over () as id, l.id as linija_id, count(v.id)
+from linija l
+         left join instanca_na_linija inl on l.id = inl.linija_id
+         left join vozenje v on inl.id = v.instanca_na_linija_id
+where v.start_date between date_trunc('year', now()) and date_trunc('month', date_trunc('year', now())+ interval '7 months')
+group by l.id;
+}}}
+
+==== Number of passengers per line and station 
+
+{{#!sql
+create materialized view number_passengers_per_line_and_station as
+select row_number() over () as id, p.id as station_id, l.id as line_id, count(p.id)
+from postojka p
+         join postojka_na_linija pnl on p.id = pnl.postojka_id
+         join vozenje v on pnl.id = v.postojka_na_linija_start_id
+         join patnik pat on v.patnik_id = pat.id
+         join linija l on pnl.linija_id = l.id
+where v.start_date between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30))
+group by p.id, l.id
+order by count(p.id) desc;
+}}
+
+==== Ticket sales
+
+{{{#!sql
+create materialized view ticket_sales as
+select row_number() over () as id, filtered.tip_id, filtered.interval_1h, filtered.broj_pati
+from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
+      from (select *,
+                   (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
+                    to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int, '00') ||
+                    ':00') as interval_1h
+            from tipbilet t
+                     left join bilet b on t.id = b.tip_id
+--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
+
+           ) as filter
+      group by filter.tip_id, interval_1h) filtered
+where broj_pati = (select max(filtered.broj_pati)
+                   from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
+                         from (select *,
+                                      (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
+                                       to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int,
+                                               '00') ||
+                                       ':00') as interval_1h
+                               from tipbilet t
+                                        left join bilet b on t.id = b.tip_id
+--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
+
+                              ) as filter
+                         group by filter.tip_id, interval_1h) filtered)
+  and tip_id in (select id
+                 from tipbilet
+                 where ime in ('Hourly', 'Daily'));
+}}}
+
+==== Total income (from ticket sales and fines)
+
+{{{#!sql
+create materialized view total_income as
+WITH kazna_sum AS (
+    SELECT
+        EXTRACT(YEAR FROM k.date_payed) AS year,
+        SUM(k.iznos) AS sum_kz
+    FROM
+        kazna k
+    WHERE
+        k.plateno = TRUE
+    GROUP BY
+        EXTRACT(YEAR FROM k.date_payed)
+),
+     bilet_sum AS (
+         SELECT
+             EXTRACT(YEAR FROM b.datum_kupuvanje) AS year,
+             SUM(tb.cena) AS sum_b
+         FROM
+             bilet b
+                 JOIN tipbilet tb ON tb.id = b.tip_id
+         GROUP BY
+             EXTRACT(YEAR FROM b.datum_kupuvanje)
+     )
+SELECT
+    row_number() over () as id,
+    COALESCE(k.year, b.year)::text AS year,
+    COALESCE(sum_kz, 0) as fines_income,
+    COALESCE(sum_b, 0) as ticket_income,
+    COALESCE(sum_kz, 0) + COALESCE(sum_b, 0) AS total_income
+FROM
+    kazna_sum k
+        FULL OUTER JOIN bilet_sum b ON k.year = b.year
+ORDER BY
+    year desc;
+}}}