В контексте статьи картинка обретает двойной смысл.
Дисклеймер
В обществе в принципе много "наносного" касательно "правильной" еды, "диет", "стиля жизни" и прочего. Как правило это просто проявления относительно высокого уровня зарплат в конкретном регионе и низкого уровня грамотности людей. Мы с моей девушкой поставили для себя очень простую задачу — есть вкусно, просто, дешево, правильно и быстро.
И так получилось, что при выборе инструмента планирования такого питания взор пал на PostgreSQL, который стоит на домашнем сервере. Аналогично можно легко сделать и в Excel или Гугл-таблицах, но в нашем случае SQL оказался более быстрым + есть открытые базы с готовыми данными. Данная статья может рассматриваться вами как "заготовка" для аналогичного SQL сервиса или просто как идея, которую вы можете взять и применить для себя.
Также обратите внимание — чтобы пользоваться этим в полной мере, вы должны хотя бы чуточку уметь в табличные процессоры (Excel).
Группы людей, которым "зайдет" статья:
- Люди, которые знают SQL хотя бы на уровне простейших запросов (если вы DBA — некоторые упрощения здесь сделаны для экономии времени, а не по той причине, о которой вы подумали) ;
- Люди, которые хотят контролировать что они едят;
- Люди, которые хотят есть вкусно, недорого, просто и полезно;
- Люди, которые хотят попробовать поковыряться в SQL-сервере или просто уверены в использовании этого инструмента;
Группы людей, которым не "зайдет" статья:
- Почему функция возвращает просто запрос? Надо в 100 раз сложнее!
- Так схемы делают только… / я ем все что попало / зачем SQL — давайте вообще монгу / кто так форматирует запросы итд;
- Готовить — не барское дело!
- Надо все нормализовать еще на 10 рядов!
Вступление:
Так уж получилось, что некоторое время я жил в Москве. Москва (в моем мире), как правило, характеризуется:
Низким качеством доступного питания или высокой ценой чуть менее доступного;
Общей резиновостью продуктов из магазина (если вы были на Кипре хоть раз — вы поймете);
Отсутствием способов питаться одновременно и правильно, и дешево и вкусно не прилагая усилий (не говорю про маркетинговые сервисы с маржинальностью в 50-60%, где все равно надо готовить);
Наличием рынков, где в принципе все доступно по нормальным ценам, но в "сыром" виде;
TLDR для нетерпеливых и продвинутых:
- Скачайте себе дамп базы PostgreSQL (9.5+) базы по ссылке и распакуйте;
- Если устраивают пресеты, которые есть, то просто вбейте запрос:
SELECT * FROM get_random_menu() - Запрос отдаст вам json (если вы дочитали до сюда, то вы легко поменяете на нужный вам формат или дернете запрос каким-нибудь драйвером) с меню на неделю (7 дней) из расчета:
- 2 взрослых человека — мужчина и женщина (recommended_daily_intake);
- Расчет количества еды — просто экстраполяцией через калорийность (почему — описано ниже);
-
- Питание 4 раза в сутки
-
- Завтрак из нескольких блюд;
-
- Обед;
-
- Ужин;
-
- Перекус вечером;
- Меню выбирается случайно при каждом запросе;
- Если не устраивают пресеты или нужно поменять количество людей (тут установлено для мужчины и женщины со средними потребностями):
- Функция get_random_menu() подскажет, что поменять;
- Основные таблицы, которые стоит подстраиваться под себя
-
- recommended_daily_intake;
-
- Все таблицы, содержащие слово dish;
-
- Структура немного сложная, но она станет понятной, если вы почитаете запросы;
Результат:
По этой причине мы решили сделать мини-сервис для себя, куда мы внесли простые блюда которые нам нравятся. Вообще мы пробовали работать с базой USDA — но она оказалась избыточно сложной. По такому сервису мы попробовали покупать еду в течение ~ 10 недель, и выяснили по опыту, что:
- В алгоритме были разные баги с пропорциями, которые мы поправили (15 килограмм квашеной капусты в неделю это сильно);
- В среднем в неделю на 2 человек уходит порядка 4 000 рублей (!) + 1.5-2 часа на покупку + 30-40 минут на готовку вдвоем ежедневно. В месяц получается в районе 8 000-10 000 рублей на человека;
- Девушка еще и значительно похудела (приятный бонус);
- Оба перестали тратить деньги на покупку пищи вообще. И на обеды тоже;
- Самый удобный формат работы с базой — выгрузил через любую доступную программу в таблицу, сделал сводную таблицу, залил на телефон;
- Отмечать готовку блюд проще всего с телефона / компьютера в таблице;
- Простая и вкусная еда и огромная экономия времени на планировании (10 часов макс. на разработку этого позволяет жить почти 3 месяца не думая про еду);
Вообще, может когда-нибудь руки дойдут и до такого (описание как превратить алгоритм в приложение и продукт), но пока общение с коллегами и рынок подсказывают, что "богатые" готовить не будут (скорее заплатят маржу в 50-80% сервисам), а "бедные" в России — не будут платить за приложение.
Почему "наивная" оптимизация по калориям? Почему не по белкам?
Потому, что работает, а механизма идеального измерения потребностей все равно нет (или мы его не знаем).
Функция get_random_menu() также отдает идеальное потребление калорий, белков и углеводов — вы можете сравнивать руками. Я пробовал применять алгоритмы линейной и нелинейной оптимизации в Питоне (сгенерировал 10,000 меню случайно, попробовал улучшать веса для "идеальной" подгонки, за час-два не добился результата, оставил), но скорее всего из-за нашего набора блюд там в принципе не особо возможно иметь 100% попадание — белков и углеводов в среднем меньше на 15-20% чем "идеал".
Описание технической составляющей, базы и функций:
В общем заполнение структуры данных и написание функций заняло примерно 3-4 часа на таблицы и 2-3 часа на функции и хорошо выражается ER схемой:
Обратите внимание на то, что:
- Лучше использовать PostgreSQL 9.5+, т.к. используется несколько функции по работе с json, которые не так давно появились;
- Есть понятие блюда (dish), есть понятие того в рамках каких приемов пищи оно может приниматься (dish_serving_choice, dish_serving);
- dish_menu — пример лога купленной пищи, потом оказалось проще собирать в экселе;
- Не каждое блюдо можно есть, скажем, на завтрак для этого есть dish_type;
- Блюда состоят из ингредиентов (dish_ingredient), но не содержат воды (в ней нет калорий — оптимизация не сработает) — все пропорции посчитаны с учетом этого факта;
- Мы приняли по сути несколько предположений:
- Пропорции приема калорий в рамках приемов пищи (dish_serving_choice);
- Требуемая калорийность (ниже);
- Состав блюд (dish_contents);
Также есть 2 таблицы с описанием потребностей людей, их модификация требуется для изменения числа людей
Проще всего понять (если вы знаете SQL) как это все работает посмотрев на пару основных функций
getPrimitiveMenu
- Базовая функция. которая просто создает случайное меню;
- Создает случайное меню на 7 дней;
- Рандомизация по сути делается через ORDER BY random();
- Неделю мы создаем простой конструкцией unnest(ARRAY[1,2,3,4,5,6,7]);
- Остальное — тривиально;
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
- Функция, которая взвешивает меню по калориям и отдает случайное меню;
- Если не знаете про window functions — почитайте;
- По сути функция берет то, что сделала предыдущая функция + считает количество калорий подзапросом (… ) nut;
- Условно сложная формула по сути является просто школьной пропорцией;
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 ;
Если понравилось — пишите в личку.
ссылка на оригинал статьи https://habrahabr.ru/post/327254/
Добавить комментарий