Напредни извештаи од базата (SQL и складирани процедури)

Извештаи за корисник според тромесечие, просечен број на калории, масти, протеини и јаглени хидрати

SELECT
  U.name AS UserName,
  DATE_TRUNC('quarter', D.date) AS QuarterStart,
  AVG(COALESCE(I.calories, 0)) AS AvgCalories,
  AVG(COALESCE(I.fats, 0)) AS AvgFats,
  AVG(COALESCE(I.protein, 0)) AS AvgProteins,
  AVG(COALESCE(I.carbs, 0)) AS AvgCarbs
FROM
  _User U
JOIN
  UserHasDays UHD ON U.uID = UHD.uID
JOIN
  _Day D ON UHD.dID = D.dID
LEFT JOIN
  DayHasMeal DHM ON D.dID = DHM.dID
LEFT JOIN
  Meal M ON DHM.mID = M.mID
LEFT JOIN
  MealHasIngredient MHI ON M.mID = MHI.mID
LEFT JOIN
  Ingredient I ON MHI.iID = I.iID
GROUP BY
  U.name, DATE_TRUNC('quarter', D.date)
ORDER BY
  U.name, QuarterStart;

Извештаи за корисник според тромесечие, просечно времетраење на тренинг

SELECT
  U.name AS UserName,
  DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID)) AS QuarterStart,
  AVG(W.duration) AS AvgWorkoutDurationMins
FROM
  _User U
JOIN
  UserHasDays UHD ON U.uID = UHD.uID
JOIN
  DayHasWorkout DHW ON UHD.dID = DHW.dID
JOIN
  Workout W ON DHW.wID = W.wID
WHERE
  DHW.wID IS NOT NULL
  AND W.duration IS NOT NULL
GROUP BY
  U.name, DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID))
ORDER BY
  U.name, QuarterStart;