Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

09/24/26 12:49:29 (5 days ago)

Author:

201165

Comment:

--

AdvancedReports

@@ -103 +103 @@
 )
 }}}
-== Извештај 3: Ден со најголем внес по корисник
+== Извештај 3: Ден со најголем внес по корисник (АЖУРИРАНО)
 // Callorie-maxing view //
 === Опис на барањата за податоци
 Фокус на денот со најмногу калории на секој корисник наспроти неговиот сопствен просек. секој корисник што води дневник на исхрана, извештајот го наоѓа денот во кој внел најмногу енергија. Тој ден го споредува со просечниот дневен внес на истиот корисник.
-* RANK() ги дели деновите по корисник и ги подредува по енергија
-* AVG() врз истата партиција го дава просекот.
-//ROW_NUMBER() прикажуваше само една инстанца ако имаше дупликати/денови со ист број калории//
+* RANK() го избира најтешкиот ден
+* AVG() врз партицијата личниот просек
+* LAG() претходниот ден
+* втор AVG() со ROWS BETWEEN 2 PRECEDING AND CURRENT ROW тридневен подвижен просек.
+
+1. weight_near_peak: тежината чиј датум е еднаков на датумот избран од внатрешен потпрашалник што ги подредува мерењата по временска оддалеченост од врвниот ден.
+2. avg_peak_all_users и users_with_higher_peak: агрегат врз изведена табела од максимуми по корисник, која самата е агрегат врз изведена табела од дневни збирови.
 === SQL - приказ
 {{{
-WITH daily_intake AS (
-    SELECT u.username,
+WITH daily AS (
+    SELECT ip.user_id,
            ip.date_time::date AS intake_day,
            SUM(ip.kcal)       AS kcal_consumed,
-           COUNT(*)           AS meals_logged,
-           ROUND(AVG(SUM(ip.kcal)) OVER (PARTITION BY u.id)) AS avg_daily_kcal,
-           RANK() OVER (PARTITION BY u.id ORDER BY SUM(ip.kcal) DESC) AS day_rank
-    FROM "user" u
-    JOIN intake_planner ip ON ip.user_id = u.id
+           COUNT(*)           AS meals_logged
+    FROM intake_planner ip
     WHERE ip.is_consumed = TRUE
-    GROUP BY u.id, u.username, ip.date_time::date
-)
-SELECT username, intake_day, kcal_consumed, meals_logged, avg_daily_kcal
-FROM daily_intake
-WHERE day_rank = 1
-ORDER BY kcal_consumed DESC;
-}}}
-=== Релациона алгебра - приказ
-{{{
-Daily ← γ_{u.id, u.username, date(ip.date_time) → intake_day;
-          SUM(ip.kcal) → kcal_consumed, COUNT(*) → meals_logged} (
-    σ_{ip.is_consumed = TRUE} (User ⋈_{u.id = ip.user_id} Intake_Planner)
-)
-
+    GROUP BY ip.user_id, ip.date_time::date
+),
+ranked AS (
+    SELECT d.*,
+           RANK() OVER (PARTITION BY d.user_id
+                        ORDER BY d.kcal_consumed DESC)      AS day_rank,
+           ROUND(AVG(d.kcal_consumed)
+                 OVER (PARTITION BY d.user_id))             AS avg_daily_kcal,
+           LAG(d.kcal_consumed) OVER (PARTITION BY d.user_id
+                                      ORDER BY d.intake_day) AS prev_day_kcal,
+           ROUND(AVG(d.kcal_consumed) OVER (PARTITION BY d.user_id
+                        ORDER BY d.intake_day
+                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1)
+                                                            AS moving_avg_3d
+    FROM daily d
+)
+SELECT (SELECT u.username FROM "user" u WHERE u.id = r.user_id) AS username,
+       r.intake_day,
+       r.kcal_consumed,
+       r.meals_logged,
+       r.avg_daily_kcal,
+       ROUND(r.kcal_consumed - r.prev_day_kcal, 2) AS change_vs_prev_day,
+       r.moving_avg_3d,
+       -- level 2: the weight measured closest in time to the peak day
+       (SELECT bm.weight
+        FROM biometrics bm
+        WHERE bm.user_id = r.user_id
+          AND bm.date = (SELECT bm2.date
+                         FROM biometrics bm2
+                         WHERE bm2.user_id = r.user_id
+                         ORDER BY abs(bm2.date - r.intake_day)
+                         LIMIT 1)) AS weight_near_peak,
+       -- level 3: the average peak day across all users, for context
+       (SELECT ROUND(AVG(peaks.peak))
+        FROM (SELECT MAX(inner_daily.k) AS peak
+              FROM (SELECT ip2.user_id AS uid,
+                           ip2.date_time::date AS d,
+                           SUM(ip2.kcal) AS k
+                    FROM intake_planner ip2
+                    WHERE ip2.is_consumed = TRUE
+                    GROUP BY ip2.user_id, ip2.date_time::date) inner_daily
+              GROUP BY inner_daily.uid) peaks) AS avg_peak_all_users,
+       -- level 3: how many users had a higher peak than this one
+       (SELECT COUNT(*)
+        FROM (SELECT MAX(x.k) AS peak
+              FROM (SELECT ip3.user_id AS uid,
+                           ip3.date_time::date AS d,
+                           SUM(ip3.kcal) AS k
+                    FROM intake_planner ip3
+                    WHERE ip3.is_consumed = TRUE
+                    GROUP BY ip3.user_id, ip3.date_time::date) x
+              GROUP BY x.uid) others
+        WHERE others.peak > r.kcal_consumed) AS users_with_higher_peak,
+       CASE WHEN r.kcal_consumed > 1.25 * r.avg_daily_kcal THEN 'spike day'
+            WHEN r.kcal_consumed < 0.75 * r.avg_daily_kcal THEN 'light day'
+            ELSE 'typical day' END AS day_profile
+FROM ranked r
+WHERE r.day_rank = 1
+ORDER BY r.kcal_consumed DESC;
+
+}}}
+=== Релациона алгебра - приказ (АЖУРИРАНО)
+{{{
+Daily    ← γ_{ip.user_id, date(ip.date_time) → intake_day;
+             SUM(ip.kcal) → kcal_consumed, COUNT(*) → meals_logged} (
+             σ_{ip.is_consumed = TRUE} (Intake_Planner))
+ 
+Ranked   ← ω_{PARTITION BY user_id;
+             RANK() ORDER BY kcal_consumed DESC     → day_rank,
+             AVG(kcal_consumed)                     → avg_daily_kcal,
+             LAG(kcal_consumed) ORDER BY intake_day → prev_day_kcal,
+             AVG(kcal_consumed) ORDER BY intake_day
+                 ROWS [-2, 0]                       → moving_avg_3d} (Daily)
+ 
+Peaks    ← γ_{user_id; MAX(kcal_consumed) → peak} (Daily)
+AvgPeak  ← γ_{; AVG(peak) → avg_peak_all_users} (Peaks)
+Higher(x) ← γ_{; COUNT(*) → users_with_higher_peak} (σ_{peak > x} (Peaks))
+ 
+Nearest(row) ← π_{weight} (
+               σ_{bm.user_id = row.user_id ∧
+                  bm.date ∈ π_{date}( τ_{|bm2.date − row.intake_day|}
+                                      (σ_{bm2.user_id = row.user_id}(Biometrics)) [1] )}
+               (Biometrics))
+ 
 τ_{kcal_consumed DESC} (
-  π_{username, intake_day, kcal_consumed, meals_logged, avg_daily_kcal} (
-    σ_{day_rank = 1} (
-      ω_{PARTITION BY u.id;
-         RANK() ORDER BY kcal_consumed DESC → day_rank,
-         AVG(kcal_consumed)                 → avg_daily_kcal} (Daily)
-    )
+  π_{username, intake_day, kcal_consumed, meals_logged, avg_daily_kcal,
+     kcal_consumed − prev_day_kcal → change_vs_prev_day, moving_avg_3d,
+     weight_near_peak, avg_peak_all_users, users_with_higher_peak, day_profile} (
+    σ_{day_rank = 1} (Ranked) ⋈_{user_id = u.id} User
+      ⟕ Nearest × AvgPeak × Higher(kcal_consumed)
   )
 )
+
 }}}
 == Извештај 4: Целосно растителни рецепти