Медиана агрегации по требованию для большого набора данных

Question

TLDR: мне нужно сделать несколько средних агрегаций в большом наборе данных для веб-приложения, но производительность низкая. Можно ли улучшить мой запрос / есть ли лучшая БД, чем AWS Redshift для этого варианта использования?

Я работаю над командным проектом, который включает в себя агрегирование по требованию большого набора данных для визуализации через наше веб-приложение. Мы используем Amazon Redshift, загруженный почти с 1 000 000 000 строк, по ключу по дате (у нас есть данные с 2014 года по сегодняшний день, с ежедневным использованием 900 000 точек данных) и ключ сортировки по уникальному идентификатору. Уникальный идентификатор может иметь отношение "один ко многим" с другими уникальными идентификаторами, для которых отношение "многие" можно рассматривать как "дети" идентификатора.

Из-за конфиденциальности, подумайте о таких структурах таблиц

TABLE NAME: meal_nutrition
DISTKEY(date),
SORTKEY(patient_id),
patient_name varchar,
calories integer,
fat integer,
carbohydrates integer,
protein integer,
cholesterol integer,
sodium integer,
calories integer

TABLE NAME: patient_hierarchy
DISTKEY(date date),
SORTKEY(patient_id integer),
parent_id integer,
child_id integer,
distance integer

Думайте об этом как о мире, для которого существует иерархия врачей. Пациенты заключены в капсулы как настоящие пациенты, так и сами врачи, для которых врачи могут быть пациентами других врачей. Врачи могут передавать право собственности на пациентов / врачей в любое время, поэтому иерархия постоянно меняется.

     DOCTOR (id: 1)
      /         \
PATIENT(id: 2) DOCTOR (id: 3)
              /        \      \
       P (id: 4)    D (id: 8) D(id: 20)
                     /  \     / \ / \ \
            ................

Одной из визуализаций, с которыми у нас возникают проблемы (из-за производительности), является график временных рядов, показывающий ежедневную медиану нескольких метрик, для которых диапазон дат по умолчанию должен составлять 1 год. Таким образом, в этом примере мы хотим получить медиану жиров, углеводов и белков из всех блюд, потребляемых пациентом / врачом и их "детьми", с учетом пациента_ид. Используемый запрос будет:

SELECT patient_name,
    date,
    max(median_fats),
    max(median_carbs),
    max(median_proteins)
FROM (SELECT mn.date date,
    ph.patient_name patient_name,
    MEDIAN(fats) over (PARTITION BY date) AS median_fats,
    MEDIAN(carbohydrates) over (PARTITION BY date) AS median_carbs,
    MEDIAN(proteins) over (PARTITION BY date) AS median_proteins
        FROM meal_nutrition mn
        JOIN patient_hierarchy ph
        ON (mn.patient_id = ph.child_id)
        WHERE ph.date = (SELECT max(date) FROM patient_hierarchy)
        AND ph.parent_id = ?
        AND date >= '2016-12-17' and date <= '2017-12-17'
)
GROUP BY date, patient_name

Самыми тяжелыми операциями в этом запросе являются сортировки для каждой медианы (каждая из которых требует сортировки ~200 000 000 строк), но мы не можем этого избежать. В результате выполнение этого запроса занимает ~30 с, что означает плохой UX. Можно ли улучшить мой запрос? Есть ли лучшая БД для этого варианта использования? Спасибо!

Answer 1

Как сказано в комментариях, сортировка / распространение ваших данных очень важно. Если вы получаете только один фрагмент данных иерархии пациентов, все данные, которые вы используете, находятся на одном узле с распределением по дате. Лучше распространять по meal_nutrition.patient_id а также patient_hierarchy.child_id поэтому данные, которые объединяются, скорее всего, находятся на одном узле, а таблицы сортируются по date,patient_id а также date,child_id соответственно, вы можете эффективно найти нужные срезы / диапазоны дат, а затем эффективно искать пациентов.

Что касается самого запроса, есть несколько вариантов, которые вы можете попробовать:

1) Приблизительная медиана, как это:

SELECT mn.date date,
ph.patient_name patient_name,
APPROXIMATE PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY fats) AS median_fats
FROM meal_nutrition mn
JOIN patient_hierarchy ph
ON (mn.patient_id = ph.child_id)
WHERE ph.date = (SELECT max(date) FROM patient_hierarchy)
AND ph.parent_id = ?
AND date >= '2016-12-17' and date <= '2017-12-17'
GROUP BY 1,2

Примечания: это может не сработать, если будет превышен стек памяти. Кроме того, у вас должна быть только одна такая функция для каждого подзапроса, чтобы вы не могли получать жиры, углеводы и белки в одном и том же подзапросе, но вы можете рассчитать их отдельно и затем присоединиться. если это работает, вы можете проверить точность, запустив оператор 30-х годов для нескольких идентификаторов и сравнив результаты.

2) Биннинг. Сначала сгруппируйте по каждому значению или установите разумные ячейки, затем найдите группу / ячейку, которая находится в середине распределения. Это будет ваша медиана. Один пример переменной будет:

WITH
groups as (
    SELECT mn.date date,
    ph.patient_name patient_name,
    fats,
    count(1)
    FROM meal_nutrition mn
    JOIN patient_hierarchy ph
    ON (mn.patient_id = ph.child_id)
    WHERE ph.date = (SELECT max(date) FROM patient_hierarchy)
    AND ph.parent_id = ?
    AND date >= '2016-12-17' and date <= '2017-12-17'
    GROUP BY 1,2,3
)
,running_groups as (
    SELECT *
    ,sum(count) over (partition by date, patient_name order by fats rows between unlimited preceding and current row) as running_total
    ,sum(count) (partition by date, patient_name) as total
    FROM groups
)
,distance_from_median as (
    SELECT *
    ,row_number() over (partition by date, patient_name order by abs(0.5-(1.0*running_total/total))) as distance_from_median
    FROM running_groups
)
SELECT
date,
patient_name,
fats
WHERE distance_from_median=1

Это, вероятно, позволило бы группировать значения на каждом отдельном узле, и последующие операции с ячейками будут более легкими и позволят избежать сортировки необработанных наборов. Опять же, вы должны сравнить. Чем меньше уникальных значений, тем выше прирост производительности, потому что у вас будет небольшое количество бинов из большого количества необработанных значений, и сортировка будет намного дешевле. Результат является точным, за исключением варианта с четным числом различных значений (для 1,2,3,4 он вернул бы 2, а не 2.5), но это можно решить, добавив еще один слой, если это критично. Главный вопрос заключается в том, значительно ли повышает эффективность сам подход.

3) Выполните расчет для каждой даты / идентификатора пациента. Если ваш единственный параметр - это терпеливый, и вы всегда рассчитываете медианы за последний год, вы можете быстро выполнить запрос в сводной таблице и запросить ее. Лучше, даже если (1) или (2) поможет оптимизировать производительность. Вы также можете скопировать сводную таблицу в экземпляр Postgres после ее материализации и использовать ее в качестве бэкэнда для своего приложения, у вас будет лучший пинг (Redshift хорош для материализации больших объемов данных, но не хорош как бэкэнд веб-приложения). Это связано с затратами на поддержание работы по передаче данных, поэтому, если материализация / оптимизация сделали достаточно хорошую работу, вы можете оставить ее в Redshift.

Мне действительно интересно получить обратную связь, если вы попробуете какой-либо из предложенных вариантов, это хороший вариант использования Redshift.