Разработка мобильных приложений*, Разработка веб-сайтов*, PostgreSQL*
В контексте статьи картинка обретает двойной смысл.
В обществе в принципе много "наносного" касательно "правильной" еды, "диет", "стиля жизни" и прочего. Как правило это просто проявления относительно высокого уровня зарплат в конкретном регионе и низкого уровня грамотности людей. Мы с моей девушкой поставили для себя очень простую задачу — есть вкусно, просто, дешево, правильно и быстро.
И так получилось, что при выборе инструмента планирования такого питания взор пал на PostgreSQL, который стоит на домашнем сервере. Аналогично можно легко сделать и в Excel или Гугл-таблицах, но в нашем случае SQL оказался более быстрым + есть открытые базы с готовыми данными. Данная статья может рассматриваться вами как "заготовка" для аналогичного SQL сервиса или просто как идея, которую вы можете взять и применить для себя.
Также обратите внимание — чтобы пользоваться этим в полной мере, вы должны хотя бы чуточку уметь в табличные процессоры (Excel).
Так уж получилось, что некоторое время я жил в Москве. Москва (в моем мире), как правило, характеризуется:
Низким качеством доступного питания или высокой ценой чуть менее доступного;
Общей резиновостью продуктов из магазина (если вы были на Кипре хоть раз — вы поймете);
Отсутствием способов питаться одновременно и правильно, и дешево и вкусно не прилагая усилий (не говорю про маркетинговые сервисы с маржинальностью в 50-60%, где все равно надо готовить);
Наличием рынков, где в принципе все доступно по нормальным ценам, но в "сыром" виде;
SELECT
*
FROM
get_random_menu()
По этой причине мы решили сделать мини-сервис для себя, куда мы внесли простые блюда которые нам нравятся. Вообще мы пробовали работать с базой USDA — но она оказалась избыточно сложной. По такому сервису мы попробовали покупать еду в течение ~ 10 недель, и выяснили по опыту, что:
Вообще, может когда-нибудь руки дойдут и до такого (описание как превратить алгоритм в приложение и продукт), но пока общение с коллегами и рынок подсказывают, что "богатые" готовить не будут (скорее заплатят маржу в 50-80% сервисам), а "бедные" в России — не будут платить за приложение.
Потому, что работает, а механизма идеального измерения потребностей все равно нет (или мы его не знаем).
Функция get_random_menu() также отдает идеальное потребление калорий, белков и углеводов — вы можете сравнивать руками. Я пробовал применять алгоритмы линейной и нелинейной оптимизации в Питоне (сгенерировал 10,000 меню случайно, попробовал улучшать веса для "идеальной" подгонки, за час-два не добился результата, оставил), но скорее всего из-за нашего набора блюд там в принципе не особо возможно иметь 100% попадание — белков и углеводов в среднем меньше на 15-20% чем "идеал".
В общем заполнение структуры данных и написание функций заняло примерно 3-4 часа на таблицы и 2-3 часа на функции и хорошо выражается ER схемой:
Обратите внимание на то, что:
Также есть 2 таблицы с описанием потребностей людей, их модификация требуется для изменения числа людей
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
;
Если понравилось — пишите в личку.
К сожалению, не доступен сервер mySQL