A9_FoodAnalysis_Documentation
docx
keyboard_arrow_up
School
University of Notre Dame *
*We aren’t endorsed by this school
Course
COP4708
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
2
Uploaded by MasterWater6219
Aggregation Documentation
The stored procedure "GetTopHealthyUnhealthyFoods" is designed to analyze the food database provided by Professor Eaglin and provide insights into the healthiness of various food items based on specific criteria. The stored procedure takes two input parameters: @RankingCriteria and @RankingCriteria, which determine the criteria for ranking and quartiles, respectively. It then calculates quartiles for healthy and unhealthy criteria using the PERCENTILE_CONT aggregation function. Next, it filters foods based on these quartiles and ranks The stored procedure returns the top 10 healthiest and unhealthiest foods based on the provided criteria. The result includes details such as display name, portion amount, portion display name, and the values of the criteria
used for quartile and rank.
The stored procedure prominently utilizes aggregation functions, particularly the PERCENTILE_CONT function, to calculate quartiles for healthy and unhealthy criteria. This aggregation function allows the procedure to determine the cutoff values for the first quartile, facilitating the subsequent filtering and ranking of
food items. By aggregating data across the entire dataset, the procedure efficiently identifies the top 25% of foods based on specified criteria, enhancing the accuracy of the analysis.
Below is the code I used to make the stored procedure:
@UnhealthySolidFatsQuartile = PERCENTILE_CONT
(
0.75
) WITHIN GROUP (
ORDER BY Solid_Fats
)
OVER ()
FROM FoodData
;
-- Get the top 10 healthiest foods
IF @RankingCriteria = 'Calories'
BEGIN
SELECT TOP 10 Display_Name
, Portion_Amount
, Portion_Display_Name
, Grains
, Whole_Grains
, Vegetables
, Calories
FROM FoodData
WHERE Grains <= @HealthyGrainsQuartile AND Whole_Grains <= @HealthyWholeGrainsQuartile
AND Vegetables <= @HealthyVegetablesQuartile
ORDER BY Calories ASC
;
END
ELSE IF @RankingCriteria = 'Milk'
BEGIN
SELECT TOP 10 Display_Name
, Portion_Amount
, Portion_Display_Name
, Grains
, Whole_Grains
, Vegetables
, Milk
FROM FoodData
WHERE Grains <= @HealthyGrainsQuartile AND Whole_Grains <= @HealthyWholeGrainsQuartile
AND Vegetables <= @HealthyVegetablesQuartile
ORDER BY Milk DESC
;
END
ELSE IF @RankingCriteria = 'Meats'
BEGIN
SELECT TOP 10 Display_Name
, Portion_Amount
, Portion_Display_Name
, Grains
, Whole_Grains
, Vegetables
, Meats
FROM FoodData
WHERE Grains <= @HealthyGrainsQuartile AND Whole_Grains <= @HealthyWholeGrainsQuartile
AND Vegetables <= @HealthyVegetablesQuartile
ORDER BY Meats DESC
;
END
;
-- Get the top 10 unhealthiest foods
IF @RankingCriteria = 'Calories'
BEGIN
SELECT TOP 10 Display_Name
, Portion_Amount
, Portion_Display_Name
, Added_Sugars
, Saturated_Fats
, Solid_Fats
, Calories
FROM FoodData
WHERE Added_Sugars >= @UnhealthySugarQuartile AND Saturated_Fats >= @UnhealthySaturatedFatsQuartile AND Solid_Fats >= @UnhealthySolidFatsQuartile
ORDER BY Calories DESC
;
END
ELSE IF @RankingCriteria = 'Milk'
BEGIN
SELECT TOP 10 Display_Name
, Portion_Amount
, Portion_Display_Name
, Added_Sugars
, Saturated_Fats
, Solid_Fats
, Milk
FROM FoodData
WHERE Added_Sugars >= @UnhealthySugarQuartile AND Saturated_Fats >= @UnhealthySaturatedFatsQuartile AND Solid_Fats >= @UnhealthySolidFatsQuartile
ORDER BY Milk DESC
;
END
ELSE IF @RankingCriteria = 'Meats'
EXEC [dbo]
.
[GetTopHealthyUnhealthyFoods] 'Milk'
EXEC [dbo]
.
[GetTopHealthyUnhealthyFoods] 'Meats'
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help