From 42c71faa73b89c5bf1acea6626d8df8b92ebd3b7 Mon Sep 17 00:00:00 2001 From: Shane Jaroch Date: Sat, 24 Feb 2024 11:21:37 -0500 Subject: [PATCH] add std_dev extension for building USDA sqlite --- .gitignore | 1 + docs/usda.svg | 55 +++++++++++++++++++++++++++++------------ sql/extensions/fetch.sh | 5 ++++ sql/tables_aux.sql | 7 ++++-- 4 files changed, 50 insertions(+), 18 deletions(-) create mode 100755 sql/extensions/fetch.sh diff --git a/.gitignore b/.gitignore index 94196e7..8ee6227 100644 --- a/.gitignore +++ b/.gitignore @@ -11,6 +11,7 @@ # Our files .env* *.swp +*.so # Database files, csv, intermediates *.db diff --git a/docs/usda.svg b/docs/usda.svg index 13672a7..cf688fc 100644 --- a/docs/usda.svg +++ b/docs/usda.svg @@ -4,26 +4,26 @@ - + undefined - -usda.sqlite + +usda.sqlite version - -version - -id* -integer -version -text -created -timestamp -notes -text + +version + +id* +integer +version +text +created +timestamp +notes +text @@ -137,7 +137,7 @@ num_data_pts int std_err -float +float src_cd text deriv_cd @@ -348,5 +348,28 @@ + + +nutrients_overview + +nutrients_overview + +id +int +rda +real +unit +text +tagname +text +nutr_desc +text +n_foods +int +avg_val +real +std_dev +real + diff --git a/sql/extensions/fetch.sh b/sql/extensions/fetch.sh new file mode 100755 index 0000000..bbf5b95 --- /dev/null +++ b/sql/extensions/fetch.sh @@ -0,0 +1,5 @@ +#!/bin/bash -e + +rm -f *.so *.zip +wget https://github.com/nalgeon/sqlean/releases/download/0.21.10/sqlean-linux-x86.zip +unzip *.zip diff --git a/sql/tables_aux.sql b/sql/tables_aux.sql index ca1e720..4aae589 100644 --- a/sql/tables_aux.sql +++ b/sql/tables_aux.sql @@ -1,3 +1,5 @@ +-- NOTE: need to first run: fetch.sh +.load ./extensions/stats -- Saves time intensive query in new table CREATE TABLE nutrients_overview AS SELECT @@ -6,8 +8,9 @@ SELECT unit, tagname, nutr_desc, - COUNT(nut_data.nutr_id) AS n_foods, - ROUND(avg(nut_data.nutr_val), 3) AS avg_val + CAST(COUNT(nut_data.nutr_id) AS INTEGER) AS n_foods, + CAST(ROUND(avg(nut_data.nutr_val), 3) AS real) AS avg_val, + CAST(ROUND(stddev(nut_data.nutr_val), 2) AS real) AS std_dev FROM nutr_def INNER JOIN nut_data ON nut_data.nutr_id = id -- 2.52.0