voedingscentrum/migrations.pgsql

CREATE TABLE producten (guid TEXT, naam TEXT, energie_kcal NUMERIC,
                        vet_g NUMERIC,
                        verzadigd_vet_g NUMERIC,
                        koolhydraten_g NUMERIC,
                        eiwit_g NUMERIC,
                        vezels_g NUMERIC,
                        zout_g NUMERIC,
                        alcohol_g NUMERIC,
                        water_g NUMERIC,
                        natrium_mg NUMERIC,
                        kalium_mg NUMERIC,
                        calcium_mg NUMERIC,
                        magnesium_mg NUMERIC,
                        ijzer_mg NUMERIC,
                        selenium_µg NUMERIC,
                        zink_mg NUMERIC,
                        vitamine_a_µg NUMERIC,
                        vitamine_d_µg NUMERIC,
                        vitamine_e_mg NUMERIC,
                        vitamine_b1_g NUMERIC,
                        vitamine_b2_mg NUMERIC,
                        vitamine_b6_mg NUMERIC,
                        foliumzuur_µg NUMERIC,
                        vitamine_b12_µg NUMERIC,
                        nicotinezuur_mg NUMERIC,
                        vitamine_c_mg NUMERIC,
                        jodium_µg NUMERIC,
                        fosfor_mg NUMERIC,
                        suikers_g NUMERIC,
                        eiwit_plantaardig_g NUMERIC);

CREATE TABLE consumpties_USERNAME (id SERIAL, datum DATE, product_guid TEXT, periode TEXT, hoeveelheid_g NUMERIC);

CREATE VIEW voedingswaarden_per_dag_USERNAME AS
(WITH sub AS (SELECT datum,
                     hoeveelheid_g / 100 * energie_kcal AS energie_kcal,
                     hoeveelheid_g / 100 * vet_g AS vet_g,
                     hoeveelheid_g / 100 * verzadigd_vet_g AS verzadigd_vet_g,
                     hoeveelheid_g / 100 * koolhydraten_g AS koolhydraten_g,
                     hoeveelheid_g / 100 * eiwit_g AS eiwit_g,
                     hoeveelheid_g / 100 * vezels_g AS vezels_g,
                     hoeveelheid_g / 100 * zout_g AS zout_g,
                     hoeveelheid_g / 100 * alcohol_g AS alcohol_g,
                     hoeveelheid_g / 100 * water_g AS water_g,
                     hoeveelheid_g / 100 * natrium_mg AS natrium_mg,
                     hoeveelheid_g / 100 * kalium_mg AS kalium_mg,
                     hoeveelheid_g / 100 * calcium_mg AS calcium_mg,
                     hoeveelheid_g / 100 * magnesium_mg AS magnesium_mg,
                     hoeveelheid_g / 100 * ijzer_mg AS ijzer_mg,
                     hoeveelheid_g / 100 * selenium_µg AS selenium_µg,
                     hoeveelheid_g / 100 * zink_mg AS zink_mg,
                     hoeveelheid_g / 100 * vitamine_a_µg AS vitamine_a_µg,
                     hoeveelheid_g / 100 * vitamine_d_µg AS vitamine_d_µg,
                     hoeveelheid_g / 100 * vitamine_e_mg AS vitamine_e_mg,
                     hoeveelheid_g / 100 * vitamine_b1_g AS vitamine_b1_g,
                     hoeveelheid_g / 100 * vitamine_b2_mg AS vitamine_b2_mg,
                     hoeveelheid_g / 100 * vitamine_b6_mg AS vitamine_b6_mg,
                     hoeveelheid_g / 100 * foliumzuur_µg AS foliumzuur_µg,
                     hoeveelheid_g / 100 * vitamine_b12_µg AS vitamine_b12_µg,
                     hoeveelheid_g / 100 * nicotinezuur_mg AS nicotinezuur_mg,
                     hoeveelheid_g / 100 * vitamine_c_mg AS vitamine_c_mg,
                     hoeveelheid_g / 100 * jodium_µg AS jodium_µg,
                     hoeveelheid_g / 100 * fosfor_mg AS fosfor_mg,
                     hoeveelheid_g / 100 * suikers_g AS suikers_g,
                     hoeveelheid_g / 100 * eiwit_plantaardig_g AS eiwit_plantaardig_g
                FROM consumpties_USERNAME
                JOIN producten
                  ON consumpties_USERNAME.product_guid = producten.guid)
  SELECT datum,
         SUM(energie_kcal) AS energie_kcal,
         SUM(vet_g) AS vet_g,
         SUM(verzadigd_vet_g) AS verzadigd_vet_g,
         SUM(koolhydraten_g) AS koolhydraten_g,
         SUM(eiwit_g) AS eiwit_g,
         SUM(vezels_g) AS vezels_g,
         SUM(zout_g) AS zout_g,
         SUM(alcohol_g) AS alcohol_g,
         SUM(water_g) AS water_g,
         SUM(natrium_mg) AS natrium_mg,
         SUM(kalium_mg) AS kalium_mg,
         SUM(calcium_mg) AS calcium_mg,
         SUM(magnesium_mg) AS magnesium_mg,
         SUM(ijzer_mg) AS ijzer_mg,
         SUM(selenium_µg) AS selenium_µg,
         SUM(zink_mg) AS zink_mg,
         SUM(vitamine_a_µg) AS vitamine_a_µg,
         SUM(vitamine_d_µg) AS vitamine_d_µg,
         SUM(vitamine_e_mg) AS vitamine_e_mg,
         SUM(vitamine_b1_g) AS vitamine_b1_g,
         SUM(vitamine_b2_mg) AS vitamine_b2_mg,
         SUM(vitamine_b6_mg) AS vitamine_b6_mg,
         SUM(foliumzuur_µg) AS foliumzuur_µg,
         SUM(vitamine_b12_µg) AS vitamine_b12_µg,
         SUM(nicotinezuur_mg) AS nicotinezuur_mg,
         SUM(vitamine_c_mg) AS vitamine_c_mg,
         SUM(jodium_µg) AS jodium_µg,
         SUM(fosfor_mg) AS fosfor_mg,
         SUM(suikers_g) AS suikers_g,
         SUM(eiwit_plantaardig_g) AS eiwit_plantaardig_g
    FROM sub
GROUP BY datum
ORDER BY datum ASC);