Code:food2.sql - Revision history

Elie at 21:58, 14 March 2023

2023-03-14T21:58:02Z

← Older revision		Revision as of 17:58, 14 March 2023
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 ~~and kalories~~		-- 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>

Elie at 18:36, 26 August 2022

2022-08-26T18:36:57Z

← Older revision		Revision as of 14:36, 26 August 2022
Line 4:		Line 4:
	* [[Code:fao2usda.csv]]		* [[Code:fao2usda.csv]]
	* [[:File:sr28-collated.csv]]		* [[:File:sr28-collated.csv]]
	* [[:File:~~PRIMARY-~~fao-livestock-primary.csv]]		* [[:File:fao-livestock-primary.csv]]


Line 14:		Line 14:
	.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 fao2usda		.import fao2usda.csv fao2usda
	-- nutrition (USDA):		-- nutrition (USDA):
	.import data/sr28-collated.csv usda		.import data/sr28-collated.csv usda
	-- crop production (FAO):		-- crop production (FAO):
	.import data~~/primary~~/fao/livestock-primary.csv animal		.import data/fao-livestock-primary.csv animal
	.mode list		.mode list

Elie at 13:55, 25 August 2022

2022-08-25T13:55:09Z

← Older revision		Revision as of 09:55, 25 August 2022
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:PRIMARY-fao-livestock-primary.csv]]

Elie: 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). -- 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..."

2022-08-25T13:25:43Z

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..."

New page

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).

<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 fao2usda
-- nutrition (USDA):
.import data/sr28-collated.csv usda
-- crop production (FAO):
.import data/primary/fao/livestock-primary.csv animal
.mode list

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 and kalories
SELECT SUM(protein_grams_per_day_per_capita) FROM summary;
SELECT SUM(kalories_per_day_per_capita) FROM summary;

-------------------------------
----- 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";
</syntaxhighlight>