Code:food2.sql: Difference between revisions
No edit summary |
No edit summary |
||
Line 6: | Line 6: | ||
* [[:File:fao-livestock-primary.csv]] | * [[:File:fao-livestock-primary.csv]] | ||
==Code== | |||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
-- Analysis of the production of animal products | -- Analysis of the production of animal products -- | ||
--------------------------------------------------- | |||
Line 21: | Line 22: | ||
.mode list | .mode list | ||
--## Animal products, worldwide production per capita | |||
CREATE TABLE summary AS | CREATE TABLE summary AS | ||
Line 44: | Line 49: | ||
-- Total animal protein | -- Total animal protein produced: | ||
SELECT SUM(protein_grams_per_day_per_capita) FROM summary; | SELECT SUM(protein_grams_per_day_per_capita) FROM summary; | ||
SELECT SUM(kalories_per_day_per_capita) FROM summary; | SELECT SUM(kalories_per_day_per_capita) FROM summary; | ||
-- Meat production from all ruminants: | |||
SELECT SUM(food_grams_per_day_per_capita), | |||
SUM(protein_grams_per_day_per_capita) FROM summary | |||
WHERE Item="Meat, buffalo" | |||
OR Item="Meat, cattle" | |||
OR Item="Meat, goat" | |||
OR Item="Meat, sheep"; | |||
-- Milk production from all livestock species: | |||
SELECT SUM(food_grams_per_day_per_capita)*0.9776 AS milk_mL_per_day_per_capita, -- Milk is slightly denser than water. To convert "grams" to "milliliters milk", multiply by 97.76% (according to GNU Units) | |||
SUM(protein_grams_per_day_per_capita) FROM summary WHERE Item LIKE "Milk%"; | |||
Line 57: | Line 79: | ||
SELECT COUNT(DISTINCT `Item`) FROM animal; | SELECT COUNT(DISTINCT `Item`) FROM animal; | ||
SELECT COUNT(DISTINCT `Item`) FROM animal WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes"; | SELECT COUNT(DISTINCT `Item`) FROM animal WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes"; | ||
-- Test that "World" is the only aggregate | |||
SELECT SUM(`Value`) FROM animal WHERE `Area` ="World" AND `Element`="Production" AND `Unit`="tonnes"; | |||
SELECT SUM(`Value`) FROM animal WHERE `Area`<>"World" AND `Element`="Production" AND `Unit`="tonnes"; | |||
</syntaxhighlight> | </syntaxhighlight> |
Latest revision as of 17:58, 14 March 2023
Like Code:food1.sql, this analyzes food production data from FAO, and nutrition data from USDA. But while food1 is focused on plants (crops primary), food2 is focused on animal products (livestock primary).
Dependencies:
Code
-- Analysis of the production of animal products --
---------------------------------------------------
-- Load data from various sources
.mode csv
-- cross-reference table 2: livestock products (FAO data) with foods (USDA nutrition data):
.import fao2usda.csv fao2usda
-- nutrition (USDA):
.import data/sr28-collated.csv usda
-- crop production (FAO):
.import data/fao-livestock-primary.csv animal
.mode list
--## Animal products, worldwide production per capita
CREATE TABLE summary AS
SELECT `Item`,
food_grams_per_day_per_capita,
food_grams_per_day_per_capita * 0.01*`Energy (kcal)` AS kalories_per_day_per_capita,
food_grams_per_day_per_capita * 0.01*`Protein (g)` AS protein_grams_per_day_per_capita,
food_grams_per_day_per_capita * 0.01*`Total lipid (fat) (g)` AS fat_grams_per_day_per_capita
FROM (
SELECT *,
AVG(`Value`) -- tonnes per year
* 2737.9093 -- convert to grams per day
/ 7.9e9 -- per capita, world population
--* 0.01*(100-`Refuse`)*`Multiplier` -- edible portion, but this is commented out because actually FAO already specifies that the data is for edible portion, and so does USDA
AS food_grams_per_day_per_capita
FROM animal
JOIN fao2usda USING (`Item Code (FAO)`)
JOIN usda USING (`NDB_No`)
WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes"
GROUP BY `Item`
);
-- Total animal protein produced:
SELECT SUM(protein_grams_per_day_per_capita) FROM summary;
SELECT SUM(kalories_per_day_per_capita) FROM summary;
-- Meat production from all ruminants:
SELECT SUM(food_grams_per_day_per_capita),
SUM(protein_grams_per_day_per_capita) FROM summary
WHERE Item="Meat, buffalo"
OR Item="Meat, cattle"
OR Item="Meat, goat"
OR Item="Meat, sheep";
-- Milk production from all livestock species:
SELECT SUM(food_grams_per_day_per_capita)*0.9776 AS milk_mL_per_day_per_capita, -- Milk is slightly denser than water. To convert "grams" to "milliliters milk", multiply by 97.76% (according to GNU Units)
SUM(protein_grams_per_day_per_capita) FROM summary WHERE Item LIKE "Milk%";
-------------------------------
----- DATA & CODE TESTING -----
-- Test that all items have "Production" in tonnes/year (both queries should return the same number)
SELECT COUNT(DISTINCT `Item`) FROM animal;
SELECT COUNT(DISTINCT `Item`) FROM animal WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes";
-- Test that "World" is the only aggregate
SELECT SUM(`Value`) FROM animal WHERE `Area` ="World" AND `Element`="Production" AND `Unit`="tonnes";
SELECT SUM(`Value`) FROM animal WHERE `Area`<>"World" AND `Element`="Production" AND `Unit`="tonnes";