В контексте статьи картинка приобретает двойной смысл.
Отказ от ответственности
В обществе в принципе много «поверхностной» информации относительно «правильного» питания, «диеты», «образа жизни» и прочего.Как правило, это просто проявления относительно высокого уровня заработной платы в конкретном регионе и низкого уровня грамотности населения.
Мы с подругой поставили перед собой очень простую задачу – питаться вкусно, просто, дешево, полезно и быстро.
И так получилось, что при выборе инструмента для планирования такого обеда мой взгляд упал на PostgreSQL, который установлен на моем домашнем сервере.
То же самое можно легко сделать в Excel или Google Sheets, но в нашем случае SQL оказался быстрее + есть открытые базы с готовыми данными.
Эту статью вы можете рассматривать как «заготовку» для подобного SQL-сервиса или просто как идею, которую вы можете взять и применить к себе.
Также обратите внимание – чтобы в полной мере воспользоваться этим, вы должны иметь хотя бы небольшие знания процессоров электронных таблиц (Excel).
Группы людей, которым будет полезна эта статья:
- Люди, знающие SQL хотя бы на уровне простейших запросов (если вы администратор базы данных — некоторые упрощения здесь сделаны для экономии времени, а не по той причине, о которой вы думаете);
- Люди, которые хотят контролировать то, что они едят;
- Людям, которые хотят питаться вкусно, недорого, просто и полезно;
- Люди, которые хотят попытаться глубже изучить SQL-сервер или просто уверены в использовании этого инструмента;
Группы людей, которым эта статья не принесет пользы:
- Почему функция возвращает только запрос? Это должно быть в 100 раз сложнее!
- Так делают только схемы.
/я ем что угодно/почему SQL - давайте все равно использовать Mongo/кто так форматирует запросы и т.д.;
- Готовка – это не джентльменское дело!
- Нам нужно нормализовать все еще 10 строк!
Введение:
Так уж получилось, что я некоторое время жил в Москве.
Москва (в моем мире) вообще характеризуется: Низкое качество доступных продуктов питания или высокие цены на несколько менее доступные продукты питания;
Общая резиновость продуктов из магазина (если вы хоть раз были на Кипре, то поймете);
Отсутствие способов питаться полезно, дешево и вкусно одновременно, не прилагая при этом никаких усилий (я не говорю о маркетинговых сервисах с наценкой в 50-60%, где все равно придется готовить);
Наличие рынков, где в принципе все есть по нормальным ценам, но в «сыром» виде;
TLDR для нетерпеливых и продвинутых:
- Скачать для себя свалка Базы данных PostgreSQL (9.5+) по ссылке и распаковать ;
- Если вас устраивают имеющиеся пресеты, то просто введите запрос:
SELECT * FROM get_random_menu()
- Запрос выдаст вам json (если вы дочитали до этого места, то можете легко изменить его на нужный вам формат или запустить запрос с каким-нибудь драйвером) с меню на неделю (7 дней) на основе:
- 2 взрослых - мужчина и женщина (рекомендуемый_дневной_прием);
- Расчет количества еды – это просто экстраполяция через калорийность (почему – ниже);
-
- Питание 4-х разовое
-
- Завтрак из нескольких блюд;
-
- Ужин;
-
- Ужин;
-
- Перекус вечером;
- Меню выбирается случайным образом при каждом запросе;
- Если вас не устраивают пресеты или вам нужно изменить количество человек (здесь оно установлено для мужчины и женщины со средними потребностями):
- Функция get_random_menu() сообщит вам, что изменить;
- Основные таблицы, которые стоит адаптировать под себя
-
- рекомендуемый_ежедневный_прием;
-
- Все таблицы, содержащие слово блюдо;
-
- Структура немного сложная, но станет понятно, если прочитать запросы;
Результат:
По этой причине мы решили сделать для себя мини-сервис, куда включили простые блюда, которые нам нравятся.В общем, мы пробовали работать с базой данных Министерства сельского хозяйства США, но это оказалось слишком сложно.
Мы пробовали покупать еду с помощью этого сервиса в течение ~ 10 недель , и на собственном опыте узнал, что :
- В алгоритме с пропорциями были различные ошибки, которые мы исправили (15 килограммов квашеной капусты в неделю – это сильно);
- В среднем неделя на двоих обходится примерно в 4000 рублей (!) + 1,5-2 часа на шоппинг + 30-40 минут на совместную готовку ежедневно.
Месяц получается в районе 8000-10000 рублей на человека;
- Еще девушка сильно похудела (приятный бонус);
- Оба вообще перестали тратить деньги на еду.
И на обеды тоже;
- Самый удобный формат работы с базой данных — загрузить ее в таблицу с помощью любой доступной программы, составить сводную таблицу и загрузить ее на телефон;
- Проще всего отмечать приготовление блюд с телефона/компьютера в таблице;
- Простая и вкусная еда и огромная экономия времени на планировании (максимум 10 часов на разработку это позволяет прожить почти 3 месяца, не думая о еде);
такой ( описание того, как превратить алгоритм в приложение и продукт ), но пока общение с коллегами и рынок говорят о том, что "богатые" не будут готовить (они скорее будут платить наценку в 50-80% за услуги), а "бедные" в России не будут платить за заявку .
Почему «наивная» оптимизация калорий? Почему не для белков?
Потому что это работает, но механизма идеального измерения потребностей до сих пор нет (или мы его не знаем).Функция get_random_menu() также возвращает идеальное количество калорий, белков и углеводов — вы можете сравнить их вручную.
Я пробовал применять алгоритмы линейной и нелинейной оптимизации на Python (сгенерировал 10 000 меню случайным образом, пытался улучшить веса для «идеальной» подгонки, не добился результата за час-два, оставил), но, скорее всего, из-за нашего набора блюд там в принципе не так много, чтобы можно было иметь 100% попадание - белков и углеводов в среднем на 15-20% меньше, чем в "идеале".
Описание технической составляющей, базы и функций:
В целом заполнение структуры данных и написание функций заняло примерно 3-4 часа для таблиц и 2-3 часа для функций и хорошо выражено ER-диаграммой:
Обратите внимание:
- Лучше использовать PostgreSQL 9.5+, потому что… Используется несколько функций для работы с json, появившихся не так давно;
- Есть понятие блюда (блюда), есть понятие, в каких блюдах его можно принимать (dish_serving_choice, Disch_serving);
- блюдо_мену — пример журнала купленных продуктов, тогда его проще было собрать в Excel;
- Не каждое блюдо можно съесть, скажем, на завтрак есть «dish_type»;
- Блюда состоят из ингредиентов (dish_ingredient), но не содержат воды (в ней нет калорий — оптимизация не пройдет) — все пропорции рассчитаны с учетом этого факта;
- По сути, мы сделали несколько предположений:
- Пропорции потребления калорий во время еды (dish_serving_choice);
- Требуемые калории (ниже);
- Состав блюд (dish_contents);
Самый простой способ понять (если вы знаете SQL), как все это работает, — рассмотреть пару основных функций.
Получить примитивное меню - Основная функция.
который просто создает случайное меню;
- Создает случайное меню на 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
- Функция, взвешивающая меню по калориям и возвращающая случайное меню;
- Если вы не знаете об оконных функциях, прочтите;
- По сути, функция берет то, что делала предыдущая функция + вычисляет количество калорий с помощью подзапроса (.
) 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
;
Если вам понравилось, напишите мне личное сообщение.
Теги: #postgresql #здоровый образ жизни #веб-сервисы #запросы к базам данных #красиво #разработка веб-сайтов #postgresql #разработка мобильных приложений
-
Как Я Поймал Хакера
19 Oct, 24
