Changes between Version 9 and Version 10 of AdvancedReports

Timestamp:

02/02/26 20:16:44 (4 days ago)

Author:

231136

Comment:

--

AdvancedReports

@@ -5 +5 @@
 
 === 1. Детален извештај за број на лајкови, слушања и просечна оценка за секој албум
+
+==== SQL
+
 {{{
 SET search_path TO project;
@@ -46 +49 @@
 }}}
 
+==== Релациона Алгебра
+
+{{{
+LikeCount <- γ album_id := a.id;
+  total_likes := COUNT(l.listener_id)
+(
+  albums a ⟕ (a.id = l.musical_entity_id) likes l
+)
+
+ListenCount <-
+γ album_id := s.album_id;
+  total_listens := COUNT(l.timestamp)
+(
+  σ s.album_id IS NOT NULL
+  (
+    (listens l ⨝ (l.song_id = s.id) songs s)
+      ⨝ (s.album_id = a.id) albums a
+  )
+)
+
+AverageGrade <-
+γ album_id := a.id;
+  average_grade := AVG(r.grade)
+(
+  reviews r ⨝ (r.musical_entity_id = a.id) albums a
+)
+
+Result <-
+π me.title, lk.total_likes, lc.total_listens, ag.average_grade
+  (
+    (
+      (
+        musical_entities me ⟕ (lk.album_id = me.id) LikeCount lk
+      )
+      ⟕ ListenCount lc
+    )
+    ⟕ AverageGrade ag
+  )
+)
+}}}
 
 === 2. Детален извештај за број на лајкови, број на слушања, просечна оценка за една песна, како и бројот на плејлисти кои ја содржат 
+
+==== SQL
 {{{
 SET search_path TO project;
@@ -97 +142 @@
 }}}
 
+==== Релациона Алгебра
+
+{{{
+LikeCount <-
+γ song_id := s.id;
+  total_likes := COUNT(l.listener_id)
+(
+  songs s ⨝ (s.id = l.musical_entity_id) likes l
+)
+
+ListenCount <-
+γ song_id := l.song_id;
+  total_listens := COUNT(l.timestamp)
+(
+  listens l
+)
+
+AverageGrade <-
+γ song_id := r.musical_entity_id;
+  average_grade := AVG(r.grade)
+(
+  reviews r ⨝ (r.musical_entity_id = s.id) songs s
+)
+
+PlaylistCount <-
+γ song_id := ps.song_id;
+  count_playlists := COUNT(ps.song_id)
+(
+  songs s ⨝ (s.id = ps.song_id) playlist_songs ps
+)
+
+Result <-
+π me.title,
+  ls.total_listens,
+  lc.total_likes,
+  ag.average_grade,
+  pc.count_playlists
+(
+  (
+    (
+      (
+        (
+          musical_entities me
+          ⨝ (me.id = s.id) songs s
+        )
+        ⟕ (s.id = lc.song_id) LikeCount lc
+      )
+      ⟕ (s.id = ls.song_id) ListenCount ls
+    )
+    ⟕ (ls.song_id = pc.song_id) PlaylistCount pc
+  )
+  ⟕ (lc.song_id = ag.song_id) AverageGrade ag
+)
+}}}
 
 === 3. Детален извештај за најпопуларен артист за секој настан рангиран по бројот на следбеници, а потоа по вкупно слушања на сите негови песни 
+
+==== SQL
 {{{
 SET search_path TO 'project';
@@ -145 +246 @@
 }}}
 
+==== Релациона Алгебра
+{{{
+FollowersCount <-
+γ artist := a.user_id,
+  event_id := pa.event_id;
+  followers := COUNT(f.follower)
+(
+  (artists a ⨝ (a.user_id = pa.artist_id) performs_at pa)
+  ⟕ (a.user_id = f.followee) follows f
+)
+
+ListensCount <-
+γ artist := a.user_id;
+  listen_count := COUNT(l.timestamp)
+(
+  (
+    (artists a ⨝ (a.user_id = pa.artist_id) performs_at pa)
+    ⨝ (pa.artist_id = me.released_by) musical_entities me
+  )
+  ⟕ (me.id = l.song_id) listens l
+)
+
+BaseResult <-
+(
+  (
+    (
+      (
+        events e
+        ⨝ (e.event_id = pa.event_id) performs_at pa
+      )
+      ⨝ (pa.artist_id = fc.artist) FollowersCount fc
+    )
+    ⨝ (fc.artist = lc.artist) ListensCount lc
+  )
+  ⨝ (lc.artist = u.user_id) users u
+)
+
+Dominates <-
+σ
+  fc.event_id = fc1.event_id ∧
+  (
+    fc.followers < fc1.followers
+    ∨ (fc.followers = fc1.followers ∧ lc.listen_count < lc1.listen_count)
+    ∨ (fc.followers = fc1.followers ∧ lc.listen_count = lc1.listen_count ∧ lc.artist < lc1.artist)
+  )
+(
+  (FollowersCount fc ⨝ ListensCount lc)
+  ×
+  (ρ fc1(FollowersCount) ⨝ ρ lc1(ListensCount))
+)
+
+FinalArtists <-
+BaseResult − π attributes(BaseResult)
+(
+  BaseResult ⨝ (BaseResult.artist = Dominates.artist) Dominates
+)
+
+Result <-
+π e.location,
+  e.venue,
+  u.full_name,
+  fc.followers,
+  lc.listen_count
+(
+  FinalArtists
+)
+}}}
 
 === 4. Детален извештај за најпопуларниот артист според месечни слушања за изминатата година
+
+==== SQL
 {{{
 SET search_path TO 'project';
@@ -205 +375 @@
 }}}
 
+==== Релациона Алгебра
+{{{
+
+OneYear <-
+{ (year_start, year_end) }
+
+MonthlyArtistListens <-
+γ month := MONTH(l.timestamp),
+  user_id := a.user_id;
+  count := COUNT(l.timestamp)
+(
+  (
+    (
+      listens l
+      ⨝ (l.timestamp ≥ oy.year_start ∧ l.timestamp ≤ oy.year_end) OneYear oy
+    )
+    ⨝ (l.song_id = me.id) musical_entities me
+  )
+  ⨝ (me.released_by = a.user_id) artists a
+)
+
+MaxPerMonth =
+γ month := mal.month;
+  max_month_counter := MAX(mal.count)
+(
+  MonthlyArtistListens mal
+)
+
+MaxCandidates <-
+MonthlyArtistListens mal
+⨝ (mal.month = mpm.month ∧ mal.count = mpm.max_month_counter)
+MaxPerMonth mpm
+
+Dominated <-
+σ
+  mal.month = mal1.month ∧
+  mal.user_id > mal1.user_id
+(
+  ρ mal(MaxCandidates)
+  ×
+  ρ mal1(MaxCandidates)
+)
+
+MostPopular <-
+MaxCandidates − π attributes(MaxCandidates)
+(
+  MaxCandidates ⨝_{user_id} Dominated
+)
+
+Months(month_num, month_name)
+
+FinalResult <-
+π
+  CONCAT(YEAR(oy.year_start), '-', m.month_name),
+  u.full_name,
+  mp.max_month_counter
+(
+  (
+    (
+      Months m
+      ⟕ (m.month_num = mp.month) MostPopular mp
+    )
+    ⟕ (mp.user_id = u.user_id) users u
+  )
+  × OneYear oy
+)
+}}}
 
 === 5. Детален извештај за процентуалната промена на бројот на слушања на артистите за изминатите 30 дена
 
+==== SQL
 {{{
 SET search_path TO 'project';
@@ -263 +501 @@
 
 }}}
+
+==== Релациона Алгебра
+
+{{{
+
+OneMonth <-
+{ (prev_period_start,
+   prev_period_end,
+   this_period_start,
+   this_period_end) }
+   
+LastPeriod <-
+σ last_period_count > 0
+(
+  γ user_id := a.user_id;
+    last_period_count := COUNT(l.timestamp)
+  (
+    (
+      (
+        listens l ⨝ (l.timestamp ≥ om.prev_period_start ∧
+           l.timestamp ≤ om.prev_period_end)
+        OneMonth om
+      )
+      ⨝ (l.song_id = me.id) musical_entities me
+    )
+    ⟖ (me.released_by = a.user_id) artists a
+  )
+)
+
+ThisPeriod <-
+γ user_id := a.user_id;
+  this_period_count := COUNT(l.timestamp)
+(
+  (
+    (
+      listens l ⨝ (l.timestamp ≥ om.this_period_start ∧
+         l.timestamp ≤ om.this_period_end)
+      OneMonth om
+    )
+    ⨝ (l.song_id = me.id) musical_entities me
+  )
+  ⟖ (me.released_by = a.user_id) artists a
+)
+
+Stats <-
+π
+  u.full_name,
+  lp.last_period_count,
+  tp.this_period_count,
+  om.prev_period_start,
+  om.prev_period_end,
+  om.this_period_start,
+  om.this_period_end,
+  pct_change := ((tp.this_period_count − lp.last_period_count)
+                 / lp.last_period_count) * 100
+(
+  (
+    (
+      LastPeriod lp ⟕ ThisPeriod tp
+    )
+    ⨝ users u
+  )
+  × OneMonth om
+)
+
+Result <-
+τ pct_change DESC
+(
+  π
+    full_name,
+    previous_period,
+    last_period_count,
+    this_period,
+    this_period_count,
+    pct_change
+  (
+    Stats s
+  )
+)
+}}}