3,021
edits
Code:food2.sql: Difference between revisions
no edit summary
(Created page with "Like Code:food1.sql, this analyzes food production data from FAO, and nutrition data from USDA. But while ''food1'' is focused on plants (crops Term:primary), ''food2'' is focused on animal products (livestock primary). <syntaxhighlight lang="sql"> -- 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...") |
No edit summary |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Like [[Code:food1.sql]], this analyzes food production data from FAO, and nutrition data from USDA. But while ''food1'' is focused on plants (crops [[Term:primary|primary]]), ''food2'' is focused on animal products (livestock primary). | Like [[Code:food1.sql]], this analyzes food production data from FAO, and nutrition data from USDA. But while ''food1'' is focused on plants (crops [[Term:primary|primary]]), ''food2'' is focused on animal products (livestock primary). | ||
Dependencies: | |||
* [[Code:fao2usda.csv]] | |||
* [[:File:sr28-collated.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 9: | Line 15: | ||
.mode csv | .mode csv | ||
-- cross-reference table 2: livestock products (FAO data) with foods (USDA nutrition data): | -- cross-reference table 2: livestock products (FAO data) with foods (USDA nutrition data): | ||
.import fao2usda.csv | .import fao2usda.csv fao2usda | ||
-- nutrition (USDA): | -- nutrition (USDA): | ||
.import data/sr28-collated.csv | .import data/sr28-collated.csv usda | ||
-- crop production (FAO): | -- crop production (FAO): | ||
.import data | .import data/fao-livestock-primary.csv animal | ||
.mode list | .mode list | ||
--## Animal products, worldwide production per capita | |||
CREATE TABLE summary AS | CREATE TABLE summary AS | ||
| Line 39: | 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 52: | 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> | ||