Прототип простого сервиса для здорового питания

image


В контексте статьи картинка обретает двойной смысл.


Дисклеймер


В обществе в принципе много "наносного" касательно "правильной" еды, "диет", "стиля жизни" и прочего. Как правило это просто проявления относительно высокого уровня зарплат в конкретном регионе и низкого уровня грамотности людей. Мы с моей девушкой поставили для себя очень простую задачу — есть вкусно, просто, дешево, правильно и быстро.


И так получилось, что при выборе инструмента планирования такого питания взор пал на PostgreSQL, который стоит на домашнем сервере. Аналогично можно легко сделать и в Excel или Гугл-таблицах, но в нашем случае SQL оказался более быстрым + есть открытые базы с готовыми данными. Данная статья может рассматриваться вами как "заготовка" для аналогичного SQL сервиса или просто как идея, которую вы можете взять и применить для себя.
Также обратите внимание — чтобы пользоваться этим в полной мере, вы должны хотя бы чуточку уметь в табличные процессоры (Excel).


Группы людей, которым "зайдет" статья:


Группы людей, которым не "зайдет" статья:


Вступление:


Так уж получилось, что некоторое время я жил в Москве. Москва (в моем мире), как правило, характеризуется:


Низким качеством доступного питания или высокой ценой чуть менее доступного;
Общей резиновостью продуктов из магазина (если вы были на Кипре хоть раз — вы поймете);
Отсутствием способов питаться одновременно и правильно, и дешево и вкусно не прилагая усилий (не говорю про маркетинговые сервисы с маржинальностью в 50-60%, где все равно надо готовить);
Наличием рынков, где в принципе все доступно по нормальным ценам, но в "сыром" виде;


TLDR для нетерпеливых и продвинутых:



Результат:


По этой причине мы решили сделать мини-сервис для себя, куда мы внесли простые блюда которые нам нравятся. Вообще мы пробовали работать с базой USDA — но она оказалась избыточно сложной. По такому сервису мы попробовали покупать еду в течение ~ 10 недель, и выяснили по опыту, что:



Вообще, может когда-нибудь руки дойдут и до такого (описание как превратить алгоритм в приложение и продукт), но пока общение с коллегами и рынок подсказывают, что "богатые" готовить не будут (скорее заплатят маржу в 50-80% сервисам), а "бедные" в России — не будут платить за приложение.


Почему "наивная" оптимизация по калориям? Почему не по белкам?


Потому, что работает, а механизма идеального измерения потребностей все равно нет (или мы его не знаем).


Функция get_random_menu() также отдает идеальное потребление калорий, белков и углеводов — вы можете сравнивать руками. Я пробовал применять алгоритмы линейной и нелинейной оптимизации в Питоне (сгенерировал 10,000 меню случайно, попробовал улучшать веса для "идеальной" подгонки, за час-два не добился результата, оставил), но скорее всего из-за нашего набора блюд там в принципе не особо возможно иметь 100% попадание — белков и углеводов в среднем меньше на 15-20% чем "идеал".


Описание технической составляющей, базы и функций:


В общем заполнение структуры данных и написание функций заняло примерно 3-4 часа на таблицы и 2-3 часа на функции и хорошо выражается ER схемой:


image


Обратите внимание на то, что:



Также есть 2 таблицы с описанием потребностей людей, их модификация требуется для изменения числа людей


image


Проще всего понять (если вы знаете SQL) как это все работает посмотрев на пару основных функций


getPrimitiveMenu



CREATE OR REPLACE FUNCTION "usda28"."getPrimitiveMenu"()
  RETURNS SETOF "pg_catalog"."record" AS $BODY$
BEGIN
   RETURN QUERY
            SELECT
                raw_data1.week_day ::INTEGER as week_day,
                raw_data1.meal_order :: INTEGER as meal_id, 
                raw_data1.meal :: VARCHAR as meal,
                raw_data1.balance ::NUMERIC as dish_share,
                raw_data1.dish_type :: VARCHAR as dish_type,
                d.title :: VARCHAR as dish_title,
                d.deliciousness :: INTEGER as dish_taste,
                dc.portion :: NUMERIC as proportion,
                di.id::INTEGER as dish_ingredient_id,
                di.title ::VARCHAR as di_title,
                di.calories :: INTEGER as calories_per_100,
                di.carbs :: INTEGER as carbs_per_100,
                di.fat :: INTEGER as fat_per_100,
                di.protein :: INTEGER as protein_per_100
            FROM
                (
                    SELECT
                        dsc.calorie_balance     as balance,
                        ds.title                            as meal,
                        dsc.dish_serving_id,
                        dsc.choice_id,
                        ds.id                               as meal_order,
                        dt.title                            as dish_type,
                        presets.week_day            as week_day,
                        (
                            SELECT
                                d."id"
                            FROM
                                usda28.dish d 
                                /* Checking that the dish has actual ingredients, otherwise errors are possible */
                                JOIN usda28.dish_contents       dc  ON dc.dish_id = d."id"      
                            WHERE
                                d.dish_type_id = dsc.dish_type_id
                            ORDER BY    
                                random()
                            LIMIT 
                                1
                        ) as dish_id
                    FROM
                        (
                            SELECT
                                servings_count.dsc_id as dsc_id,
                                trunc(servings_count.choice_count * random() + 1)::INTEGER as preset_choice,
                                unnest(ARRAY[1,2,3,4,5,6,7]) as week_day
                            FROM
                            (
                            SELECT DISTINCT
                                dsc.dish_serving_id as dsc_id,
                                COUNT(DISTINCT dsc.choice_id) as choice_count
                            FROM
                                usda28.dish_serving_choice dsc
                            GROUP BY
                                dsc.dish_serving_id
                            ) servings_count
                            ORDER BY
                                unnest(ARRAY[1,2,3,4,5,6,7])
                        ) presets
                        JOIN usda28.dish_serving_choice     dsc     ON dsc.choice_id = presets.preset_choice AND dsc.dish_serving_id = presets.dsc_id
                        JOIN usda28.dish_serving                    ds      ON ds."id" = dsc.dish_serving_id
                        JOIN usda28.dish_type                       dt      ON dt."id" = dsc.dish_type_id
                    ORDER BY 
                        presets.week_day ASC,
                        dsc.dish_serving_id ASC
                ) raw_data1
                JOIN usda28.dish                            d   ON d."id" = raw_data1.dish_id
                JOIN usda28.dish_contents       dc  ON dc.dish_id = d."id"
                JOIN usda28.dish_ingredient     di  ON di."id" = dc.ingredient_id 
            ORDER BY
                raw_data1.week_day ASC, 
                raw_data1.meal_order ASC,
                d.title ASC;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
 ROWS 1000
;

get_random_menu



CREATE OR REPLACE FUNCTION "usda28"."get_random_menu"()
  RETURNS "pg_catalog"."json" AS $BODY$ SELECT
        to_json(array_agg(a))
    FROM 
    (
SELECT
    (SELECT md5(''||now()::text||random()::text) as menu_uuid),
    raw_data.week_day as week_day,
    raw_data.meal_id as meal_id,
    raw_data.meal as meal,
    raw_data.dish_type as dish_type,
    raw_data.dish_title as dish_title,
    raw_data.dish_ingredient_id as dish_ingredient_id,
    raw_data.ingredient_title as ingredient_title,
    raw_data.dish_share,
    raw_data.proportion,
    raw_data.calories_per_100,
    raw_data.carbs_per_100,
    raw_data.fat_per_100,
    raw_data.protein_per_100,
    trunc( raw_data.proportion * raw_data.dish_share * raw_data.calories * 100 / SUM(raw_data.stat_weight)  OVER (PARTITION BY  
        raw_data.week_day,
        raw_data.meal_id,
        raw_data.meal,
        raw_data.dish_type,
        raw_data.dish_title
    ) )as grams_guesstimate
FROM 
(
        SELECT
            menu.week_day as week_day,
            menu.meal_id as meal_id,
            menu.meal as meal,
            menu.dish_type as dish_type,
            menu.dish_title as dish_title,
            menu.dish_ingredient_id as dish_ingredient_id,
            menu.di_title as ingredient_title,
            menu.dish_share,
            menu.proportion,
            menu.calories_per_100,
            menu.carbs_per_100,
            menu.fat_per_100,
            menu.protein_per_100,
            nut.calories,
            menu.proportion * menu.calories_per_100 as stat_weight
        FROM
            (
            SELECT
                week_day,
                meal_id,
                meal,
                dish_share,
                dish_type,
                dish_title,
                dish_taste,
                proportion,
                dish_ingredient_id,
                di_title,
                calories_per_100,
                carbs_per_100,
                fat_per_100,
                protein_per_100
            FROM
                usda28."getPrimitiveMenu"()
            ) menu
            JOIN
                (
                SELECT
                    SUM (rdi.carbs) * mlp.proportion as carbs,
                    SUM (rdi.fat) * mlp.proportion as fat,
                    SUM (rdi.protein) * mlp.proportion as protein,
                    SUM (rdi.calories) * mlp.proportion as calories,
                    ml.title as meal_title,
                    ml."id" as meal_id
                FROM
                    usda28.recommended_daily_intake         rdi
                    JOIN usda28.activity_types              atp     ON atp."id" = rdi.activity_type_id AND atp."id" = 1 
                    JOIN usda28.meal_proportions            mlp     ON 1=1
                    JOIN usda28.dish_serving                        ml      ON ml.id = mlp.meal_id
                GROUP BY
                    ml.title,
                    mlp.proportion,
                    ml."id"
                ) nut ON nut.meal_id = menu.meal_id
) raw_data
ORDER BY
    raw_data.meal_id ASC,
    raw_data.week_day ASC,
    raw_data.dish_type ASC
) a
$BODY$
  LANGUAGE 'sql' VOLATILE COST 100
;

Если понравилось — пишите в личку.

@snakers4
24.04.2017 18:15 UTC
Первоисточник