| Version 8 (modified by , 5 days ago) ( diff ) |
|---|
Nutritioneer | Напредни извештаи од базата
| Ознака | Операција |
|---|---|
| σ | Selection |
| π | Projection |
| ⋈ | Join |
| ⟕ | Left outer join |
| γ | Аggregation |
| τ | Sorting |
| − | Difference |
| ω | Complete Set |
| ← | Temp relation |
| SUM(x | у) | Conditional Sum |
Извештај 1: Преглед на активноста во заедницата
Account Activity Log
Опис на барањата за податоци
Извештајот покажува колку е активна секоја сметка во четирите дела на апликацијата. За секој корисник ги брои креираните рецепти, објавите, коментарите и записите во дневникот, заедно со неговата улога од USER кон RECIPE, POST, COMMENT и INTAKE_PLANNER.
SQL - приказ
SELECT u.username,
u.role,
COUNT(DISTINCT r.id) AS recipes_created,
COUNT(DISTINCT p.id) AS posts_created,
COUNT(DISTINCT c.id) AS comments_written,
COUNT(DISTINCT ip.id) AS diary_entries
FROM "user" u
LEFT JOIN recipe r ON r.user_id = u.id
LEFT JOIN post p ON p.user_id = u.id
LEFT JOIN comment c ON c.user_id = u.id
LEFT JOIN intake_planner ip ON ip.user_id = u.id
GROUP BY u.id, u.username, u.role
ORDER BY recipes_created DESC, posts_created DESC, u.username;
Релациона алгебра - приказ
τ_{recipes_created DESC, posts_created DESC, username} (
γ_{u.id, u.username, u.role;
COUNT(DISTINCT r.id) → recipes_created,
COUNT(DISTINCT p.id) → posts_created,
COUNT(DISTINCT c.id) → comments_written,
COUNT(DISTINCT ip.id) → diary_entries} (
User ⟕_{u.id = r.user_id} Recipe
⟕_{u.id = p.user_id} Post
⟕_{u.id = c.user_id} Comment
⟕_{u.id = ip.user_id} Intake_Planner
)
)
Извештај 2: Профил на макронутриенти по порција
Health Stat Check
Опис на барањата за податоци
Протеини, јаглехидрати, масти и влакна во една порција од секој рецепт. Спојува две одделни M:N врски во еден нутритивен приказ: рецепт содржи состојки, а состојка содржи нутриенти. За секој рецепт пресметува колку грама од секој макронутриент дава една порција, и ги рангира рецептите по протеини.
- (нутриент на 100 g) × (употребени грамови) / 100 преку сите состојки, поделен со бројот на порции.
SQL - приказ
SELECT r.name AS recipe,
ROUND(r.kcal_sum / r.servings) AS kcal_per_portion,
ROUND(SUM(CASE WHEN n.description = 'Protein'
THEN icn.quantity * rci.ingredient_quantity / 100
END) / r.servings, 1) AS protein_g,
ROUND(SUM(CASE WHEN n.description = 'Total carbohydrate'
THEN icn.quantity * rci.ingredient_quantity / 100
END) / r.servings, 1) AS carbs_g,
ROUND(SUM(CASE WHEN n.description = 'Total fat'
THEN icn.quantity * rci.ingredient_quantity / 100
END) / r.servings, 1) AS fat_g,
ROUND(SUM(CASE WHEN n.description = 'Dietary fibre'
THEN icn.quantity * rci.ingredient_quantity / 100
END) / r.servings, 1) AS fibre_g
FROM recipe r
JOIN recipe_contains_ingredient rci ON rci.recipe_id = r.id
JOIN ingredient_contains_nutrient icn ON icn.ingredient_id = rci.ingredient_id
JOIN nutrient n ON n.id = icn.nutrient_id
WHERE n.type = 'macro'
GROUP BY r.id, r.name, r.kcal_sum, r.servings
ORDER BY protein_g DESC;
Релациона алгебра - приказ
amount ≡ icn.quantity · rci.ingredient_quantity / 100
τ_{protein_g DESC} (
γ_{r.id, r.name, r.kcal_sum, r.servings;
SUM(amount | n.description = 'Protein') / r.servings → protein_g,
SUM(amount | n.description = 'Total carbohydrate') / r.servings → carbs_g,
SUM(amount | n.description = 'Total fat') / r.servings → fat_g,
SUM(amount | n.description = 'Dietary fibre') / r.servings → fibre_g} (
σ_{n.type = 'macro'} (
Recipe
⋈_{r.id = rci.recipe_id} Recipe_Contains_Ingredient
⋈_{rci.ingredient_id = icn.ingredient_id} Ingredient_Contains_Nutrient
⋈_{icn.nutrient_id = n.id} Nutrient
)
)
)
Извештај 3: Ден со најголем внес по корисник (АЖУРИРАНО)
Callorie-maxing view
Опис на барањата за податоци
Фокус на денот со најмногу калории на секој корисник наспроти неговиот сопствен просек. секој корисник што води дневник на исхрана, извештајот го наоѓа денот во кој внел најмногу енергија. Тој ден го споредува со просечниот дневен внес на истиот корисник.
- RANK() го избира најтешкиот ден
- AVG() врз партицијата личниот просек
- LAG() претходниот ден
- втор AVG() со ROWS BETWEEN 2 PRECEDING AND CURRENT ROW тридневен подвижен просек.
- weight_near_peak: тежината чиј датум е еднаков на датумот избран од внатрешен потпрашалник што ги подредува мерењата по временска оддалеченост од врвниот ден.
- avg_peak_all_users и users_with_higher_peak: агрегат врз изведена табела од максимуми по корисник, која самата е агрегат врз изведена табела од дневни збирови.
SQL - приказ
WITH daily AS (
SELECT ip.user_id,
ip.date_time::date AS intake_day,
SUM(ip.kcal) AS kcal_consumed,
COUNT(*) AS meals_logged
FROM intake_planner ip
WHERE ip.is_consumed = TRUE
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,
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: Целосно растителни рецепти
Go Green Filter
Опис на барањата за податоци
Две релациски делења во еден прашалник, и двете напишани како NOT EXISTS во NOT EXISTS — двојната негација со која во SQL се изразува „за сите".
- Првото делење прима рецепт само ако не постои негова состојка што не е од растителна категорија.
- Второто го прима само ако не постои макронутриент што рецептот не го дава.
- Делителот на второто сам се пресметува со вгнезден EXISTS: не сите макронутриенти од каталогот, туку само оние што навистина се запишани кај некоја состојка.
- Резултатот истовремено е и проверка на конзистентност.
- Медот и јајцето се од типот other, па го дисквалификуваат рецептот како што треба.
SQL - приказ
SELECT r.name AS recipe,
(SELECT u.username FROM "user" u WHERE u.id = r.user_id) AS author,
ROUND(r.kcal_sum / r.servings) AS kcal_per_portion,
(SELECT COUNT(*) FROM recipe_contains_ingredient rci
WHERE rci.recipe_id = r.id) AS ingredients,
(SELECT COUNT(DISTINCT icn.nutrient_id)
FROM recipe_contains_ingredient rci
JOIN ingredient_contains_nutrient icn
ON icn.ingredient_id = rci.ingredient_id
WHERE rci.recipe_id = r.id
AND icn.quantity > 0
AND icn.nutrient_id IN (SELECT n.id FROM nutrient n
WHERE n.type = 'macro')) AS macros_supplied,
EXISTS (SELECT 1
FROM recipe_contains_restriction rcr
WHERE rcr.recipe_id = r.id
AND rcr.restriction_id IN (SELECT res.id FROM restriction res
WHERE res.type = 'vegan')) AS tagged_vegan
FROM recipe r
WHERE
NOT EXISTS (
SELECT 1
FROM recipe_contains_ingredient rci
WHERE rci.recipe_id = r.id
AND NOT EXISTS (
SELECT 1
FROM ingredient i
WHERE i.id = rci.ingredient_id
AND i.type IN ('vegetable', 'fruit', 'legume', 'nut', 'seed',
'oil', 'herb', 'spice', 'carb', 'beverage')))
AND NOT EXISTS (
SELECT 1
FROM nutrient n
WHERE n.type = 'macro'
AND EXISTS (SELECT 1 FROM ingredient_contains_nutrient icn0
WHERE icn0.nutrient_id = n.id)
AND NOT EXISTS (
SELECT 1
FROM recipe_contains_ingredient rci2
JOIN ingredient_contains_nutrient icn
ON icn.ingredient_id = rci2.ingredient_id
WHERE rci2.recipe_id = r.id
AND icn.nutrient_id = n.id
AND icn.quantity > 0))
ORDER BY kcal_per_portion;
Релациона алгебра - приказ
Plant ← {'vegetable','fruit','legume','nut','seed','oil','herb','spice','carb','beverage'}
AllRec ← π_{recipe_id} (Recipe_Contains_Ingredient)
-- division 1: recipes that use an ingredient outside Plant
NonPlant ← π_{rci.recipe_id} (
σ_{i.type ∉ Plant} (
Recipe_Contains_Ingredient ⋈_{rci.ingredient_id = i.id} Ingredient))
-- the divisor: macronutrients that are recorded for at least one ingredient
Macros ← π_{n.id} (σ_{n.type = 'macro'} (Nutrient) ⋉ Ingredient_Contains_Nutrient)
-- what each recipe actually supplies
Supplied ← π_{rci.recipe_id, icn.nutrient_id} (
σ_{icn.quantity > 0} (
Recipe_Contains_Ingredient
⋈_{rci.ingredient_id = icn.ingredient_id}
Ingredient_Contains_Nutrient))
-- division 2, written as a difference: recipes missing at least one macro
Missing ← π_{recipe_id} ( (AllRec × Macros) − Supplied )
Result ← AllRec − NonPlant − Missing
τ_{kcal_per_portion} (
π_{name, username → author, kcal_sum/servings → kcal_per_portion,
ingredients, macros_supplied, tagged_vegan} (
Result ⋈_{recipe_id = r.id} (Recipe ⋈_{r.user_id = u.id} User)
)
)
AI алатки во процесот
Искрористен беше агентот „deepseek.ai“ за тестирање на функциите на релационата алгебра во локален sandbox и зафаќање на edge-cases и оптимизирање на извештаите.
Навигација низ проектот
| Фаза | Име на фаза | Статус |
|---|---|---|
| P0 | Дефинирање проект | Одобрен |
| P1 | Концептуален дизајн и ЕР Дијаграм | Одобрен |
| P2 | Логички и физички дизајн - DDL | Одобрен |
| P3 | Кориснички/апикациски сценарија со базата на податоци | Одобрен |
| P4 | Протип со основни функционалности | WIP |
| М1 | Презентација на прототип | WIP |
| P5 | Нормализација и оптимизација на дизајн | WIP |
| P6 | Напредни извештаи од базата | WIP |
| P7 | Понапреден развој на базата | WIP |
| P8 | Напреден апликативен развој | WIP |
| P9 | Дополнителни имплементации | WIP |
| М2 | Финализиран проект | WIP |
(Trac навигација)
- TracGuide Documentation
Note:
See TracWiki
for help on using the wiki.
